Looking for a column formula that applies to the last 10 entries in a row

[Chris_Kriek]

Hi there, 

I am looking for a formula (to apply in the cells in the red column below) that will automatically give me 1,2 * the MAX of the last 5 data entries directly to the left of a particular red cell. 

For example: Potatoes Large: I want the red column on the right to use the last 5 data entries (i.e. 7, 8, 9, 7.5 & 9), apply MAX (i.e. 9) and then multiply it by 1.2.

More specifically, if I drag the red column (with formula) 10 columns to the right, I want the formula to then only apply to the last 5 cells to the left (i.e. not reference the original 5 cells).

Basically, like a moving average (but instead moving max) that applies only to the last 5 data entries in a given row, regardless of where you drag the column... Is this possible?

I hope I'm explaining this well enough to get meaningful feedback...  

Thanks for your time ;)

[#2 · Accepted solution]

@Chris_Kriek 

Let's say the first formula is currently in G2. Enter the following formula there:

=1.2*MAX(OFFSET(G2,0,-5):OFFSET(G2,0,-1))

Fill down. The formulas will always refer to the 5 columns to the left of the cell with the formula.

[#3]

@Chris_Kriek Hans's formula uses cells to the left whether or not they have content.

For my formula, your version of Excel must support the LAMBDA function.  The LAMBDA function supports recursion – an intentional self-reference – and that's how the calculation can advance through multiple cells, calculating along the way. The use of LAMBDA for recursion is barely mentioned in the Microsoft article, so for a description of that, see also Excel LAMBDA function: how to write and use.

To allow this recursion, the custom function is defined in the Name Manager under the Formulas menu item. I named this custom function MaxOf5ValuesToLeft. (Yes, I try to use descriptive names.)  The copy shown on the Formulas worksheet is for development and study; it is not functional.

=LAMBDA(max_so_far,count_so_far,cell, LET(
    done, IF( OR(ISTEXT(cell),ISFORMULA(cell)), TRUE, FALSE),
    new_max, IF( ISBLANK(cell), max_so_far, MAX(max_so_far, cell) ),
    new_count, IF(ISBLANK(cell), count_so_far, count_so_far + 1),
    result, IF( done, max_so_far, IF(new_count>=5, new_max,
        MaxOf5ValuesToLeft(new_max,new_count,OFFSET(cell,0,-1,1,1)) ) ),
    result) )

(If your version of Excel does not support the LAMBDA function, this calculation could be done in a custom VBA function.)

Assumptions:

• Blank cells to the left are ignored.
• The formula can stop looking when it encounters a text value (perhaps the name of the produce) or a formula (perhaps another red-column formula).
• The formula can stop looking when it has examined five (5) non-blank, non-text, non-formula values.

In the attached workbook, I multiplied the custom function's return value by 1 just to make it easier to show the result of the custom function. You, of course, will multiply by 1.2 or any other appropriate value.

(For your future posts, please attach a workbook, so we don't have to type and format your data. And mention which version of Excel you are using.)

Edit: terminology correction and… I did not set up those formulas in column V as well as I could have.  For ease of dragging those around, refer to OFFSET(V2,0,-1) instead of U2.

[#4]

Thanks so much, that works perfectly!

[#5]

Thank you, Snowman. This works perfectly in Excel.

[#6]

@Chris_Kriek 

Perhaps this:

=MAX(TAKE(TOROW(B2:N2,1),,-5))*1.2