I'm getting the #VALUE! error

[Michael of Hanson]

I have a spreadsheet to work out Auto Mileage used for both work and personal miles. In the total column (J), I have the formula: =IF(AND(ISBLANK(G6),ISBLANK(H6)),â€â€,J5+G6+H6) to add the previous total in J5 to the personal miles in G6 to the business miles in H6. However, I think this is creating a problem because I have the following formula in G6 & H6 (the personal and business miles columns): =IF(ISBLANK(B5),â€â€,IF(B6=â€xâ€,F5-E5)) to enter a total from E5 minus F6 (the miles from the beginning of the trip minus the miles at the end of the trip). The problem is that I am getting the #VALUE! error in the total (J) column. Is this because I have the IF formula in the columns the total column is referring. And if so how can I fix it?

 

[Max]

Instead of

: =IF(AND(ISBLANK(G6),ISBLANK(H6)),"",J5+G6+H6)

try

: =IF(AND(G6="",H6=""),"",SUM(J5,G6,H6))


Here's why:


Using ... AND(G6="",H6="")....
----------------------------------------
As you have a formula in G6 and H6,

the ...AND(ISBLANK(G6),ISBLANK(H6))... part

will never evaluate to TRUE

(since G6 & H6 will always contain something, i.e. formulas)


Using ... SUM(J5,G6,H6).....
--------------------------------------
The use of SUM(J5,G6,H6) instead of J5+G6+H6

is because SUM() ignores text

(so you won't get the errors should there be text values
returned in either J6, G6 or H6)

--
hth
Max
-----------------------------------------
Please reply in thread

Use xdemechanik <at>yahoo<dot>com for email
----------------------------------------------------------------------------

I have a spreadsheet to work out Auto Mileage used for both work and

personal miles. In the total column (J), I have the formula:
=IF(AND(ISBLANK(G6),ISBLANK(H6)),"",J5+G6+H6) to add the previous total in
J5 to the personal miles in G6 to the business miles in H6. However, I think
this is creating a problem because I have the following formula in G6 & H6
(the personal and business miles columns): =IF(ISBLANK(B5),"",IF(B6="x"
,F5-E5)) to enter a total from E5 minus F6 (the miles from the beginning of
the trip minus the miles at the end of the trip). The problem is that I am
getting the #VALUE! error in the total (J) column. Is this because I have
the IF formula in the columns the total column is referring. And if so how
can I fix it?