Excel 2007 : MIN, AVE, MAX values but exclude blanks

**Quagga** · 07-14-2010, 03:28 PM

I need to find the minimum, maximum, average...etc. cost/kg from different suppliers, but exclude any supplier with a blank or zero value, from the calculation. Please see attache sample file. Yellow blocks are where I want the values.

SOLVED

Thanks NBVC

**NBVC** · 07-14-2010, 04:02 PM

In S5:

=SMALL(IF($F$4:$Q$4="R / kg",IF($F5:$Q5>0,$F5:$Q5)),1)

in T5:

=SMALL(IF($F$4:$Q$4="R / kg",IF($F5:$Q5>0,$F5:$Q5)),2)

in U5:

=SMALL(IF($F$4:$Q$4="R / kg",IF($F5:$Q5>0,$F5:$Q5)),3)

in V5:

=LARGE(IF($F$4:$Q$4="R / kg",IF($F5:$Q5>0,$F5:$Q5)),1)

in W5:

=AVERAGE(IF($F$4:$Q$4="R / kg",IF($F5:$Q5>0,$F5:$Q5)))

each confirmed with CTRL+SHIFT+ENTER and copied down

**Quagga** · 07-14-2010, 04:57 PM

Thank you NBVC you have made my day.
Much appreciated

**Quagga** · 07-14-2010, 06:57 PM

Why would I get #NUM! and /or #VALUE! displayed instead of a value?
The formula seems correct.

Thanking you

**NBVC** · 07-14-2010, 09:19 PM

Are you confirming the formula by holding the CTRL and SHIFT keys down, then pressing ENTER. If done correctly, you will see { } brackets around the formula. Then you can copy down.

**Quagga** · 07-15-2010, 02:03 PM

I need to identify which supplier has the lowest price by displaying it in the cell next to the lowest price. I can only get the "IF" formula to work on one cell and not for all 6 suppliers.
How do I do this.