Dismiss "The formula uses a Boolean instead of a number" warning on my Mortgage Spreadsheet

Hi N-G,

Formulas tend to be easier to read and interpret in the abstract (cell references instead of references by names)

Here's your formula, stripped of the distractions, and arranged to show the parts:

IF( Open IF

($B$2<G2)+(G2×($C$2%÷$D$3)), if-expression. This should be a Boolean expression which will return either

'true' or 'false'. It's where the warning is generated. Details below.

$G$2, if-true: If the expression returns 'true', return the value in G2.

G2+(G2×($C$2%÷$D$2)) if-false: If the expression returns false, do this calculation and return the result.

) Close IF

($B$2<G2)+(G2×($C$2%÷$D$3))

This says:

1. "Determine whether the value in B2 is less than the value in G2. (the result will be 'true' or 'false')
2. "Divide the value in C2 by 100 to convert it to a percentage, then divide that value by the value in D3.
   (the result will be a number)
3. Multiply the step 2 result by the value in G2. (the result will be a number)
4. Add the step 3 result (a number) to the step 1 result (either 'true' or 'false') and return the result to IF.

Do you want to compare the values in B2 and G2, then perform one follow-up action if B2 is less than G2 (true) or a different action in B2 is equal to or greater than G2?

If so, dump the parentheses around $B$2<G2, place a comma immediately after G2, and rewrite the rest to fit what you want done if B2 is less than G2, and what you want done if B2 is equal to or greater than G2.

OR

Do you want to compare the value in B2 with the result of G2+G2×($C$2%÷$D$3). then perform a second action depending on the 'true'or 'false' retuned by that calculation?

Regards,

Barry

I think the formula has some of the ( ) in the wrong place. For example if you have

($Principal $House<G2)

in the formula Numbers looks at that and evaluates it as TRUE or FALSE, then tries to the TRUE or FALSE to other values, which can be done but likely is not what you intended, hence the blue warnings.

Something like this works better:

=IF($Principal $House<(G2+G2×$Yearly rate of interest $House÷$'Tenure (in Years)' $House),$Payments per period $House,G2+G2×$Yearly rate of interest $House÷$'Tenure (in Years)' $House)

SG