#Value! error message on blank cells

Question

I have a formula that takes two cells and multiplies them then adds this product to two more cells that are multiplied, etc etc etc about 27 times. (i.e. A1*B1+A3*B3+A5*B5...) The cells are all number formatted, but these cells contain formulas that would return a blank ("") if certain conditions are not met.  Since some of the cells are blank, excell returns the #Value! error.  I wanted to keep the blanks, rather than 0s for esthetic reasons, since this is a large spreadsheet. Might there be a way to do this?

Answer 1

I have a formula that takes two cells and multiplies them then adds this product to two more cells that are multiplied, etc etc etc about 27 times. (i.e. A1*B1+A3*B3+A5*B5...) The cells are all number formatted, but these cells contain formulas that would return a blank ("") if certain conditions are not met.  Since some of the cells are blank, excell returns the #Value! error.  I wanted to keep the blanks, rather than 0s for esthetic reasons, since this is a large spreadsheet. Might there be a way to do this?

Try this...

=SUMPRODUCT(--(MOD(ROW(A1:A5)-ROW(A1),2)=0),--(ISNUMBER(A1:A5)),A1:A5,--(ISNUMBER(B1:B5)),B1:B5)

--

Biff

Microsoft Excel MVP

Actually, we don't need the ISNUMBER tests:

=SUMPRODUCT(--(MOD(ROW(A1:A5)-ROW(A1),2)=0),A1:A5,B1:B5)

--

Biff

Microsoft Excel MVP

Answer 2

Actually, we don't need the ISNUMBER tests:

=SUMPRODUCT(--(MOD(ROW(A1:A5)-ROW(A1),2)=0),A1:A5,B1:B5)

You don't really need the subtraction either...

=SUMPRODUCT(--(MOD(ROW(A1:A5),2)=1),A1:A5,B1:B5)

The row subtraction is to make the formula more robust against row insertions.

Try that version and then insert a new row 1. The MOD test now fails in relation to the data range.

It's simple and doesn't add any undue complexity or inefficiency to the formula so I always include it.

If you know for certain that you will never insert new rows before the range then you can eliminate that test.

--

Biff

Microsoft Excel MVP

Answer 3

Actually, we don't need the ISNUMBER tests:

=SUMPRODUCT(--(MOD(ROW(A1:A5)-ROW(A1),2)=0),A1:A5,B1:B5)

You don't really need the subtraction either...

=SUMPRODUCT(--(MOD(ROW(A1:A5),2)=1),A1:A5,B1:B5)

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

Answer 4

Sumproduct treats blanks and zero length strings returned by formulas as 0, already, so there's no need for the ISNUMBER functions.

Answer 5

I have a formula that takes two cells and multiplies them then adds this product to two more cells that are multiplied, etc etc etc about 27 times. (i.e. A1*B1+A3*B3+A5*B5...) The cells are all number formatted, but these cells contain formulas that would return a blank ("") if certain conditions are not met.  Since some of the cells are blank, excell returns the #Value! error.  I wanted to keep the blanks, rather than 0s for esthetic reasons, since this is a large spreadsheet. Might there be a way to do this?

Try this...

=SUMPRODUCT(--(MOD(ROW(A1:A5)-ROW(A1),2)=0),--(ISNUMBER(A1:A5)),A1:A5,--(ISNUMBER(B1:B5)),B1:B5)

--

Biff

Microsoft Excel MVP