+ Reply to Thread
Results 1 to 22 of 22

How to find a ratio with a negative number

  1. #1
    Registered User
    Join Date
    11-13-2015
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    19

    Angry 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. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    Re: How to find a ratio with a negative number

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

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,420

    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
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    11-13-2015
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    19

    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.

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,420

    Re: How to find a ratio with a negative number

    Hi

    See attached
    Attached Files Attached Files

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,669

    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%
    Entia non sunt multiplicanda sine necessitate

  7. #7
    Registered User
    Join Date
    11-13-2015
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    19

    Re: How to find a ratio with a negative number

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

  8. #8
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    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. #9
    Registered User
    Join Date
    11-13-2015
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    19

    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. #10
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    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. #11
    Registered User
    Join Date
    11-13-2015
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    19

    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. #12
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    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. #13
    Registered User
    Join Date
    11-13-2015
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    19

    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. #14
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    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. #15
    Registered User
    Join Date
    11-13-2015
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    19

    Re: How to find a ratio with a negative number

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

  16. #16
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    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. #17
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,554

    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?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  18. #18
    Registered User
    Join Date
    11-13-2015
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    19

    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. #19
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,669

    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. #20
    Registered User
    Join Date
    11-13-2015
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    19

    Re: How to find a ratio with a negative number

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

  21. #21
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,669

    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).
    Last edited by shg; 02-24-2018 at 07:43 PM.

  22. #22
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,464

    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.
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Find cell address of first negative number in a row
    By dviper in forum Excel General
    Replies: 6
    Last Post: 10-15-2017, 10:27 AM
  2. How to calculate a number based on the given ratio. For example a 2:3 ratio.
    By Ayoub99k in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-11-2015, 08:15 AM
  3. [SOLVED] Find first negative number in column, return row number
    By slade8200 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-06-2014, 11:14 AM
  4. Find 0 or smallest negative number from range
    By agg in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-26-2009, 11:42 AM
  5. find first negative number in a list
    By JohnFo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-21-2007, 02:39 AM
  6. Find the first negative number in a column?
    By jerickson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-10-2007, 09:13 PM
  7. [SOLVED] Find a negative number in a range
    By Scott at Culvers in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-22-2005, 01:00 PM

Tags for this Thread

Bookmarks

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