How to make a formula produce a result (and not the "#Value" Error)

[Mike C]

Hello - I am trying to build a formula that shows (A1*B1) + (C1*D1).
The problem is that when A1 is empty, the result produces as #Value
Error, even when C1 and D1 have values.

Is there a best way to address this problem? I need the results for
C1 * D1 to show up, even when A1 is blank.

For clarity, the formula I am using is: =AG9*Rates!$B$2+'All current'!
AB9*Rates!$B$3

Thanks for any suggestions

 

[Pete_UK]

You could use this approach:

=IF(ISERROR(AG9*Rates!$B$2),IF(ISERROR('All current'!AB9*Rates!$B
$3),"",'All current'!AB9*Rates!$B$3),AG9*Rates!$B$2+'All current'!
AB9*Rates!$B$3)

Hope this helps.

Pete

 

[PCLIVE]

It shouldn't produce an error if A1 is actually empty. It should treat it
as zero. But if there is a space or other character(s) in A1 or B1, then
A1*B1 will certainly give you that error.

 

[Niek Otten]

<when A1 is empty, the result produces as #Value>

No, not when it's empty. Probably there is a space in the cell.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello - I am trying to build a formula that shows (A1*B1) + (C1*D1).
| The problem is that when A1 is empty, the result produces as #Value
| Error, even when C1 and D1 have values.
|
| Is there a best way to address this problem? I need the results for
| C1 * D1 to show up, even when A1 is blank.
|
| For clarity, the formula I am using is: =AG9*Rates!$B$2+'All current'!
| AB9*Rates!$B$3
|
| Thanks for any suggestions

 

[Rick Rothstein \(MVP - VB\)]

Normally, an empty cell will be treated as a zero inside a calculation and
you would get a #VALUE! error if the cell contained text. Do you have a
formula in A1 that is outputting "" as a result? If so, you could have it
output 0 and that should fix the problem. Otherwise you could use this...

IF(A1="",0,A1)

instead of just A1 in your calculation. So, your formula would look like
this...

=IF(A1="",0,A1)*B1 + C1*D1

Of course, you could change B1, C1 and D1 in the same way if they are
outputting "" as the result of a formula.

Rick