Blanks

[stuartkk]

Howdy,
I'm working on some reporting where each day of the month is a colunm containing forumlas. For whatever reason, the people who receive the report want days that haven't happened yet to be blank rather than contain zeros. This means I have to copy all the forumlas 1 column over every day. I am trying to work around that using the ISBLANK function to check to see if the raw data is there in the corresponding columns before displaying a result.

Anyway, this is as far as I got:
=IF(OR(ISBLANK(MAJ!F139),ISBLANK(INS!F139)),"",MAJ!F139+INS!F139)

It just checks to see if the 2 cells on the other pages are blank. If they are blank it fills in nothing. If not, it adds them together.

It works fine. However, it is messing up another formula now:
=IF(SUM(B180:AF180)=0,0,SUMPRODUCT(B180:AF180*B118:AF118)/AG118)

Further along the line the first formulas are being incorporated in SUMPRODUCT formulas, like the one above.

They are resulting in a #VALUE! result.

I am thinking that this is because the first forumla is producing a text result when it outputs the "" if the original raw data is blank.

If I remove the "" and leave the formula as:
=IF(OR(ISBLANK(MAJ!F139),ISBLANK(INS!F139)),,MAJ!F139+INS!F139)
it outputs a 0.

My question is: how can I get it to output a true blank so that the secondary sum is not thrown off by thinking that there are text fields in the middle of its sums?

Thanks!
Stu

 

[Ivan F Moala]

Why not, in the 1st instance set the sheets
option to NOT display zero values ie.
Tools > Options > View then
deselect> Display zeros.

 

[Brian from Maui]

Stu,

Try this

=IF(ISNUMBER(MAJ!F139),MAJ!F139+INS!F139,"")

 

[stuartkk]

Ivan -
Its a good idea, but if the two cells its checking are zeros and not blanks it would output a blank when it should output a zero.

I mean, it would put blanks in the middle of valid columns. We frequently would have occurences where the valid sum of the two cells is zero.

Thanks though!

 

[stuartkk]

Brian -
Isn't that going to still be outputing a text blank if the cell is not a number? I think that forumla would have the same problem.
Thanks tho!

 

[stuartkk]

This message was edited by stuartkk on 2002-09-06 12:23

 

[Aladin Akyurek]

On 2002-09-06 11:51, stuartkk wrote:
Howdy,
I'm working on some reporting where each day of the month is a colunm containing forumlas. For whatever reason, the people who receive the report want days that haven't happened yet to be blank rather than contain zeros. This means I have to copy all the forumlas 1 column over every day. I am trying to work around that using the ISBLANK function to check to see if the raw data is there in the corresponding columns before displaying a result.

Anyway, this is as far as I got:
=IF(OR(ISBLANK(MAJ!F139),ISBLANK(INS!F139)),"",MAJ!F139+INS!F139)

It just checks to see if the 2 cells on the other pages are blank. If they are blank it fills in nothing. If not, it adds them together.

It works fine. However, it is messing up another formula now:
=IF(SUM(B180:AF180)=0,0,SUMPRODUCT(B180:AF180*B118:AF118)/AG118)

Further along the line the first formulas are being incorporated in SUMPRODUCT formulas, like the one above.

They are resulting in a #VALUE! result.

I am thinking that this is because the first forumla is producing a text result when it outputs the "" if the original raw data is blank.

If I remove the "" and leave the formula as:
=IF(OR(ISBLANK(MAJ!F139),ISBLANK(INS!F139)),,MAJ!F139+INS!F139)
it outputs a 0.

My question is: how can I get it to output a true blank so that the secondary sum is not thrown off by thinking that there are text fields in the middle of its sums?

Thanks!
Stu

See whether this works:

=IF(AG118,SUMPRODUCT(B180:AF180,B118:AF118)/AG118,0)

And, change:

=IF(OR(ISBLANK(MAJ!F139),ISBLANK(INS!F139)),"",MAJ!F139+INS!F139)

to:

=IF(COUNT(MAJ:INS!F139) < 2 ,"",SUM(MAJ:INS!F139))

 

[stuartkk]

I'm sorry, I guess I'm not being clear. The problem ultimately lies with SUMPRODUCT.

To test this, type in a column of numbers, put in one row of text and use regular SUM. It will total the numbers and ignore the text.
Type in 2 columns of nubers, and 1 text item and SUMPRODUCT them all. You will get a #VALUE! result, not zero.

If there is any text in a range for SUMPRODUCT it will error.

The output of a formula like:
=IF(COUNT(MAJ:INS!F139) < 2 ,"",SUM(MAJ:INS!F139))
if the condition is true would be "". Excel appears to be treating this like text. It is causing the SUMPRODUCT to produce a #VALUE! result.

I guess I would be looking for an alternative to "" as output.

 

[Aladin Akyurek]

On 2002-09-06 12:50, stuartkk wrote:
I'm sorry, I guess I'm not being clear. The problem ultimately lies with SUMPRODUCT.

To test this, type in a column of numbers, put in one row of text and use regular SUM. It will total the numbers and ignore the text.
Type in 2 columns of nubers, and 1 text item and SUMPRODUCT them all. You will get a #VALUE! result, not zero.

If there is any text in a range for SUMPRODUCT it will error.

The output of a formula like:
=IF(COUNT(MAJ:INS!F139) < 2 ,"",SUM(MAJ:INS!F139))
if the condition is true would be "". Excel appears to be treating this like text. It is causing the SUMPRODUCT to produce a #VALUE! result.

I guess I would be looking for an alternative to "" as output.

I'm using a COMMA, not a * in the SUMPRODUCT bit. So try it.

 

[stuartkk]

Ah ha! I missed that subtle little difference. That did the job! Thanks much!