Sum of items: Get sum of all items to calculate one item
Hi there,
I very often come along this question, which should be simple to answer. Each time I fail. And do an easy workaround. BUT … how can this be done nicely?
Typical examples:
- Calculate ratio of total: Ratio:Item1 = Value:Item1 / sum (All Items)
- Re-Scale percentage User-Input to 100% (My case below)
Final:Percentage:Comp1 = Unscaled:Percentage:Comp1 / sum (Unscaled:Percentage:AllComponents)
(I could write all components like “Comp1..Comp7”, but this is not scaling when category grows.)
I tried – unsucessfully: forcelist ….Any ideas???
Thanks, Gilbert
Hi S A U,
Thank you very much for your suggestion. It perfectly works!
Lyndsey earlier suggested to me to use “summary”.
It looks to me that all three variants
Final = Unscaled / sum ( forcelist (Unscaled:Comp:Percentage) )
Final = Unscaled / sum ( summary (Unscaled:Comp:Percentage))
Final = Unscaled / sum (Unscaled:Comp:Percentage)
work exactly the same. Thus “summary” / “forcelist” are not needed (there might be cases which a simple “sum” does not work, and these additional functions might help, I don’t know. If somebody has case, please show us the example).
BUT: the important bit – and this was the point I always struggled with: “sum” can only work if you tell it along which category to take the sum. So in my case, the magic is to add the category “Comp” in the cell reference.
I compiled a very simple model to show different cases (sum, average, and sum along two categories) for better understanding. Hope this helps somebody.
And S A U: you won the prize again! Thanks.
Gilbert
Honestly, this is not a marketing gag. But seems that always if I think hard how I could explain a problem to somebody (e.g. the board), it clears my head and I find the solution. So hope this helps somebody:
The solution is:
in Percentage, Final = Unscaled / sum ( forcelist(Percentage:Unscaled:Comp))
So: all fixed items + the category that should be repeated go into the “forcelist” call.
Easy, no? 🙂
Gilbert