How to find the next occurance of a logic question

I am normally pretty good about figuring these things out but this is beyond my abilities. In the columns below B is just 95% of A. What I want to do in column C is to put the maximum value of B over the range that corresponds until the value in A is less than that. For example for my 1st run the value would be $1,125.68 because this is the maximum amount before you fall below that in column A, or in this case hits $1,106.40. I want to be able to perform this automatically down the line (expanding the range until the logic test is true) but can't figure out how to do this. The formula would basically be the max of the range in Column B is > Column A until this is not true, then return the max in Column B for entry in Column C. Then in Column D I would run an identical analysis but return the Row identifer for the dataset. Any help is appreciated.

Welcome to the forum.

If you want help with Excel, please post workbooks and not pictures.

Thanks.

I don't understand your explanation, sorry. Someone else may.

I apologize for not explaining this well and will give it another shot.

Regarding the naming, I eliminated some of that because it was confusing and resaved the spreadsheet here.

My inputs in column C show the number I want to return, not how to get there. I have tried IF then statements but as this can go beyond the 7 or so that Excel allows I gave up on that alternative.

I want to populate Column C based on the following logic. For row one, the 1st iteration would see if the max of (B1:B2)>A2. Since it is not, the next iteration would see if the max of (B1:B3)>B3 and so on until this condition proves true. In this case, it occurs at row 10, when the maximum of (B1:B10) is equal to $1125.68, and is greater than A10, which is equal to $1085.78. Therefore, $1125.68 is entered into cell C1.

In row two the same process occures, i.e the 1st iteration compares the max of (B2:B3)>B3. The next iteration checks max of (B2:B4)>B4 and so on until the condition proves true, which in this instance also occurs at A10, so $1125.68 is entered into cell C2.

This is repeated for row 3, although in this case the value entered into C3 is $1120.25 because this is the max of (B3:B10).

Process is repeated for row 4, and in this case the greater than situation does not occur until the series analyzed reaches row 11, i.e. max of (B4:B11) is $1103.43 and is greater than A11, which is $1085.78.

Column C would continue to be populated for as many points as I have, but the evaluation needs to proceed sequentially for each cell.

StephenR,
Thanks very much for your input. I am new to VBA so am struggling through. I was not able to get your code to work so have tried a few different things without success. I tried to label this as a function so that I can place it directly in the cell I would like to return the number (C1 for instance) and got rid of the absolute reference to B1 but am not able to get this to work.

Please Login or Register  to view this content.

Please add code tags - see "how to" at the top of the page.

That's not right is it? Closing tag is [/code]