How to Assign Number values to List Items

**Pera** · 09-24-2009, 12:54 PM

Hello everyone,

I read the forum rules and tried to be as specific in my topic as I can be and I hope the topic is not very confusing.

Ultimately, what I would like to accomplish is this:

I want to have a total of 4 columns A, B, C, and D

Colum A has 3 list items for user to select "one" from drop down list.
Colum B has 2 list items for user to select "one" from drop down list.
Colum C has 3 list items for user to select "one" from drop down list.

So far, I was able to create the list with selection items for these 3 columns. Where I got stuck is column D:

I would like to have in column D predetermined ranges based on number ranges such as:

0-3 - Low
4-6 - Medium
7-9 - High

Column D should do an addition of the values assigned to each list item based on the user selection from the column A, B, and C, for example, list item 1 in column A has a value of 0, list item 2 has a value of 3, etc.

Based on this tally score, let's say after user picks a list item for column A, B, and C we have a score of 6, I want column D to populate Medium on the field.

Like I mentioned before, I was able to create a list using Validate option in excel, however, I am not able to assign number values to the list items and to the addition in column D as I mentioned. I would appreciate the help.

Thanks!

**NBVC** · 09-24-2009, 01:02 PM

Perhaps

=Lookup(Sum(A2:C2),{0,4,7},{"Low","Medium","High"})

where A2:C2 are first entries

**Pera** · 09-24-2009, 01:49 PM

Thanks for the quick reply. I believe this will take care of the column D.

How can I go about assigning values to the list items for column A, B, and C? For example if I have 3 list items in Column A, I want to be able to assign numeric values to them, like:

Red 0
Yellow 1
Green 3

Thanks!

**NBVC** · 09-24-2009, 01:55 PM

You mean something like?

=LOOKUP(SUM(LOOKUP(A1,{"Green","Red","Yellow"},{3,0,1}),LOOKUP(B1,{"Green","Red","Yellow"},{3,0,1}), LOOKUP(C1,{"Green","Red","Yellow"},{3,0,1})),{0,4,7},{"Low","Medium","High"})

adjust the text strings and corresponding numbers for each Lookup() based on the cell reference in the first argument...

... note, the text strings must be listed in ascending alphetical order in each lookup function...

**Pera** · 09-24-2009, 03:05 PM

Thanks a lot. This worked like a charm. Instead of using only one row, I added a range of rows since there are multiple rows involved.

One last question, how can I go about adding color based on the range of the column D? As in Low would be green, medium yellow, and high red.

Thanks again!

**NBVC** · 09-24-2009, 03:07 PM

Format|Conditional Formatting...

.. select Cell Value Is >> Equal to >> ="Low"

click Format, choose colour, click Ok.

Click Add,

repeat for other 2 conditions.