Would you know how to crack this one up?
Say:
B
1 Year Start Cash is: 10,000
2 End of Year Target: 20,000
3 Current Balance is: 16,000
4 Target % Complete 60% (should be)
What would be a formula to find what % of 'end of year target' the 'current balance' is?
And opposite would be:
B
1 Year Start Debt is: 20,000
2 End of Year Target: 10,000
3 Current Balance is: 16,000
4 Target % Complete 40% (should be)
Tricky...
Last edited by vill; 07-18-2017 at 03:37 PM.
Isn't that justWhat would be a formula to find what % of 'end of year target' the 'current balance' is?
=CurrentBalance/EndOfYearTarget
Thats what I thought at first! But here is a problem:
Say current balance is 10,000, target is 20,000. So =CurrentBalance/EndOfYearTarget would return 50%. Good.
But what if Year Start Balance was 10,000 and current balance is also 10,000, then Target % Complete is 0%
It gets even more challenging when trying to find formula for debt reduction balances..
Then maybe
=(CurrentBalance-YearStartBalance)/EndOfYearTarget
Nope, with this approach if year start is 10,000, target is 20,000 and current is 15,000 -
the result I get is 25%. When it should be 50%, as its already half-way there
2017-07-19_0032.png
=(CurrentBalance-YearStartBalance)/(EndOfYearTarget-YearStartBalance)
another just add in 1- to get the opposite value
=1-(CurrentBalance-YearStartBalance)/(EndOfYearTarget-YearStartBalance)
PS: if the first formula more than 100%
second formula will have negative value
Hope you can learn every time you visit here.
If you still confuse on how it work, kindly ask or go to
i) Formula - Formula (Ribbon) > Formula Auditing (Section) > Evaluate Formula > Evaluate; or
ii) VBA/Code - Click F8 to see how it work step by step.
It it take care of your question, Please:
Mark tread as [Solved] [Thread Tools->Mark thread as Solved];and
Click *Add Reputation to thank anyone solved your question.
That was exactly it. Thank you so much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks