#value! Error

**yeeran** · 02-20-2010, 05:20 AM

The formula below returns the error #VALUE!

The formula is on a separate worksheet, and it is adding up the value of different cells on different worksheets, in the same excel file.

=ROUND(IF(C283=0,0,IF(ABS(C283)<=Purchase!C283,C283*Purchase!D283,IF(ABS(C283)-Purchase!C283<=Beginning!F283,Purchase!E283+(C283-Purchase!C283)*Beginning!G283,"form T/R"))),2)

The cells it is refering to all have formulas in them that relate to other criteria.

If there are values in all the cells the formula will work.

However if one of the reference cells is empty or null value, the error #Value!, seems to occur.

If there is a value of '0' the formula works fine.

How can I correct this?

**DonkeyOte** · 02-20-2010, 05:41 AM

Formula Nulls are in effect 0 length text strings and thus can not be coerced to number (as being conducted above by means of arithmetic operators).

Without changing precedents in your formula you need to validate numerics before conducting any arithmetic operation (use ISNUMBER or COUNT test as appropriate)

**yeeran** · 02-20-2010, 11:14 PM

Thanks DonkeyOte

But am not sure I understand, I am not so advanced the formula was created by my account who is now on maternity leave and can't be reached for help. So am a bit stuck and hoping that somebody would be able to hlep fix it.

But really appreciate it though, I will try looking through the suggested links

**John1052** · 02-21-2010, 02:20 AM

I expect one or more of the formula have a if statement within it it that returns "" if a certain condition is not met. Replace the "" with 0. If you do not want the zeros to show go to options and untick show zero values

**DonkeyOte** · 02-21-2010, 04:27 AM

John1052 has provided one route - ie change the direct precedents such that instead of returning a Null ("") they instead return 0 - this may or may not be viable we don't know.

The direct precedents of your formula are:

C283
Purchase!C283:E283
Beginning!F283:G283

Without knowing what is contained in the above giving the most efficient solution is not really possible at this stage.

Looking at the existing formula:

=ROUND(IF(C283=0,0,IF(ABS(C283)<=Purchase!C283,C283*Purchase!D283,IF(ABS(C283)-Purchase!C283<=Beginning!F283,Purchase!E283+(C283-Purchase!C283)*Beginning!G283,"form T/R"))),2)

You could leave the explicit precedents as they are and try modifying the above to:

=ROUND(IF(N(C283)=0,0,IF(ABS(C283)<=N(Purchase!C283),C283*N(Purchase!D283),IF((ABS(C283)-N(Purchase!C283))<=N(Beginning!F283),N(Purchase!E283)+(C283-N(Purchase!C283))*N(Beginning!G283),"form T/R"))),2)

Let us know.