That old #VALUE! problem

[Nick Wakeham]

I have made up a spreadsheet that has lots of cells with IF formulas but I
want the result of those cells to add up as a total. However, no matter how
I try, the total cell keeps coming out with the dreaded #VALUE!.

All the IF formulas are fine and work well but I need them all to add up but
show nothing in the cell unless there is a value worked out in the original
IF cells.

Hope that's clear and that someone can help.

I am using OFFICE2000.

Nick

[sybmathics]

What exactly is your formula?
If you don't show us the formula that creates the problem we can't identify
the problem,

Anyone in tis group is willing to help.

Greets,

Sybolt

[Zack Barresse]

Hi Nick,

Have you tried the formula auditor to find the source of the error?
Generally that error is from trying to sum text.

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Nick Wakeham" <nick@dr5.co.uk> wrote in message
news:443d1feb$0$10741$9a6e19ea@news.newshosting.com...
>I have made up a spreadsheet that has lots of cells with IF formulas but I
>want the result of those cells to add up as a total. However, no matter how
>I try, the total cell keeps coming out with the dreaded #VALUE!.
>
> All the IF formulas are fine and work well but I need them all to add up
> but show nothing in the cell unless there is a value worked out in the
> original IF cells.
>
> Hope that's clear and that someone can help.
>
> I am using OFFICE2000.
>
> Nick
>

[Pete_UK]

I suspect you have something like this in your IF statements:

=IF(condition,"1","0")

Although this will look as if you are returning numbers to the cells,
they are in fact text values. If you then try to carry out some
arithmetic on them the arithmetic function will return #VALUE.

Hope this helps.

Pete

[Nick Wakeham]

Thank you - that is a huge help and has explained a lot!

Many thanks

Nick

"Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
news:O2f2IwkXGHA.3740@TK2MSFTNGP03.phx.gbl...
> The value error derives from calculating text so in your if formulas you
> might have included either "" or "5" as an example. Also when totaling if
> you use SUM(A1,A3 etc) or =SUM(B1:B10) they will ignore text and only sum
> the numerical values. OTOH =A1+A3 as an example will cause an error if a
> cell included in the calculation is text
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> http://nwexcelsolutions.com
>
>
> "Nick Wakeham" <nick@dr5.co.uk> wrote in message
> news:443d1fc4$0$1214$9a6e19ea@news.newshosting.com...
>>I have made up a spreadsheet that has lots of cells with IF formulas but I
>>want the result of those cells to add up as a total. However, no matter
>>how I try, the total cell keeps coming out with the dreaded #VALUE!.
>>
>> All the IF formulas are fine and work well but I need them all to add up
>> but show nothing in the cell unless there is a value worked out in the
>> original IF cells.
>>
>> Hope that's clear and that someone can help.
>>
>> I am using OFFICE2000.
>>
>> Nick
>>
>>
>
>


"Pete_UK" <pashurst@auditel.net> wrote in message
news:1144869005.248775.220750@i39g2000cwa.googlegroups.com...
>I suspect you have something like this in your IF statements:
>
> =IF(condition,"1","0")
>
> Although this will look as if you are returning numbers to the cells,
> they are in fact text values. If you then try to carry out some
> arithmetic on them the arithmetic function will return #VALUE.
>
> Hope this helps.
>
> Pete
>