Getting 2nd lowest value (after 0) in a Pivot Table

[bhatiadisha@gmail.com]

Hello,


I have the following list for which I am trying to get the highest and lowest value in a Pivot Table. Getting the highest value was easy as I got the relevant fields in the Pivot Table field list and right clicked on 'Total' under 'Values' area in the filed list and changed it to MAX to get the highest Total per Classification. Hoever, since my Data list has a lot of '0 Totals' I want excel to take the 2nd lowest value instead of 0's for the 'Minimum Total'. Is this possible through the Pivot functionality itself without using SMALL() or COUNTIF() formulas? Below is an example of my data :-


Classification Total


Apple 12

Orange 0

Grape 2

Apple 0

Grape 0

Orange 25

Apple 1

Grape 8

Orange 3


So when I made my Pivot Table, I get the correct values for the highest :-

Apple - 12

Orange - 25

Grape - 8


I want to get similar values for the lowest, but excluding non-zeros and by using Pivot functionality:-


Apple - 1

Orange - 3

Grapes- 2


Thanks

 

[Faseeh]

Hi bhatiadisha,


Welcome to the forums!!


You can add a helper column with this formula:

=IF(B2<>0,B2,"-")

[pre]Class	Total	Total2
Apple	12	12
Orange	0	-
Grape	2	2
Apple	0	-
Grape	0	-
Orange	25	25
Apple	1	1
Grape	8	8
Orange	3	3

[/pre]

and use helper column to get min values,


Regards,

 

[SirJB7]

Hi, bhatiadisha@gmail.com!

May I suggest you to change your nick name? It's too long and it overlaps the comment area making it unreadable. If you want to keep your email address you can put it in the website field of your profile, then when clicking on you nick on any comment the user will be led to your email.

Regards!