cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
dmg34
Community Contributor

Problem with a list/sort function for a formula problem

Jump to solution

I was very eager to try applying some of the techniques in  @James ​'s post, Looking up Values in Formula Questions. I'm trying to write problems for a stats class, and I think my case is actually even a little bit easier than the one addressed in the blog post. My list is the areas to the left of z, over a range of z-scores (for this question, the z scores can range from z=0.10 to z=1.50).

I believe I understand everything in the blog post and felt ready to tackle designing my question. I have produced the comma-delimited list (I use Excel for the values and then pasted into Word and cleaned it up). But, when I try to enter it into Canvas per the directions in James' post, I get the following error:

Capture.PNG

I have tried to determine the issue by test-entering a short list, such as list=sort(5,3,4) or even just sort(5,3,4), and various permutations and variations thereof. Nothing works, I always get this error. I also tried the "reverse" function. I am at a loss for why this isn't working, and obviously, it has stopped me dead in my tracks for the result I am trying to accomplish! I will be tickled pink if I can get this question to work the way I want it to (it opens up soooo many possibilities for using Canvas to test this topic!) but I don't know what to do now. Any help would be greatly appreciated!

13 Replies
James
Community Champion

I took formula 26.2.17 from Abramowitz and Stegun, p932, and converted it into a Canvas formula.

It will give you almost 7 decimal place accuracy (|error| < 7.5e-8). As confirmation, I went from z=0 to z=7.19 (I had to stop somewhere) by 0.01 and compared the value returned from the formula to the value returned by Excel, the largest error was 7.45061e-8

The formula they gave is only for positive values of x, but I modified it to work positive or negative values. I also used z instead of x, which frees people up to use x in the problem and compute z separately (to 4 decimals at least).

There was a formula directly above it that was simpler and would guarantee 4 decimal place accuracy. However, I have my students use a probability calculator that I wrote instead of the tables in the back of the book and it gives 6 decimal places. I did generate 200 possible solutions involving positive and negative z scores between -5 and 5 and then copy/pasted those into Excel where I compared to the value Excel gave (also rounded to four decimal places). There were no errors, so I feel pretty good about the formula.

Just to be clear, the formula does return the area to the left of the z-score.

If you want them to have answers that compare to Minitab or other software that don't round until the very end, then paste this formula into another calculation.

if(z,if(1+z/abs(z),1-(0.31938153/(1+0.2316419*z)-0.356563782/(1+0.2316419*z)^2+1.781477937/(1+0.2316419*z)^3-1.821255978/(1+0.2316419*z)^4+1.330274429/(1+0.2316419*z)^5)*e^(-0.5*z^2)/sqrt(2*pi),(0.31938153/(1-0.2316419*z)-0.356563782/(1-0.2316419*z)^2+1.781477937/(1-0.2316419*z)^3-1.821255978/(1-0.2316419*z)^4+1.330274429/(1-0.2316419*z)^5)*e^(-0.5*z^2)/sqrt(2*pi)),0.5)

If you want them to have answers rounded to 4 decimal places like they would get from the back of a textbook, then you need to force rounding of the numbers. I originally thought that generating it with 4 decimal places would round it to 4 decimal places, but it seems that now Canvas only displays it to 4 decimal places and keeps the full number rather than actually rounding it to 4 decimal places. I'm not sure if this is a change, but I'm pretty sure it is and I'm also pretty sure it's going to mess up some people's formulas who are counting on Canvas rounding.

round(10000*if(z,if(1+z/abs(z),1-(0.31938153/(1+0.2316419*z)-0.356563782/(1+0.2316419*z)^2+1.781477937/(1+0.2316419*z)^3-1.821255978/(1+0.2316419*z)^4+1.330274429/(1+0.2316419*z)^5)*e^(-0.5*z^2)/sqrt(2*pi),(0.31938153/(1-0.2316419*z)-0.356563782/(1-0.2316419*z)^2+1.781477937/(1-0.2316419*z)^3-1.821255978/(1-0.2316419*z)^4+1.330274429/(1-0.2316419*z)^5)*e^(-0.5*z^2)/sqrt(2*pi)),0.5))/10000

James
Community Champion

The problem with your approach is that you're rounding some of the intermediate steps to 4 decimal places, but missed the important one.

Unaware of what you were writing, I took some time and converted the formula to be an all-in-one so there is no rounding issue. After I had saved it, I saw this message and realized I had just answered the next problem.

If you want to mimic the behavior of looking up values in a book, then save the result to a variable with 4 decimals and then use that variable.

There is still a potential issue - if your calculations yield a z-score with more than 4 decimals, it might be off. So, you might have to tell your students to round z-scores to 2 decimals (you're probably doing this anyway if you're making them use tables). Then make sure when you compute z that you round it to 2 decimal places in Canvas.

For the in-between numbers, you can just reassign z before doing the calculation

204772_pastedImage_0.png

However, even though the probabilities will match the back of the textbook to 4 decimal places, that doesn't mean that they will come out right.

Consider the following:

204842_pastedImage_1.png

The formula correct returned 0.4090 for the area to the left of z=-0.23 and 0.9545 for the area to the left of z=1.69.

However, Canvas subtracted 0.9545-0.4090 and got 0.5454 instead of 0.5455

However, if you don't round, then the actual value is 0.545440138, which rounds to 0.5454 - the value that Canvas returned.

It looks like Canvas is not using the rounded value, only rounding for display purposes. That's good, but it throws off the people who want their student's to look up values from a table.

I added a round statement to the check to force rounding to 4 decimal places.

round(10000*if(z,if(1+z/abs(z),1-(0.31938153/(1+0.2316419*z)-0.356563782/(1+0.2316419*z)^2+1.781477937/(1+0.2316419*z)^3-1.821255978/(1+0.2316419*z)^4+1.330274429/(1+0.2316419*z)^5)*e^(-0.5*z^2)/sqrt(2*pi),(0.31938153/(1-0.2316419*z)-0.356563782/(1-0.2316419*z)^2+1.781477937/(1-0.2316419*z)^3-1.821255978/(1-0.2316419*z)^4+1.330274429/(1-0.2316419*z)^5)*e^(-0.5*z^2)/sqrt(2*pi)),0.5))/10000

Now the formula correctly matches what's in the table from the textbook and uses the rounded values in the subtraction.

204843_pastedImage_3.png

I'll fix my other post to reflect that Canvas does not round the other values.

James
Community Champion

In tracking this down, I found the problem, but forgot to go back and fix the first sentence. It's not that you're rounding to 4 steps, it's that you're only rounding some of the results. Your issue with it not coming out right was that you didn't rounding a1left and aleft2 and Canvas doesn't round variables like I'm pretty sure that it used to. I'll edit my first sentence to reflect the change.

dmg34
Community Contributor

 @James ​, I know I sound like a broken record, but thank you again! This approach is so much more slick and concise than mine. And gives the exact result that I'm trying to achieve! I had originally tried to enter the Zelen and Severo formula in one line, but I was getting errors and having trouble tracking down the problem, so I broke it up into pieces. (Ironic, since I often harp on my students about rounding in intermediate steps.... lol.) Your use of the if statement to both check for z=0 and also account for z<0 or z>0 is much more efficient, and the round() element should take care of the table-matching problem. I only have them use the table at this point. I do show them how to use their calculator function to get the area, but I find that only a small handful of students actually prefer to do it that way.

Have a great weekend!