Weighted_Avg_SumProductTransaction DateItemPrice Per UnitUnits Sold5/7/07Coffee$ 205006/7/07Coffee$ 257507/6/07Coffee$ 352008/9/07Coffee$ 30300

Avg. PriceWeighted Avg. Price:

&A

Summations12345678910

Arrays must be entered by pressing: CONTROL + SHIFT + ENTER

&A

FactorialsNumber of different types available (n):Number of items being selected (k):Combinations (with replacement):

Fact() Exercise: Combinations With ReplacmentYou have an unlimited supply of Coconuts and Pineapples (n=2). How many different ways can you fill a bowl which will contain exactly 3 items (k=3)?

FrequencyNameScoreBinsJim100100Sue6289.99Jane5179.99Anne9969.99Mike4359.99Jon89.999Wess86Kraig84LetterGradeLisa89AJay82BBob79CTony76DGary80FSteve64Annette98Lisa84Carrie0Susan90David90Micheal89

Solution67223

Bins ArrayData Array=Frequency(Data Array, Bins Array)Because this is an array function, you must:- Highlight the answer area before you type.- Press CONTROL + SHIFT + ENTER rather then enter.- Top value is inclusive but bottom value is exclusive.

Example:Determine how many students fall into each grade category (i.e. A, B, C, D, F)Grade Ranges>89.99 and A>79.99 and B>69.99 and C>59.99 and D F

