Best way for showing 3 lowest areas

[bartmanekul]

I've tried using the SMALL function, but this doesn't work when you want the 3 lowest results and they can often be the same amounts.

My data is pulled from another table via pivot table, and looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Area 1[/TD]
[TD]Area 2[/TD]
[TD]Area 3[/TD]
[TD]Area 4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]2.5[/TD]
[TD]2.5[/TD]
[/TR]
[TR]
[TD]2.65[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

My data has a lot more areas, most cases 14 rather than 4 above.

The problem I have is that I want to display the 3 lowest areas elsewhere in a nice format. I'd done this using MATCH and SMALL.

However, the 3 cells I created will always show the same one if there is areas sharing the same low rate (like the last row in my example). So for the last row, my cells would all show Area 2, even though they are set to look for the lowest 1, 2 and 3.

Is there a better way to do this? Or is there code I can add into my formula to stop this from happening?

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

H​

[/td][td]

I​

[/td][/tr][tr][td]

1​

[/td][td] Area 1[/td][td] Area 2[/td][td] Area 3[/td][td] Area 4[/td][td]

3​

[/td][td][/td][/tr]
[tr][td]

2​

[/td][td] 3[/td][td] 2[/td][td] 2.5[/td][td] 2.5[/td][td]

3​

[/td][td][/td][/tr]
[tr][td]

3​

[/td][td] 2.65[/td][td] 1[/td][td] 1[/td][td] 1[/td][td]values[/td][td]areas[/td][/tr]
[tr][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td]

1​

[/td][td]Area 2[/td][/tr]
[tr][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td]

1​

[/td][td]Area 3[/td][/tr]
[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td]

1​

[/td][td]Area 4[/td][/tr]
[tr][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In H2 just enter:

=COUNTIFS(A2:D3,"<="&SMALL(A2:D3,H1))

In H4 just enter and copy down:

=IF(ROWS($H$3:H3)>$H$2,"",SMALL($A$2:$D$3,ROWS($H$3:H3)))

In I4 control+shift+enter, not just enter, and copy down:

=IF($H4="","",INDEX($A$1:$D$1,SMALL(IF($A$2:$D$3=$H4,COLUMN($A$1:$D$1)-COLUMN($A$1)+1),COUNTIFS($H$4:H4,H4))))

 

[bartmanekul]

Thanks for the help. Unfortunately when I try to replicate this, I get a value error.

 

[Aladin Akyurek]

Thanks for the help. Unfortunately when I try to replicate this, I get a value error.

Control+shift+enter means: Press down the control and the shift keys at the same time while you hit the enter kay. If done as suggested, Excel itself puts a pair of { and } around the formula in recognition.

 

[bartmanekul]

Thanks for the help. Unfortunately when I try to replicate this, I get a value error.

I get a NUM error now, but I realise something critical I missed out - this would need to be for each row, since it's month by month (months being in the row).

 

[Aladin Akyurek]

I get a NUM error now, but I realise something critical I missed out - this would need to be for each row, since it's month by month (months being in the row).

See the workbook that implements the suggested set up: Dropbox - bartmanekul top 3 lowest area.xlsx

 

[bartmanekul]

Thanks for your help again. I can't apply this since the indexes would need to go over columns, not rows (can't put 3 down for each one) but that's my fault for not specifying that I'd have to have the lowest 3 for each row.

 

[Aladin Akyurek]

Thanks for your help again. I can't apply this since the indexes would need to go over columns, not rows (can't put 3 down for each one) but that's my fault for not specifying that I'd have to have the lowest 3 for each row.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][/tr][tr][td]

1​

[/td][td] Area 1[/td][td] Area 2[/td][td] Area 3[/td][td] Area 4[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td] 3[/td][td] 2[/td][td] 2.5[/td][td] 2.5[/td][td]Area 2[/td][td]Area 3[/td][td]Area 4[/td][td][/td][/tr]
[tr][td]

3​

[/td][td] 2.65[/td][td] 1[/td][td] 1[/td][td] 1[/td][td]Area 2[/td][td]Area 3[/td][td]Area 4[/td][td][/td][/tr]
[tr][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In H2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX($A$1:$D$1,SMALL(IF($A2:$D2<=SMALL($A2:$D2,MIN(3,COUNT($A2:$D2))),COLUMN($A$1:$D$1)-COLUMN($A$1)+1),COLUMNS($H2:H2))),"")

 

[bartmanekul]

Many thanks for the help, it's the first chance I've had to test the formula.

It doesn't display the lowest ones, and results are the same for all rows. I'm sure it's an error my end, but I appreciate the time taken to try and help!

 

[Aladin Akyurek]

Many thanks for the help, it's the first chance I've had to test the formula.

It doesn't display the lowest ones, and results are the same for all rows. I'm sure it's an error my end, but I appreciate the time taken to try and help!

See the workbook for the rowwise output: https://www.dropbox.com/s/bjn2i6psp...g 3 lowest areas edited for rowwise.xlsx?dl=0