# 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.

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

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

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

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.

Hi

See attached

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%

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

I am looking to obtain a ratio, not a percentage.

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.

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.

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.

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

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

Then your formula is:
``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).

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.

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.

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

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

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.

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?

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.

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

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

I don't understand what that table gives me....

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).

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.

#### Thread Information

##### Users Browsing this Thread

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1