Rank_and_PercentSIDScoreRank.Eq() (Descending)Class RankPercentRank.Exc()ClassPercentileNorm.Dist()National PercentileRank and Percentile Information (Local)Bins60611.490th Percentile Cutoff Score with Percentile.Exc()060261.8Number of Students in the 90th Percentile with Countif()1065722.3Percent of Students from class in 90th Percentile2058363.1Mean3063123.1Standard Deviation with Stdev.P(Range)4061243.95065874Comparison to National Statistics6059374.1National Mean (given)657059154.8National Standard Deviation (given)158064905.1National Cutoff Score for 90th Percentile with Norm.Inv()9064335.3Number of our Student in National 90th Percentile: CountIF()10058385.7Percent of our Students from Class in National 90th Percentile 66075.767095.966326.361226.566446.561397.856818.367478.564508.767558.757828.866898.86163956609.159519.365459.366049.556689.8574910660310.5640910.9657110.9665511.2583711.4604511.4620911.4594111.5585511.7589511.8615012652912.2622612.3659912.3663112.4668112.9617013609313.1626713.1568213.5628613.5655713.6580113.7647313.7646514625914.2676514.3625414.5560514.8603515603115.2597615.4657715.7564316.2645516.2670716.2633616.3595216.7608317.2597717.3634617.5635317.5636717.5584017.6605017.7571417.9636018.3559718.7627218.9637218.9649218.9653118.9623419.1592219.2658919.2619819.4635719.4655419.5582619.6649919.6652219.7650119.8652119.9584520642520609620.2675720.2633420.6620520.7614320.9583921665321.2650421.3647021.4670521.5639821.7612321.8630621.9651622581722.2636522.2619722.3656622.3573222.4654122.5565722.6578122.6607822.6597222.7671622.7617622.8654722.9619523589223.2635023.2675423.2587923.3634323.3561923.6564423.6652523.7662923.8561123.9618423.9660123.9627724559424.2626824.2602524.3663424.3671724.5577124.7626924.7558825604925656925.1676025.1617425.3672825.3565225.4666225.7595525.8574125.9630826558726.1588426.1633826.3673226.3581926.4587226.5611526.5661626.7588926.8659026.8673926.8594226.9666626.9652627591027.1621527.1611327.2569027.3607227.3651127.3571627.4587127.4590927.4618127.5617827.6655827.6588027.7619327.7665827.7672527.8645427.9658327.9663927.9603228590728.1635428.1671928.4573728.5588828.5629228.5607428.7609528.8628828.8584228.9571129559229.3622829.4633929.5586929.6596229.6625529.6585129.7627629.8641030.1661030.1567130.2600530.2619030.2594930.3623530.4664730.4655230.5645330.6604630.7675130.7588230.9655930.9674530.9656031583031.1611631.2638831.2648331.2674031.3616531.4662831.4619431.6662431.6665731.6589631.8606031.8609431.8582831.9662631.9668631.9593132630732644132616632.1667632.1639932.3650932.3660832.3570932.4575132.4620832.4574432.6642632.6569632.7576532.7673732.7574532.8617332.8622332.8665032.8655033610133.1624033.1627333.1615133.2585833.3662133.3566233.4625333.4666033.4567333.5607033.5625133.5639233.5561633.7651433.7655633.7640633.9662034572834.1659734.1669434.1674834.1638734.2563534.6610634.6575834.7647434.7647534.7561334.8657434.8665934.8666434.8561034.9574634.9582134.9633034.9642934.9650334.9673534.9580735571735.2573335.2648235.2671435.2649535.3654435.4609135.5637735.6654935.6562035.8592535.8577236.1582236.1657936.2563336.3639136.4641736.4624436.5591936.6659336.6613336.7650836.7606836.8643236.8561436.9584936.9598836.9559837566337594737581537.1618337.1620037.1647937.1666537.2573537.3575237.3607337.3667837.3585937.4614737.4587637.5641237.5664837.5563937.6650737.8592037.9593238610838620338.1638038.1649838.1561838.2659838.2580338.3661338.4668538.4568338.5585338.5669638.6600838.7670638.7572338.8596438.8665238.9604439605839614139645739648539593039.2583439.3634939.3660639.3621939.4639039.4651239.4626239.6675639.8599439.9559640586640594340631840611040.1645240.1660940.1662740.1667240.1591140.2592140.2612540.2630040.2570440.3604240.3645640.3560040.4598040.4602840.4568740.6647240.7655340.7663540.9664141.1571241.2613841.2642041.2586441.3621041.3626141.3661941.3620141.4657841.4571841.5646641.6574741.7603641.7597441.8601241.8574241.9580642606642618242.1566942.2627542.2630342.2630542.2600442.3648842.4584342.5602942.5646242.5587842.6593542.6618042.6641642.6657042.7563442.8650042.8570742.9572542.9582443564243.1569843.1578743.1611743.1596543.2622743.4658543.4675043.4613243.5640343.5648643.5567043.7604743.7602343.8588343.9674243.9634144588144.1606244.2640044.2668444.2612844.3576744.4652844.5615544.6663344.6672644.6649444.7651744.8586844.9589144.9633144.9637144.9579545.1607145.2558945.3601645.3667445.3563045.4581045.4601745.4643945.4654245.5575445.6602245.6643145.6595945.7565945.9567945.9644946598446.1616446.1662546.1617946.2619646.3666746.3570646.4571946.4600746.4603946.4612646.4643746.4671246.4638346.5623346.6663746.6605446.7612046.7653446.8667347573447.1642247.1572047.2587447.2624147.2629647.2628447.3660547.3668847.3569147.4669547.4605547.5621847.5573947.6605647.6637947.6631447.7653047.7626547.8577347.9561548.2626648.3562848.4572748.4573048.4615648.4576248.5580548.5619948.5585248.6669248.6565448.7581648.7605948.7621248.7596048.8631648.8651848.8590348.9623148.9627448.9628148.9668248.9560449564549671849598649.1631149.1592649.2562749.3596949.3575349.4581349.4607549.4671049.4595449.5625249.5628049.5653349.5591349.6647749.6652749.6565349.7564749.8575049.8637849.8641149.8651549.8632749.9621650622150641550656150592750.1629850.1647650.2562950.3603050.3654050.3657350.5578550.6605750.6640150.6670050.7562650.8565051603751586251.1601551.2673051.2586351.3621151.3639751.3649151.3664551.3605251.5615951.5632251.5661151.5664351.5564951.6569351.6605351.6674351.6625851.7573851.8614851.8600951.9636851.9571352582952629052661852577652.1607752.1620452.1632152.1667052.1594652.2604052.2585752.4612152.4667152.4669052.4580052.5589852.5624952.6658052.7659652.7564052.8574852.8577452.9580852.9587052.9623252.9650652.9583253600253588553.1671353.1563153.2581253.2592453.3615453.3625753.3656353.3623053.4659453.4567253.5655553.6565853.7610553.7665653.7603453.8640853.8581854577054.1633254.1558654.2599854.2656854.2566754.3575654.3675954.3580254.5582054.5610254.5629454.5645154.6562454.7565154.7594854.7631754.7659254.7664054.7612954.8614954.9643054.9603855567755.1645955.1558455.3560755.3578855.3648955.3642355.4581155.5584755.6611155.7649755.7572155.8632955.9636155.9589756638256640756661456601856.1646456.2652056.2661256.2666956.2673656.2570356.3578056.4606456.5598556.6616156.6653956.7659556.7665456.7671156.7602156.8615256.8610356.9640256.9642456.9578957619257622957576057.1642857.2562157.4601957.4658257.4578357.5587557.5599057.5615757.5637057.5596157.6632357.6577557.7634257.7672757.7636357.8616258.2618958.2624858.2669858.2573658.3592958.3631058.3664658.3579058.4601158.5674958.5585658.6594058.6618558.6627958.6598158.7615358.7616058.7625658.7579658.8607958.8608558.9673158.9675858.9611459658159586559.1658659.1614059.2639559.3609259.6668359.7580459.8567859.9640559.9663660.1644260.2608760.3600360.4570060.7591860.7621460.7654660.7646960.8579461593661638461568561.3607661.3636961.3642761.3652361.3617161.4592861.5613661.5673361.5582761.6579361.7648761.8666161.9613762618762576662.1559162.2667762.2599162.4602462.4569562.5621362.5561762.6600662.6582562.8574362.9661762.9583163619163.1673863.1601063.3638163.3558563.5613063.5626463.6653263.6610063.7654863.9564164599264.1598964.3591764.4624364.4645864.4648164.4568964.5596764.5629964.5564664.6634464.6584164.7561264.8622064.8631564.8634764.9669964.9572265614565.1637665.2659165.2662265.2593465.4646865.5676465.5613465.6566465.7568065.7576465.7666365.7604365.8639465.8658865.9660065.9672465.9584666569966.1669166.1597066.2563766.3595666.3616966.3676166.3637366.4646066.5674466.5559966.6599766.6658466.6676766.6567566.7625066.7660266.8628366.9560667597967657567606767.1608267.1609767.1627867.1597167.2667567.2629567.3652467.3597367.4611967.5644867.5653767.6560167.7668767.7606367.8626067.8590267.9662367.9562568633768670168563668.1675268.1670868.2654368.4562268.6565668.6597568.6665168.6634068.7596668.8623968.9676368.9663069579169.1586769.1641469.1565569.3603369.4606569.4647169.4631369.5651369.5585069.6614469.6611869.7620269.8666869.8571569.9610769.9598770634870.1596870.3648070.3664970.3576970.4594570.4583370.5624570.6646770.6595870.7605170.7562370.8641970.9580971627171630171649371675371623771.1613171.2578471.7614671.7609871.8642171.8579871.9601471.9620771.9656572670472589972.1653572.1613572.2647872.2577972.3622472.3635572.3621772.4663872.4599572.8608872.8634572.8577772.9644072.9591673637573.1560273.2657673.2648473.3672373.3610973.4643573.4650273.4589373.6672173.6566673.7617273.7664273.7585474575774.1617574.1628974.1567674.2643674.2559374.3676674.3629174.4636674.4673474.4570274.5646174.5604874.6576174.8644574.8589074.9672974.9610475.1593875.2628275.3608675.7627075.7628775.7644675.7653875.7632475.8635275.8635975.8638975.9595376643476576376.3587776.3612776.3649676.3670376.3601376.4630976.4639376.4568876.6577876.6594476.6570876.7596376.7609976.7572676.8600176.9563277571077622577633577.1590877.3636477.3615877.4631977.4617777.5575578559578.1670278.1583578.2608078.2644478.2656478.2579778.4572478.6624678.6630278.6569778.7563878.9614279620679581479.1582379.1616879.1579279.3568479.4591279.4644379.4623879.5656279.6573179.7618679.8624779.9572980655180629780.1590680.5669380.5584480.9633380.9602781668081626381.2588681.4638581.8632581.9640481.9595082646382.2604182.3564882.4622282.4608182.5641382.7590582.9609082.9566183586083597883.1591483.4592383.5560983.7568683.8587384593384608984635884635684.2608484.3656784.4674184.7595784.8599384.8589485624285570585.1676285.2569285.3616785.3630485.5643885.6598285.8651985.9590486.4674686.4629386.5602086.6599986.9600086.9672287.4606987.6590087.8635187.9661587.9584888.1632688.1667988.1590189.5611289.5579989.6650589.6569489.9639690.7669791570191.2637491.8644792599692.2560392.3672092.4651093623694.1560894.2653694.3632894.4588794.5578695.3586195.4638695.5558395.6636296575996.2593996.9598396.9628597.1567497.5574097.8576898671598.2559098.4566598.5618898.9641899.46320100

