# Blanks

#### stuartkk

##### New Member
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

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
Why not, in the 1st instance set the sheets
option to NOT display zero values ie.
Tools > Options > View then
deselect> Display zeros.

Stu,

Try this

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

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!

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!

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

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))

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.

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.

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

Replies
4
Views
856
Replies
17
Views
702
Replies
3
Views
477
Replies
1
Views
291
Replies
16
Views
439

1,218,808
Messages
6,144,598
Members
450,555
Latest member
sheldor

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back