Choosing cells dependant on values

[richgw]

Hi everyone hope everyone is well,

I work in science and I was wondering if anyone could help wth my excel dilema. I have attached a zipped excel file of my current sheet with a formula i use, its a rather cumbersome formula and I was wondering if excel had any other way of choosing data dependant on other values.

For those of you who haven't downloaded the xls file i'll try and explain.

Say I have value 1 (a control value that all latter values are to be compared to) of 50, i then have 5 test values: 20, 60, 63, 50, 48 and this formula works out that 20 is the biggest change from 50 (in botht the positive and negative direction) and selects this as the representative value for the group of test values. I was wondering whether there would be a slimmed down version of my formula maybe using the choose or lookup fx.

If say I want to increase the number of columns with my current formula it is an absolute nightmare!

Hers is my version below, get ready for the mass!!

=IF((ISBLANK(B3)),"",IF(ABS(A3-B3)=(MAX((ABS(A3-B3)),(ABS(A3-C3)),(ABS(A3-D3)),(ABS(A3-E3)),(ABS(A3-F3)))),B3,IF(ABS(A3-C3)=(MAX((ABS(A3-B3)),(ABS(A3-C3)),(ABS(A3-D3)),(ABS(A3-E3)),(ABS(A3-F3)))),C3,IF(ABS(A3-D3)=(MAX((ABS(A3-B3)),(ABS(A3-C3)),(ABS(A3-D3)),(ABS(A3-E3)),(ABS(A3-F3)))),D3,IF(ABS(A3-E3)=(MAX((ABS(A3-B3)),(ABS(A3-C3)),(ABS(A3-D3)),(ABS(A3-E3)),(ABS(A3-F3)))),E3,IF(ABS(A3-F3)=(MAX((ABS(A3-B3)),(ABS(A3-C3)),(ABS(A3-D3)),(ABS(A3-E3)),(ABS(A3-F3)))),F3))))))


All help would be appreciated!

[sweep]

Hi,

=IF(ISERROR(INDEX(B3:F3,1,MATCH(A3-MAX(ABS($A3-B3:F3)),B3:F3,0))),INDEX(B3:F3,1,MATCH(A3+MAX(ABS($A3-B3:F3)),B3:F3,0)),INDEX(B3:F3,1,MATCH(A3-MAX(ABS($A3-B3:F3)),B3:F3,0)))

on row 3 and copied down will work. It's an array, so enter it with CTRL, SHIFT and ENTER. (If you do this right the formula will appear in the bar with curly brackets round it {})

as your number of columns increases, alter the coloured ranges, but don't forget to confirm with CTRL, SHIFT and ENTER if you do

[richgw]

Thanks so much thats solved my problems, however I now have to get my head around arrays which im having a little trouble doing

lol thanks again