|
Using formulas from Quattro Pro in Excel 2007
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,) Access 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,"") Cell P8; =IF(L80,L8,IF(N80,N8,"")) Cell Q8: =IF(M80,M8,IF(O80,O8,"")) Cell A9: =IF(P80,A8-P8,IF(Q80,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. -- Thanks; in advance, Richard |
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. |
Using formulas from Quattro Pro in Excel 2007
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. -- Thanks; in advance, Richard "Harlan Grove" wrote: 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. |
Using formulas from Quattro Pro in Excel 2007
Richard 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. |
| All times are GMT +1. The time now is 03:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com