Been banging my head against this one for a while, and can't quite get it sorted, so hopefully someone with a finer mind than mine can help!

I have a list of dated historical orders, with quantities, and costs per item...

Qty | Cost Per Item | Order Date |
---|---|---|

100 | 8.00 | 08/01/2016 |

200 | 7.00 | 20/12/2015 |

100 | 8.20 | 10/11/2015 |

100 | 8.00 | 23/09/2015 |

The number of entries in the historical orders table above can obviously range from 0 up to a few hundred.

The user wants to find out what the mean average price per item has been for the past X number of items purchased, across all historical orders, when sorted by order date (descending).

So, for the historical orders above, if the user wanted to look at the last 350 orders, we'd be looking for...

100 X 8.00 +

200 X 7.00 +

50 X 8.20 <---- 50 is used as opposed to 100, as there is only 50 remaining from the specified 350 after the previous two orders...

/ 350

= 7.457

Caveats:

This needs to be done as part of a calculation, as opposed to a looping script (if possible). I'm guessing this requires a recursive funciton of some sort?

I've tried looking through various custom function sites, but I can't really articulate what I'm after above into a meaningful search term, so I'm a bit stuck!

ANY HELP GRATEFULLY RECEIVED!

To add some clarity here, I have got this working in a script, by passing the relevant parameters as values, which returns the mean average as a script result, but I need this to be a calculation...

# Find the mean average of the values of ListA multiplied by corresponding values in ListB

# Only uses as many 'rows' in the lists as defined by the value defined by 'FindValue'

# Define Variables:

# FindValue is single number value.

# ListA is a commaseperated list of numbers

# ListB is a commaseperated list of numbers

# Script Exits with the Mean Average

Set Variable [ $initialfind ; Value: gsp (1) ]

Set Variable [ $listqty ; Value: Substitute ( gsp (2) ; "," ; "¶" ) ]

Set Variable [ $listcosts ; Value: Substitute ( gsp (3) ; "," ; "¶" ) ]

Set Variable [ $counter ; Value: 0 ]

Set Variable [ $runningtotal ; Value: 0 ]

Set Variable [ $find ; Value: $initialfind ]

Loop

Set Variable [ $counter ; Value: $counter +1 ]

Set Variable [ $qty ; Value: GetValue ($listqty ; $counter) ]

Set Variable [ $cost ; Value: GetValue ($listcosts ; $counter) ]

If [ GetAsNumber ( $find ) ≥ GetAsNumber ( $qty ) ]

Set Variable [ $runningtotal ; Value: $runningtotal + ($qty * $cost) ]

Set Variable [ $find ; Value: $find - $qty ]

Else

Set Variable [ $runningtotal ; Value: $runningtotal + ($find * $cost) ]

Set Variable [ $exit ; Value: 1 ]

End If

Exit Loop If [ $exit = 1 ]

End Loop

Set Variable [ $meancost ; Value: $runningtotal / $initialfind ]

Exit Script [ Result: $meancost ]