Using formulas from Quattro Pro in Excel 2007
Richard wrote...
I can't seem to unravel the following formula from Quattro Pro so
that they will work in Excel.
Quattro Pro
Cell "L8": @IF(D80,(D8*J8)+K8,"")
Cell "M8": @IF(E80,(E8*J8)-K8,"")
Cell "N8": @IF(G80,(G8*J8)+K8,"")
Cell "O8": @IF(F80,(F8*J8)-K8,"")
Cell "P8": @IF(B80#OR#C80,+L8+N8,"")
Cell "Q8": @IF(B80#OR#C80,+M8+O8,"")
Cell "A9": @IF(Q80,A8+Q8,@IF(P80,A8-P8,"")
The last formula is missing a right parenthesis at the end.
Access Excel 2007
What's Access Excel 2007? Do you mean Excel 2007?
Cell "L8": =IF(D80,(D8*J8)+K8,"")
Cell "M8": =IF(E80,(E8*J8)-K8,"")
Cell "N8": =IF(G80,(G8*J8)+K8,"")
Cell "O8": =IF(F80,(F8*J8)-K8,"")
Do these work? They should unless the D8, E8, G8 or F8 cells contain
text. In QP, text is ALWAYS evaluated as numeric zeros in numeric
contexts. Ain't the case in Excel (with Transition Formula Evaluation
disabled) - any text is greater than any number, so
=""9.99999999999999E+307
evaluates TRUE. If these cells contain spaces or zero length strings,
you'll need to change the L8:O8 formulas to
L8: =IF(N(D8)0,(D8*J8)+K8,"")
M8: =IF(N(E8)0,(E8*J8)-K8,"")
N8: =IF(N(G8)0,(G8*J8)+K8,"")
O8: =IF(N(F8)0,(F8*J8)-K8,"")
Cell "P8"; =IF(L80,L8,IF(N80,N8,""))
Cell "Q8": =IF(M80,M8,IF(O80,O8,""))
These don't match your QP formulas above. Taken as given, they need
similar modifications.
P8: =IF(N(L8)0,L8,IF(N(N8)0,N8,""))
Q8: =IF(N(M8)0,M8,IF(N(O8)0,O8,""))
though you could use
P8: =IF(L8<"",L8,IF(N8<"",N8,""))
Q8: =IF(M8<"",M8,IF(O8<"",O8,""))
Cell "A9": =IF(P80,A8-P8,IF(Q80,A8+Q8,""))
And this needs to be either
A9: =IF(N(P8)0,A8-P8,IF(N(Q8)0,A8+Q8,""))
or
A9: =IF(P8<"",A8-P8,IF(Q8<"",A8+Q8,""))
Or you could just enable Transition Formula Evaluation, but I don't
have XL2007 myself, so someone else needs to tell you where Microsoft
hid it in the ribbon UI.
|