| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I find the cell/data the closest/nearest to another data?
Hi everyone!
I'm sorry, I can't find my answer in the general help program. How do I find the cell or the data from a list, that I cannot sort, the closest to another data. e.g. If I have 150 values in a column and I do the average of this column, I want to know which data or cell is the closest from this average. Thank you so much! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I find the cell/data the closest/nearest to another data?
Assuming the cells are in A2:A151
Select A2, go to Format, Conditional Format. Formula is: =ABS(A2-AVERAGE($A$2:$A$151))=MIN(ABS($A$2:$A$151-AVERAGE($A$2:$A$151))) Select a format you want to appear if cell meets your criteria. Select A2, copy, then select rest of column, right click, paste special, formatting only. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "V" wrote: Hi everyone! I'm sorry, I can't find my answer in the general help program. How do I find the cell or the data from a list, that I cannot sort, the closest to another data. e.g. If I have 150 values in a column and I do the average of this column, I want to know which data or cell is the closest from this average. Thank you so much! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I find the cell/data the closest/nearest to another data?
Try this array formula** :
=INDEX(A1:A15,MATCH(TRUE,ABS(A1:A15-AVERAGE(A1:A15))=MIN(ABS(A1:A15-AVERAGE(A1:A15))),0)) Or, you can use a separate cell to hold the average and then reference that cell: B1: =AVERAGE(A1:A15) Still array entered** : =INDEX(A1:A15,MATCH(TRUE,ABS(A1:A15-B1)=MIN(ABS(A1:A15-B1)),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Note that if there is more than 1 instance of a closest value the formula will return the *first* instance from top to bottom. -- Biff Microsoft Excel MVP "V" wrote in message ... Hi everyone! I'm sorry, I can't find my answer in the general help program. How do I find the cell or the data from a list, that I cannot sort, the closest to another data. e.g. If I have 150 values in a column and I do the average of this column, I want to know which data or cell is the closest from this average. Thank you so much! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I find the cell/data the closest/nearest to another dat
Hi,
This is the perfect time to use range names with Valko's solution: If you name the range N (for nearest) then: =INDEX(N,MATCH(1,--(ABS(N-AVERAGE(N))=MIN(ABS(N-AVERAGE(N)))),)) -- If this helps, please click the Yes button Cheers, Shane Devenshire "T. Valko" wrote: Try this array formula** : =INDEX(A1:A15,MATCH(TRUE,ABS(A1:A15-AVERAGE(A1:A15))=MIN(ABS(A1:A15-AVERAGE(A1:A15))),0)) Or, you can use a separate cell to hold the average and then reference that cell: B1: =AVERAGE(A1:A15) Still array entered** : =INDEX(A1:A15,MATCH(TRUE,ABS(A1:A15-B1)=MIN(ABS(A1:A15-B1)),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Note that if there is more than 1 instance of a closest value the formula will return the *first* instance from top to bottom. -- Biff Microsoft Excel MVP "V" wrote in message ... Hi everyone! I'm sorry, I can't find my answer in the general help program. How do I find the cell or the data from a list, that I cannot sort, the closest to another data. e.g. If I have 150 values in a column and I do the average of this column, I want to know which data or cell is the closest from this average. Thank you so much! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I find the cell/data the closest/nearest to another dat
Thank you so much!
It's not exactly what I want but it helps a lot. Thank you, thank you! "Luke M" wrote: Assuming the cells are in A2:A151 Select A2, go to Format, Conditional Format. Formula is: =ABS(A2-AVERAGE($A$2:$A$151))=MIN(ABS($A$2:$A$151-AVERAGE($A$2:$A$151))) Select a format you want to appear if cell meets your criteria. Select A2, copy, then select rest of column, right click, paste special, formatting only. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "V" wrote: Hi everyone! I'm sorry, I can't find my answer in the general help program. How do I find the cell or the data from a list, that I cannot sort, the closest to another data. e.g. If I have 150 values in a column and I do the average of this column, I want to know which data or cell is the closest from this average. Thank you so much! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I find the cell/data the closest/nearest to another dat
Thank you so much!
You have it right! It's fantastic! Thank you so much! "T. Valko" wrote: Try this array formula** : =INDEX(A1:A15,MATCH(TRUE,ABS(A1:A15-AVERAGE(A1:A15))=MIN(ABS(A1:A15-AVERAGE(A1:A15))),0)) Or, you can use a separate cell to hold the average and then reference that cell: B1: =AVERAGE(A1:A15) Still array entered** : =INDEX(A1:A15,MATCH(TRUE,ABS(A1:A15-B1)=MIN(ABS(A1:A15-B1)),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Note that if there is more than 1 instance of a closest value the formula will return the *first* instance from top to bottom. -- Biff Microsoft Excel MVP "V" wrote in message ... Hi everyone! I'm sorry, I can't find my answer in the general help program. How do I find the cell or the data from a list, that I cannot sort, the closest to another data. e.g. If I have 150 values in a column and I do the average of this column, I want to know which data or cell is the closest from this average. Thank you so much! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I find the cell/data the closest/nearest to another dat
It works too! Thank you so much!
"Shane Devenshire" wrote: Hi, This is the perfect time to use range names with Valko's solution: If you name the range N (for nearest) then: =INDEX(N,MATCH(1,--(ABS(N-AVERAGE(N))=MIN(ABS(N-AVERAGE(N)))),)) -- If this helps, please click the Yes button Cheers, Shane Devenshire "T. Valko" wrote: Try this array formula** : =INDEX(A1:A15,MATCH(TRUE,ABS(A1:A15-AVERAGE(A1:A15))=MIN(ABS(A1:A15-AVERAGE(A1:A15))),0)) Or, you can use a separate cell to hold the average and then reference that cell: B1: =AVERAGE(A1:A15) Still array entered** : =INDEX(A1:A15,MATCH(TRUE,ABS(A1:A15-B1)=MIN(ABS(A1:A15-B1)),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Note that if there is more than 1 instance of a closest value the formula will return the *first* instance from top to bottom. -- Biff Microsoft Excel MVP "V" wrote in message ... Hi everyone! I'm sorry, I can't find my answer in the general help program. How do I find the cell or the data from a list, that I cannot sort, the closest to another data. e.g. If I have 150 values in a column and I do the average of this column, I want to know which data or cell is the closest from this average. Thank you so much! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I find the cell/data the closest/nearest to another dat
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "V" wrote in message ... Thank you so much! You have it right! It's fantastic! Thank you so much! "T. Valko" wrote: Try this array formula** : =INDEX(A1:A15,MATCH(TRUE,ABS(A1:A15-AVERAGE(A1:A15))=MIN(ABS(A1:A15-AVERAGE(A1:A15))),0)) Or, you can use a separate cell to hold the average and then reference that cell: B1: =AVERAGE(A1:A15) Still array entered** : =INDEX(A1:A15,MATCH(TRUE,ABS(A1:A15-B1)=MIN(ABS(A1:A15-B1)),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Note that if there is more than 1 instance of a closest value the formula will return the *first* instance from top to bottom. -- Biff Microsoft Excel MVP "V" wrote in message ... Hi everyone! I'm sorry, I can't find my answer in the general help program. How do I find the cell or the data from a list, that I cannot sort, the closest to another data. e.g. If I have 150 values in a column and I do the average of this column, I want to know which data or cell is the closest from this average. Thank you so much! |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I find the cell/data the closest/nearest to another dat
This is the perfect time to use range names
There's never a perfect time! =INDEX(N,MATCH(1,--(ABS(N-AVERAGE(N))=MIN(ABS(N-AVERAGE(N)))),)) More efficient to match TRUE. -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message ... Hi, This is the perfect time to use range names with Valko's solution: If you name the range N (for nearest) then: =INDEX(N,MATCH(1,--(ABS(N-AVERAGE(N))=MIN(ABS(N-AVERAGE(N)))),)) -- If this helps, please click the Yes button Cheers, Shane Devenshire "T. Valko" wrote: Try this array formula** : =INDEX(A1:A15,MATCH(TRUE,ABS(A1:A15-AVERAGE(A1:A15))=MIN(ABS(A1:A15-AVERAGE(A1:A15))),0)) Or, you can use a separate cell to hold the average and then reference that cell: B1: =AVERAGE(A1:A15) Still array entered** : =INDEX(A1:A15,MATCH(TRUE,ABS(A1:A15-B1)=MIN(ABS(A1:A15-B1)),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Note that if there is more than 1 instance of a closest value the formula will return the *first* instance from top to bottom. -- Biff Microsoft Excel MVP "V" wrote in message ... Hi everyone! I'm sorry, I can't find my answer in the general help program. How do I find the cell or the data from a list, that I cannot sort, the closest to another data. e.g. If I have 150 values in a column and I do the average of this column, I want to know which data or cell is the closest from this average. Thank you so much! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to find number in a cell , a cell contains character data ornumeric data | Excel Worksheet Functions | |||
| Array Lookup to Find Closest Date and Next Closest Date | Excel Worksheet Functions | |||
| How to index the closest data(cash flow)? | Excel Worksheet Functions | |||
| Find MAX data in sheet (Cell) - Any cell that exceeds X amount of data | Excel Discussion (Misc queries) | |||
| The match and lookup functions can find literal data but not the same data referenced from a cell | Excel Discussion (Misc queries) | |||
Linear Mode
