Blanks

stuartkk

New Member
Joined
Sep 5, 2002
Messages
6
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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
Why not, in the 1st instance set the sheets
option to NOT display zero values ie.
Tools > Options > View then
deselect> Display zeros.
 

stuartkk

New Member
Joined
Sep 5, 2002
Messages
6
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

New Member
Joined
Sep 5, 2002
Messages
6

ADVERTISEMENT

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!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203

ADVERTISEMENT

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

New Member
Joined
Sep 5, 2002
Messages
6
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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

New Member
Joined
Sep 5, 2002
Messages
6
Ah ha! I missed that subtle little difference. That did the job! Thanks much!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,271
Members
416,963
Latest member
samfuge

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top