Finding MAX value (TRICKY!)

[pushkinpassey]

Hello,
I have been searching for this problem for the past 2 days and have had no luck! I was hoping if someone here knew the answer.

I have a 2 columns. Columns A is 1 to 24 which loops till Row 8760 (It's the number of hours in 365 days). Column B is the internal temperature in my space.

Is there a way to find the max value in Column B BUT only for values which have Column A values in the range of 1-9 and 21-24 (peak night temperature) without creating new columns?

Regards,
Pushkin

[twiggywales]

sounds like somthing you could do with an array formula could you upload a book?
.

[zbor]

try this:

=MAX(IF(ABS(15-A1:A10000)>5, B1:B10000))

comfirmed with ctrl+shift+enter (not just enter)

[Pete_UK]

Put this array* formula in C1 (for example):

=MAX(IF(((A1:A8760<=9)+(A1:A8760>=21)),B1:B8760))

The + in this situation is equivalent to OR.

*An array formula must be committed using the key combination Ctrl-Shift-Enter (CSE) rather than the usual <Enter>. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar, but you should not type these yourself. If you need to amend the formula subsequently, you need to use CSE again.

Hope this helps.

Pete

[pushkinpassey]

Hello guys! Both answers worked perfectly! Really appreciate your help. Thanks!

[Pete_UK]

So, it wasn't all that tricky !! <bg>

Thanks for feeding back.

Pete