1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Percentage range

Discussion in 'Ask an Excel Question' started by Thomas Kuriakose, Oct 9, 2018.

  1. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    707
    Respected Sirs,

    We have a list of percentages that need to be grouped.

    The percentage format (74.35%) is not getting grouped and I tried to use the number to apply grouping and was partially successful.

    The below lookup is giving result for all positive values but for negative it is #N/A.
    =LOOKUP(A3,{0,0.05,0.1,0.15,0.2,0.25,0.3,0.35,0.4,0.45,0.5,0.55,0.6,0.65,0.7,0.75,0.8,0.85,0.9,0.95,1},$D$3:$D$23)

    Kindy guide in the attached file on how to get the results with percentage format and f negative values.

    Thank you very much for you support always,

    with regards,
    thomas

    Attached Files:

  2. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,948
    Maybe,

    Change this :

    =LOOKUP(A3,{0,0.05,0.1,0.15,0.2,0.25,0.3,0.35,0.4,0.45,0.5,0.55,0.6,0.65,0.7,0.75,0.8,0.85,0.9,0.95,1},$D$3:$D$23)

    Into this :

    =LOOKUP(A3,{-9.9E+307,0,0.05,0.1,0.15,0.2,0.25,0.3,0.35,0.4,0.45,0.5,0.55,0.6,0.65,0.7,0.75,0.8,0.85,0.9,0.95,1},$D$2:$D$23)

    Regards
    Bosco
    Last edited: Oct 10, 2018
    Thomas Kuriakose likes this.
  3. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    707
    Respected Sir,

    This works perfectly. What does -9.9E+307 mean.

    Thank you very much for your support always,

    Much appreciated.

    with regards,
    thomas
  4. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    796
    It's a very large negative number (with 307 trailing zeros)
    Thomas Kuriakose likes this.
  5. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    707
    Respected Sir,

    Thank you very much for this insight and your guidance.

    Much appreciated.

    with regards,
    thomas

Share This Page