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?

 

[bartmanekul]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Area1[/TD]
[TD]Area2[/TD]
[TD]Area3[/TD]
[TD]Area4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2.5[/TD]
[TD]2.5[/TD]
[TD]2.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Area2[/TD]
[TD]Area3[/TD]
[TD]Area4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2.65[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Area1[/TD]
[TD]Area3[/TD]
[TD]Area4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I was working on this for ages until after checking I realised that the one you made doesn't seem to give the lowest area.

Row 2 seems to work, but row three shows the lowest area as 1, when it should be 3 or 4 (which it has afterwards). Try as I might I couldn't find anything different with the Row 3 formulas.

 

[Aladin Akyurek]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Area1[/TD]
[TD]Area2[/TD]
[TD]Area3[/TD]
[TD]Area4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2.5[/TD]
[TD]2.5[/TD]
[TD]2.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Area2[/TD]
[TD]Area3[/TD]
[TD]Area4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2.65[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Area1[/TD]
[TD]Area3[/TD]
[TD]Area4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I was working on this for ages until after checking I realised that the one you made doesn't seem to give the lowest area.

Row 2 seems to work, but row three shows the lowest area as 1, when it should be 3 or 4 (which it has afterwards). Try as I might I couldn't find anything different with the Row 3 formulas.

I'm sorry, my fault. Here is the correction.

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

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

 

[bartmanekul]

Thanks, I'll try that as soon as possible.

It's a relief to know I wasn't getting it wrong, I was going over it bit by bit to see where I'd made a mistake.

Have to say I'm surprised at it being so hard to do in excel, I'd have thought this kind of thing would be easier.

 

[bartmanekul]

Have it working now, thank you for the solution and your perseverance.

 

[Aladin Akyurek]

Have it working now, thank you for the solution and your perseverance.

Glad to help. Thanks for the kind update.