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

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.