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 a
-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

Can I use Formulas to Calculate Chart Series?
 

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 a
-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

Can I use Formulas to Calculate Chart Series?
 

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

Can I use Formulas to Calculate Chart Series?
 

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

Can I use Formulas to Calculate Chart Series?
 

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

Can I use Formulas to Calculate Chart Series?
 

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


--
sasquatchbill
------------------------------------------------------------------------
sasquatchbill's Profile: http://www.excelforum.com/member.php...o&userid=36550
View this thread: http://www.excelforum.com/showthread...hreadid=563011