Determining Percent Range

[mgbeye]

I have a set of numbers that I have determined percent accuracies for. what I would like to do is display in cell next to these percents what range they fit into. (IE: 30-40% or 40-50%) Does anyone know how I can do this without making one huge If-Then statement??

THANKS!

 

[Goska]

Assuming the value is in cell A1, and the new one would be in B1, then the formula for B1 would be

=INT(A1*10)*10 & "%-" & ((INT(A1*10)*10)+10) & "%"


Will return 10%-20% if the value is between them.

This is also assuming that the value in A1 is formatted as a percent.

 

[Goska]

Sorry, forgot to leave an explanation. Basically, I moved the decimal point so that 15% (actually .15) becomes 1.5 when multiplied by 10. The int(1.5) is 1, which returns a 1 for this part. Then, mutiply by 10 again to show 10, then add a percent symbol. Then tack on another 10% for the second half of the range.
This is no longer numbers, it is now a text string, so you won't be able to calculate anythign else on this unless you break it up into multiple cells, where the % formating is going to throw off my 10/10 multiplier.

Hope this helps.

 

[DaleWatson123321]

Hi nyteiz,

I believe the formula you are looking for is as follows:
(for this example, the formula would normally reside in cell B1)

=CHOOSE(INT(A1*10)+1,"0-10%","10-20%","20-30%","30-40%","40-50%","50-60%","60-70%","70-80%","80-90%","90-100%&quot

This requires that A1 be formatted as percentage, preferably with at least 1 decimal place. If formatted with 0 decimals and the number 9.9 was entered into A1, it would show as 10%, but the formula in B1 would (properly) show "0-10%", whereas I expect you want 10% to be classed in the 10-20% range.

You can copy and paste this formula to any location in your worksheet. You will probably want to paste it to the right of your first number (of your set of numbers). Once pasted, change the reference from "A1" to the cell your first number is in. Once you make the change, you can copy the formula for other numbers in your set.

I checked this formula out and confirmed that it works. If for some reason you have difficulty, please let me know.

Regards, ... Dale Watson dwatson@bsi.gov.mb.ca

 

[gurnerworld]

I would do this using the Vlookup function.

If you create a two column table (either somewhere else on the same sheet or on a separate sheet) as follows:

0% 0-10%
11% 11-20%

and so on, then add a formula which does this:

=vlookup(your percentage figure,the two column table you've just created,the column in that table you wish to enter the answer from)

This looks for your percentage figure in the first column of the table and when it finds it, it returns the value in the column you specify, which in this case would be column 2).


If your percentage figure is in cell A1 and your table is in cells c1:d10, then the formula would look lke this:

=vlookup(a1,$c$1:$d$10,2)

I hope this helps.