How to find the next occurance of a logic question

**wastewater** · 05-07-2009, 01:50 PM

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.

**shg** · 05-07-2009, 01:53 PM

Welcome to the forum.

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

Thanks.

**wastewater** · 05-08-2009, 08:35 AM

File amended with copy of spreadsheet.

**wastewater** · 05-08-2009, 12:45 PM

seems like I must need some type of loop command to keep checking to see whether the progressively larger max(range)> than the column A

**shg** · 05-08-2009, 01:30 PM

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

**modytrane** · 05-08-2009, 02:21 PM

You need to give more details or better explanation.
First of all, why do you have column A listing numbers and Row 1 listing A B C?
It's confusing to understand your logic, because you are mixing Excel column designations with your column headers.

In Excel cell D2 you have a formula Max(C2:C11). Explain why that is?

It shouldn't be difficult to convert the logic in to formula as long as logic is clear.

modytrane