error in excel with formula

Question

Hi everyone,

I have fixed a lot so far in my limited knowledge of excel but still have one more question. I have a Formula issue.  I am getting #value! in my Total Column which adds 4 columns BECAUSE one of the columns is the Tax calculation which has a formula in the cell so the Total column sees it as the formula and causes the error. IF I just stick a "0" in the tax rate col so the Tax column calculates the total is OK but not if I leave the rate empty.

My total calculation formula is =SUM(P4+Q4+S4+T4). The S col has =IF(R4="","",SUM((P4+Q4)*R4)).  CONFUSING????

Any thoughts. I am so close to finishing this!!

Answer 1

Hi,  That was a great answer and it WORKED!.  Since you know this...let me ask you two more.

The total U col is =SUM((P2+Q2),(R2:S2),T2) and it seems to work. But as you can see if I haven't  entered any data in M I get the error message.  What would be the formula to just show nothing if I haven't populated any data yet?  I am guessing that it has something to do with the "","" expression???

Secondly, and this one may be UNSOLVABLE...

If I copy the formulas down the columns, (just the formulas like in rows 10 and 11) when I open the FORM to enter a new customer record, it shows every row that I  have a formula in as a record. Is there any way around this??  Maurie

Answer 2

I am getting #value! [....] BECAUSE [....] the Total column sees it as the formula and causes the error. IF I just stick a "0" in the tax rate col so the Tax column calculates the total is OK [....]. My total calculation formula is =SUM(P4+Q4+S4+T4).

The S col has =IF(R4="","",SUM((P4+Q4)*R4)).

No, it is not because S4 has a formula.  It is because the value of S4 is the null string (sometimes), not a numeric value.

And presumably, you "just stuck" a 0 (zero), not "0" (a string) as a work-around.  Do not put quotes around numbers that should be treated as such.

Finally, there is no point in wrapping SUM around arithmetic expressions.  Just write =P4+Q4+S4+T4 and

=IF(R4="","",(P4+Q4)*R4) .

But ironically, the correct use of SUM in your first formula would avoid the #VALUE error, to wit:  =SUM(P4,Q4,S4,T4).

Note the comma separating parameters, not a plus ("+") separating parts of an expression.

Answer 3

Hey Bill,

Thank you I think!  Are you saying that my excel question was already moved to the correct place? If yes then thanks for the info and if not then I will repost to the correct topic.  Maurie

Answer 4

There is no need to post a duplicate thread.

Moderators move them to the correct forums.

And there is a thread specifically set up in the Community Participation Center for regular Members to request a thread move.

Answer 5

The best place for Office/Excel questions is in their own community where their helpful experts hand out (this is the Windows 7 Community).

At the top of the page here click Community, then click Office and make it look like this, then ask your question: