Need help with an excel formula 2

[dbuffer]

I am trying to see up a employee review report. I have the following columns

Low med medh High
_x_ ___ ___ ____
___ _x_ ___ ____
___ _x_ ___ ____
___ ___ ___ __x_

_1_ _2_ _0_ _1__ Overall

I put an X in whichever column is scored. Now I have the following formula to count the number of x's in each column
=COUNTIF (G29:G40,"x") so I would then have number totals
in the overall columns. So what I want to do is have each of those columns low, med, medh, high equal an average. for example if "X" is in low low is 1.00 to 1.74, med is 1.75 to 2.74 and medh is 2.74 to 3.74 and high is 3.75 to 4.00
So I want to take the total number in the overall section and put in a formula with the above ranges and then get an total overall averge score for all the columns. I don't know if I have explained this correctly or even if it can be done. Any suggestions??

Thanks
Debbie

 

[CBasicAsslember]

=X*COUNTIF (G29:G40,"x")

Where X = the weighting 1, 2, 3 ...

 

[SkipVought]

Debbie,

Why don't you consider working from RAW DATA?
[tt]
EmpID Score
[/tt]
Have another table for your Score Ranges like
[tt]
Score
1
1.75
2.75
3.75
[/tt]
and use MATCH(v,r,1). Match will return values of 1 thru 4, which correspond to low - high.

For the raw adata and the Score table you can produce the report similar to your example and get the totals you are looking for using SUMIF or SUMPRODUCT.

Skip,
[sub]
[red][/red]
[/sub]

 

[dbuffer]

Thank you very much for your info, I will try both ideas and let you know how they work.

Thanks again
debbie

 

[dbuffer]

I have tried both ideas and I am doing something wrong because I am not coming up with any correct answers. I am not very good at these formulas, i happened upon the countif by accident. So I need a bit more specific info

Can I email either of you the page I am working on so that you will be able to see my page when you give your advice?

Debbie

 

[SkipVought]

ii36250 at bellhelicopter dot textron dot com

Skip,
[sub]
[red][/red]
[/sub]

 

[CBasicAsslember]

Need some clarification first.

for example if "X" is in low low is 1.00 to 1.74, med is 1.75 to 2.74 and ...

"X" has to represent a single number for each category. For example:
Low = 1
Med = 1.75
Medh = 2.75
Hign = 3.75

Keeping it simple for now. In the "Overall" row for each column (assuming column G is 'Low'):

=1*COUNTIF(G29:G40,"x")
=1.75*COUNTIF(H29:H40,"x")
=2.75*COUNTIF(I29:I40,"x")
=3.75*COUNTIF(J29:J40,"x")

That should give you the totals for each column. Make sure the cells in "Overall" are formatted "General".

 

[dbuffer]

To: CBasicAsslember,

Now I understand what to put in each of the rows and the countif from your example. Thank you very much I will try it.

To Skip:
the email is on the way

Thank you both I really appreciate your help.

 

[SkipVought]

It seems that the resolution to this issue, is in using a range of values (Named Rating) for limits in this formula...
[tt]
=($G23*INDEX(Rating,1,1)+$H23*INDEX(Rating,2,1)+$I23*INDEX(Rating,3,1)+$J23*INDEX(Rating,4,1))/4
[/tt]
which in many ways is similar to CBasicAsslember's suggestion.

Skip,
[sub]
[red][/red]
[/sub]

 

[CBasicAsslember]

Skip,

You always come up with interesting ways to do things.

 

[SkipVought]

I'd rather use a table that can be referenced, and where changes can be made in ONE PLACE, than embed DATA in expressions.

Skip,
[sub]
[red][/red]
[/sub]

 

[CBasicAsslember]

I agree about embedding data in a formula Skip. It is sometimes easier for a novice to understand what is going on if it is kept simple. My next step would have been to replace the constants in the formulas with named ranges.

 

[SkipVought]

Nice "bate 'n' switch" approch --> ;-)

Skip,
[sub]
[red][/red]
[/sub]

 

[dbuffer]

Both of you have been so helpful, and yes I am a NOVICE!!, I can do some of the basic formulas but it would have taken me months to figure out the info that you have given me. And everything that you have done.

Thank you very much for everything.
I know now that if I ever have another question I will be directing it to you.

Thanks
Debbie

 

[SkipVought]

Debbie,

There are scores of competent contributors in this forum.

Feel free to just post your question.

I am sure that you will receive one or more solid helpful options.

Skip,
[sub]
[red][/red]
[/sub]