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.