Need help with a #Value! Error - (want cell to show as BLANK when no data)

[Sharon]

I just need a little help with the following formula(s) from someone smarter than me.

I am trying to show a BLANK cell in THREE cells. I have accomplished my task in the 1st two cells, but cannot get the 3rd one (which should be a percentage) to show a BLANK cell. It shoes a #Value! until there is data. I want all three cells to be blank unless there is data.

I have copied my formulas below. The 1st two formulas do result in a BLANK Cell, which is what I want. The 3rd one does not, which is bad. BUT, maybe the 1st two formulas that are working are actually creating the problem for the 3rd formula. So here goes:

In Cell B5 I have: =IF((E5+G5+I5+K5+M5+O5+Q5+S5+U5+W5+Y5=0),"",E5+G5+I5+K5+M5+O5+Q5+S5+U5+W5+Y5)

In Cell B6 I have: =IF((F5+H5+J5+L5+N5+P5+R5+T5+V5+X5+Z5=0),"",F5+H5+J5+L5+N5+P5+R5+T5+V5+X5+Z5)

BUT, the cell that I want blank and is NOT blank and gives me a #VALUE! ERROR is Cell B7. This is my formula for that cell:
=IF((C5/B5=0),"",C5/B5)

Can anyone guide me in what I am doing wrong? I want Cell B7 Blank too.

THANKS for any help!!!

[Claus Busch]

Hi Sharon,

Am Thu, 11 Oct 2012 10:31:16 -0700 (PDT) schrieb Sharon:

> In Cell B5 I have: =IF((E5+G5+I5+K5+M5+O5+Q5+S5+U5+W5+Y5=0),"",E5+G5+I5+K5+M5+O5+Q5+S5+U5+W5+Y5)
>
> In Cell B6 I have: =IF((F5+H5+J5+L5+N5+P5+R5+T5+V5+X5+Z5=0),"",F5+H5+J5+L5+N5+P5+R5+T5+V5+X5+Z5)
>
> BUT, the cell that I want blank and is NOT blank and gives me a #VALUE! ERROR is Cell B7. This is my formula for that cell:
> =IF((C5/B5=0),"",C5/B5)

"" is an empty string and text. You can't divide a number with text.
Try in B5:
=SUMPRODUCT((MOD(COLUMN(E5:Z5),2)=1)*E5:Z5)
in B6:
=SUMPRODUCT((MOD(COLUMN(F5:Z5),2)=0)*F5:Z5)
and in B7:
=IF(B5=0,"",C5/B5)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[Claus Busch]

Hi Sharon,

Am Thu, 11 Oct 2012 20:08:43 +0200 schrieb Claus Busch:

> Try in B5:
> =SUMPRODUCT((MOD(COLUMN(E5:Z5),2)=1)*E5:Z5)
> in B6:
> =SUMPRODUCT((MOD(COLUMN(F5:Z5),2)=0)*F5:Z5)
> and in B7:
> =IF(B5=0,"",C5/B5)

if it is important for you to leave the cells blank, then try in B5:
=IF(SUMPRODUCT((MOD(COLUMN(E5:Z5),2)=1)*E5:Z5)=0,"",SUMPRODUCT((MOD(COLUMN(E5:Z5),2)=1)*E5:Z5))
in B6:
=IF(SUMPRODUCT((MOD(COLUMN(F5:Z5),2)=0)*F5:Z5)=0,"",SUMPRODUCT((MOD(COLUMN(F5:Z5),2)=0)*F5:Z5))
in B7 for version xl2007 or later:
=IFERROR(C5/B5,"")
else
=IF(LEN(B5)=0,"",C5/B5)

[Sharon]

Hi Claus,

Thank you SO MUCH! You are a genius. I would NEVER have figured out thsoe formulas. I worked all morning on this, finally deciding to ask for help.

Both versions you suggested work well. I am not sure which one I like the best. The second version is what I had envisioned, but the first version actually looks very nice in my data spreadsheet.

I am saving these directions for future reference! Maybe I can even learn what some of the terms mean! LOL

(new terms to me are MOD and SUMPRODUCT and IFERROR)

Thank you again for your help!!