# How to find a ratio with a negative number

1. ## 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.  Register To Reply

2. ## Re: How to find a ratio with a negative number

... you mean *(1- 6.5%) or something like that?  Register To Reply

3. ## Re: How to find a ratio with a negative number

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  Register To Reply

4. ## Re: How to find a ratio with a negative number

I think I want Change in Margin as % of Margin, but I'd like to see all the calculations please.  Register To Reply

5. ## Re: How to find a ratio with a negative number

Hi

See attached  Register To Reply

6. ## Re: How to find a ratio with a negative number

I think the meaningful interpretation is that productivity (revenue per expense dollar) is up about 50%:

=(1 + 40%) / (1 - 6.5%) - 1 ~ 49.7%  Register To Reply

7. ## Re: How to find a ratio with a negative number

I am looking to obtain a ratio, not a percentage.  Register To Reply

8. ## Re: How to find a ratio with a negative number

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.  Register To Reply

9. ## Re: How to find a ratio with a negative number

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.  Register To Reply

10. ## Re: How to find a ratio with a negative number

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.  Register To Reply

11. ## Re: How to find a ratio with a negative number

(FY18 Revenue / FY17 Revenue-1)*100 = x
(FY18 Expenses / FY17 Expenses-1)*100 = y
x/y  Register To Reply

12. ## Re: How to find a ratio with a negative number

Then it's plugging references into the cells.

So for example:
A1 = FY17 Revenue
A2 = FY18 Revenue

B1 = FY17 Expenses
B2 = FY18 Expenses ``Please Login or Register  to view this content.``
(The *100 in the numerator/denominator comes out in the wash).

Now this delivers something crazy like -571.4% (or -5.71, or whatever, based on what you've got the cell formatted to display).  Register To Reply

13. ## Re: How to find a ratio with a negative number

The issue is that the ratio should be positive as decreasing expenses and increasing revenue are both positive changes.  Register To Reply

14. ## Re: How to find a ratio with a negative number

So I think what you need is: ``Please Login or Register  to view this content.``
That will work out as +50.5% change ; it's 140% / 93% -1 = 150.5% -1 = 50%
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.  Register To Reply

15. ## Re: How to find a ratio with a negative number

I'm still looking for a ratio and not a percentage change.  Register To Reply

16. ## Re: How to find a ratio with a negative number

...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.  Register To Reply

17. ## Re: How to find a ratio with a negative number

Or are you looking for the 150% (1.50) result: =1.40/0.935? =(A2/A1)/(B2/B1) =A2/A1*B1/B2?  Register To Reply

18. ## Re: How to find a ratio with a negative number

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.  Register To Reply

19. ## Re: How to find a ratio with a negative number

 A B C D E 1 Revenue Cost Rev/Cost 2 CY2016 \$ 100.00 \$ 5.00 20.0 C2: =A2/B2 3 CY2017 \$ 140.00 \$ 4.68 29.9  Register To Reply

20. ## Re: How to find a ratio with a negative number

I don't understand what that table gives me....  Register To Reply

21. ## Re: How to find a ratio with a negative number

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).  Register To Reply

22. ## Re: How to find a ratio with a negative number

...I'm still looking for a ratio...
wrt #16, you can custom format the divisions cells as fractions.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 