pls help making mathematical puzzels for children

[Navid Sami]

Hi
I appreciate if any one who is a bit more expert in excel than me can
help me make a mathematical puzzle for children. I try to explain what I
mean here:

The Idea is that we can generate numbers (say from one to ten) randomly
in column A1:A10 then generate one of + or - in front of them in column
B1:B10 and yet another random number random number between 1 and 10 in
column C1:C10

The rest I think is easy, we check their answear in D110 and give them
point if it's correct

any ideas please?
thanks

 

[Bob Phillips]

Navid,

Normally, you use the RAND function do generate random numbers. The problem
with that is that when you add something else to the spreadsheet, it
calculates a new random number. So your kids might see a line that says 3 +
4, type in 7, and the sum changes to 7 - 2.

This can be overcome like so

First, ensure cell A1 is empty and goto Tools>Options and on the Calculation
tab check the Iteration checkbox to stop the Circular Reference message.

Then in A1 add this formula: =IF($F$1="","",INT((RAND()*10+1)))

In B1 add: =IF($F$1="","",IF(INT(RAND()*2),"+","-"))

And C1 add: =IF($F$1="","",INT((RAND()*10+1)))

At this point, A1, B1 and C1 will be empty.

Copy A1:C1 down to A10:C10.

When ready to go, just put some character, say an x, in F1.

To force a reset, clear cell F1, edit cell A1:C1, don't change them,
just edit to reset to blank, copy A1:C1 down to A10:C10, and re-input F1.


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[joeu2004]

The Idea is that we can generate numbers (say from one to ten) randomly
in column A1:A10 then generate one of + or - in front of them in column
B1:B10 and yet another random number random number between 1 and
10 in column C1:C10

As Bob mentioned, the problem is that Excel's RAND() function is
volatile. Every change to the spreadsheet caused RAND() to be
recalculated. I avoid that by creating MYRAND(). (I will explain how
below.) Then create the following formulas:

A1: =int(10*myrand()+1)
B1: =if(myrand()<=0.5,"+","-")
C1: =int(10*myrand()+1)

Copy A1:C1 down through A10:C10.

Use ctrl-alt-F9 to generate a new set of random numbers.

Create MYRAND() as follows.

1. Press alt-F11 to open the VBA editor window.
2. Click Insert -> Module.
3. Enter the following text:

function myrand()
myrand = rnd()
end function

4. Close the VBA editor window.

 

[joeu2004]

PS....

function myrand()
myrand = rnd()
end function

As an embellishment, you can write "function myrand(optional rng)"
instead.

Then, if you enter =int(10*myrand($F$1)+1) in A1 and C1, whenever you
modify F1, the random numbers will change. That would be an
alternative to pressing ctrl-alt-F9.

Since the parameter is "optional rng", you can still write myrand().
You can also write myrand($F$1:$F$10) in situations where you would
like new random numbers whenever any one of a range of cells is
modified.

 

[Navid Sami]

It's great, thanks, but with this approach then I don't know how to
check whether the answer is right or not. I mean I can not just write
A1B1C1 I tried also =A1{B1}C1 in cell D1 and hoped excel would calculate
the result in B1 before passing it, but no success! and another thing
how can we make sure that if B1 is - then A1 should be greater than C1
(this to avoid negative numbers)
tnx,

 

[Nasir]

Naveed, you might want to try conditional formatting. Its under Format
option. You can use red or green colour for right and wrong if the
cell value doesnt generate the right answer.

Nasir.

 

[Navid Sami]

yes, that's absolutely a good Idea but I need to generate the answer
first, and that's the question how do I make excel evaluate B1 and uses
it in a formula

 

[Bob Phillips]

Navid,

What you could do is generate a randome number in C between -10 and 10
depending upon the sign in B, but hide that sign using a custom format of
0:0

BTW, I forgot some bits in the formulae, theye should be (including the sign
bit)

A1: =IF(($F$1="")+(A1<>0),A1,INT((RAND()*10+1)))

B1: =IF(($F$1="")+(B1>0),B1,IF(INT(RAND()*2),"+","-"))

C1: =IF(($F$1="")+(C1<>0),C1,--(B1&INT((RAND()*10+1))))


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Nasir]

Navid,

this might help: use the suggested rand() function to generate the
random numbers. then go to "Tools" then options then choose
calculation tab. There, select manual calculation instead of
automatic. That way, once the random numbers are generated, they will
persist unless you hit "F9" for manual calculation. I think this
should work for the time being.
If it doesn then you might wanna write a macro generating the random
numbers as you wish. That way you will customize the selection of
fields and running the program for number generation as you wish.

Nasir.

 

[Nasir]

I have tried this and it should work for your requirement as well.
use this for col A and col B
=ROUND((RAND()*(10-1)+1),0)
use this for col D
=Brow+Crow
for column C use conditional formatting as: cell value "is equal to"
=drow
Make sure, you have changed the calculation from automatic to manual.
"F9" will calculate the sheet manually(for new values)
All the best,
Nasir.

 

[MartinW]

Hi Navid

I haven't tried any of this but a few ideas to throw into the pot.

To avoid negative numbers you could use RANDBETWEEN instead
of RAND. In column A RANDBETWEEN(10,20) and in column C
RANDBETWEEN(1,10) or you could use (6,10) and (1,5)
or whatever.

To generate your + or - in column B you could use =IF(ISODD(A1),"+","-")
and then you can use ISODD(A1) to generate the correct answer

To show the correct answer you could use a wingding tick and cross.
In wingding font alt +0251 is a cross and alt +0252 is a tick.
(To use unicode numbers hold down alt and type the numbers then let go
of the alt key. You must use the number pad not the numbers at the top
of your keyboard.). Put these characters in some cells off to the right like
M1
and M2. Then in say F1 put something like =IF(D1=E1,M1,M2) where
D1 is your kids answer and E1 is the correct answer.

HTH
Martin

 

[Navid Sami]

I still have cases where A1 is bigger than C1 and B1 is -
and how can we check whether the result kids have entered in D1 is
correct? something like A1{B1}C1 where {B1} will return the value it has
gotten from the rand function

 

[Navid Sami]

Hi Navid

I haven't tried any of this but a few ideas to throw into the pot.

To avoid negative numbers you could use RANDBETWEEN instead
of RAND. In column A RANDBETWEEN(10,20) and in column C
RANDBETWEEN(1,10) or you could use (6,10) and (1,5)
or whatever.

If I use this then I will never have for expample 5-2 or 6+1

To generate your + or - in column B you could use =IF(ISODD(A1),"+","-")
and then you can use ISODD(A1) to generate the correct answer

then I will always have + with odd numbers and - with pair
I will never get 3-2 or 4+2

To show the correct answer you could use a wingding tick and cross.
In wingding font alt +0251 is a cross and alt +0252 is a tick.
(To use unicode numbers hold down alt and type the numbers then let go
of the alt key. You must use the number pad not the numbers at the top
of your keyboard.). Put these characters in some cells off to the right like
M1
and M2. Then in say F1 put something like =IF(D1=E1,M1,M2) where
D1 is your kids answer and E1 is the correct answer.

this sounds interesting but I just didn't work when I tried I get a û
in M1 and M2 could you explain more detailed how we do this please?

 

[Bob Phillips]

Just add A1 & C1

=D1=A1+C1

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[MartinW]

Hi Navid,

this sounds interesting but I just didn't work when I tried I get a û
in M1 and M2 could you explain more detailed how we do this please?

That bit is easy, just change the font in that cell to wingding before you
use the unicode keystrokes.

As for your other questions, my ideas were more along the idea
of food for thought rather than solutions. It's two o'clock in the morning
here, so for now I'm off to bed. I'll post back here tomorrow when
I've had some time to think it through a bit more.

Regards
Martin

 

[Guest]

Navid

I think it is better to set the sums with a macro then they they are just
values

Sub setSums()
Dim i As Integer, tmp As Integer, op As String
'set the numbers
Range("D2:d11").ClearContents
For i = 2 To 11
Cells(i, 1) = Int(Rnd() * 10) + 1
Cells(i, 3) = Int(Rnd() * 10) + 1
'Enter the operator
x = Rnd
If x > 0.5 Then
Cells(i, 2) = "+"
Else
Cells(i, 2) = "-"
End If
' make sure there are no minus answers
'swap column C with column A
If Cells(i, 1) < Cells(i, 3) And _
Cells(i, 2) = "-" Then
tmp = Cells(i, 3)
Cells(i, 3) = Cells(i, 1)
Cells(i, 1) = tmp
End If

End Sub

Copy this into a VB Module (ALT + F11, Insert, Module)
Then draw a rectangle using Excel's drawing tools, right-click and choose
assign macro to the object

So you have Numb 1 in column A, operator in Column B, Numb 2 in C. The
answer goes in D. In E2 copy this formula

=IF($D2="","K",IF(AND($B2="-",$D2=A2-C2),"J",IF(AND($B2="+",$D2=$A2+$C2),"J","L")))

Copy the formula down to A11 and format the range to font Windings. This
will give a smiley face if the answer in Col is right.

Alternatively, email (e-mail address removed) and I'll post you a workbook.

Regards
Peter

 

[Tushar Mehta]

In addition to the ideas in this discussion so far, check out
http://www.tushar-mehta.com/excel/education/

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Navid Sami]

In addition to the ideas in this discussion so far, check out
http://www.tushar-mehta.com/excel/education/

Thanks everyone for your greate help