| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
pick highest number from a list
Hi I have a list of numbers in a1 is like 45 b1 32 c1 47 d1 42 e1 25. I want
to be able to have the cell with the number closest to a1 come up but not going over 45 so d1 42 is the number i want to stand out. Where c1 is closer to 45 but is over 45 so anything over 45 is not counted. Thanks Karl |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
pick highest number from a list
Hi!
One way: =SUMPRODUCT(MAX((B1:E1<=A1)*B1:E1)) Biff "Karl" wrote in message ... Hi I have a list of numbers in a1 is like 45 b1 32 c1 47 d1 42 e1 25. I want to be able to have the cell with the number closest to a1 come up but not going over 45 so d1 42 is the number i want to stand out. Where c1 is closer to 45 but is over 45 so anything over 45 is not counted. Thanks Karl |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
pick highest number from a list
I can't get this to work what cell do i put it in. B20 38 C20 33 D20 45
E20 78 F20 24 G20 26. These numbers are in these cells. I want only 1 number to be highlighted. The one closest to B20 which is 38. So cell C20 which is 33 is closest without going over 38.So I want it to be highlighted. Thanks Hope i'm making sense here. Karl "T. Valko" wrote: Hi! One way: =SUMPRODUCT(MAX((B1:E1<=A1)*B1:E1)) Biff "Karl" wrote in message ... Hi I have a list of numbers in a1 is like 45 b1 32 c1 47 d1 42 e1 25. I want to be able to have the cell with the number closest to a1 come up but not going over 45 so d1 42 is the number i want to stand out. Where c1 is closer to 45 but is over 45 so anything over 45 is not counted. Thanks Karl |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
pick highest number from a list
You want the cell highlighted?
Select the range C20:G20 Goto FormatConditional Formatting Formula Is: =AND(C20<"",C20=SUMPRODUCT(MAX(($C20:$G20<=$B20)* $C20:$G20))) Click the Format button Select the desired style(s) OK out Biff "Karl" wrote in message ... I can't get this to work what cell do i put it in. B20 38 C20 33 D20 45 E20 78 F20 24 G20 26. These numbers are in these cells. I want only 1 number to be highlighted. The one closest to B20 which is 38. So cell C20 which is 33 is closest without going over 38.So I want it to be highlighted. Thanks Hope i'm making sense here. Karl "T. Valko" wrote: Hi! One way: =SUMPRODUCT(MAX((B1:E1<=A1)*B1:E1)) Biff "Karl" wrote in message ... Hi I have a list of numbers in a1 is like 45 b1 32 c1 47 d1 42 e1 25. I want to be able to have the cell with the number closest to a1 come up but not going over 45 so d1 42 is the number i want to stand out. Where c1 is closer to 45 but is over 45 so anything over 45 is not counted. Thanks Karl |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
pick highest number from a list
Hi sorry for getting back to your post late. This is going to do the job
Thanks . Karl "T. Valko" wrote: You want the cell highlighted? Select the range C20:G20 Goto FormatConditional Formatting Formula Is: =AND(C20<"",C20=SUMPRODUCT(MAX(($C20:$G20<=$B20)* $C20:$G20))) Click the Format button Select the desired style(s) OK out Biff "Karl" wrote in message ... I can't get this to work what cell do i put it in. B20 38 C20 33 D20 45 E20 78 F20 24 G20 26. These numbers are in these cells. I want only 1 number to be highlighted. The one closest to B20 which is 38. So cell C20 which is 33 is closest without going over 38.So I want it to be highlighted. Thanks Hope i'm making sense here. Karl "T. Valko" wrote: Hi! One way: =SUMPRODUCT(MAX((B1:E1<=A1)*B1:E1)) Biff "Karl" wrote in message ... Hi I have a list of numbers in a1 is like 45 b1 32 c1 47 d1 42 e1 25. I want to be able to have the cell with the number closest to a1 come up but not going over 45 so d1 42 is the number i want to stand out. Where c1 is closer to 45 but is over 45 so anything over 45 is not counted. Thanks Karl |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
pick highest number from a list
You're welcome. Thanks for the feedback!
Biff "Karl" wrote in message ... Hi sorry for getting back to your post late. This is going to do the job Thanks . Karl "T. Valko" wrote: You want the cell highlighted? Select the range C20:G20 Goto FormatConditional Formatting Formula Is: =AND(C20<"",C20=SUMPRODUCT(MAX(($C20:$G20<=$B20)* $C20:$G20))) Click the Format button Select the desired style(s) OK out Biff "Karl" wrote in message ... I can't get this to work what cell do i put it in. B20 38 C20 33 D20 45 E20 78 F20 24 G20 26. These numbers are in these cells. I want only 1 number to be highlighted. The one closest to B20 which is 38. So cell C20 which is 33 is closest without going over 38.So I want it to be highlighted. Thanks Hope i'm making sense here. Karl "T. Valko" wrote: Hi! One way: =SUMPRODUCT(MAX((B1:E1<=A1)*B1:E1)) Biff "Karl" wrote in message ... Hi I have a list of numbers in a1 is like 45 b1 32 c1 47 d1 42 e1 25. I want to be able to have the cell with the number closest to a1 come up but not going over 45 so d1 42 is the number i want to stand out. Where c1 is closer to 45 but is over 45 so anything over 45 is not counted. Thanks Karl |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Drop-Down List Choice Affects Multiple Cells? | Excel Worksheet Functions | |||
| Create formula which will number a list in Excel 2003 | Excel Discussion (Misc queries) | |||
| 2 rows, highest No in row 1, then highest number in row 2 relating to that column, possible duplicates | Excel Worksheet Functions | |||
| Lookup in a list of number with a headline | Excel Discussion (Misc queries) | |||
| Finding the 3rd largest number in a list | Excel Worksheet Functions | |||
Hybrid Mode
