Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,670
Default How to determine the value from a list?

Does anyone have any suggestions on how to determine the value from a list?

There is a list of number on row 1
226 [A1], 230 [B1], 233 [C1], 238 [D1], ...

There is a number on row 2
228 [A2]

I would like to determine which max number on the list have the minimum
difference with the number 228 [A2]. In this case, the number having minimum
difference with 228 [A2] are 226 [A1] and 230 [B1], because both number have
the same difference 2.
228 - 226 = 2
230 - 228 = 2
then I prefer to select the max number 230 between 226 and 230, and return
this value in cell C1
Does anyone have any suggestions on how to do it in excel?
Thank in advance for any suggestions
Eric
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 806
Default How to determine the value from a list?

Hello Eric,

Enter with CTRL + SHIFT + ENTER
=MAX(IF(ABS($A$1:$D$1-A2)=MIN(ABS($A$1:$D$1-A2)),$A$1:$D$1))

Regards,
Bernd

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,670
Default How to determine the value from a list?

Thank you very much for your suggestions

Does anyone have any suggestions what the default value should be assign for
each empty cell?
I would like to assign the default value for if statement on row 1, such as
If(True, any number, default), but I cannot assign "" into formula
If(True, any number, ""), which does not work for following statement,
because of this character "".
Enter with CTRL + SHIFT + ENTER
=MAX(IF(ABS($A$1:$D$1-A2)=MIN(ABS($A$1:$D$1-A2)),$A$1:$D$1))
Does anyone have any suggestions?
Thank for any suggestions
Eric


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,339
Default How to determine the value from a list?

try:

=MAX(IF($A$1:$D$1<"",IF(ABS($A$1:$D$1-A2)=MIN(ABS($A$1:$D$1-A2)),$A$1:$D$1)))

to ignore empty cells

Enter with Ctrl+Shift+Enter

"Eric" wrote:

Thank you very much for your suggestions

Does anyone have any suggestions what the default value should be assign for
each empty cell?
I would like to assign the default value for if statement on row 1, such as
If(True, any number, default), but I cannot assign "" into formula
If(True, any number, ""), which does not work for following statement,
because of this character "".
Enter with CTRL + SHIFT + ENTER
=MAX(IF(ABS($A$1:$D$1-A2)=MIN(ABS($A$1:$D$1-A2)),$A$1:$D$1))
Does anyone have any suggestions?
Thank for any suggestions
Eric


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to determine the value from a list? Eric Excel Discussion (Misc queries) 6 May 22nd 07 06:51 PM
How to determine the values from a list? Eric Excel Discussion (Misc queries) 9 March 24th 07 02:59 PM
How to determine the values from a list? Eric Excel Worksheet Functions 1 March 23rd 07 06:26 AM
How to determine second (and then third) highest value in a list Scott M. Lyon Excel Discussion (Misc queries) 2 September 12th 05 11:09 PM
List ? - How do I make information in one cell determine list to u Brad_A Excel Worksheet Functions 1 January 18th 05 05:10 PM


All times are GMT +1. The time now is 11:24 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"