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 #.
Solved! Go to Solution.
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?
@binuacs 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 | 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
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.
Thanks! Pretty similar to what I ended up doing myself though admittedly mine is more....barbaric 🤣
User | Count |
---|---|
107 | |
82 | |
70 | |
54 | |
40 |