I have a sum product formula (H4) that gives me the average from the array in G7:k11. Is there anyway the get the standard deviation for this array?
Thanks for the help,
John
I have a sum product formula (H4) that gives me the average from the array in G7:k11. Is there anyway the get the standard deviation for this array?
Thanks for the help,
John
Last edited by johnexceljohn; 10-09-2014 at 04:08 PM.
There are no formulas in your spreadsheet.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
can you use the STDEV() function?
currently your deviation is 0 and your average is 1 because all the data is 1
note also that there are a few different "types" of STDEV in XL....HTH....
Ernest
Please consider adding a * if I helped
Nothing drives me crazy - I'm always close enough to walk....
Sorry, I'll explain better. These are survey responses ranging from 1-5. The "1's" are where the person responded for that question. So a 1 under 3 is a 3.The formula in H4 gives me the average response for each question. I'm looking for a similar formula that could give me the standard deviation. So the SD for question 1 is 0.70, but I don't know how do get that from the info given.
that helps...
As ChemistB Stated....you don't have any formulas in your spreadsheet....
did you mean to do that?
Oops, wrong version. I'll try to upload the new one.
Ok, I updated the attachment.
This would be a lot easier if you made your data table less painful to operate on.
With:
1) IF(G-row = non-blank integer, Result, "")
2) IF(that above <>"", sumproduct(response, count), "") & pull down for each respondent
3) copypaste (transpose) on the whole range
4) "remove duplicates" command
I flipped that table right around into something easy-to-use, then it was this easy:
AVERAGE(row)
STDEV(row)
see attached.
Click the [* Add Reputation] Button to thank people for help!
Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.
Yeah, I was hoping for a way to do it without changing the format, because that's how the online site delivers the info to me.
I dig that.
But sometimes it's better to do some re-arranging to put data in good layouts friendly to analysis, then to struggle with data in a layout with other priorities.
Here, I recorded what I did as a macro, so you can run it yourself and see what I mean:
Please Login or Register to view this content.
I think you're right. Thank you for this!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks