Changing 0 to N/A

[edost4]

Hey guys, I am trying to make the "0%" (in the day over day change context) to show as N/A in the dashboard (in a different tab).
The data shows in a tab called Sheet 5, and is pulled to a tab called Dash.
This is under top 10 reasons so it needs to be one function for all 10 reasons (which differ daily)..
I tried =IFERROR(INDEX(Sheet5!$FJ$26:$FJ$105,MATCH(Dash!B42,Sheet5!$A$26:$A$105,0)),"N/A") but thats a no go.
Any suggestions? Thanks.

[TMS]

... but thats a no go

What does that mean?

Please post a sample workbook indicating what you want and why.

Regards, TMS

[FDibbins]

Hi, welcome tpo the forum

If your formula returns a 0, then that is not an error, and the IFERROR() part will not trigger....
=IFERROR(INDEX(Sheet5!$FJ$26:$FJ$105,MATCH(Dash!B42,Sheet5!$A$26:$A$105,0)),"N/A")

Perhaos try something like....
=if(INDEX(Sheet5!$FJ$26:$FJ$105,MATCH(Dash!B42,Sheet5!$A$26:$A$105,0))=0,,"N/A"),INDEX(Sheet5!$FJ$26:$FJ$105,MATCH(Dash!B42,Sheet5!$A$26:$A$105,0)))

This may be a ble to be simplified if we could see a sample of your data

[edost4]

I will try to explain better (the solution provided contained an error- but it looked too complex for to to understand anyways. I made a pivot table in tab “Sheet5” (from a .csv file in a tab called “csv”) which lists each “Reason for Contact” for today and their respective “# of Attempts”. I also created a tab labeled “Day over Day % Change” and its calculation equals: (today’s value-yesterday’s value)/yesterday’s value.
The “Dash” is another tab (the main one) and it only pulls the top 10 reasons for contact (out of over 80), based on their # of attempts. Obviously, if the # of attempts today equals the value of attempts yesterday, the result will be 0. However, I want the value of “0” in the “Sheet5” tab to be reported a N/A in the “Dash” tab.
I tried using the formula: =IFERROR(INDEX(Sheet5!$FJ$26:$FJ$105,MATCH(Dash!B42,Sheet5!$A$26:$A$105,0)),"N/A") but that just gives me the value as it is, meaning if answer is 0, I get 0 instead of N/A in the “Dash” tab..
Note, I need a formula I can pull for all 10 cells of top 10 reasons for contact, since the top 10 vary.
To explain the formula, The Day over Day % Data is found in column FJ between the rows 26 and 105 in the “Sheet5” tab (this is how many different reasons for contact I have). Cell B42 in the “Dash” tab is where I have the selected “reason for contact” name (the 10 are between B35-B44) where I got a value of 0. The name of the “Reason for Contact” in Sheet5 is found in column A between rows 26 and 105.
Thanks in advance! Ive attached 2 print screens as well.
Dash prtsc..jpgDash prtsc..jpgDash prtsc..jpg

[edost4]

I think something went wrong with the attachment, here is the other one (I hope it works) Sheet5 psc.jpg

[FDibbins]

None of those images are coming through, but instead of uploading a pic, upload a sample workbook. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff)

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

[FDibbins]

None of those images are coming through, but instead of uploading a pic, upload a sample workbook. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff)

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

[edost4]

understood. Attached is the spreadsheet with just the info discussed. Look for account revoked in Sheet5