@TMSchucks - didn't think you were on line sorry - I wouldn't have chimed in !
Originally Posted by
TMS
Please can you explain what the tilda (~) does in the second AND formula
Tilde - in this context used purely as a delimiter between the values.
Should G5 contain YY, M5 blank and T5 Y testing G5&M5&T5 to YYY would return a false positive ... by using an appropriate delimiter you negate that, ie: YY~~Y <> Y~Y~Y
Originally Posted by
TMS
what does the SIGN do in the OR formula
is just an alternative to
ie the SIGN of any number > 0 is 1, SIGN of 0 being 0 ... given we can't have a negative result in this context there is no issue of a -1 multiplier
some argue that the additional Function call makes it less desirable than the latter approach
for me, however, having debugged a few SUMPRODUCTs I would say SIGN is far easier to read through
If the overhead of using SIGN is significant enough to be a concern then SUMPRODUCT should not be being used in the first instance !
Bookmarks