Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula
Can anyone assit with a formula for detirmining if a number in a group of 20 is one of the lowest 8. I have been trying "=IF(F1<MEDIAN(F$1:F$20),F1,0)" This seems to give me one of the lowest 10 out of the group of 20.
Any suggestions would be greatly appreciated. Thanking you in anticipation Garry |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula
Hi Garry,
Am Tue, 6 Mar 2012 10:12:45 +0000 schrieb Garry Oder: Can anyone assit with a formula for detirmining if a number in a group of 20 is one of the lowest 8. I have been trying "=IF(F1<MEDIAN(F$1:F$20),F1,0)" This seems to give me one of the lowest 10 out of the group of 20. try: =IF(RANK(F1,$F$1:$F$20,1)<=8,F1,0) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula
"Garry Oder" wrote:
Can anyone assit with a formula for detirmining if a number in a group of 20 is one of the lowest 8. =IF(F1<=SMALL(F$1:F$20,8),"yes","no") |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula
PS.... I wrote:
"Garry Oder" wrote: Can anyone assit with a formula for detirmining if a number in a group of 20 is one of the lowest 8. =IF(F1<=SMALL(F$1:F$20,8),"yes","no") It just occurred to me that you wrote "if a number ... __is__ one of the lowest 8", not if it fits among the lowest 8. Try the following normal formula (just press Enter despite the array constant): =IF(OR(F1=SMALL(F$1:F$20,{1,2,3,4,5,6,7,8})),"yes" ,"no") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula | Excel Worksheet Functions | |||
copy formula down a column and have cell references change within formula | New Users to Excel | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |