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

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

Percentage range

Thomas Kuriakose

Active Member
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
 

Attachments

  • Range.xlsx
    49.1 KB · Views: 9
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:
Respected Sir,

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

Thank you very much for your support always,

Much appreciated.

with regards,
thomas
 
Back
Top