how to look up max value on another worksheet

[Lauren_Oz]

Hi!

I would really appreciate some help. I am soooo confooosed

I have a workbook with two sheets on it (see below pics).
\1

\1

On the "Consolidated Stats" worksheet, I need a formula that will scan Column C on the "Closed PECR" sheet, find everything between the dates in Columns A & B on the Consolidated Stats sheet, and then return the value from Column D on the Closed PECR sheet.

I'd ultimately like to do the same for the next column on the "Consolidated stats" sheet, being "youngest pecr" .. but I would expect this would be the same fomula just using the =MIN formula rather than MAX?

Is this even possible? Or have I wasted the last 2 days staring at my computer and trying every formula I can think of? lol.

I will be forever grateful to anyone that can help me!!

[vane0326]

Try...

Input formula in cell F2 and copy down.

Please Login or Register  to view this content.

Hope it helps!

[Lauren_Oz]

Thankyou SO much for helping vain!!

It's halfway there... I have a 0 showing ... which is better than an error message lol. But I got some sort of "Circular Reference" error.. which I have never heard of! So I now need to go and find out what that means and how I fix it! lol

[vane0326]

Its probably best to post a sample of your workbook so I could see your error.

[Lauren_Oz]

Hi Vain, thanks so much for this. I'm having some trouble attaching it for some reason

Would I be able to email the spreadsheet to you maybe?

[daddylonglegs]

Not sure Vane's suggestion has the right columns referenced, you're getting a circular reference because the suggested formula in F2 references cell F2, also you'd have difficulty replicating that formula using MIN.

Try this in F2

=MAX(IF('Closed PECR Graph Data'!$C$2:$C$100>=$A2,IF('Closed PECR Graph Data'!$C$2:$C$100<=$B2,'Closed PECR Graph Data'!$D$2:$D$100)))

This formula needs to be confirmed with CTRL+SHIFT+ENTER. To do this select the cell with the formula, press "F2" key on keyboard then hold down SHIFT and CTRL keys whilst pressing ENTER. Curly braces [ like { and } ] will appear around the formula in the formula bar and you'll get the correct result (hopefully!).

You can then copy this down the column. repeat for G2 with MAX replaced by MIN.

Obviously you may need to extend the ranges if you have data beyond row 100.....

[vane0326]

Thanks for pointing it out daddylonglegs I had the reference wrong.

Maybe try...

Please Login or Register  to view this content.

[Lauren_Oz]

Oh my gawd thankyou thankyou THANKYOU!!!

That is just brilliant!! It works perfectly! I can't even begin to tell you both how grateful I am!!

[Lauren_Oz]

I have one more question ... I've adapted the formula to show an average:

{=AVERAGE(IF('Closed PECR Graph Data'!$C$2:$C$3000>=$A2,IF('Closed PECR Graph Data'!$C$2:$C$3000<=$B2,'Closed PECR Graph Data'!$D$2:$D$3000)))}

But I am getting the #DIV/0! error. I have put in the If blah blah,"", but am still getting the error. I am positive I have closed all parenthesis but this just won't work

[daddylonglegs]

{=AVERAGE(IF('Closed PECR Graph Data'!$C$2:$C$3000>=$A2,IF('Closed PECR Graph Data'!$C$2:$C$3000<=$B2,'Closed PECR Graph Data'!$D$2:$D$3000)))}

That formula looks fine to me. It should be exactly the same as the MAX and MIN versions with AVERAGE instead. The only time you would get #DIV/0! is when there are no end dates between your A2 and B2 dates

[Lauren_Oz]

Thanks daddylonglegs. Problem seems to be that there will be instances where there was no PECR closed within some given weeks. Therefore this formula is causing the Div error. Which wouldn't be a problem, except that this table is being turned into a graph.

I need to find a way to adjust the formula to show 0 instead of the Div/0 error. Any ideas where to put it? Ive tried everything!

[daddylonglegs]

Try this

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0, AVERAGE(IF('Closed PECR Graph Data'!$C$2:$C$3000>=$A2,IF('Closed PECR Graph Data'!$C$2:$C$3000<=$B2,'Closed PECR Graph Data'!$D$2:$D$3000)))))

confirmed again with CTRL+SHIFT+ENTER

[Lauren_Oz]

All good now! Thanks again to everyone for your help!

I'm now off to find a suitable section for trouble with an Excel Add-In