Finding the location of MAX value in column
I know this is a very simple question but I have found the MAX value in a
column using the usual formuls, the only problem is I don't know it's location and I need to relate back to another value on the same row. The column contains nearly 3000 readings so I don't fancy trawling through them all tring to find it! Can anyone please help asap Thanks a million |
Finding the location of MAX value in column
Hi Milly
If you know the column this will do =ADDRESS(MATCH(MAX(A1:A15),A1:A15),1) It find the max in column A. If you wanted to find the max in column C then change the ",1)" in the formula to ",C) and change the range to suit. Regards Peter "milly" wrote: I know this is a very simple question but I have found the MAX value in a column using the usual formuls, the only problem is I don't know it's location and I need to relate back to another value on the same row. The column contains nearly 3000 readings so I don't fancy trawling through them all tring to find it! Can anyone please help asap Thanks a million |
Finding the location of MAX value in column
Hi Milly
Try =ADDRESS(MATCH(MAX(A:A),A:A,0),COLUMN(A1)) would return $A$10 for example If you copy across, then A:A would change to B:B and COLUMN(B1) and so on. In other words, you need to alter both parameters depending upon which column you are searching. Add a ,4 at the end if you wish to change the formula from Absolute to relative =ADDRESS(MATCH(MAX(A:A),A:A,0),COLUMN(A1),4) -- Regards Roger Govier "milly" wrote in message ... I know this is a very simple question but I have found the MAX value in a column using the usual formuls, the only problem is I don't know it's location and I need to relate back to another value on the same row. The column contains nearly 3000 readings so I don't fancy trawling through them all tring to find it! Can anyone please help asap Thanks a million |
All times are GMT +1. The time now is 06:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com