Dynamic Range?

[alecloudenback]

I want to create a formula in column C that refers to the maximum value in column B, up to that row. I.e. I would like a formula that equates to MAX(B8:*current_row*) where *current_row* is the whatever row the current formula in column C is in.

This is what I am *trying* to do: =IF(B9>MAX(B8:B9),B9*0.05,C8). So if the value in B9 is the largest value up to that point, then it takes 5% of B9, otherwise it takes the previous C value, in this case, C8. And I want the relative positioning of the referenced cells to stay the same, except for B8 to stay the same and B:9 be whatever the current C row is.

I hope that made sense...

Thanks!

 

[Gerald Higgins]

A bit confusing, but to calculate the MAX in Col B so far, try

=max(B$1:B1)

and copy down as far as required.

By the way, you say IF(B9>MAX(B8:B9 . . .
How is it possible for B9 to be greater than the maximum value of a range including B9 ?
Either B9 is the maximum value in that range, in which case it can't be greater than itself, OR it isn't the max value in that range.

 

[facethegod]

I want to create a formula in column C that refers to the maximum value in column B, up to that row. I.e. I would like a formula that equates to MAX(B8:*current_row*) where *current_row* is the whatever row the current formula in column C is in.

This is what I am *trying* to do: =IF(B9>MAX(B8:B9),B9*0.05,C8). So if the value in B9 is the largest value up to that point, then it takes 5% of B9, otherwise it takes the previous C value, in this case, C8. And I want the relative positioning of the referenced cells to stay the same, except for B8 to stay the same and B:9 be whatever the current C row is.

I hope that made sense...

Thanks!

I don't think the first condition of the If statement would ever be true, because if a value is the max of the range at a point, then it can't be greater than the max, if a value is not the max of the range at a point then it can't be greater than the max....

IF(B9>MAX(B8:B9),B9*0.05,C8)...

 

[alecloudenback]

That's my fault. The B9 in the ...B9>MAX... should reflect the same row as whatever row it is currently in.

And I will try your suggestion Gerald. Thanks!