Error message - #VALUE!

[Kazza]

Hi
If I use this formula =O14+L14+I14+F14 I get an error message.
If I try this =IF(ISERROR(O14+L14+I14+F14),"",O14+L14+I14+F14) nothing
shows even though cell O14 has the number (45).
Any help appreciated.

Karen

 

[Jeff Standen]

My guess would be that one or more of the cells you are adding is text.

Jeff

 

[Pete_UK]

If you use:

=SUM(O14,L14,14,F14)

you should get the right result - if, as Jeff suggests, you have a text
value which just looks like a number, then this will not be added but
you will avoid the error message.

Hope this helps.

Pete

 

[Kazza]

Thanks guys.
I omitted to mention that to suppress the error messages within the
originating cells I used =IF(OR(N14<>"",M14<>""),N14-M14,"").
Apologies.
Do I need to change these originators or rather paste the values and
then use =SUM(O14,L14,14,F14)?



Karen

 

[Pete_UK]

I think you need AND in your IF statement, not OR, so that only if both
N14 and M14 are not blanks do you subtract one from the other.

You should be able to use the SUM formula without having to paste the
values. By the way, I mis-typed it slightly - the third parameter
should be I14.

Hope this helps.

Pete

 

[Kazza]

Hi Pete
Thanks for the quick reply.
Using "and" results in a blank cell if one of them has an entry.
Appreciate your help.

Karen

 

[Pete_UK]

Karen,

two of my earlier responses have got lost, so I'll try yet again:

If M14 and N14 can contain only numbers that you type in or (true)
blanks, then the simple subtraction = N14 - M14 would work. I suspect,
though, that M14 and N14 contain formulae which can return "" in
certain circumstances. These are not true blanks, so the subtraction
will return #VALUE if either of them are blank. By suggesting you
change the OR to AND in your formula, this would overcome this - the
subtraction is only carried out if both M14 and N14 contain numbers. Is
this not what you want?

If you want to treat "" as being equivalent to zero, so that you want
the result N14 if M14 contains "" or a result of -M14 if N14 contains
"", then the following will work:

=IF(AND(N14<>"",M14<>""),N14-M14,IF(N14<>"",N14,IF(M14<>"",-M14,"")))

which simplifies to:

=IF(AND(--N14,--M14),N14-M14,IF(N14,N14,IF(M14,-M14,"")))

A different approach, though, would be to return 0 instead of "" in the
formulae in M14 and N14, so that the subtraction will work as will your
original addition formula. If you do not want to see a zero, but would
prefer a blank cell, then you could apply conditional formatting to the
cells to turn the font colour to white if the cell contents are 0 -
white on white appears blank, even though the cell actually contains a
number.

In either approach, the SUM formula I gave you will work instead of
your original addition formula.

Hope this helps, and hope it finally gets through to you.

Pete

 

[Kazza]

Pete
will have to leave this till next week but couldn't go without saying
thanks for your trouble.


Karen

 

[Pete_UK]

That's okay, Karen - let's hope you can get it working next week.

Pete