• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

inconsistent result with identical formula

Kimber

Kimber

Member
I have three workbooks with identical template sheets and formulas. I don't want the quarter totals to show until the quarter is complete, so the formula I have had success with is =IF(D14>0,SUM(D12:D14),"").

The three periods are in rows 12, 13, and 14. Two of the three matching worksheets are working as planned. However, the third is showing the total row with only the first period of the second quarter entered. The data for the period cells is a link from another sheet in the workbook - which is linked to another workbook. The reference cell is empty - there is nothing entered. I am stumped.

I have attached two of these workbooks; I'm asking about the second tab (MFG vs PLAN.) Sample A is the one that is working and Sample B is the problem file. Also, I have used different formulas in (for example) cell D14 in both in order to eliminate the "0" in Sample B.

Thanks to anyone who can shed some light on this for me. I realize there are inconsistencies on other pages as well. :(

kimber
 

Attachments

  • 20171110 Sample A.xlsx
    58.7 KB · Views: 3
  • 20171110 Sample B.xlsx
    57.5 KB · Views: 3
Kimber said:
I have three workbooks with identical template sheets and formulas. I don't want the quarter totals to show until the quarter is complete, so the formula I have had success with is =IF(D14>0,SUM(D12:D14),"").

The three periods are in rows 12, 13, and 14. Two of the three matching worksheets are working as planned. However, the third is showing the total row with only the first period of the second quarter entered. The data for the period cells is a link from another sheet in the workbook - which is linked to another workbook. The reference cell is empty - there is nothing entered. I am stumped.

I have attached two of these workbooks; I'm asking about the second tab (MFG vs PLAN.) Sample A is the one that is working and Sample B is the problem file. Also, I have used different formulas in (for example) cell D14 in both in order to eliminate the "0" in Sample B.

Thanks to anyone who can shed some light on this for me. I realize there are inconsistencies on other pages as well. :(

kimber
Click to expand...
Hello Kimber,
I guess problem in using IF in D12,D13& D14 cells "=IF('SALES-PROD-SHIP'!$F10>0,'SALES-PROD-SHIP'!$F10,"")"

If you directly link those cells (D12,D13& D14 ). formula in D15 will work.
 
Div0 error is due to your formula in precedents.

In D19:
=IF('SALES-PROD-SHIP'!$F$14>0,'SALES-PROD-SHIP'!$F$14,"")

Formula generated blank (i.e. "") is considered text and will always evaluate true to ">0".

Several ways to handle this.

1. Change D19 formula to...
=IF('SALES-PROD-SHIP'!$F$14>0,'SALES-PROD-SHIP'!$F$14,0)

2. Change Average formula in D21 to...
=IFERROR(AVERAGE(D17:D19),"")

Alternately... D21 to
=IF(ISNUMBER(D19),AVERAGE(D17:D19),"")
 
Hi ,

A simple thumb rule is that the IFERROR function should be used either because despite error values further processing needs to be done on the data , or because you do not want error values to be displayed in the final output or report / dashboard.

But for these two reasons , IFERROR should not be used.

Otherwise , as Chihiro has already pointed out , use =IFERROR(formula , 0) where formula returns a numeric value , and use =IFERROR(formula , "") where formula returns a text string.

Narayan
 
Thangavel said:
Hello Kimber,
I guess problem in using IF in D12,D13& D14 cells "=IF('SALES-PROD-SHIP'!$F10>0,'SALES-PROD-SHIP'!$F10,"")"

If you directly link those cells (D12,D13& D14 ). formula in D15 will work.
Click to expand...

Thank you, Thangavel. When I change it to a direct link, a "0" appears in D12 and D14! And I am only having the problem in Sample B file and not in the same worksheet in Sample A file.
 
Chihiro said:
Div0 error is due to your formula in precedents.

In D19:
=IF('SALES-PROD-SHIP'!$F$14>0,'SALES-PROD-SHIP'!$F$14,"")

Formula generated blank (i.e. "") is considered text and will always evaluate true to ">0".

Several ways to handle this.

1. Change D19 formula to...
=IF('SALES-PROD-SHIP'!$F$14>0,'SALES-PROD-SHIP'!$F$14,0)

2. Change Average formula in D21 to...
=IFERROR(AVERAGE(D17:D19),"")

Alternately... D21 to
=IF(ISNUMBER(D19),AVERAGE(D17:D19),"")
Click to expand...

Thank you, Chihiro! I now understand more about using the "" and will remember your information. I chose your alternate/3rd suggestion and it worked beautifully! How grateful I am.
 
NARAYANK991 said:
Hi ,

A simple thumb rule is that the IFERROR function should be used either because despite error values further processing needs to be done on the data , or because you do not want error values to be displayed in the final output or report / dashboard.

But for these two reasons , IFERROR should not be used.

Otherwise , as Chihiro has already pointed out , use =IFERROR(formula , 0) where formula returns a numeric value , and use =IFERROR(formula , "") where formula returns a text string.

Narayan
Click to expand...

Thank you, Narayan.
 
You must log in or register to reply here.
Back
Top