LinkBack URL
About LinkBacks
I am trying to find a ratio in which one of the numbers is negative. My example is as follows: My revenue is up 40% and my Expenses are down -6.5%. What is that ratio? I cannot figure out how to do this in Excel.
How to find a ratio with a negative number
I am trying to find a ratio in which one of the numbers is negative. My example is as follows: My revenue is up 40% and my Expenses are down -6.5%. What is that ratio? I cannot figure out how to do this in Excel.
... you mean *(1- 6.5%) or something like that?
Click the [* Add Reputation] Button to thank people for help!
Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.
A ratio is another way of expressing a % but it's not clear what statistic you are trying to demonstrate.
Here's a small example of several resulting %s based on your % changes to revenue and expenses.
What are you wanting to show?
Capture.JPG
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
I think I want Change in Margin as % of Margin, but I'd like to see all the calculations please.
Hi
See attached
I think the meaningful interpretation is that productivity (revenue per expense dollar) is up about 50%:
=(1 + 40%) / (1 - 6.5%) - 1 ~ 49.7%
Entia non sunt multiplicanda sine necessitate
I am looking to obtain a ratio, not a percentage.
OK!
What is the actual math you're trying to accomplish? Don't worry about excel syntax, just give us the equation you need to calculate.
Okay, here are some numbers to use for the calculation. Revenue FY17 - $10,000. Revenue FY18 - $14,000. Expenses FY17 - $1000. Expenses FY18 $930. I want to see the change in Revenue to Expenses expressed as a ratio Year over Year.
No, I'm not asking for "what numbers to use in the calculation", I'm asking what is the calculation itself. Give us that equation, and we can translate it into Excel syntax for you.
(FY18 Revenue / FY17 Revenue-1)*100 = x
(FY18 Expenses / FY17 Expenses-1)*100 = y
x/y
Then it's plugging references into the cells.
So for example:
A1 = FY17 Revenue
A2 = FY18 Revenue
B1 = FY17 Expenses
B2 = FY18 Expenses
Then your formula is:
(The *100 in the numerator/denominator comes out in the wash).
Please Login or Register to view this content.
Now this delivers something crazy like -571.4% (or -5.71, or whatever, based on what you've got the cell formatted to display).
The issue is that the ratio should be positive as decreasing expenses and increasing revenue are both positive changes.
So I think what you need is:
That will work out as +50.5% change ; it's 140% / 93% -1 = 150.5% -1 = 50%Please Login or Register to view this content.
Increasing Revenue (140% => 40% up) divided by decreasing expenses (93% => 7% down) works out to a total of 150%. Then subtract one to get the change, rather than, like, the coefficient.
Note this is pretty much the same direction that shg first suggested.
I'm still looking for a ratio and not a percentage change.
...are you looking for something like this?
Please Login or Register to view this content.
Note: that right up there? It's is a bad idea.
Or are you looking for the 150% (1.50) result: =1.40/0.935? =(A2/A1)/(B2/B1) =A2/A1*B1/B2?
Originally Posted by shg
I'm not even sure it's possible to do what I am trying to do at this point. I'm trying to get the ratio of spend to revenue. So, for every $100 I make, it cost $5. That is a 20:1 ratio. I make $20 for every $1 I spend. I'm trying to figure out if I am doing better this year versus last year. There will be 4 different situations. 1. Revenue is Up and Expenses are Down. 2. Revenue is Up and Expenses are Up. 3. Revenue is Down and Expenses are Down. 4. Revenue is Down and Expenses are Up. I'm trying to get a calculation that works for all those examples.
CY2016 C2: =A2/B2 CY2017
I don't understand what that table gives me....
In the first line, you had $100 in revenue for $5 cost; that's a 20:1 ratio (your example, post #18).
In the second line, revenue increased by 40% and cost decreased by 6.5% (your example, post #1). The new ratio is 29.9.
Which is a 49.7 percent year-over-year increase (post #6).
Last edited by shg; 02-24-2018 at 07:43 PM.
...I'm still looking for a ratio...
wrt #16, you can custom format the divisions cells as fractions.
Ben Van Johnson
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks