Solved Find Cell Based On Value Of Other Cells

Microsoft Office excel 2007 home & stude...
August 9, 2010 at 18:34:39
Specs: Windows Vista
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.

See More: Find Cell Based On Value Of Other Cells

Report •

#1
August 9, 2010 at 18:42:33
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


Report •

#2
August 9, 2010 at 19:00:03
✔ Best Answer
re: then based on that result select the corresponding cell in the same coulomb but in Row1

You can not select a 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 the cols argument and return the value offset from A1 by 0 Rows and cols columns.

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.


Report •

#3
August 9, 2010 at 19:22:39
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")


Report •
Related Solutions


Ask Question