3

I have a Google Spreadsheet in which I am comparing projectors. Basically it has formulas to divide the bulb life/cost and contrast ratio/cost. Then it adds the total of the two and gives me a number. Using the max function I can determine which is the largest number in the set, but I would like to know if there is any formula to select the column header at the top based on the number.

If the highest number is under an Epson 2000. I want a formula to make it say Epson 2000.

Improve this question

1 Answer 1

Reset to default
3

You can do this with a combination of the INDIRECT and MATCH functions.

In the last column of your table add the following formula in line with the data that you would like to determine the max of:

Google Spreadsheet

=INDIRECT(ADDRESS([Name_Row], MATCH(MAX([Data_Span]), [Data_Span], 0) + [Column_Offset]))

Excel

=INDIRECT(ADDRESS([Name_Row], MATCH(MAX([Data_Span]), [Data_Span]) + [Column_Offset]))

Where [Name_Row] is the index of the row where your projector names are (e.g. 1 if the projector names are in row 1), [Data_Span] is the span of values that you're trying to find the max of, and [Column_Offset] is the distance of your first data value from column A (e.g. 1 if your data starts in column B).

See picture here.

Improve this answer
2
  • So i tried to use this formula and thought i understood it, but can you take a look again and see if i did something wrong. link
    – Isaac Brekke
    Dec 31, 2015 at 18:26
  • 1
    @IsaacBrekke you should have mentioned that you were using google docs and not Excel. Google docs formulas are almost the same but not exactly. For this to work in google docs you need to add a third argument to the Match formula to tell it that the list is not sorted. The final formula would then be. =INDIRECT(ADDRESS([Name_Row], MATCH(MAX([Data_Span]), [Data_Span], 0) + [Column_Offset]))
    – kgoodrick
    Dec 31, 2015 at 19:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.