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

IFERROR does not seem to be working

N

Nikki91775

Member
My spreadsheet pulls from a Pivot Table by Date. For example 6/3/2013, 6,4,2013, etc. In my spreadsheet it states to lookup the pivot table for that date. I also have in front of the formula an IFERROR in the front and the "" at the end.


However, if my date field does not have anything in it, it will come up as a Zero on my spreadsheet. I do not want any number populating until the date has happened and sometimes there could be days where there are 0's. It is just making my variance look bad all the way across the month when it has not even happened.


Here is my formula: =IFERROR(GETPIVOTDATA("QTY Ord",'E40 Pivot'!$A$3,"Order Date",DATE(2013,6,3)),"")
 
Hi Nikki ,


IFERROR will work only when the formula evaluates to an error value such as #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!.


A zero value is a valid value , and IFERROR will leave it as it is.


If you don't want zeroes , you need to use an IF statement to replace the zero with a blank.


Narayan
 
This one works, what is the difference?


=IF(ISERROR(GETPIVOTDATA("Sum of Orders Booked",'Total Book to Bill'!$A$7,"MO","06","Day",3)),"",(GETPIVOTDATA("Sum of Orders Booked",'Total Book to Bill'!$A$7,"MO","06","Day",3)))
 
Hi Nikki ,


You have posted two formulae :


1. GETPIVOTDATA("QTY Ord",'E40 Pivot'!$A$3,"Order Date",DATE(2013,6,3))


2. GETPIVOTDATA("Sum of Orders Booked",'Total Book to Bill'!$A$7,"MO","06","Day",3)


Without any IFERROR or ISERROR around them , on their own what do these two formulae display ?


IFERROR and ISERROR work the same way ; ISERROR is used in Excel 2003 and prior versions , where the IFERROR function is not available.


Narayan
 
Hi, Nikki9775!

But that's not the same example. This works?

=IFERROR(GETPIVOTDATA("Sum of Orders Booked",'Total Book to Bill'!$A$7,"MO","06","Day",3)),"")

Note that IFERROR function works for Excel versions 2007+ so for 2003- you should use IF & ISERROR combinations.

Regards!
 
They are two different sheets displaying from different pivots, but they are drawing for the day. the ISERROR makes sense that it is older because it is in a sheet that has evolved over time and no one has updated it. I just inherited it. We are using Excel 2010.
 
Hi, Nikki91775!

In understand that, but my previous question was regarding the use of the same functions IFERROR or IF & ISERROR with the same arguments. You should test the same formulas with different parameters, or different formulas with same parameters, to arrive a logical conclusion; testing different formulas with different parameters doesn't arrive anywhere.

Regards!
 
You must log in or register to reply here.
Back
Top