Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine the value?
Referring to the post in General Question
Does anyone know on how to determine the value in following case? In cell A1 = 10 In cell B1 = 12 In cell A2 = 15 In cell B2 = 17 In cell A3 = 20 In cell B3 = 23 I would like to determine which pair [A & B] will be the minimum difference, let take the abs of the difference in order to avoid negvative results. In this case, abs(12-10) = 2 and abs(17-15) = 2, there are 2 pairs of value to meet the minimum difference between A & B columns. Then I would like to determine the minimum median value from those pairs, in this case (12+10) = 11, then the 11 will display in cell C1. Does anyone have any suggestion on how to do this calculation in excel? Thank you in advance Eric Choi |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine the value?
Try this:
Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =MIN(IF(ABS(A1:A3-B1:B3)=MIN(ABS(A1:A3-B1:B3)),SUBTOTAL(1,OFFSET(A1:B3,ROW(A1:B3)-1,,1,2)))) Biff "Eric" wrote in message ... Referring to the post in General Question Does anyone know on how to determine the value in following case? In cell A1 = 10 In cell B1 = 12 In cell A2 = 15 In cell B2 = 17 In cell A3 = 20 In cell B3 = 23 I would like to determine which pair [A & B] will be the minimum difference, let take the abs of the difference in order to avoid negvative results. In this case, abs(12-10) = 2 and abs(17-15) = 2, there are 2 pairs of value to meet the minimum difference between A & B columns. Then I would like to determine the minimum median value from those pairs, in this case (12+10) = 11, then the 11 will display in cell C1. Does anyone have any suggestion on how to do this calculation in excel? Thank you in advance Eric Choi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to determine the value? | Excel Discussion (Misc queries) | |||
How to determine the value? | Excel Discussion (Misc queries) | |||
How to determine the first two carachters in a name | Excel Discussion (Misc queries) | |||
How can I determine the components of a sum? | Excel Discussion (Misc queries) | |||
How to Determine 1st, 2nd & 3rd for a PWD | Excel Discussion (Misc queries) |