anyone good with doing math on excel?

alexman617 · 2012-02-18 22:06:53

iv been trying to get this problem on excel but so far nothing anyone think they can help?

Say that every single class in a College Masters Degree in Game Design has a 5% failure rate per month. Say also that when a student fails once, he or she drops from the program. There are twelve months. Given a cohort of 100 that starts the program, about how many are left after twelve months? help?

bobbym · 2012-02-19 00:35:03

Hi alexman617;

You do not require a computer for this answer. You can derive the formula and then use a calculator.

The first month we have

.95 * 100 = 95 left.

The second month we have:

.95 * .95 * 100 = 90.25 left

The third month we have:

.95 * .95 * .95 * 100 = 85.7375

For the nth month we have:

.95^n * 100

For 12 months we get

.95^12 * 100 = 54.0360087 left.

We can round to 54. Welcome to the forum.

phrontister · 2012-02-19 03:08:37

Hi alexman617,

If looking for whole-body eliminations (as opposed to partial bodies, which could get a bit messy!), using Excel's INT function for each month's calculation is a good option. That gives a final count of 51 students that remain after 12 months.

The image shows the formulas I used. Just enter B2's and A3's values and B3's formula, select A3 and B3 (combined) and drag the fill handle down to row 14.

I couldn't think of a one-liner formula to solve your problem that way.

alexman617 · 2012-02-19 10:44:03

hey thanks guys helps me alot, i have 2 more i need help with, if you want to help it would be appreciated thanks

bobbym · 2012-02-19 10:45:14

Please post them.

alexman617 · 2012-02-19 11:30:38

heres the second one

You are going to roll four twenty-sided dice. If the rolls total to 20 or less, roll two more twenty-sided dice and add that to the total. For instance, if the total of my four die rolls is 32, then that is the score of the game. If the total is 17, then I roll two more dice and add that to the total. If these two bonus dice total 20, then the score of that game is 37. Of ten thousand games, what is the average score?

bobbym · 2012-02-19 11:38:20

Hi alexman617;

Okay, I will work on it.

alexman617 · 2012-02-19 11:39:00

thanks for the help im glad theres people out there that are willing to help

bobbym · 2012-02-19 14:02:32

Hi alexman617;

The average value of this game is 1364349 / 32000 = 42.63590625.

The above answer is the exact answer given by expanding the generating function that represents this problem. It will do you little good because it appears they want you to write an excel program to do this.

Programming is a very personal thing and you should do it on your own. Anyways, I do not program in excel but the exact answer can at least point you in the right direction.

bobbym · 2012-04-25 07:20:31

Hi HambrickWeldon;

Sadly I have the same problem mathematically I have it figured out

As far as I know I am the only person in the galaxy to have figured it out using mathematics. If you have another solution then I would like to see it.

Excel is not the end of the world. There are other programming tools, try one of them.

bobbym · 2012-04-25 19:24:48

That is what I would like to see your math.

Bob · 2012-04-25 19:39:18

hi HambrickWeldon

Earlier versions of Excel don't allow 10 000 rows so check by doing CTRL <down arrow>.

The function RAND() will return a random number between 0 and 1 0 can be returned but not 1.

So = 20 * RAND() will raise the values to 0 and 19.9999

Then take the integer part.

=INT(20*RAND())

The range is now 0 to 19.

Finally = INT(20*RAND()) + 1

to get numbers in the range 1 to 20.

Copy this into 5 more columns so you are simulating 6 throws.

The IF command will then allow you to sum up just 4 columns or all 6.

