CODEWITHSUNDEEP
excelexcel-formula
dataowl
dataowl

Reputation: 21

Excel formula for position of minimum value using embedded calculations for arguments

I have this data in Excel

A1 = 12
A2 = 10
A3 = 1
A4 = 6

I would like to return the position of the A1:A3 value that is the shortest distance from A4. Therefore you need to compare the ABS(A1-A4) to ABS(A2-A4) to ABS(A3-A4). The answer in this case is 2.

From Excel formula position of minimum value

the answer should be

=MATCH(2,1/FREQUENCY(MIN(ABS(A1-A4),ABS(A2-A4),ABS(A3-A4)),(ABS(A1-A4),ABS(A2-A4),ABS(A3-A4)))

That returns an error.

Another solution should be

=MATCH(MIN(ABS(A1-A4),ABS(A2-A4),ABS(A3-A4)),{ABS(A1-A4),ABS(A2-A4),ABS(A3-A4)},0)

That also returns an error. I know I can calculate the above in multiple steps(cells) or VBA but I need to have one formula that returns the answer. Thanks.

Upvotes: 0

Views: 66

Answers (1)

A.S.H
A.S.H

Reputation: 29352

You need the following simple array formula, enter it then press CtrlShiftEnter

=MATCH(MIN(ABS(A1:A3-A4)), ABS(A1:A3-A4),0)

CtrlShiftEnter

Upvotes: 3

Related Questions