+ Reply to Thread
Results 1 to 4 of 4

Calculate percentile of two cells (must work with minus numbers)

  1. #1
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Calculate percentile of two cells (must work with minus numbers)

    Hello,

    In cell A1 i have the number -20
    in cell A2 i have the number 5
    so the range of the two numbers is 25.

    i would like B1 to have a formula that calculates 20% between the two numbers (-20 and 5)
    the answer should be -15

    thank you

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Calculate percentile of two cells (must work with minus numbers)

    If I follow your logic correctly, then:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,756

    Re: Calculate percentile of two cells (must work with minus numbers)

    Seems like it should be a simple proportionality: (A2-A1)/(1-0)=(x-A1)/(0.2-0) then solve for x.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Calculate percentile of two cells (must work with minus numbers)

    ollys worked great. cheers

+ 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. Replies: 8
    Last Post: 06-11-2013, 05:34 PM
  2. [SOLVED] Remove minus infront and behind of numbers in rang of cells
    By abjac in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-10-2013, 03:07 AM
  3. Excel 2007 : How to auto calculate a percentile
    By stevetothink in forum Excel General
    Replies: 5
    Last Post: 01-18-2012, 01:48 PM
  4. format cells not to show minus numbers and replace with zero
    By troysie in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-18-2011, 04:55 AM
  5. Macro to calculate what percentile a value is in
    By Toneranger in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-16-2011, 03:20 AM

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