Using formulas from Quattro Pro in Excel 2007

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Richard - 06 Mar 2007 21:53 GMT

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.

Signature

Thanks; in advance,
Richard

Reply to this Message

Harlan Grove - 06 Mar 2007 22:16 GMT

Richard <Rich...@discussions.microsoft.com> wrote...

>I can't seem to unravel the following formula from Quattro Pro so
>that they will work in Excel.
[quoted text clipped - 7 lines]
>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<>"",M8,IF(O8<>"",O8,""))

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

Reply to this Message

Richard - 06 Mar 2007 23:59 GMT

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.

Signature

Thanks; in advance,
Richard

> Richard <Rich...@discussions.microsoft.com> wrote...
> >I can't seem to unravel the following formula from Quattro Pro so
[quoted text clipped - 62 lines]
> have XL2007 myself, so someone else needs to tell you where Microsoft
> hid it in the ribbon UI.

Reply to this Message

Harlan Grove - 07 Mar 2007 01:12 GMT

Richard <Rich...@discussions.microsoft.com> wrote...
...

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

Reply to this Message

Don Guillett - 06 Mar 2007 23:26 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>I can't seem to unravel the following formula from Quattro Pro so that they
> will work in Excel.
[quoted text clipped - 21 lines]
> 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.

Reply to this Message