A problem has arisen with this spreadsheet I do monthly analyzing net income 30 branch locations for my company. I need a formula that will calculate actual net income as a % of budget at each location. Obviously, when both numbers are positive, this is simply 'actual/budget'. But I need something that will calculate the correct % regardless of positive or negative values.
For example, 1 branch has an actual net income of 10,000 and a budget of 8,000; another branch has an actual of (2,000) and a budget of 5,000 while yet another branch has an actual of (15,000) and a budget of (10,000). My problem arises because the 'actual/budget' for the second and third is not accurate since it creates a negative % or a % over 100% respectively. Is there a formula that could accurately calculate the percentage of budget for all three of these without having to modify anything? I'm thinking of a complex "if" statement, but I can't figure out how to put it. Thanks for any input.
Bookmarks