Using formulas from Quattro Pro in Excel 2007

R

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.
 
H

Harlan Grove

Richard said:
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,""))
Q8: =IF(M8 said:
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.
 
D

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,""))
 
R

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.
 
H

Harlan Grove

Richard said:
. . . 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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top