Wednesday, May 28, 2014

How to change #REF or #N/A to 0 (or whatever you want)

Change #REF or #N/A to 0


Ooohhh Excel tips. I love them.

I'm in the middle of making a report where data will be added weekly. Right now, I have no data (calls) for the 5pm timeslot, but there might be some in the future. So, for design sake, I'm making a place for 5pm and entering the formula that will work once there is data. Presently though, the formula returns #REF. Since that's just ugly, I want a way to change the #REF to display as zero. A quick search brought up this delightful formula:

=IF(ISERROR(yourformula),0,yourformula)

In my case, the full formula looks like this:

=IF(ISERROR(GETPIVOTDATA("Count",$S$12,"Time",17,"Day","Friday")/K7),0,(GETPIVOTDATA("Count",$S$12,"Time",17,"Day","Friday")/K7))

Currently, it displays as zero. Once there is data for Friday at 5pm, my formula will take that number and divide it by however many Fridays (K7) we have been tracking. Then give me an average of calls per hour, per Friday.

Sidenote - Since this is based off of a pivot table, you would think that you could just select "Average" in the values for this. However, due to what is included in the data, it is not dividing the total calls but the amount of actual Fridays but rather how many times "Friday" occurs. So, we take the long way around. If you are reading this and thinking "wow, why did she not try ___", please feel welcome to comment your suggestion. I often take the long way around when it's actually quite straight forward. This is my source data, which is then converted into a pivot table and grouped by hour, day, date, user (whatever is needed, you know how it is)

Sales calls in Excel




No comments: