Assigning a set value to a cell that has a percentage?

[Pullge]

I am trying to create a formula that will assign a value such as 1, 2 or 3
based on the cell percentage value.

If the cell is < 70.00% then it's value will be a 1
If the cell is between 70.01% and 83.00% then it's value will be 2
If the cell is between 83.01% and 89.00% then the value will be 3

The cell currently will pull data from another source and I have a new sheet
that needs to be able to look at the current percentage and then take the
value of 1 or 2 or 3 and then multiply it by a set variable.

So if Cell C3 has a current value of 73.00% it would need to be assigned the
value of 1. Then the formula would need to take the value 1 and muliply it
by 2 to give me my final value of 2

I have tried many variables, but not able to get the syntax or formula
correct. Any help is most appreciated.

 

[Fred Smith]

Try it this way:
=if(c3<=70%,1,if(c3<=83%,2,if(c3<=89%,3,"Outside range")))

You didn't say what happens when c3 is exactly 70%, so I assumed you wanted
1.

Regards,
Fred.

 

[Jacob Skaria]

73% should return 2 and multiplied by 2 should nt that give 4...Try the below
formula

=IF(C3<=0,0,IF(C3<70%,1,IF(C3<=83%,2,IF(C3<=89,3,0))))*2

If this post helps click Yes

 

[JoeU2004]

So if Cell C3 has a current value of 73.00% it would need to be
assigned the value of 1.

If you want to replace 73% in C3 with a 1 in C3, that is not easy to do. It
requires a macro of one form or another.

Is that really what you truly want to do?

Or is it sufficient to put the 1 into another cell, say D3, and leave C3
alone?

If the cell is < 70.00% then it's value will be a 1
If the cell is between 70.01% and 83.00% then it's value will be 2
If the cell is between 83.01% and 89.00% then the value will be 3
The cell currently will pull data from another source

Careful with this. Note that numbers with decimal fractions are almost
never represented exactly in the internal form that Excel uses (binary
floating point). 83.01% is not the "next number after" 83.00%, for example.

Moreover, "another source" might not export exactly the values as they
appear in that application. For example, there is a recent thread where it
appears that MS Access exported some surprising approximations; by analogy,
69.9999999999996%, which you probably see as 70%.

I think you mean: if the cell __displays__ less than 70%, the value should
be 1; if the cell __displays__ less than 83%, then 2; if the cell
__displays__ less then 89%, then 3.

Therefore, I think the following is what you really want:

=if(round(C3,2) < 70%, 1, if(round(c3,2) < 83%, 2, if(round(c3,2) < 89%, 3,
4)))

I round to 2 decimal places because percentages are actually decimal
fractions. For example, 70% is 0.70.

(If you wanted to round to 2 percent decimal places, you would do
ROUND(C3,4). Confusing, huh?!)

Also, I "invented" the value 4 for numbers >= 89%, which you did not cover.
If you wish, change 4 to "" (double quote; the null string) so that cell
appears blank (but it is not!) when C3 >= 89%.

(But that might not work well when you try to multiply by another "set
variable", below.)

Finally, a better way to write this might be:

=lookup(round(C3,2), {0, 0.70, 0.83, 0.89}, {1, 2, 3, 4})

Note that that asumes C3 >= 0. If C3 might be negative, then:

=lookup(max(0,round(C3,2)), {0, 0.70, 0.83, 0.89}, {1, 2, 3, 4})

then take the value of 1 or 2 or 3 and then multiply it by a set variable.

Perhaps something like:

=B3 * lookup(round(C3,2), {0, 0.70, 0.83, 0.89}, {1, 2, 3, 4})

where B3 is the other "set variable".


----- original message -----

 

[JoeU2004]

Finally, a better way to write this might be: =lookup(round(C3,2), {0,
0.70, 0.83, 0.89}, {1, 2, 3, 4})

Since you want 1, 2, 3, etc, even simpler:

=match(round(C3,2), {0, 0.70, 0.83, 0.89})


----- original message -----