Create formula referencing drop down list (data validation)
 

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

Example:

In the drop down list, there a

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.

Create formula referencing drop down list (data validation)
 

Try this:

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

RD

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

"mldancing" wrote in message
...
I have problem creating formula referencing a drop down list, can someone
help?

Example:

In the drop down list, there a

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.

Create formula referencing drop down list (data validation)
 

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" wrote in message
...
Try this:

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

RD

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

"mldancing" wrote in message
...
I have problem creating formula referencing a drop down list, can someone
help?

Example:

In the drop down list, there a

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.

Create formula referencing drop down list (data validation)
 

=SUM(IF(ISNUMBER(MATCH(G4:G7,{"Below
Average","Average","Good","Excellent"},0)),MATCH(G 4: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" wrote in message
...
I have problem creating formula referencing a drop down list, can someone
help?

Example:

In the drop down list, there a

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.

Create formula referencing drop down list (data validation)
 

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" wrote in message
...
I have problem creating formula referencing a drop down list, can someone
help?

Example:

In the drop down list, there a

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.