Thank you Boris!
Is this what you mean?
Is this what you mean?
Statistical Nerds League (SNL)
- Thread starter blethenstrom
- Start date
- Thread starter
- #62
That is exactly what I am talking about! Now I have to see how you transpose a spreadsheet. Give me a second
- Thread starter
- #63
Found it......
Once you have all your data in there you can transpose by using this command
=TRANSPOSE(<left upper most cell>: <right lower most cell>) This should flip the row and columns for you and you are set!
Hi Boris
Is this what you mean?
When I press enter I'm hit with the following warning:
Attachments
Last edited:
@blethenstrom @Guido75 I Used the Power Query editor to transpose the data. Here is what the table looks like now:
Once I selected Close and Load my spreadsheet looks like this:
Much nicer!! Thank you!!!
Much nicer!! Thank you!!!
Hey Paul!
Looking good! Regarding this error message - do you have a formula in one of the cells? This particular error means that a formula is referencing to itself.
Looking good! Regarding this error message - do you have a formula in one of the cells? This particular error means that a formula is referencing to itself.
Thank you Guido!!
I'll take a look and see if any other cell has a formula. Thank you for helping to light the creative fires!
Paul
I'll take a look and see if any other cell has a formula. Thank you for helping to light the creative fires!
Paul
- Thread starter
- #69
Very cool! Now you are cooking with gas! The error message could be that there was not enough room for your transposed data. The command I shared will generate a second copy of your data. However your figured out a different way. Awesome! Now you can just add your new shave data to the bottom of this list and you can manipulate and look at your data in many different ways.
Thank you very much Boris! This is fantastic! You guys are awesome!!
- Thread starter
- #71
I'm so happy that we were able to help you out. Granted you really did the leg work. The Google sheets and excel are really powerful in manipulating data.
You might want to take the next step and maybe add a column or two for some numerical shave ratings going forward. That way you open up the possibility for lot more ways to look at the data. Blade and razor performance etc etc.
In an attempt to make analysis easier I have introduced a new variable called Total which is a weighted average of Quality of the shave (for me meaning the amount of blood I am drawing), Comfort (level of smoothness) and end result (BBS, et cetera). The weights I have attached to each of these scores is 1 for Quality (I don´t like to draw blood obviously, but it is also part of life, and my technique is currently as such that it's no longer havoc and mayhem), 4 for Comfort and 5 for End result (I am most OCD about that - why would I else want to shave?). Combined these weights amount to 10 and so I calculate variable Total as [(Q*1)+(C*4)+(E*5)]/10. I then let Excel calculate the average T per razor and blade in my Pivotal Table.
If I then reverse sort these with the largest average on top there are definitely some changes in my Top 5 razors! For example while the Blackbird is the most effective razor overall, if I factor in Quality and Comfort there are times when the shave as a whole is less pleasant. So with a combined average of 9.12 the Mühle Rocca R94 is the razor that gives me the best shaves and also really consistently as the standard deviation is small as well. In blades there is also a shift with Eddison coming out as best blade followed by GSB (
).
Mind you these are raw scores, so I haven’t standardised anything.
If I then reverse sort these with the largest average on top there are definitely some changes in my Top 5 razors! For example while the Blackbird is the most effective razor overall, if I factor in Quality and Comfort there are times when the shave as a whole is less pleasant. So with a combined average of 9.12 the Mühle Rocca R94 is the razor that gives me the best shaves and also really consistently as the standard deviation is small as well. In blades there is also a shift with Eddison coming out as best blade followed by GSB (
).Mind you these are raw scores, so I haven’t standardised anything.
With the way I actually worded these weights the numbers might have to be reversed because end result is more or less a given for me. Hence more emphasis is then put on quality or comfort.
That's a great suggestion! I think for starters I'll add one column which will include a shave rating from 1 to 0 where 10 is off the charts awesome while one is as bad as it can get.
Ok I just added a column for ratings. Ran the Power Query to transpose:
Resultant data:
Resultant data:
Looks great Paul! If I were you I would transpose your dataset again to have line items per date rather than having the dates in columns - that will make it easier to glance at.
But you have unique data in each cell so that’s the basis right there for you!
Excellent!
Guido
- Thread starter
- #77
That's a cool way to look at things if we want to put weights on certain criterion and emphasize those things that are more important to us.In an attempt to make analysis easier I have introduced a new variable called Total which is a weighted average of Quality of the shave (for me meaning the amount of blood I am drawing), Comfort (level of smoothness) and end result (BBS, et cetera). The weights I have attached to each of these scores is 1 for Quality (I don´t like to draw blood obviously, but it is also part of life, and my technique is currently as such that it's no longer havoc and mayhem), 4 for Comfort and 5 for End result (I am most OCD about that - why would I else want to shave?). Combined these weights amount to 10 and so I calculate variable Total as [(Q*1)+(C*4)+(E*5)]/10. I then let Excel calculate the average T per razor and blade in my Pivotal Table.
If I then reverse sort these with the largest average on top there are definitely some changes in my Top 5 razors! For example while the Blackbird is the most effective razor overall, if I factor in Quality and Comfort there are times when the shave as a whole is less pleasant. So with a combined average of 9.12 the Mühle Rocca R94 is the razor that gives me the best shaves and also really consistently as the standard deviation is small as well. In blades there is also a shift with Eddison coming out as best blade followed by GSB (
Mind you these are raw scores, so I haven’t standardised anything.
- Thread starter
- #78
Now if you do that for a few weeks we can start doing some averages and look at scores vs razor, blade etc
Thank you Guido! I will take a look to see if I can find that setting. Timing is great because I had an afternoon shave so I have more day at to enter!
My brain is just no good for mathematical statistics. I enjoy reading them. When I read stats I'm on the verge of comprehension then it quickly dissipates. It's probably my ADHD. I hit watch thread and will enjoy the posts as I read them. 
Similar threads