Local (i.e. internal) Comparisons Find our student's local rank using Rank.Eq() Find our student's local percentil using PrecentRank.Exc() What is the cutoff sore for our students to be within the local 90th percentile? Use Percentile.Exc() How many of our students are within the local 90th percentile? Use CountIf() What percent of the class is in the 90th percentile? What is the class average? Comparison with National Test Data Find our student's percentile when compared to the national test scores where the national mean is 65 and the National Standard Deviation is 15 using Norm.Dist() What is the National Cutoff Score for the 90th Percentile? Norm.Inv() Number of Students in this Class that are in the National 90th Percentile? --> CountIF(Range,criteria) Percent of Students in this class who are above the 90th Percentile.

Probability_ExerciseAverage Height69.1inchesStandard Deviation2.9inchesLow Data Point70.0inchesHigh Data Point74.0inchesProbability of Exaxtly 70"Probability of 70" or LessProbability of 70" to 74" Probability of greater than 70"

Using Norm.Dist() for Probability Between Two Data PointsA new students is being sent to our height minded Volleyball coach to be on his team. Again, Google tells him that in the USA, the average height of a man is 69.1" with a standard deviation of 2.9". What is the probability that the student will be exactly 70 inches tall? What is the probability that the student will be 70 inches tall or less? What is the probability that the student will be between 70 to 74 inches tall? What is the probability that the student will be greater than 70 inches tall?

