Trying to get max of a column from a range of cells in a different column.

[sheaww200571]

Hello, hoping someone can give me some help with this spreadsheet from work. In the circled cell I need the max number in column B. But not the entire column, only the range that’s shown in the cells with the arrows over them. So in this instance, I need the max of B2:B122. Is it possible to input the range I need in these two cells and it carry over to give me the max in the circled cell?

 

[jasonb75]

You could try something like

=MAX(INDEX(B:B,A1):INDEX(B:B,A2))

Where A1 and A2 refer to the 2 cells work the yellow arrows in your image.

 

[sheaww200571]

I’ll try this and report back, thank you!

 

[sheaww200571]

That worked great! Thank you so much!! One more question, With the formula you provided on some of the slots where I don’t have data for the day and put a “zero” in my two range cells it’s giving me the MAX for the entire B:B column. Is there a way to make the circled cell show zero as well?

 

[Fluff]

How about like

=IF(OR(A1=0,A2=0),0,MAX(INDEX(B:B,A1):INDEX(B:B,A2)))

 

[sheaww200571]

This worked perfect..thank you guys so much for your help!!

 

[Fluff]

Glad we could help & thanks for the feedback.