large formula with criteria

[Dickeymint]

Hello,

I have the following table of data: -

2294944487994	Leeds	3	5060926*2		115000		57155		91000		52278.88
2294944100061	Leeds	5	5060934*2		400000		198800		315000		193470.65
44565000100106	Forest Heath	1	5057641*4		157000		78029		156000		76357.94
00001430154010	Cambridge	2	5057633*1		28500		14164.5		28500		13309.5
1310113317008	Broadland	2.5	5061015*1		22500		10140.84		27000		12496.28
01030000903338	Ipswich	2.5	5066980*1		14250		2437.57		15500		7223
2040766	Nuneaton and Bedworth	6	5059126*1		392500		195072.5		417500		200400
2520600900020045	Bridgend	1	5057674*2		139000		67554		101000		50399
321080000335000600	Cardiff	2	5057658*1		13750		6682.5		13750		6861.25

<tbody>
</tbody>

I am trying to find the top 5 in the list based on value (column 8) where the item number is not 2 or 2.5 (column 3)

I also need the same where the item number is 2 and 2.5 (column 3)

I am currently using the following but its not working with column 3: -

=IF(OR('LSH Data'!$D$2:$D$250<>2,'LSH Data'!$D$2:$D$250<>2.5),LARGE('LSH Data'!$K$2:$K$250,1))


Please advise

 

[Scott Huish]

Try using AND instead of OR

 

[Tetra201]

Try the following formulas (both to be entered using Ctrl+Shift+Enter, not just Enter):

Excluding 2 and 2.5 =LARGE(IF(('LSH Data'!$D$2:$D$250<>2)*('LSH Data'!$D$2:$D$250<>2.5),'LSH Data'!$K$2:$K$250),1)
Only 2 or 2.5 =LARGE(IF(('LSH Data'!$D$2:$D$250=2)+('LSH Data'!$D$2:$D$250=2.5),'LSH Data'!$K$2:$K$250),1)

Note: if your 2's and 2.5's are calculated, make sure they are properly rounded.

 

[Dickeymint]

Try the following formulas (both to be entered using Ctrl+Shift+Enter, not just Enter):

Excluding 2 and 2.5 =LARGE(IF(('LSH Data'!$D$2:$D$250<>2)*('LSH Data'!$D$2:$D$250<>2.5),'LSH Data'!$K$2:$K$250),1)
Only 2 or 2.5 =LARGE(IF(('LSH Data'!$D$2:$D$250=2)+('LSH Data'!$D$2:$D$250=2.5),'LSH Data'!$K$2:$K$250),1)

Note: if your 2's and 2.5's are calculated, make sure they are properly rounded.

Worked a treat, much appreciated.

 

[Tetra201]

Glad to be of a help.