Remove Zero Value from Cell

[mbrown279]

Hi All,

I am using an Index/Match to look up value from another table, and using that data to pivot out average scores. However it is placing zeros in the filed that have no data, which is causing Excel to count that cell.

What would I need to do so that a zero value is not returned and counted by Excel when pivoting out the data get the average score. I hope I have explained this properly.

=IF(ISNA(INDEX(Updated[NPS],MATCH([Conversation ID],Updated[Conversation ID],0))),"",INDEX(Updated[NPS],MATCH([Conversation ID],Updated[Conversation ID],0)))

I set up the pivot table to show count so I could see the difference between the current score and update score. The updated score is the one using the formula. It should be the same number.

[TABLE="width: 156"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Current Score [/TD]
[TD]Updated[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]152[/TD]
[/TR]
[TR]
[TD]947[/TD]
[TD]1933[/TD]
[/TR]
[TR]
[TD]693[/TD]
[TD]1193[/TD]
[/TR]
[TR]
[TD]545[/TD]
[TD]1079[/TD]
[/TR]
[TR]
[TD]624[/TD]
[TD]1045[/TD]
[/TR]
[TR]
[TD]542[/TD]
[TD]1021[/TD]
[/TR]
[TR]
[TD]230[/TD]
[TD]446[/TD]
[/TR]
[TR]
[TD]763[/TD]
[TD]1472[/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]862[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD]746[/TD]
[/TR]
[TR]
[TD]1015[/TD]
[TD]1936[/TD]
[/TR]
</tbody>[/TABLE]

 

[yky]

You can always set the if statement to return a null string.

=IF(MyIF=0,"",MyIF) 'where MyIF is your IF statement.

 

[Phuoc]

Or try:

=IFERROR(1/(1/"your_formula"),"")