Why Can't I Get These VERY Basic Calculations

[eliotjconnor]

I am taking an Introduction to Statistics course where we use Excel. For some reason, I cannot seem to make my process look like the professor’s. 

I’ve been struggling with this for three days now. I’m not sure what I’m doing wrong here. As far as I see, I’m doing exactly what he shows to find the solution. But the answer just doesn’t come out the same.

At this point, I’m really freaking out, because this problem relies on some of the most basic features of Excel, like squaring, square roots, addition, subtraction, and multiplication. If I can’t figure this out, then honestly, I’m going to get eaten alive by this class.

Alternatively, I guess, he could be wrong here. But I’ve used excel for three days, and he teaches with it. So I’m going to assume that he’s right and I’m wrong unless/until people tell me differently

One thing that is important to note: his work is all from a pdf template where he just plugged the numbers manually. ONLY THE FINAL SCREENSHOT IS AN ACTUAL SCREENSHOT OF MICROSOFT EXCEL (it's my Excel). So I'm plugging in the numbers exactly as I should be.

Anyway, here’s the numerous issues:

1. When I enter the command that he says to enter for getting the sum of B2-H2, he shows 1.000 but I show 1.001. If I try to add the numbers by calculator, I also get 1.001. How is he coming up with 1.000?
2. E3 should be found by multiplying .069 by 3. That’s .207. Why does he have .206? And E4 should be found by squaring 3 and multiplying that by .069, which should get you .621, yet he has .617.
3. Ditto for F3/F4. F3 should be .117*4, which should be .468, yet he has .469. F4 should be 1.872, as it’s the square of 4 multiplied by .117. Yet he’s got 1.875
4. The calculation that he uses to get “m” is =SQRT(L4-L3^2). I’d assume that would mean Column L, Row 4. But as you can see, THERE IS NOTHING IN COLUMN L AT ALL!!! So of course, when I put in the command =SQRT(L4-L3^2), I get zero. I think maybe he is mixing up the value for “l” in the equation? Because the way I’m looking at it, those are the values that might make sense.
5. Finally, if you do use I3 and I4 for the above command, I get somewhat close, I guess...?...I get 2.198 and he gets 2198. I'm not quite sure how he's getting that the square root of 22.597 minus the square of 4.215 is....over two thousand?

A couple of final things. First, don't tell me to ask the professor, drop the class, or whatever. I'm not here for life advice. I'm here for Excel advice. Anyone who is so kind as to help me, thank you. But I don't need advice, and I'm not going to explain myself further. Let's keep it to Excel and Excel only, please (sorry, but when I posted this question in Facebook groups, I got a lot of people giving the advice that I wasn't there for).

Secondly, this is NOT an issue of rounding or decimal places, so please don't offer that. If you're adding six numbers, all of which are out to three decimal places, your answer will have three decimal places. Every time. So (again, people were saying this on Facebook), we're not "both right." The proper answer in I2 is either 1.000 or 1.001. No other options. Similarly, if you multiply a number with three decimal places by a whole number, your final answer will not have more than three decimal places. Finally, rounding wouldn't even cause some of the discrepancies if it were pertinent. For example, where 3*.069 is .207 for me and he has .206...that's not a rounding issue. If you dropped it to one decimal, it would be .2, to two decimals would be .21, and to four or more would be .20700000000 or whatever. Simply, .206 cannot come up from rounding the result of multiplying .069 and 3. Period. 

Sorry if I came off like a jerk at the end there, but every Facebook notification that I got seemed to be "Why don't you ask the professor," or "Why don't you drop the class," or "Maybe his rounding was different." You can imagine how frustrating that was to think that maybe someone answered my question, only to find that it was just some guy I've never met who decided that for some reason he was entitled to an opinion about my academic future.

And finally, a big thank you for anyone who is willing to take time from their day to help a total stranger on the internet!

[#2]

@eliotjconnor 

Excerpted from all that you've written:

Alternatively, I guess, he could be wrong here. But I’ve used excel for three days, and he teaches with it. So I’m going to assume that he’s right and I’m wrong unless/until people tell me differently.

As someone who used to teach diagnostic thinking (finding the cause for a deviation from expectations or "norm"), it seems to me that you've pretty effectively eliminated most (if not all) of the alternative explanations. And, consistent with solid diagnostic thinking, you correctly state "I'm going to assume that he's right and I'm wrong." That indeed is an assumption. The next step in good diagnostic thinking is to check out our assumptions to verify one way or the other whether the assumption is valid. Yes, he could be wrong, or at least, the data that he's given you as his results could be off. That IS an entirely reasonable hypothesis. It deserves to be subjected to validation (or invalidation).

You're clearly very  bright--the way you've written is evidence supporting that. Clear and thorough. I get the same 1.001 that you get in my Excel. Frankly, from all you've said, I see no reason to suspect that Excel--or your use of it--is at fault.

The sad thing is that you've pretty much eliminated other hypotheses and, for those of us who would be willing to spend time to help, you've effectively cut off steps to check a very reasonable hypothesis. You haven't specifically, explicitly rejected the notion of asking fellow students what they're experiencing, but not wanting to be an insensitive jerk myself, I won't give that advice. 

I'm eager to see what others come up with.

By the way, if you could attach actual documents (the PDF, any Excel sheets), or place them on OneDrive or GoogleDrive, that could help us do some more diagnostic work. Images are good, but have their limits.

[#3]

@eliotjconnor 

Chill!  Putting pedantics aside, I assure you that the explanations are very simple.  So take a deep breath and read carefully.

My guess is:  the "x" values are the all possible outcomes of something, and the "p" values are their probabilities.  So the SUM(B2:H2) should indeed be 1 (100%).

The reason why you are getting different results is very simple:  the professor is working with exact "p" values, or at least with "p" values that have more than 3 decimal places of precision.  The 3dp values that he presents are rounded.  And the sum of rounded parts does not always equal the whole.

IMHO, that is the professor's error.  He should have done one of several things:

(a) Include a footnote stating something to the effect that "your calculations might differ because the presented numbers have been rounded".

(b) Present sufficient data so that you can calculate the same "p" values that he is working with.

(c) Fudge the presented numbers and dependent calculations so that everything is consistent with the presented numbers.

#c is challenging because Excel represents numbers in binary internally, not decimal, and most decimal fractions cannot be represented exactly in binary.

Instead, they must be approximated.  And the binary approximation for the same decimal fraction might vary depending on the magnitude of the number.  That is why, for example, 10.01 - 10 = 0.01 returns FALSE(!).

So it is very important that you understand and accept the fact that some Excel calculations might not match mathematical calculations and principles exactly.

-----

The bottom line is:  With the understanding of the above, you might expect some differences in calculations.

The question is:  What should you do about it:  tolerate the differences; or adjust the numbers in order to avoid or at least hide the differences?

I don't know because I don't the purpose of the exercises.

-----

I have much more explanation that should be helpful.  But I must take a break right now.

I will post more soon.  I just wanted to give you something now to put your mind at ease, and to let you know that the explanation is easy and easy to understand.

More later....

[#4]

I agree with @Joe User that a) the problem is most likely the p values being rounded to 3 decimal and b) that this is a professor issue for not giving you the proper information to use. The only thing i have to add is that I agree the L4 and L3 refer to column I and in fact there is 'red ink' showing that I3 = L so apparently there is some confusion happening where m should be = SQRT(I4 - L^2) and then add to the confusion it appears he missed the decimal point. Maybe it is me but it appears that particular value for m appears to be overtyped and not a value from the spreadsheet (i.e. human error).
In the end, from what I see, I think you are correct/on track and the 'solution' is misleading or confusing at best

[#5]

@eliotjconnor  wrote:  ``this is NOT an issue of rounding or decimal places``

Yes, it is.  But I think "you" misunderstand what the rounding issue is.

(I put "you" in quotes because perhaps it is really an explanation that someone offered.)

You are correct that it is __not__ a rounding issue with your Excel calculations.

Instead, it is a rounding issue with the numbers that the professor presents.  Let's look at your examples.

1. ``E3 should be found by multiplying .069 by 3. That’s .207. Why does he have .206? ``

So, we can conclude that the actual "p" value is between 0.0685 and 0.06949...9, which the professor rounded to 0.069.

For simplicity, I'll say "between 0.0685 and 0.0695" with that understanding that I mean 0.0685 <= p < 0.0695.

And in fact, 0.206 (sic) is actually a value between 0.2055 and 0.2065.

That is, 0.2055 <= 3*p < 0.2065

So 0.2055/3 <= p < 0.2065/3.  So 0.0685 <= p < 0.0688333333333333

2. ``F3 should be .117*4, which should be .468, yet he has .469.

    F4 should be 1.872, as it’s the square of 4 multiplied by .117. Yet he’s got 1.875``

So, 0.117 (sic) can be a value between 0.1165 and 1175.  0.469 (sic) is between 0.4685 and 0.4695.  And 1.875 (sic) is between 1.8745 and 1.8755.  Thus:

0.4685 <= 4p < 0.4695 implies 0.4685/4 <= p < 0.4695/4.

So 0.117125 <= p < 0.117375

1.8745 <= 16*p < 1.8755 implies 1.8745/16 <= p < 1.8755/16

So 0.11715625 <= p < 0.11721875

The latter is the better range for "p" because it is more restrictive.

3. We might work through similar calculations for each "p" value.

Unfortunately, then there are many combinations of the "p" ranges that might sum to 1.

(And many more combinations that do not.  Those would be invalid combinations.)

We cannot know which combination is best.

If all of this is very daunting for you, I sympathize.  You are struggling to learn statistics and Excel.  It is unfair that the professor foisted another burden on you, namely sussing out his misrepresentations.

4. You wrote:  ``don't tell me to ask the professor [....] I'm not here for life advice.``

Fair enough.  But I think this might be valuable feedback for the professor.  And he/she might provide you with the more accurate numbers so that you can proceed with the assignment with confidence.

More importantly, hopefully that feedback might avoid such confusion in future assignments.  (Although I doubt it.)

5. ``The calculation that he uses to get “m” is =SQRT(L4-L3^2) [....] I think maybe he is mixing up the value for “l” in the equation?``

Excellent intuition!  Give yourself some credit. (smile)

Speaking from personal experience, I'm afraid that is a very common mistake that we all make with our examples.  We start with one design (the sums in column L, in this case), then we "simplify" our design, but forget to update all of the formulas.

Again, I understand your frustration.  It's an unfair burden while you are struggling with what you're supposed to be learning.   But I'm sorry to say, as Forrest Gump did:  (sh)it happens. (sigh)

6. ``if you do use I3 and I4 for [...m...] I get 2.198 and he gets 2198``

You mean, if you use values that the professor presents in I3 and I4.

I think it is "obvious" that the professor got sloppy and omitted the decimal point.

Or "m" should be 1000*SQRT(...).  But I don't really believe that.

But note:  we really get 2.19790240911647, not 2.198.  So you committed the same confusing mistake that the professor did:  you presented a rounded number without telling us.  (wink)

The bottom line is:  Re ``I'm here for Excel advice``, it looks to me like you've nailed the Excel usage.  Congrats!

[#6]

@eliotjconnor 

I wrote:  ``the professor is working with exact "p" values, or at least with "p" values that have more than 3 decimal places of precision``.

We cannot know what values the professor uses.

But the following shows some values with 4 decimal places that you can use to duplicate the professor's presentation.

At the very least, that should allow you to proceed with verifying your Excel implementation.

Row 9 shows the actual values that are in row 2, but formatted to display only 3 decimal places.

B10 shows that actual value in B6 up to 15 significant digits, the most that Excel formats.

I hope that helps you understand my explanation of the rounding issue.