Excel Forum Help Please
Hi
I have a 4000+ row sheet.
I have 3 values in cells in a row so for example A1 9.5, A2 -4, A3 -11
I need to be able to work out which 2 of the 3 values are furthest from zero, so in the above example it would be A1 at 9.5 and A3 at -11
Once highlighted then need if possible to be able to rank those two values so A3 first and A1 second and be able to apply labels depending on which cell it came from so if it came from A3 it would need to be labelled D1
I attach a sample file which I hope shows this better than I have explained it and further explanation is on the sheet, I have entered it manually for a few rows to give the idea
| Attachment | Size |
|---|---|
| Excel sample explanation.xlsx | 10.93 KB |
messy but works
There could be a quicker way to do this but I have done the following. in columns T:V I have entered the following formula and copied down:
=IF(G1<0,0-G1,G1)
in column W enter the following:
=IF(LARGE($T1:$V1,1)=$T1,1,IF(LARGE($T1:$V1,1)=$U1,2,3))
In column X enter the following:
=IF(LARGE($T1:$V1,2)=$T1,1,IF(LARGE($T1:$V1,2)=$U1,2,3))
You can now hide T:X (or whatever area you decide to use.
Change the format of columns K:L from text to general then enter the following:
=IF(INDEX($G1:$I1,1,W1)<0,"L"&W1,"B"&W1)
Like I said a little messy but does what you want
Regards
Mark