Base the value of one cell on another

[cmartin2459]

I have a spread sheet where A5:A237 are dates. Columns B5237 are
inputted numbers based on our water usage at 3 different times during
the day.

Cell C2 is =MAX(B5231)

I need cell C3 to look at the value in cell C2, search for this value
in cells B5237, find the corresponding date in cells A5:A237 and put
that date into C3.

I can't find a function to do this, anyone have any ideas?

 

[a7n9]

Not very efficient but you can try this formula:


Code

 

[Bernie Deitrick]

Array enter (enter using Ctrl-Shift-Enter) the formula

=INDEX(A:A,MAX((B5237=MAX(B5237))*ROW(B5237)))

Format as a date.

HTH,
Bernie
MS Excel MVP

 

[cmartin2459]

Bernie,

That worked like a charm! Thank you so much.
One more question--why did I have to do the Control-Shift-Enter to mak
it work?

Again, Thank you!

 

[Bernie Deitrick]

That worked like a charm! Thank you so much.

You're welcome.

One more question--why did I have to do the Control-Shift-Enter to make
it work?

That is how Excel knows to treat the ranges inside the function as arrays, where each element is
evaluated seperately, rather than just as a range, where (often) all elements are handled at once.
For more on array formulas, visit

http://www.cpearson.com/excel/array.htm

HTH,
Bernie
MS Excel MVP

 

[cmartin2459]

I went into the site on Arrays. I'll probably look at it again tonight
when I have more time at home. But I keep looking at your formula
trying to figure out what its doing. For instance why does the max
function have to be there twice? Would you mind going through it from
the beginning to end and explain the steps? I hate to just copy an
idea and not be able to figure out how to do it. I might need to do it
again.
Thanks for your patience and help!

 

[Bernie Deitrick]

Let's shorten the formula from

=INDEX(A:A,MAX((B5237=MAX(B5237))*ROW(B5237)))

to

=INDEX(A:A,MAX((B5:B7=MAX(B5:B7))*ROW(B5:B7)))

with the values 3,6,5 in B5, B6, and B7, so that the max value is in cell
B6. Excel would interpret that formula as

=INDEX(A:A,MAX((B5:B7=MAX(B5:B7))*ROW(B5:B7)))
=INDEX(A:A,MAX(({3,6,5}=MAX(B5:B7))*ROW(B5:B7)))
=INDEX(A:A,MAX(({3,6,5}=6)*{5,6,7}))

This part is the array:

MAX(({3,6,5}=6)*{5,6,7})

Which Excel would evaluate to

MAX({False,True,False)*{5,6,7})
which becomes (element by element in the multiplication)
MAX({0,6,0})
or just
6

Then with the INDEX part, that becomes

=INDEX(A:A,6)

it would return the value from A6, which is the cell next to the MAX value
in B6.

HTH,
Bernie
MS Excel MVP


"cmartin2459" <[email protected]>
wrote in message
news:[email protected]...

 

[cmartin2459]

Now I can see what it's doing. I can't believe that I actually get it
Your explaination is great. I can't think you enough, I love learnin
what can be done in Excel. But most people find it easier to just do i
for me instead of showing me how it's done. Normally when I have
problem I just keep trying in a hit and miss sort of manner and us
Excel's help. But today I had to have an answer fast and it reall
paid off when I posted this. I don't think I could've figured it out o
my own. You've spurred me on to keep trying new things. Thank you