Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value = this
I have been asked to set up a cell that will tell the user his or her
Accomplishment based on a percent. So if a person is told to do 2 (Cell A1) and they did 2 (Cell A2) the person would be at 100%. so if the value falls between 100% to 105% I would like the user to see the word Green if the Cell Value was 50% falling between 45% to 99.99% it would show the word RED. I have a total of 6 words from black to Gold. and I know how to start the code. =(A2/A1) to get me the percent. I just dont know how to have it look at that percent and give it a word. Thanks KEITH |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value = this
You need to use IF
=IF(test,value if true,value if false) So you can say: =IF(A2/A1<20%,"black","pink") But you can also replace the "pink" by a further test. This is a nested IF. I will start you off: =IF(A2/A1<20%,"black",IF(A2/A1<40%,"pink",IF(A2/A1<60%,"orange",...))) Keep doing it like that. Keep them in order from the smallest to the largest and it should be OK. Excel will help you to close the brackets at the end. -- Allllen "KAnoe" wrote: I have been asked to set up a cell that will tell the user his or her Accomplishment based on a percent. So if a person is told to do 2 (Cell A1) and they did 2 (Cell A2) the person would be at 100%. so if the value falls between 100% to 105% I would like the user to see the word Green if the Cell Value was 50% falling between 45% to 99.99% it would show the word RED. I have a total of 6 words from black to Gold. and I know how to start the code. =(A2/A1) to get me the percent. I just dont know how to have it look at that percent and give it a word. Thanks KEITH |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value = this
Allen works great. Just one more thing. I get #DIV/0! in the cell if the
person is not required to get a sale. what can I do to hide the #DIV/0! THNAKS FOR YOUR HELP Keith "Allllen" wrote: You need to use IF =IF(test,value if true,value if false) So you can say: =IF(A2/A1<20%,"black","pink") But you can also replace the "pink" by a further test. This is a nested IF. I will start you off: =IF(A2/A1<20%,"black",IF(A2/A1<40%,"pink",IF(A2/A1<60%,"orange",...))) Keep doing it like that. Keep them in order from the smallest to the largest and it should be OK. Excel will help you to close the brackets at the end. -- Allllen "KAnoe" wrote: I have been asked to set up a cell that will tell the user his or her Accomplishment based on a percent. So if a person is told to do 2 (Cell A1) and they did 2 (Cell A2) the person would be at 100%. so if the value falls between 100% to 105% I would like the user to see the word Green if the Cell Value was 50% falling between 45% to 99.99% it would show the word RED. I have a total of 6 words from black to Gold. and I know how to start the code. =(A2/A1) to get me the percent. I just dont know how to have it look at that percent and give it a word. Thanks KEITH |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value = this
=if(And(A1<0,A1<""),IF(A2/A1<20%,"black",IF(A2/A1<40%,"pink",IF(A2/A1<60%,"orange",...))),"")
-- Regards, Tom Ogilvy "KAnoe" wrote: Allen works great. Just one more thing. I get #DIV/0! in the cell if the person is not required to get a sale. what can I do to hide the #DIV/0! THNAKS FOR YOUR HELP Keith "Allllen" wrote: You need to use IF =IF(test,value if true,value if false) So you can say: =IF(A2/A1<20%,"black","pink") But you can also replace the "pink" by a further test. This is a nested IF. I will start you off: =IF(A2/A1<20%,"black",IF(A2/A1<40%,"pink",IF(A2/A1<60%,"orange",...))) Keep doing it like that. Keep them in order from the smallest to the largest and it should be OK. Excel will help you to close the brackets at the end. -- Allllen "KAnoe" wrote: I have been asked to set up a cell that will tell the user his or her Accomplishment based on a percent. So if a person is told to do 2 (Cell A1) and they did 2 (Cell A2) the person would be at 100%. so if the value falls between 100% to 105% I would like the user to see the word Green if the Cell Value was 50% falling between 45% to 99.99% it would show the word RED. I have a total of 6 words from black to Gold. and I know how to start the code. =(A2/A1) to get me the percent. I just dont know how to have it look at that percent and give it a word. Thanks KEITH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|