Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I display multiple series in a pie chart? | Charts and Charting in Excel | |||
How to select numeric 'series axis' in a surface chart? | Charts and Charting in Excel | |||
How do I pass series data x-value range to a chart from a cell? | Charts and Charting in Excel | |||
create chart from series grouped by value in another column | Charts and Charting in Excel | |||
Can I add an average series to a chart with 2 or more series? | Charts and Charting in Excel |