Hi, I am trying to use a cell value based on the result of other cells in a row. I have 3 rows and 12 coulombs. Row1 is the number of hours, Row2 is the energy used, Row3 is the result of Row1 divided by Row2 which is a ratio. The 12 coulombs are number 1 to 12 and are the months in the year. I need to be able to find the lowest ratio result in Row3 and then based on that result select the corresponding cell in the same coulomb but in Row1.

A posting suggestion: When you post in a help forum such as this one, please try to use a Subject Line that gives us an idea of what your question is about.

If everyone used a subject line such as Excel Help, we wouldn't be able to tell one question from another. In addition, it makes searching the archives for answers a lot harder.

I have edited the subject line for this thread.

Thanks!

DerbyDad03

Office Forum Moderator

re: then based on that result select the corresponding cell in the same coulomb but in Row1You can not

selecta cell with formulas in Excel. You can return the value of a cell based on a formula, but you can't select it.You would need to use a macro, written in VBA, to actually select the cell.

If what you really want to do is return the name of the month from Row 1 corresponding with the minimum value in Row 3, try this:

This assumes your data is in columns A:L.

=OFFSET($A$1,0,MATCH(MIN($A$3:$L$3),$A$3:$L$3,0)-1)

What this formula will do is:

MIN - Find the minimum value in A3:L3

MATCH - Return the Column number of that minimum value

OFFSET - Use the Column number returned by the MATCH function, minus 1, as thecolsargument and return the value offset from A1 by 0 Rows andcolscolumns.In other words, if the MIN value is in Column E, MATCH will return 5 and OFFSET will return the value in the cell that is offset from A1 by 0 Rows and 4 Columns.

BTW...the formula I suggested above, which returns a value from a cell, just happens to a return the name of a month because that is what is in Row 1. This formula will return the name of a month without needing to have the months in Row 1:

=TEXT(MATCH(MIN(A3:L3),A3:L3,0)*29,"mmmm")

This works by multiplying the value returned by the MATCH function (which will be 1 - 12) by 29 to get a specific value:

0, 29, 58, 87...348.

Each of those numbers is recognized as the serial number of a specific date in Excel.

The TEXT function then returns that "date" formatted with mmmm which means "the full month's name". January, February, March, etc.

mmm would return Jan, Feb, Mar, etc.

To prove this to yourself, test this:

=TEXT(MATCH(MIN(A3:L3),A3:L3,0)*29,"mmm dd yyyy")

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History