# Dynamic Range?

#### alecloudenback

##### New Member
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.

Thanks!

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### Gerald Higgins

##### Well-known Member
A bit confusing, but to calculate the MAX in Col B so far, try
Code:
``=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

##### Well-known Member
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.

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

##### New Member
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!

Replies
3
Views
210
Replies
21
Views
506
Replies
6
Views
658
Replies
31
Views
729
Replies
10
Views
778

1,191,483
Messages
5,986,848
Members
440,053
Latest member
jhollingworth

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back