Using formulas from Quattro Pro in Excel 2007

[Richard]

I can't seem to unravel the following formula from Quattro Pro so that they
will work in Excel.

Quattro Pro
Cell “L8â€: @IF(D8>0,(D8*J8)+K8,â€â€)
Cell “M8â€: @IF(E8>0,(E8*J8)-K8,â€â€)
Cell “N8â€: @IF(G8>0,(G8*J8)+K8,â€â€)
Cell “O8â€: @IF(F8>0,(F8*J8)-K8,â€â€)
Cell “P8â€: @IF(B8>0#OR#C8>0,+L8+N8,â€â€)
Cell “Q8â€: @IF(B8>0#OR#C8>0,+M8+O8,â€â€)

Cell “A9â€: @IF(Q8>0,A8+Q8,@IF(P8>0,A8-P8,â€â€)

Access Excel 2007
Cell “L8†: =IF(D8>0,(D8*J8)+K8,"")
Cell “M8†: =IF(E8>0,(E8*J8)-K8,"")
Cell “N8â€: =IF(G8>0,(G8*J8)+K8,"")
Cell “O8â€: =IF(F8>0,(F8*J8)-K8,"")
Cell “P8â€; =IF(L8>0,L8,IF(N8>0,N8,""))
Cell “Q8â€: =IF(M8>0,M8,IF(O8>0,O8,""))

Cell “A9â€: =IF(P8>0,A8-P8,IF(Q8>0,A8+Q8,""))

And from what I've read so far; I don't think a VBA macro is what I'm
looking for. The above formula worked fine in Quattro Pro.

 

[Harlan Grove]

I can't seem to unravel the following formula from Quattro Pro so
that they will work in Excel.

Quattro Pro
Cell "L8": @IF(D8>0,(D8*J8)+K8,"")
Cell "M8": @IF(E8>0,(E8*J8)-K8,"")
Cell "N8": @IF(G8>0,(G8*J8)+K8,"")
Cell "O8": @IF(F8>0,(F8*J8)-K8,"")
Cell "P8": @IF(B8>0#OR#C8>0,+L8+N8,"")
Cell "Q8": @IF(B8>0#OR#C8>0,+M8+O8,"")
Cell "A9": @IF(Q8>0,A8+Q8,@IF(P8>0,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(D8>0,(D8*J8)+K8,"")
Cell "M8": =IF(E8>0,(E8*J8)-K8,"")
Cell "N8": =IF(G8>0,(G8*J8)+K8,"")
Cell "O8": =IF(F8>0,(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(L8>0,L8,IF(N8>0,N8,""))
Cell "Q8": =IF(M8>0,M8,IF(O8>0,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,""))

Cell "A9": =IF(P8>0,A8-P8,IF(Q8>0,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.

 

[Don Guillett]

try these ideas
Cell "L8": =IF(D8>0,(D8*J8)+K8,"")
Cell "P8": =IF(or(B8>0,C8>0),L8+N8,"")
Cell "A9": =IF(Q8>0,A8+Q8,IF(P8>0,A8-P8,""))

 

[Richard]

Thank you Harlan Grove. Yes, the L8, M8, N8, and O8 formulas were OK. It was
the P8, Q8, and A9 formulas that I couldn't rectify. And you've solved my
problem. Both solutions you gave, worked. Can you recomend a good book that
goes into detail and gives examples of what they're trying to explain? Thank
you, again.

 

[Harlan Grove]

. . . Can you recomend a good book that goes into detail and gives
examples of what they're trying to explain? Thank you, again.

....

Not really. John Walkenbach's Excel formulas book may be OK, but I've
only read his Excel VBA programming book, which was pretty good. And I
doubt there's any book that covers QP/123 to Excel transition problems
to any extent.