TTest_Ex1Treated w/DrugControl Group146144136166138178132114114136132148140150126134142160146156

T-Test Exercise 1: Two-Sample, One Tailed using Data Analysis ToolpakA researcher gave one group a drug to lower toxins in their cells and another group a placebo. He wishes to test the hypothesis that the amount of toxins in the group treated with the drug will be less than the group given the placebo.

TTest_Ex2IndividualBeforeAfterA231216.7B225.5214.5C212.3210.1D200.2191.4E284.9259.6F262.9248.6G180.4172.7H216.7215.6I244.2221.1J232.1215.6K205.7199.1L192.5180.4M204.6199.1N267.3251.9O270.6254.1

T-Test Exercise 2: Paired, TwoTailed using Data Analysis TookpakA company wishes to see if a new drug it has developed for diabetes has any effect on people's weight. They weighed several individuals before giving them the drug and then again after giving them the drug. The table below shows the weights for the individuals before and after taking the drug.

TTest_Ex3Sunnydate Avg TV Minutes Per DayNational Average265130225160162158220162183190245220240256300

T-Test Exercise 3: One Sample, Two Tails"TV's R Us" is considering opening a store in the town of Sunnydale and wishes to see how much TV per day the town actually watches. They surveyed 15 Sunnydale residents and came up with a mean of 207 minutes per day. They wish to compare this to the national average of 290 minutes per day to see if their results are due to randomness (null hypothesis) or there is an actual difference in the means.

ZTest_Ex1Variance OldVariance New

Old Fire LogsNew Fire Logs49.368.342.672.851.562.729.158.242.657.149.362.767.262.772.868.349.352.648.277.356.054.957.154.971.754.942.657.151.584.068.357.157.154.968.357.168.354.950.458.257.147.043.741.451.550.462.758.257.188.559.454.948.259.456.058.241.451.547.065.071.752.649.377.354.954.959.454.958.254.948.256.057.171.742.651.5

Slope_and_InterceptXYMonthAdv. CostSalesJan$ 10,000$ 200,000SlopeFeb$ 12,500$ 215,000Y-InterceptMar$ 13,000$ 217,750Apr$ 13,500$ 218,000May$ 14,000$ 220,000Jun$ 14,500$ 226,038Jul$ 15,000$ 227,338Aug$ 17,000$ 229,320Sep$ 18,000$ 230,571Oct$ 19,000$ 236,000Nov$ 20,000$ 230,000Dec$ 21,000$ 237,250Jan$ 14,000Feb$ 15,000Mar$ 16,000

Slope (m) - How steeply Y values increase or decrease over a given time period. Higher numbers indicate an increase, negative numbers indicate a decrease.

