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 #.
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.
I managed a solution but it's rather ugly and if there's a better way I'd still like to explore :-)
@moblender what is the calculation for the values 2,229.58 and 92,867.47?
@binu_acs The 92,867.47 is just a sum total. 2,229.58 is (66,513.14/92,867.47)*3,113
Do you have a sample dataset to look at?
Raw Data example
Sale Amount
What I'm after is the allocated expense
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
Thanks! Pretty similar to what I ended up doing myself though admittedly mine is more....barbaric 🤣
