Empty cells

[zapppsr]

Good Afternoon:

I need a cell return empty when a condition is matched:

Eg.: IF(A1=0;"";B1/A1)

If A1 is zero then do not divide B1 for A1, give me an empty cell, instead of div/0.

The problem is that the "" to define an empty cell doesnot work in some calculations, such as SUMPRODUCT, wich is the function I need.

I need a replacement for "" that has the same result as a regular empty cell.

I think when we use "", Excel 2007 thinks it is a text, compromising some calculations.

Any hint?

Sorry if the syntax is kinda different, because my Excel is in Portuguese, and I know ; sometimes will be , in English.

 

[T. Valko]

Good Afternoon:

I need a cell return empty when a condition is matched:

Eg.: IF(A1=0;"";B1/A1)

If A1 is zero then do not divide B1 for A1, give me an empty cell, instead of div/0.

The problem is that the "" to define an empty cell doesnot work in some calculations, such as SUMPRODUCT, wich is the function I need.

I need a replacement for "" that has the same result as a regular empty cell.

I think when we use "", Excel 2007 thinks it is a text, compromising some calculations.

Any hint?

Sorry if the syntax is kinda different, because my Excel is in Portuguese, and I know ; sometimes will be , in English.

You are correct in that the formula blank ("") is an empty TEXT string.

This is no alternative to the formula blank that will leave a cell empty. If the cell contains a formula then it is not an empty cell it's just that the formula blank makes it appear to be an empty cell.

The only thing you can do is to modify any downstream calculations to account for the formula blanks.

This is normally not too difficult.

Post your SUMPRODUCT formula so we can see how it needs to be modified to account for the formula blanks.

 

[grizz]

this may work for you
=IF(OR(A1=0,B1=0),"",A1/B1)

 

[zapppsr]

=SUMPRODUCT((AN_Mod=$B5)*(AN_Ano=C$4)*(INDIRECT($B$3)))

AN_Mod - First Criteria
AN_Ano - Second Criteria
INDIRECT ($B$3) - Range do Sum

Note: The formula works perfectly when INDIRECT ($B$3) has no "" (empty) cells:

Ex.: 100, 100, 100

But give me #VALUE when it is 100, 100, "", 100

I tried another criteria before the last range (INDIRECT($B$3)<>"") but it didnt work.

P.S: grizz, I cant have the ""

T. Valko: Tks for the quick answer, as always!!

 

[T. Valko]

=SUMPRODUCT((AN_Mod=$B5)*(AN_Ano=C$4)*(INDIRECT($B$3)))

AN_Mod - First Criteria
AN_Ano - Second Criteria
INDIRECT ($B$3) - Range do Sum

Note: The formula works perfectly when INDIRECT ($B$3) has no "" (empty) cells:

Ex.: 100, 100, 100

But give me #VALUE when it is 100, 100, "", 100

I tried another criteria before the last range (INDIRECT($B$3)<>"") but it didnt work.

P.S: grizz, I cant have the ""

T. Valko: Tks for the quick answer, as always!!

Are the ranges 1 dimensional arrays (single rows or single columns)?

Maybe this...

=SUMPRODUCT(--(AN_Mod=$B5),--(AN_Ano=C$4),INDIRECT($B$3))

When using this syntax text in the sum range is ignored.

 

[engineer69]

Try getting the SUM, then dividing by the number of occurances greater than Zero. Any cell that is empty or Zero will not be used.

=IF(ISERROR(SUM(C7:N7)/(COUNTIF(C7:N7,">0"))),0,SUM(C7:N7)/
(COUNTIF(C7:N7,">0")))

 

[zapppsr]

Thats why I love this Board!!!!

You guys rule!!

Thanks T. Valko, your MrExcel MVP tittle is well deserved.

I didnt know that sysntax for SUMPRODUCT

Thnaks you very much!!! It works!!!

 

[T. Valko]

Thats why I love this Board!!!!

You guys rule!!

Thanks T. Valko, your MrExcel MVP tittle is well deserved.

I didnt know that sysntax for SUMPRODUCT

Thnaks you very much!!! It works!!!

You're welcome. Thanks for the feedback!

See this for more on SUMPRODUCT:

http://xldynamic.com/source/xld.SUMPRODUCT.html

 

[zapppsr]

Tks. I will read it right now!

 

[T. Valko]

Tks. I will read it right now!

There's a lot to read!

Good luck!