cancel
Showing results for
Did you mean:  Helper III

## Calculating an average value based on partial collection

Hi superusers,

Looking for some help calculating

I have the following table/collection

 Order Nr Date Qty Selling Price Total Price 1 30-Jun-21 100 \$         5.00 \$     500.00 2 15-Jun-21 50 \$         6.00 \$     300.00 3 7-Jun-21 75 \$         7.00 \$     525.00 4 2-Jun-21 25 \$         7.50 \$     187.50 5 15-May-21 100 \$         5.00 \$     500.00 6 7-May-21 150 \$         4.00 \$     600.00

I need to calculate the average selling price of the most recent sales of 300 items (top 4 rows and 1/2 of 5th row)

In other words

=((100*\$5.00)+(50*\$6.00)+(75*\$7.00)+(25*\$7.50)+(50*\$5.00))/300 = \$5.88

Any assistance greatly appreciated

1 ACCEPTED SOLUTION

Accepted Solutions  Community Support

Hi @KarlNixon .

You can use this formula to get the total price of these 300 items:

``Sum(FirstN(collection1,5),Price)-Last(FirstN(collection1,5)).Price/2``

You can use this formula to get the number of items(the result is 300):

``Sum(FirstN(collection1,5),Qty)-Last(FirstN(collection1,5)).Qty/2``

Then you can use this formula to get the average selling price:

``(Sum(FirstN(collection1,5),Price)-Last(FirstN(collection1,5)).Price/2)/(Sum(FirstN(collection1,5),Qty)-Last(FirstN(collection1,5)).Qty/2)``

If you want to make the result into a 2 decimal numbers, you can use this formula:

``Text(XXX,"#.##")``
``Text((Sum(FirstN(collection1,5),Price)-Last(FirstN(collection1,5)).Price/2)/(Sum(FirstN(collection1,5),Qty)-Last(FirstN(collection1,5)).Qty/2),"#.##")`` Best regards,

Allen  Community Support

Hi @KarlNixon .

You can use this formula to get the total price of these 300 items:

``Sum(FirstN(collection1,5),Price)-Last(FirstN(collection1,5)).Price/2``

You can use this formula to get the number of items(the result is 300):

``Sum(FirstN(collection1,5),Qty)-Last(FirstN(collection1,5)).Qty/2``

Then you can use this formula to get the average selling price:

``(Sum(FirstN(collection1,5),Price)-Last(FirstN(collection1,5)).Price/2)/(Sum(FirstN(collection1,5),Qty)-Last(FirstN(collection1,5)).Qty/2)``

If you want to make the result into a 2 decimal numbers, you can use this formula:

``Text(XXX,"#.##")``
``Text((Sum(FirstN(collection1,5),Price)-Last(FirstN(collection1,5)).Price/2)/(Sum(FirstN(collection1,5),Qty)-Last(FirstN(collection1,5)).Qty/2),"#.##")`` Best regards,

Allen Announcements   