Forum Discussion
How to assign numerical value to a list?
Hello,
I am currently working on a new training program for my team and I am running into a little difficulty with formatting. I'd like to get this set so that the selected cell will "read" the cells above it in the column as a numerical value to calculate a percent complete.
"Not Started" should be read as a 0 or 0%
"In Progress" should be read as a 1 or 50%
"Completed" should be read as a 2 or 100%
Ideally, then I'd like to have the selected cell tally each cell's value and give me a red, yellow, or green read-out based on how much they have completed. So for example: Column C could be read as 25% Completed, but overall, less than 50% so still RED.
Any help or recommendations for this is appreciated.
-Cathy
In Excel for the web, you can use the following approach to assign numerical values to your list and calculate the percent complete:
- Select the cell where you want to display the percent complete.
- Go to the "Insert" tab in the Excel for the web toolbar and click on "Dropdown."
- In the dropdown menu, select "Data Validation."
- In the data validation settings, choose "List" as the validation criteria.
- In the "Source" field, enter the following values: "Not Started,In Progress,Completed" (without quotes).
- Click "OK" to apply the data validation to the selected cell.
- Now, in a separate cell, you can use the following formula to calculate the percent complete based on the selected value:
=IF(A1="Not Started",0,IF(A1="In Progress",50,IF(A1="Completed",100,"")))
Replace A1 with the cell reference of the selected cell.
This formula checks the value in the selected cell and assigns the corresponding numerical value: 0 for "Not Started," 50 for "In Progress," and 100 for "Completed."
To get the overall progress color read-out, you can use conditional formatting based on the calculated percent complete. Select the range of cells you want to format, go to the "Home" tab, click on "Conditional Formatting," and choose "Color Scales" or "Icon Sets" to apply color formatting based on the percent complete.
Please note that Excel for the web may have limitations compared to the desktop version, so some advanced features may not be available.
- NikolinoDEGold Contributor
In Excel for the web, you can use the following approach to assign numerical values to your list and calculate the percent complete:
- Select the cell where you want to display the percent complete.
- Go to the "Insert" tab in the Excel for the web toolbar and click on "Dropdown."
- In the dropdown menu, select "Data Validation."
- In the data validation settings, choose "List" as the validation criteria.
- In the "Source" field, enter the following values: "Not Started,In Progress,Completed" (without quotes).
- Click "OK" to apply the data validation to the selected cell.
- Now, in a separate cell, you can use the following formula to calculate the percent complete based on the selected value:
=IF(A1="Not Started",0,IF(A1="In Progress",50,IF(A1="Completed",100,"")))
Replace A1 with the cell reference of the selected cell.
This formula checks the value in the selected cell and assigns the corresponding numerical value: 0 for "Not Started," 50 for "In Progress," and 100 for "Completed."
To get the overall progress color read-out, you can use conditional formatting based on the calculated percent complete. Select the range of cells you want to format, go to the "Home" tab, click on "Conditional Formatting," and choose "Color Scales" or "Icon Sets" to apply color formatting based on the percent complete.
Please note that Excel for the web may have limitations compared to the desktop version, so some advanced features may not be available.
- SleepyTiger2013Copper ContributorThank you so much! This has worked wonderfully!
- shanioreoCopper Contributor
i have a similar question if you're able to assist. I am trying to use dropdown so i can easily pick different time options for scheduling shifts. I'm trying to making it so that each time frame (i.e. 10-6:30 A or 6:30-3 A) all equal 8 as in 8 hours worked. Then I'm trying to give the "off" valued at 0. The goal would be to calculate each week at the end to make sure every person is getting their 2 days off and 40 hours correctly. Is that possible to do in excel? NikolinoDE