Problem | Printable page |

Where to start with this? Maybe with an example:

In the Interactive Resource Multiples Chain the words "factor" or "multiple" appear when the adjustable cells are set to a factor-multiple relationship.

Here's the formula that gives the word "Multiple" (take a deep breath)

= IF (AND (NOT (OR (B3=E3,B3=1) ), MOD (E3,B3) = 0 ), "Multiple", "zz" )

It starts with = , because Excel needs to know this isn't just a line of text, it's a command to be carried out.

IF means: check something, if it's true, do this, if it's not true, do another thing.

AND means: everything in the following list has to be true.

OR means: just one true thing is all you need here.

NOT means: check that this has not happened.

Also: MOD is just a function. It means modulo arithmetic. The MOD function returns the remainder, the left-overs when you divide.

So what does this formula actually say:

= IF (AND (NOT (OR (B3=E3,B3=1) ), MOD (E3,B3) = 0 ), "Multiple", "zz" )

If something happens type "Multiple", otherwise type "zz" (zz is just dummy text - it isn't going to show anyway - more on this at the end).

What's the something being tested for?

AND (NOT (OR (B3=E3,B3=1) ), MOD (E3,B3) = 0 )

Starts with AND so everything in the bracket has to happen:

NOT (OR (B3=E3,B3=1) and MOD (E3,B3) = 0

Let's take the MOD (E3,B3) = 0 , this means that E3 divided by B3 must be zero. Well that's what happens if E3 is a multiple of B3, there's nothing left over when you divide.

Now for that NOT (OR (B3=E3,B3=1).

The MOD function seems at first to be all you need.

But, if you divide by 1 there will still be no remainder, but that doesn't make the number divided by 1 a multiple in the factor-multiple sense.

Also, if the two numbers were equal, once again the remainder would be zero without the numbers being a factor-multiple pair.

So this is how I fixed that:

NOT means check the following has not happened:

either B3 = E3, or B3 = 1

And there, at last, we have it.

The formula to show "factor" at the right moment is: =IF (AND (NOT (OR (B3 = E3, B3 = 1) ), MOD (E3,B3) = 0), "Factor", "zz")

Exactly the same, but when the trigger events occur the word "Factor" is displayed not "Multiple".

Finally, what about that "zz" not showing:

I've used conditional formatting . The basic colour settings for this cell has the font colour and the fill colour matching each other, so "zz" or anything else in that cell won't show, but I've told Excel to keep watch (conditional formatting), and if the cell contains the value "Factor" (or "Multiple" if it's the other cell) the font colour is to change. The fill colour is also to change and a cell border is to appear.

Spreadsheets. Tree diagrams. Summation of series. Experimental probability. Conditional probability. Theoretical probability. Approximate solution by numerical methods.. Random variables. Probability distributions, expectation and variance. Mathematical modelling.