how do you write a formula, so that if you have a sum made up of say 5 parts (all revenue numbers). and two of those parts are deductions from your total (the sum of 5 parts). Say year of year, that sum of five parts decreased by 5%. if you can isolate parts 1 and 2 to say make up 2% of that 5%...how do you write a formula so that the remaining 3 revenue parts, weighted to add up to the remaining 3% variance. but within those 3 revenue parts, two are negative values...where one increases yoy and the otehr decreases (meaning say rev 4 was -100 in 2019 and is now -200 in 2020...whereas rev 5 was -100 in 2019 and is now -80 in 2020.
The issue im having is the weighted average does not work when the negative components of my total income are moving in different directions.
Say Total Rev (1-5) = 5%
Rev 1 = 4%
difference = 1%...I want the formula to write so that rev's 2-5 all add up to = the 1%. But again with the issue that say 4/5 are negative values that move in oppositve increase/decrease directions year over year.
The issue im having is the weighted average does not work when the negative components of my total income are moving in different directions.
Say Total Rev (1-5) = 5%
Rev 1 = 4%
difference = 1%...I want the formula to write so that rev's 2-5 all add up to = the 1%. But again with the issue that say 4/5 are negative values that move in oppositve increase/decrease directions year over year.