bartmanekul
Board Regular
- Joined
- Apr 3, 2017
- Messages
- 58
- Office Version
- 365
- Platform
- Windows
[TABLE="width: 500"]
<tbody>[TR]
[TD]Area 1[/TD]
[TD]Area 2[/TD]
[TD]Area 3[/TD]
[TD]Area 4[/TD]
[TD]Area 5[/TD]
[TD]Lowest[/TD]
[TD]2nd lowest[/TD]
[TD]3rd lowest[/TD]
[/TR]
[TR]
[TD]1.2[/TD]
[TD]1.6[/TD]
[TD]1.2[/TD]
[TD]1.4[/TD]
[TD]1.7[/TD]
[TD]=IFERROR(INDEX($A$1:$E$1,MATCH(SMALL($A2:$E2,1),$A2:$E2,0)),"")[/TD]
[TD]=IFERROR(INDEX($A$1:$E$1,MATCH(SMALL($A2:$E2,2),$A2:$E2,0)),"")[/TD]
[TD]=IFERROR(INDEX($A$1:$E$1,MATCH(SMALL($A2:$E2,3),$A2:$E2,0)),"")[/TD]
[/TR]
[TR]
[TD]1.1[/TD]
[TD]2.8[/TD]
[TD]1.1[/TD]
[TD]1.1[/TD]
[TD]2[/TD]
[TD]Area 1[/TD]
[TD]Area 1[/TD]
[TD]Area 1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I have a selection of scores in cells, say A2 to D2.
Used formula (someone here helped adjust) to bring up the 3 lowest scoring areas. It works, but I found that if there are 2 areas with equally low scores, it simply replicates. So I get only 2 or even 1 worst result if they have the same low score. Any way around this?
The table above hopefully explains it. Formula in row 2, results example row 3.
The actual data is much bigger, around 14 rows down and there's over 15 'Areas'.
Thanks in advance for any help - don't even know how to start with this one.
<tbody>[TR]
[TD]Area 1[/TD]
[TD]Area 2[/TD]
[TD]Area 3[/TD]
[TD]Area 4[/TD]
[TD]Area 5[/TD]
[TD]Lowest[/TD]
[TD]2nd lowest[/TD]
[TD]3rd lowest[/TD]
[/TR]
[TR]
[TD]1.2[/TD]
[TD]1.6[/TD]
[TD]1.2[/TD]
[TD]1.4[/TD]
[TD]1.7[/TD]
[TD]=IFERROR(INDEX($A$1:$E$1,MATCH(SMALL($A2:$E2,1),$A2:$E2,0)),"")[/TD]
[TD]=IFERROR(INDEX($A$1:$E$1,MATCH(SMALL($A2:$E2,2),$A2:$E2,0)),"")[/TD]
[TD]=IFERROR(INDEX($A$1:$E$1,MATCH(SMALL($A2:$E2,3),$A2:$E2,0)),"")[/TD]
[/TR]
[TR]
[TD]1.1[/TD]
[TD]2.8[/TD]
[TD]1.1[/TD]
[TD]1.1[/TD]
[TD]2[/TD]
[TD]Area 1[/TD]
[TD]Area 1[/TD]
[TD]Area 1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I have a selection of scores in cells, say A2 to D2.
Used formula (someone here helped adjust) to bring up the 3 lowest scoring areas. It works, but I found that if there are 2 areas with equally low scores, it simply replicates. So I get only 2 or even 1 worst result if they have the same low score. Any way around this?
The table above hopefully explains it. Formula in row 2, results example row 3.
The actual data is much bigger, around 14 rows down and there's over 15 'Areas'.
Thanks in advance for any help - don't even know how to start with this one.