Calculation Help

[moblender]

W	X	Y	Z
1001		66,513.14	2,229.58
#	3,113		92,867.47

What I'm looking for is a way to find the values in green where the excel formula is Y/$Z$*$X$ and then to restart at every instance of #. 

[moblender]

I managed a solution but it's rather ugly and if there's a better way I'd still like to explore :-)

[binu_acs]

@moblender what is the calculation for the values 2,229.58 and 92,867.47?

[moblender]

@binu_acs The 92,867.47 is just a sum total. 2,229.58 is (66,513.14/92,867.47)*3,113

[martinson]

Do you have a sample dataset to look at?

[moblender]

Raw Data example

Sale Amount	22,191.78
Sale Amount	10,930.34
Expense	8,660

What I'm after is the allocated expense

		Allocated Amount
Sale Amount	22,191.78	5,802.19
Sale Amount	10,903.34	2,857.81
Expense	8,660
Sum of Sales	33,122.12

For each group of sales amounts there is an expense. The expense lines are denoted in my dataset with a #. I want to be able to sum the sales until each # is reached and then, create the allocation amounts based on the expense and total of the sales. Allocated amount is (Sales/Total)*Expense

[martinson · Accepted solution]

I'm still not sure how your data looks, however, this is what I have come up with. Please let me know if it helps or if you need anything else.

[moblender]

Thanks! Pretty similar to what I ended up doing myself though admittedly mine is more....barbaric 🤣