Can I use Formulas to Calculate Chart Series?

I'm sorry if this is the wrong section of the forums, but I'm not sure 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 person 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 answered 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 the Tech-Architect average for Q5 is 'Poor'. The trick is that the data comes into Excel as each question is answered - each row is a person 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 each other?
-Is there a way to separate the roles automatically, or will we have to go through by hand and select their rows each after sorting them by name?
-Will it be fairly easy to adjust the answers from Poor-Very Good into 1-5, and how is it done and where (in the chart parameters, or must it be done on a worksheet?)

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

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" wrote:

>
> I'm sorry if this is the wrong section of the forums, but I'm not sure
> 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 person
> 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 answered
> 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 the
> Tech-Architect average for Q5 is 'Poor'. The trick is that the data
> comes into Excel as each question is answered - each row is a person
> 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 each
> other?
> -Is there a way to separate the roles automatically, or will we have to
> go through by hand and select their rows each after sorting them by
> name?
> -Will it be fairly easy to adjust the answers from Poor-Very Good into
> 1-5, and how is it done and where (in the chart parameters, or must it
> be done on a worksheet?)
>
> I think that's all - hopefully that made sense to you. We have barely
> any idea how to do this, so any insight you can give us would help
> immensely. If you need more/clearer info, please ask!!
> Thanks!
>
>
> --
> sasquatchbill
> ------------------------------------------------------------------------
> sasquatchbill's Profile: http://www.excelforum.com/member.php...o&userid=36550
> View this thread: http://www.excelforum.com/showthread...hreadid=563011
>
>

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 quite right.

At the bottom of the chart on the X axis, I'm getting each group of 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 out 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 an "Average" along the Y axis.

How do I do that? Any idea?

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" wrote:

>
> 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 quite
> right.
>
> At the bottom of the chart on the X axis, I'm getting each group of
> 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 out
> 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 an
> "Average" along the Y axis.
>
> How do I do that? Any idea?
>
>
> --
> sasquatchbill
> ------------------------------------------------------------------------
> sasquatchbill's Profile: http://www.excelforum.com/member.php...o&userid=36550
> View this thread: http://www.excelforum.com/showthread...hreadid=563011
>
>

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

The next question we have is about turning the responses of Poor, Below Average, Average etc. into 1,2,3 etc.
We've heard rumours of the ability to create a (now you may have to bear with me, I've no clue what I'm talking about here) table that 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 how we might translate those into the values we need?

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

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.


"sasquatchbill" wrote:

>
> MarkM, thanks for your help with this. Even though we're not yet where
> we want to be, thanks to you we're SO much closer.
>
> The next question we have is about turning the responses of Poor, Below
> Average, Average etc. into 1,2,3 etc.
> We've heard rumours of the ability to create a (now you may have to
> bear with me, I've no clue what I'm talking about here) table that
> 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 how
> we might translate those into the values we need?
>
>
> --
> sasquatchbill
> ------------------------------------------------------------------------
> sasquatchbill's Profile: http://www.excelforum.com/member.php...o&userid=36550
> View this thread: http://www.excelforum.com/showthread...hreadid=563011
>
>