Convert Lotus 123 formula to Excel

[tn312c]

Can someone tell me how to convert a Lotus 123 formula to what must be implemented or coded in Excel?

The 123 formula is:
@DCOUNT(Sheet1!A1..Sheet1!L2000,"Status",
(MID(STATUS,FIND(B13,STATUS,1),LEN(B13))=B13)#AND#(LEFT(STATUS,LEN(B13))=B13)#AND#(TYPE<>A14))

I've tried using the following with no success:
=DCOUNT(Sheet1!A1:Sheet1!L2000,"Status",
(MID("STATUS",FIND(B13,"STATUS",1),LEN(B13))=B13)=AND(LEFT("STATUS",LEN(B13))=B13)=AND("TYPE"<>A14))

I've looked in Help and as far as I can figure out from the "Advanced Criteria Range" I must specify my criteria outside of the primary formula, namely my above formula is to be coded this way:
=DCOUNTA(Sheet1!A:B,"STATUS",Sheet2!H24:I25)

where H24=STATUS; I24=TYPE; H25=B13 and I25=A14.

But, what I think I've done is specify the following criteria: STATUS=B13 AND TYPE=A14.

I don't know how to incorporate the MID, FIND,LEFT and LEN functions into something similar to "Sheet2!H24:I25", so that my 123 formula will be equivalent in Excel.

Would someone show me the answer?

It is very obvious that I am very new to Excel, even though I've had some expertise in using 123 database functions. I've been finding out that the same thought process used for 123 is not something I can apply with Excel, especially when it comes to database functions.

Hopefully your staff can help me through this initial roadblock. Once I can get the hang of it, I think I should be fine.

Your asssitance will be greatly appreciated...

 

[Andrew Poulsom]

Try changing Status to D6 - the first row of the list.

 

[tn312c]

Would you be more specific as to the cell addresses and what go in them? Sorry, for being so inept. I did try though. Thanks

 

[Andrew Poulsom]

Cells A2 and A3, where the MID and LEFT functions are.

 

[tn312c]

I think the instruction is to replace all the STATUS specified in the formulas in cells A2 and A3 with $D6. Cell D6 is a "blank" cell.

I don't understand the requested change, but nevertheless went ahead and applied the change. Unfortunately, the result remains the same, namely zeroes.

 

[Andrew Poulsom]

Silly me - too many D's in previous posts! It should be A6 (the first row in the Status list). I knew what I meant!

 

[tn312c]

Not a problem. At least now I think I can see what is being done, rather than being completely lost

Ok, now I've replaced the STATUS specified within the formulas in cells A2 and A3 with $A6. The 2 cells now show the result "TRUE." This means to me that there's no syntax problem for the 2 formulas and they are:
A2: =MID(A6,FIND($D$1,A6,1),LEN($D$1))=$D$1
A3: =LEFT(A6,LEN($D$1))=$D$1

But, D3 and D4 are still showing zero as results. I did not make any change to these formulas and they are:
D3:=DCOUNTA(A5:B17,"Status",A1:B3)
D4:=SUMPRODUCT(NOT(ISERROR(FIND ($D$1,A6:A17,1)))*(B6:B17=$D$2))

 

[Andrew Poulsom]

If you have the word Done in D1, the number 1 in D2, the lists of data like mine in A6:B17 and you haven't forgotten to calculate (F9), then I don't know why your formulas are returning zero.

 

[tn312c]

Pressing F9 did not make any change in the results.

I was successful in making SUMPRODUCT to work, but after I change the formula to:
=SUMPRODUCT((NOT(ISERROR(FIND($D1,A6:A17))))*(NOT(ISERROR(FIND($D2,B6:B17)))))
I am not clear why the formula you had coded for cell D4 works for you, but not for me.

I am hoping that what I did for the above formula gives you a clue as to why I am getting zero for cell D3.

Thanks...

 

[Andrew Poulsom]

I don't know why you needed to change the SUMPRODUCT formula. My version:

=SUMPRODUCT(NOT(ISERROR(FIND($D$1,A6:A17,1)))*(B6:B17=$D$2))

counts the items where Done appears anywhere in column A and 1 appears in column B. And this does the same:

=DCOUNTA(A5:B17,"Status",A1:B3)

if your criteria are correct.

 

[tn312c]

Hurrah, Andrew!!!

I accidently discovered what was wrong. I had configured the TYPE column as "number", but did not re-enter the 1's and the 2's. In 123, identfying a cell or a number of cells as being number, is recognized by its functions as number fields. Apparently, that is not the case for Excel.

I was telling my spouse that, I think, you and others must think me a fool for not getting the solution to work sooner. I apologize for not catching the problem earlier.

By the way, I've also used the SUMPRODUCT solution and has it working for a lot of my formulas.

BUT, I ran into a problem and I am hoping you can help me with this too.

I have a situation where the FIND in SUMPRODUCT must find an "exact match." For example, I want to find only those records with "Done."

Or, as the case of my formula, I am counting records that has a "Version 1.0" string. Unfortunately, it is also counting records with "Version 1.0.1", where the first 11 characters are the same.

Is there a way to use SUMPRODUCT with FIND to get an exact match for exactly the string given above? I've tried, but with no success.

Again, thank you in advance...
This message was edited by tn312c on 2002-12-02 21:25