Can I use Formulas to Calculate Chart Series?

[sasquatchbill]

I'm sorry if this is the wrong section of the forums, but I'm not sur
where to file this appropriately:

We're taking a survey that will hopefully have at least 1,000
responses, and the responses will be exported to Excel. Each perso
will first answer what role they have in our corporation, for instance
Project Manager - Dev, Technical-Architect - Dev, Application-Owner
Support, etc. The rest of the questions we want to chart are answere
with a 5 point scale from Poor - Very Good.

Our goal is to be able to chart the average answers for each Role, ie
Application-Owner - Support average for question 5 is 'Good', while th
Tech-Architect average for Q5 is 'Poor'. The trick is that the dat
comes into Excel as each question is answered - each row is a perso
and their answers, with the columns being the questions.

The questions we have are:
-How do we chart the averages of these different groups against eac
other?
-Is there a way to separate the roles automatically, or will we have t
go through by hand and select their rows each after sorting them b
name?
-Will it be fairly easy to adjust the answers from Poor-Very Good int
1-5, and how is it done and where (in the chart parameters, or must i
be done on a worksheet?)

I think that's all - hopefully that made sense to you. We have barel
any idea how to do this, so any insight you can give us would hel
immensely. If you need more/clearer info, please ask!!
Thanks

 

[Guest]

Without completely understanding what you want, off the top of my head I am
thinking you might be able to set the responses up in a pivot table to
analyze this. Hope this helps point you in the right direction.

 

[sasquatchbill]

Yeah, that seems like it could do the job! Thanks for the direction!
However, right now in my preliminary tests, it's close, but not quit
right.

At the bottom of the chart on the X axis, I'm getting each group o
Roles - so there's 3 App.Owners but only one bar for their answers
This is correct. However, in the data area, it doesn't average ou
their answers, it just counts them, and puts the bar at 3.

To illustrate:
AppOwner1's answer: "Poor" or a score of 1
AppOwner2's answer: "Below Average" or a score of 2
AppOwner3's answer: "Average" or a a score of 3

The average of these: 2 [(1+2+3)/3, just so you can check my math]


So that would be the answer I want in my graph. Either a 2, or a
"Average" along the Y axis.

How do I do that? Any idea

 

[Guest]

It sounds like the score column in the data table is not set up as a number
format if it is counting them instead of summing. Check this first, it
should have defaulted to summing the fields, in which case the total should
be six. If you get this figured out, to change the pivot table field
settings for the score field from a sum to an average: right click on the
filed in the pivot table and choose Field Settings-select Average. Hope this
helps.

It is quitting time for me, I may not get a chance to check back on this
tonight. In which case I will get back to you in the AM.

 

[sasquatchbill]

MarkM, thanks for your help with this. Even though we're not yet wher
we want to be, thanks to you we're SO much closer.

The next question we have is about turning the responses of Poor, Belo
Average, Average etc. into 1,2,3 etc.
We've heard rumours of the ability to create a (now you may have t
bear with me, I've no clue what I'm talking about here) table tha
replaces a Word with a Value, ie - "Very Good" = 5, and 'Average' = 3,
throughout a worksheet...but I don't know how to do it. Any ideas on ho
we might translate those into the values we need

 

[sasquatchbill]

-Bump-
Help! We still haven't found a way to do this! Anyone able to help us
out?

 

[Guest]

Sorry about the late reply, I got busy and lost track of this post.

You can create a separate “lookup†table on a different tab or sheet. And
use the vlookup function to accomplish this. You will have your responses in
one column, insert a new column for the vlookup function, this is where it
will return your 5 for a “Very Goodâ€.

You vlookup function will look something like this:
=vlookup(C1,Sheet2!A1:B5,2,0).

Where “C1†= Your column with responses
Where “Sheet2!A1:B5†is the new sheet with lookup table

Hope this helps.