Assign underlying numeric values to text for the purpose of calculating an average.

**neverenoughtime** · 01-25-2013, 01:03 PM

Hello,

What is the best way to accomplish this?

I have cells with text (High, Medium, Low) in them. I want the text to diplay in the cell but I want to assign an underlying numeric value so I can calculate an average.

I would like High to always be worth 5, Medium always worth 3 and Low always worth 1.

Ex:

A1 = HIGH B1 = MEDIUM C1 = LOW

I would like D1 to display the average of the underlying numerical values so D1 would display 3. (5+3+1)/3

Ex2:

A1 = HIGH B1= HIGH C1 = Medium
D1 would display 4.3. (5+5+3)/3

I'm not sure what type of formula to use in D1

Thanks

**bebo021999** · 01-25-2013, 01:15 PM

Try this:
=AVERAGE(LOOKUP(A1:C1,{"HIGH","LOW","MEDIUM"},{5,1,3}))
Confirmed by Holding both Ctrl-shift then Enter

**Special-K** · 01-25-2013, 01:16 PM

=LOOKUP(LEFT(A1,1),{"H","L","M"},{5,1,3})

or

=MID("135",FIND(LEFT(A1,1),"LMH"),1)

will return an average

Replace A1 with B1 and C1 and just add them together and divide by 3

**kishoremcp** · 01-25-2013, 01:21 PM

Please find the attached, this might also help you to calculate your requirement.

**neverenoughtime** · 01-25-2013, 02:10 PM

Many thanks. For future reference - what is the best term to describe the use of the curly braces if I were to search for that again?

Assign underlying numeric values to text for the purpose of calculating an average.

LinkBack

Thread Tools

Rate This Thread

Display

Assign underlying numeric values to text for the purpose of calculating an average.

Re: Assign underlying numeric values to text for the purpose of calculating an average.

Re: Assign underlying numeric values to text for the purpose of calculating an average.

Re: Assign underlying numeric values to text for the purpose of calculating an average.

Re: Assign underlying numeric values to text for the purpose of calculating an average.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions