What was the average of their last three sales

  • Ok say I have multiple sales men and every time they post a sale I write it in a list.


    So for example,
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 67, align: right"]1[/TD]
    [TD="width: 67, align: right"]21[/TD]
    [TD="width: 67"]John[/TD]

    [/tr]


    [tr]


    [TD="align: right"]2[/TD]
    [TD="align: right"]30[/TD]

    [td]

    John

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]3[/TD]
    [TD="align: right"]50[/TD]

    [td]

    Jack

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]4[/TD]
    [TD="align: right"]20[/TD]

    [td]

    Paul

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]5[/TD]
    [TD="align: right"]10[/TD]

    [td]

    John

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]6[/TD]
    [TD="align: right"]40[/TD]

    [td]

    John

    [/td]


    [/tr]


    [/TABLE]

    I want to know the average of Johns last three sales at t = 6, which is 26.66668
    I also want to know the average of Johns last three at t = 5, which is 20.33333


    I want to be able to drag the formula down.


    Thank you in advance.


    Sam

  • If you place those values in the range A1:C6, enter this as an array formula in D1:

    Code
    1. =(B1+OFFSET($B$1,LARGE(IF($C$1:$C1=C1,ROW($C$1:$C1)-MIN(ROW($C$1:$C1))+1),2)-1,0)+OFFSET($B$1,LARGE(IF($C$1:$C1=C1,ROW($C$1:$C1)-MIN(ROW($C$1:$C1))+1),3)-1,0))/3


    and then drag down to D6.


    Entering as an array formula means using Ctrl+Shift+Enter after typing (or copying) the formula instead of just using the Enter key. After entering a formula as an array formula, Excel will show the formula enclosed in curly braces.

  • Did you enter the formula with CTRL+SHIFT+ENTER (not just ENTER) before dragging down? Did you change the ranges to suit your real data? Attach a sample workbook if you can't get it to work.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • You will get #NUM when there are not three values to average, e.g., in the first four rows of the example. (Note that you only defined what values you wanted for the last two rows.) If you want something other that that error, you can wrap the formula with IF(ISERROR( functions.


    I saw that my post was confusing, because the formula was actually for the range A1:B6 (leaving out your first, "time," column). I edited it for where the sale amounts are column B.