Find the slope of Sales over the year.

=Slope(Known Y's, Known X's)Y-Intercept (b) - Where the line crosses the Y-Axis.X = Adv. Cost & Y = Sales. Where the line crosses the Y axis tells us what sales would be if we spent nothing on advertising.

=Intercept(Known Y's, Known X's)Regression LineY Value = X Value * Slope + Y InterceptorY=mx+b

Forecast()Year200920102011201220132014New Home Start-ups800,000900,000600,000400,000425,000500,000Hardware Store Sales$ 2,000,000$ 2,250,000$ 1,500,000$ 1,000,000$ 1,060,000

Correlation:

R-Squared:

Trend()_Ex1MonthAdvertising CostSalesJanuary100$3,100February210$4,500March305$4,400April395$5,400May485$7,500June550$8,100July600August625September700October800November900December1000

Trend()_Ex2Similar Offices In the AreaX1Square FeetX2No. of OfficesX3No. of EntrancesX4AgeY1Value23102220$142,00023332212$144,000235631.533$151,00023793243$150,00024022353$139,00024254223$169,000244821.599$126,00024712234$142,90024943323$163,00025174455$169,00025402322$149,000Office to be Sold25003225

TREND(Known Y's, Known X's, New X's)

This is an array function so do two things:- Hightlight the area you want your answer to appear in prior to typing.- Press Control + Shift + Enter when done typing.

Trend can be used to estimate tha value of something based upon the values of similar items.

Example:A realtor is selling office space and wishes to determine a selling price which will be in line with similar offices in the area.

Chi_Square_2x2GenderWine PreferenceFemaleWhiteObserved Preference for Red or White WineMaleRedMaleFemaleTotalFemaleWhiteRedMaleWhiteWhiteFemaleWhiteTotalFemaleWhiteMaleRedMaleWhiteExpected Preference for Red or White WineMaleWhiteMaleFemaleTotalFemaleWhiteRedFemaleWhiteWhiteMaleWhiteTotalFemaleWhiteFemaleWhiteMaleRedP (Chi Square Test):FemaleWhiteMaleWhiteFemaleWhiteFemaleRedFemaleRedMaleWhiteMaleRedMaleRedMaleRedFemaleWhiteMaleRedMaleRedMaleRedFemaleWhiteFemaleWhiteMaleWhiteFemaleWhiteMaleWhiteFemaleWhiteFemaleRedFemaleWhiteFemaleWhiteMaleRedFemaleWhiteMaleRedFemaleWhiteFemaleWhiteMaleRedMaleRedMaleWhiteFemaleRedMaleRedMaleRedFemaleWhiteFemaleWhiteFemaleWhiteMaleRedMaleRedFemaleWhiteFemaleWhiteFemaleRedFemaleWhiteFemaleRedFemaleRedMaleRedFemaleWhiteFemaleWhiteMaleRedFemaleWhiteFemaleRedMaleRedFemaleRedMaleWhiteMaleRedMaleRedMaleWhiteMaleWhiteMaleWhiteMaleRedMaleWhiteMaleRedMaleWhiteFemaleWhiteFemaleRedMaleRedFemaleWhiteMaleWhiteMaleRedFemaleWhiteFemaleWhiteMaleRedFemaleWhiteMaleRed

Correlation_ExamplesYearAverage Interest RateAverage Home Price20056.0%$500,00020065.5%$550,00020076.5%$300,00020086.9%$290,00020095.0%$310,00020104.5%$340,000

Correlation:

Car AgeAverage Resale Value1$20,0002$18,0003$15,0004$12,0005$10,0006$8,0007$7,5008$5,0009$4,00010$3,500

Correlation

ConfidenceNo. of Sprockets554585554546474859Confidence Example55Average:50Alpha0.0549Standard Deviation Sample:49Sample Size:47Confidence:46Low End Pop. Mean54High End Pop. Mean6061626364646566676868697071727374757677798081526053626566644245665567655554526158585450

&A

Using Confidence.T() ---> With Confidence.T it is allowable to use the SDTEV of the sample rather than the population.One day at your manufacturing plant you wrote down the number of sprokets made by 60 randomly selected workers. You would now like to compute the following: The average for the day. The standard deviation of the sample. How close to the sample mean the the population mean might be expected to fall.