Max If Until a Certain Point

[Fletch74]

Hi,

Just wondering if I can get some help re a spreadsheet that I want to calculate the Max of a certain item up until a certain point. In my attached sheet, in cell B2 I want the Maximum of the TEEMO column until TEEMO is greater than the LOW column.

I have used the formula;

Please Login or Register  to view this content.

However, I want the formula to be dynamic as the prices will change every day. How do I get this to happen, it seems simple, but I just can't nail it...

Thanks

[karedog]

Hi,

This is the formula for cell B2 :

Please Login or Register  to view this content.

end this formula with holding Ctrl-Shift-Enter all at once (this is an array formula)

To copy this formula, press Ctrl-C on cell B2, then block B3:B11 and press Ctrl-V

[Fletch74]

Thanks for your response karedog, but it produced a result of 33.54.

It needs to produce a result of 36.70 which is the Maximum of the TEEMO column up to E9, which is when the TEEMO column is > the LOW column. the formula needs to be Dynamic to change when TEEMO>LOW

Thanks for your effort though

[Punnam]

Hi Fletch ,

I have a question for you , let me know how many occurrence of "TEEMO>LOW" can be happened in your real data , in case more than 1 what should be happen ?

Punnam

[Fletch74]

It can be multiple times, it just happens to be once in this example.

I just want the MAX calculation to stop when it reaches that point.

Thanks,

[samba_ravi]

=IFERROR(MAX(OFFSET($E$2,0,0,MATCH(TRUE,$E$2:$E$11>$D$2:$D$11,0)-1,1)),"")
try this array formula {Ctrl+Shift+Enter}

[Fletch74]

Brilliant nflsales

I have just added a condition for when Column E < Column D, but thanks

Fletch

[Punnam]

hi Fletch74,

Please updated the revised sheet with function ,so that i would like to have look .

Punnam

[samba_ravi]

Thanks Fletch,
for your reply and adding reputation

[karedog]

Thanks for your response karedog, but it produced a result of 33.54.

It needs to produce a result of 36.70 which is the Maximum of the TEEMO column up to E9, which is when the TEEMO column is > the LOW column. the formula needs to be Dynamic to change when TEEMO>LOW

Thanks for your effort though

You really should say "when" instead of "until" in your sentence : until TEEMO is greater than the LOW column.

In that case, it is as easy as change the less than sign with greater than sign

Please Login or Register  to view this content.

[Fletch74]

@ Punnam - File attached

@ nflsales - no problem

@ karedog - That formula is still not right as I wanted $36.70 to show if LOW > Teemo up until it's not, your formula doesn't show $36.70 all the way down, plus I need the formula to cover all 11 rows, not just 9. Thanks for your effort though, I did give you some reputation

Fletch

[Punnam]

@ Fletch74

Thanks for the attachment

Punnam