Min Function not calculating correctly with further calculations

[Jaq23]

I have a macro which generates formulae based on certain criteria.
This has seemed to work fine until now (although with a lot of these,
it's difficult to check) but I'm noticing an output that isn't
correct. The formula that has been generated is:

=MIN(1,(IF(ISNA(MATCH("PRP",$1:$1,0)),0,OFFSET(startFormula,ROW()-
ROW(startFormula),MATCH("PRP",$1:$1,0)-COLUMN(startFormula)))))

The column with "PRP" does exist, the value is empty (equating to a
FALSE or 0 value) for most rows. This should correspond to the values
in the PRP column by setting the result to 1 when the column shows
TRUE and 0 when the corresponding cell is blank.

By stepping into the formula, highlighting and calculating small
sections of the formula (using F9); it eventually drills down to
=MIN(1, {0}) = 0. But with the full formula, it's setting all outputs
to 1.

This is the most simple form of the formula, with the original
condition just stating "PRP" as opposed to some of the more
complicated criteria so if this isn't working, everything may be
wrong. Interestingly, the column to the left of this, with the formula
shown below, is producing values of 0.

=MIN(1,(IF(ISNA(MATCH("IMM",$1:$1,0)),0,OFFSET(startFormula,ROW()-
ROW(startFormula),MATCH("IMM",$1:$1,0)-
COLUMN(startFormula))))*NOT(IF(ISNA(MATCH("PRP",$1:$1,0)),
0,OFFSET(startFormula,ROW()-ROW(startFormula),MATCH("PRP",$1:$1,0)-
COLUMN(startFormula))))*NOT(IF(ISNA(MATCH("END",$1:$1,0)),
0,OFFSET(startFormula,ROW()-ROW(startFormula),MATCH("END",$1:$1,0)-
COLUMN(startFormula)))))

 

[Jaq23]

I've put an INT() around the second part of the MIN() formula and this
seems to have fixed it.

It seems to have worked fine for the others as the conditions are
multiplied or added for AND and OR respectively; which switches them
from TRUE and FALSE to the boolean operators 1 and 0 but without other
criteria, this remained as FALSE and wasn't correctly recognised by
the MIN statement.

 

[Jaq23]

Solved by placing an INT() around the second condition of the MIN()
statement.

This converted the results from TRUE/FALSE to the equivalent boolean
operators 1/0, which can be recognised by the MIN() statement.

 

[joeu2004]

Solved by placing an INT() around the second condition of the MIN()
statement.

This converted the results from TRUE/FALSE to the equivalent boolean
operators 1/0, which can be recognised by the MIN() statement.

I only glanced at the details in your initial posting. But the more
common solution is to use a double negatation (--) to convert TRUE and
FALSE to 1 and 0. This is not an unusual requirement.

For example, change MIN(1,IF(...)) to MIN(1,--IF(...)).
Alternatively, change MIN(1,IF(...,0,OFFSET(...))) to MIN(1,IF(...,0,--
OFFSET(...))).

PS: You might consider using INDEX instead of OFFSET. OFFSET is a
volatile function(!), whereas INDEX is not.