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