Dell / INSPIRION
March 24, 2009 at 09:07:56
Specs: Windows XP, 1.0 Gb
 Using Excel, I have two columns of numbers; the first contains the dollar value and the second contains the dollar value.When I populate the cells for L12, L17,L22,and L27 (dollar values) and N12, N17, N22, and N27 (cents values) by linking this data to another spreadsheet, the two formulas below work correctly:Total Dollar Value:=(L12+L17+L22+L27)+IF(LEN(N12+N17+N22+N27)>2,LEFT(N12+N17+N22+N27,LEN(N12+N17+N22+N27)-2)*1,0)Total Cents Value:=RIGHT((N12+N17+N22+N27),2)*1The problem I now have is that all four groups of cells are not always used as this is a payroll and all four employees might not work in any given pay period. When I do not enter data into a given "L" cell and its corresponding "N", cell, I get "#value" in both the total dollar cell and the total cents cell.Thank you.

#1
March 24, 2009 at 14:10:36
 OMG...He's back!

Report •

#2
March 24, 2009 at 14:15:21
 Just kidding :)OK, based on what you said, shouldn't I get a #VALUE error if I don't enter any data into any of the cells?I dropped your Total Dollar value formula into a blank worksheet and got 0.

Report •

#3
March 25, 2009 at 07:51:56
 Thank you for your response; however, I can't get it to work. When I put "0" in the dollar column and "00" in the cents column for a given employee, and have additional dollar and cents values for the other employees, both the total dollar and total cents columns work correctly. There are four employees of which three work on a regular basis.If I do not include a given employee's data because that employee didn't work during a given pay period and I can not have a row with just zeros in it, I get "#value!" in both the total dollar and the total cents column.The pay period dollar and cents are on a payroll spreadsheet which is linked to a tally page that is linked to the employees' worksheets. When an employee's name is typed into the payroll spreadsheet, the data is populated via the tally page. The tally page contains all the formulas that are needed for the payroll spreadsheet. At this time, the payroll spreadsheet should total the dollar and cents columns but that isn't happening correctly.Thank you.

Report •

Related Solutions

#4
March 25, 2009 at 20:07:17
 Unfortunately there is no way for us to replicate your linked sheets and thereby duplicate your problem.If you'd be willing to email the workbook with generic data, I could take a look at it.

Report •

#5
March 26, 2009 at 10:34:21
 DerbyDad03,I could email the workbook with generic data. To what email address should I send it to?Thank you.

Report •

#6
March 26, 2009 at 10:43:28
 I've PM'd an email address. I won't be checking that address until after the close of business, EST.

Report •

#7
March 31, 2009 at 11:08:45
 DerbyDad03,Thank you for your assistance in helping me resolve some difficult problems I was having constructing Excel formulas.Brian W

Report •

#8
March 31, 2009 at 11:29:23
 Brian,You're very welcome. I'm not sure we resolved all of the issues, but I took your spreadsheets as far as I could.Sometimes the biggest obstacle to a solution is the fact that the owner knows exactly what they are trying to do and how to use the spreadsheet. Passing this knowledge along to the "helper" via e-mail and/or an on-line forum can be difficult and sometimes frustrating. There's no blame being placed here, it's just a fact of our electronic age. If you are still having problems, post the individual questions here and maybe some other folks can come up with solutions.

Report •