= IF(SUM(A1:D1)<21, SUM(A1:F1), SUM(A1:D1)

If the first four add to under 21 then add 6 columns otherwise add 4 columns.

Copy that row to another 9999 rows to 'do' the experiiment 10 000 times.

You can then put

=AVERAGE(G1:G10000)

in cell G10001 in order to get the average score.

The screen shots below show my results for a few rows (it's random so you'll get different values) and then the formulas.
I've shrunk columns C and D to make it fit into one shot.

Hope that helps, post again if you get stuck.

Bob

Jenniferm · 2012-04-26 02:45:50

I am not sure how a forum works sorry, I am a mom of teenagers and just returned to school. So its been a looong time since I have done math besides adding things up at the grocery store. Can I post the math problems in this topic? I am going to and see if I get any feedback? I need help with a problem that has fractions in it but I don't know how to put it on this post, can someone help?
Thank you for your time
Jenniferm

phrontister · 2012-04-26 03:16:06

EDIT: Seeing Bob's nice formulas gave me this idea.

See image:

(a) RANDBETWEEN(x,y) returns a random number between the specified numbers. My Excel v2007 has that function, but I don't know if other versions have it.

(b) Enabling iterations in Excel can keep the spreadsheet entries to a minimum, and this is demonstrated in the little program (see below) that runs from just a few lines. I'd initially entered the formulas into column A, but changed to column B in order to show and explain them better (see image).

To run the program in Excel v2007:

1. Add the 'Calculation Options' button to the Ribbon: Office Button/Excel Options/Customise/All Commands/Calculation Options/Add/OK.
That button enables quick selection of manual or automatic calculation mode. Click the button and select 'Manual'.

2. Enable iterations: Office Button/Excel Options/Formulas/Calculation options. Tick the 'Enable iterative calculation' box and in the 'Maximum Iterations' box enter 10000. I left 'Maximum Change' at the default 0.001. Click OK.

3. Enter the formulas from my spreadsheet image.

4. Press F9 (or click the 'Calculation Options' button and select 'Automatic') to start the ball rolling (dice, actually)...and sit back and watch the spinning tumblers in column B.

5. To reset column B results, first click the 'Calculation Options' button and select 'Manual'. Then in each cell to be reset (B2:B5), left double-click it and press Enter.

6. Press F9 to start the calcs again. Each subsequent press increases the number of games by 10000.

7. You can check the validity of the program by setting iterations to 1 and then pressing F9 to monitor the column B results. Calcs can be set to either manual or automatic for this.

8. If you want more automated results per F9 press, increase iterations (max 32767 in Excel v2007).

9. You may want to re-enable auto calcs for your other programs...which you can do from the new Ribbon button that was set as per note 1. When leaving this program you should probably also disable iterations by removing the tick you put in the box (see note 2).

10. The "B2*0+" in B2 is the trigger that sets off the iterations to automate the calculations.

Last edited by phrontister (2017-02-26 00:23:23)

bobbym · 2012-04-26 04:55:17

Hi Jenniferm;

Welcome to the forum. You should post your problems in Help Me. Just start a new topic. This topic is for other problems.

Bob · 2012-04-26 06:24:29

hi HambrickWeldon,

This is a bit complicated but it will work.

(i) Simulate one flip with =IF(RAND()<0.5,"H","T")

(ii) Do that for another nine cells in a row, so you have 10 cells containing some Hs and some Ts

(iii) Build this into a single 'word' with =CONCATENATE(A1,B1,C1,D1,E1,F1,G1,H1,I1,J1,"HHHH")

You'll notice I have put an extra HHHH on the end. The reason for this will become clear after the next step.

(iv) Detect where a sequence of four Hs starts with =FIND("HHHH",K1)

This function returns the position number for the first instance of HHHH in a word.
eg. If K1 contains HTHTHHHHTTHHHH you will get 5.

The function doesn't like it if there is no instance of HHHH at all. You get a VALUE? error. Adding a 'dummy' of HHHH at the end of the word avoids this happening; so you'll always get a number.

If the number is < 8 it's a win. Otherwise it's a loss. = IF(L1 < 8, "Win", "Loss")

There's a function that will count the wins =COUNTIF(M1:M10000,"Win")

The screen shot shows two rows of results, with a COUNTIF on just the two rows.

Bob

ps. Thanks phrontister. I didn't know you could do that. I'm still a bit unsure so I've stuck to my 'long way' for this second question.

phrontister · 2012-04-26 13:10:42

Hey! HambrickWeldon's posts have vanished!!(??)

Anyway, I found it on a couple of other websites (as a homework question).

Below is an image for my solution. Again, inspired by Bob's method.

Same idea re iterations as for the dice puzzle (see my post #14).

The "B2*0+" in B2 is the trigger that sets off the iterations to automate the calculations.

You can check the validity of the program by setting iterations to 1 and then pressing F9 to monitor the column B results. Calcs can be set to either manual or automatic for this.

Last edited by phrontister (2017-02-26 00:21:42)

anonimnystefy · 2012-04-26 13:17:31

He deleted his posts.

phrontister · 2012-04-26 13:51:56

Does anyone have the wording for HambrickWeldon's coin-flip puzzle, so that we can include it here as reference for anyone interested in this?

If not, I can probably make something up.

EDIT: Found it on a couple of other websites, and included it in my post #17.

Last edited by phrontister (2012-04-27 03:52:37)

bobbym · 2012-04-26 14:01:52

HambrickWeldon has deleted his posts!

I will not speculate as to why he did that. I am sorry for the inconvenience that action has caused.

Hi phrontister;

Make something up or I can if you want.

phrontister · 2012-04-27 04:15:35

Hi Bobby,

I found the puzzle on a couple of other websites and have included it in my post #17.

Raito · 2012-04-27 04:17:13

Hello Mr phrontister

I am new on this forum! I am looking forward to talking to you

Are you good with Excel?

bobbym · 2012-04-27 04:25:14

Hi phrontister;

That is a good example to use. Easy by computer and a little nasty by mathematics.

phrontister · 2012-04-27 05:49:37

Hi Raito,

Welcome! You'll enjoy your experience here and you'll be able to learn much from this excellent website and by participating on the forum!

I don't know how to rate my proficiency in Excel. I'm self-taught, having picked up my knowledge from a limited amount of spreadsheet use at work and from occasional puzzle solving.

There is a huge amount about Excel that I don't know and never will know, because the program has such diverse application.

I like to use it when I can, and the more I do that the better acquainted with it I'll become and the more tricks I'll learn along the way.

Excel has good tutorials and good help files that you can copy and paste into your spreadsheet to get a quick and easy idea of how things work. There's also an incredible amount of knowledge available on the internet and you'll find an answer somehow to practically any question you can imagine.

Although this forum isn't specifically Excel-oriented many members here use the program - or one similar to it. So if you have any questions just post them here and more than likely someone will post back the answer.

Raito · 2012-04-27 05:50:37

Ok,Mr phrontister,thank you!

Math Is Fun Forum

#1 2012-02-18 22:06:53

anyone good with doing math on excel?

#2 2012-02-19 00:35:03

Re: anyone good with doing math on excel?

#3 2012-02-19 03:08:37

Re: anyone good with doing math on excel?

#4 2012-02-19 10:44:03

Re: anyone good with doing math on excel?

#5 2012-02-19 10:45:14

Re: anyone good with doing math on excel?

#6 2012-02-19 11:30:38

Re: anyone good with doing math on excel?

#7 2012-02-19 11:38:20

Re: anyone good with doing math on excel?

#8 2012-02-19 11:39:00

Re: anyone good with doing math on excel?

#9 2012-02-19 14:02:32

Re: anyone good with doing math on excel?

#10 2012-04-25 07:20:31

Re: anyone good with doing math on excel?

#11 2012-04-25 19:24:48

Re: anyone good with doing math on excel?

#12 2012-04-25 19:39:18

Re: anyone good with doing math on excel?

#13 2012-04-26 02:45:50

Re: anyone good with doing math on excel?

#14 2012-04-26 03:16:06

Re: anyone good with doing math on excel?

#15 2012-04-26 04:55:17

Re: anyone good with doing math on excel?

#16 2012-04-26 06:24:29

Re: anyone good with doing math on excel?

#17 2012-04-26 13:10:42

Re: anyone good with doing math on excel?

#18 2012-04-26 13:17:31

Re: anyone good with doing math on excel?

#19 2012-04-26 13:51:56

Re: anyone good with doing math on excel?

#20 2012-04-26 14:01:52

Re: anyone good with doing math on excel?

#21 2012-04-27 04:15:35

Re: anyone good with doing math on excel?

#22 2012-04-27 04:17:13

Re: anyone good with doing math on excel?

#23 2012-04-27 04:25:14

Re: anyone good with doing math on excel?

#24 2012-04-27 05:49:37

Re: anyone good with doing math on excel?

#25 2012-04-27 05:50:37

Re: anyone good with doing math on excel?

Board footer