I have problem creating formula referencing a drop down list, can someone help?

Example:

In the drop down list, there are:

1-Below Average
2-Average
3-Good
4-Excellent

1 is given 1 point, 2 given 2 points, 3 given 3 points and 4 given 4 points.
Assume there are 3 drop down list, all drop down list has 3-Good.
So the mean should be (3+3+3)/3 = 3

How do I create a formula that will calculate the mean?

Thanks for your help.

Try this:

=SUMPRODUCT(--LEFT(A1:A3)/3)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"mldancing" <(E-Mail Removed)> wrote in message
news:B35E3122-5C69-417F-9174-(E-Mail Removed)...
>I have problem creating formula referencing a drop down list, can someone
>help?
>
> Example:
>
> In the drop down list, there are:
>
> 1-Below Average
> 2-Average
> 3-Good
> 4-Excellent
>
> 1 is given 1 point, 2 given 2 points, 3 given 3 points and 4 given 4
> points.
> Assume there are 3 drop down list, all drop down list has 3-Good.
> So the mean should be (3+3+3)/3 = 3
>
> How do I create a formula that will calculate the mean?
>
> Thanks for your help.
>

Actually, with the divide by 3 (/3), the unary becomes unnecessary:

=SUMPRODUCT(LEFT(A1:A3)/3)
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Ragdyer" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Try this:
>
> =SUMPRODUCT(--LEFT(A1:A3)/3)
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
>
> "mldancing" <(E-Mail Removed)> wrote in message
> news:B35E3122-5C69-417F-9174-(E-Mail Removed)...
>>I have problem creating formula referencing a drop down list, can someone
>>help?
>>
>> Example:
>>
>> In the drop down list, there are:
>>
>> 1-Below Average
>> 2-Average
>> 3-Good
>> 4-Excellent
>>
>> 1 is given 1 point, 2 given 2 points, 3 given 3 points and 4 given 4
>> points.
>> Assume there are 3 drop down list, all drop down list has 3-Good.
>> So the mean should be (3+3+3)/3 = 3
>>
>> How do I create a formula that will calculate the mean?
>>
>> Thanks for your help.
>>
>
>

=SUM(IF(ISNUMBER(MATCH(G4:G7,{"Below
Average","Average","Good","Excellent"},0)),MATCH(G4:G7,{"Below
Average","Average","Good","Excellent"},0)))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"mldancing" <(E-Mail Removed)> wrote in message
news:B35E3122-5C69-417F-9174-(E-Mail Removed)...
>I have problem creating formula referencing a drop down list, can someone
>help?
>
> Example:
>
> In the drop down list, there are:
>
> 1-Below Average
> 2-Average
> 3-Good
> 4-Excellent
>
> 1 is given 1 point, 2 given 2 points, 3 given 3 points and 4 given 4
> points.
> Assume there are 3 drop down list, all drop down list has 3-Good.
> So the mean should be (3+3+3)/3 = 3
>
> How do I create a formula that will calculate the mean?
>
> Thanks for your help.
>

Forgot to add

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"mldancing" <(E-Mail Removed)> wrote in message
news:B35E3122-5C69-417F-9174-(E-Mail Removed)...
>I have problem creating formula referencing a drop down list, can someone
>help?
>
> Example:
>
> In the drop down list, there are:
>
> 1-Below Average
> 2-Average
> 3-Good
> 4-Excellent
>
> 1 is given 1 point, 2 given 2 points, 3 given 3 points and 4 given 4
> points.
> Assume there are 3 drop down list, all drop down list has 3-Good.
> So the mean should be (3+3+3)/3 = 3
>
> How do I create a formula that will calculate the mean?
>
> Thanks for your help.
>