Remove 0 results from formula

[BrittaEriksen]

We're trying to display the bottom 10 results out of a data set. We don't want 0's to be part of the results. The goal is to not display any results with a "0", but to skip them and go to the next lowest number.



Here is the formula we're trying to correct:



=INDEX(DashboardDataSheet!$B$1:$AZ$1, MATCH(1, (DashboardDataSheet!$B$5:$AZ$5 = SMALL(IF(DashboardDataSheet!$B$5:$AZ$5<>0, DashboardDataSheet!$B$5:$AZ$5), G23)) * (COUNTIF($B$22:H22, DashboardDataSheet!$B$1:$AZ$1) = 0), 0))

[Limor_OP]

Hi
what excel version do you use?

[BrittaEriksen]

It's Microsoft 365, Excel version build 2308

[TMS]

Welcome to the forum

Please update your profile to reflect the version of Excel this needs to work with. "Latest" could be 365 or 2021. There is a big difference.

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

[bebo021999]

Works for any excel version (2007+)

If YourFormula always return numeric value, drap it inside an IFERROR, like this:

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

or a bit longer

=IF(YourFormula=0,"",YourFormula)

[BrittaEriksen]

Works for any excel version (2007+)

If YourFormula always return numeric value, drap it inside an IFERROR, like this:

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

or a bit longer

=IF(YourFormula=0,"",YourFormula)

Will try!! Thank you so much

[AliGW]

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[TMS]

Thanks for the rep.