6StringJazzer/Rick,
Thanks for the additional suggestions – all work as desired.
Rick – just out of curiosity, is there a way to rewrite the “Choose” version that doesn’t required “P” to be entered twice? That’s was what I was trying to avoid since I wanted to shorten/simply the formula. The following are the formulas with the “P” replaced with the actual formulas which hopefully will make it clearer.
“One P”
=IF(SIGN(D6)=1,1,0) -SIGN(D6)*RTD("tos.rtd",,R$5,RIGHT($AC6,LEN($AC6)-IF(OR(LEN($AC6)<8,LEFT($B6,1)="/"),2,1)))
Or
=MAX(SIGN(D6),0) -SIGN(D6)*RTD("tos.rtd",,R$5,RIGHT($AC6,LEN($AC6)-IF(OR(LEN($AC6)<8,LEFT($B6,1)="/"),2,1)))
“Two Ps”
=CHOOSE(SIGN(D6)+2,RTD("tos.rtd",,R$5,RIGHT($AC6,LEN($AC6)-IF(OR(LEN($AC6)<8,LEFT($B6,1)="/"),2,1))),0,1-RTD("tos.rtd",,R$5,RIGHT($AC6,LEN($AC6)-IF(OR(LEN($AC6)<8,LEFT($B6,1)="/"),2,1))))
Bookmarks