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

 

[tn312c]

I missed the $D$2 to $D$1 change when I copied your data to Excel. Sorry!

I now have the $D$2 to $D$1 change made, but still getting zero in cell D3.

Where else did I go wrong? Thanks...

 

[Andrew Poulsom]

I don't know.

In my sample worksheet formulas are underlined (hyperlinks). Make sure all yours are the same. You can click the hyperlink, click the copy button then paste special text the formula into the same cell in your worksheet.

 

[Andrew Poulsom]

In his post above Aladin suggested that the SUMPRODUCT function may be more appropriate. Here is my previous example with the SUMPRODUCT method added:

DCOUNT.xls
	A	B	C	D
1		Type	Status	Done
2	#VALUE!	1	Type	1
3	FALSE	1	Count	4
4			SumProduct	4
5	Status	Type
6	Done	1
7	AlmostDone	1
8	ToDo	1
9	Done	2
10	AlmostDone	2
11	ToDo	2
12	Done	3
13	AlmostDone	3
14	ToDo	3
15	Done	1
16	AlmostDone	1
17	ToDo	1
Sheet1

In cell D4 the formula is:

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

Splitting it up:

FIND($D$1,A6:A17,1)

tries to find "Done" (D1) in the range A6:A17. This function returns the start position if found or #VALUE! if not found. So:

NOT(ISERROR(FIND($D$1,A6:A17,1)))

returns an array containing True if found, False if not. Then:

(B6:B17=$D$2)

returns a True/False array depending on whether each of the cells in the range B16:B17 equals 1 (D2).

Each element in the array is multiplied giving either True or False, coerced to the values 1 or 0 respectively, to give another array, the elements of which are added up to give the result of 4.

See the links in Aladin's post for further examples.

 

[tn312c]

I had to make a few changes when I copied your data and formulas to Excel and I forgot to change A2 to A1. Sorry!

I've now corrected the A2 to A1 in Excel, but the count in cell D3 is still zero.

What else did I not do? Thanks...

 

[tn312c]

Again, sorry. I didn't see the multiple pages, so re-submitted my post one more time.

 

[tn312c]

Hi Andrew... I did as you instructed, but I am getting zeros for both the Count and SumProduct.

The differences are that, in both my cells A2 and A3, I have the "little green triangle in the top left corner of the cell box and with "#NAME?" and yours didn't have the triangles. Also, in your A3 you have "FALSE" and mentioned above I have #NAME?".

If so, I have no idea how to correct them as I've entered and copied the formulas exactly from your spreadsheet.

Btw, thank you very much for the detail explanations on the SumProduct formula. I know it will help once I get past why my exact formulas are not giving the same results.

Again, thanks...

 

[Andrew Poulsom]

Tools, Options, Calculation tab. Make sure "Accept labels in formulas" is checked (under Workbook options).

 

[tn312c]

It wasn't on, but it is now. But, the 2 results are still showing zeros and the error triangles still showing.

I noticed that the option must be set each time for a new file. Can I have that option as the default for any Excel files? I think it only remembers for the file being processed.

Andrew do you have any suggestion on how to help me out of my problem? Like, perhaps we can work out an arrangement to call each other? I'll be happy to bear the cost of the phone call, if you are willing. But, I'll understand, if you are not comfortable doing that.

Waiting for your next post. Again thanks...

 

[Andrew Poulsom]

You might have to revisit the formulas after changing the Accept labels setting. Just select the cell with the formula and press F2 then Enter.

Or just change the formulas to refer to cell in the first row of the list.

You can create a template which forms the basis for all new workbooks. Choose Help, search for "templates" and see the section "Create a workbook template for new workbooks".

 

[tn312c]

I did the PF2 and Enter and was unsuccessful with one. The one is the MID formula in cell A2. It still shows the triangle and the message returned is "A value used in the formula is of the wrong data type." I think this error is perhaps causing the 2 results to continue showing zeros.

I cannot find the one with the incorrect data type. The formula is:
=MID(Status,FIND($D$1,Status,1),LEN($D$1))=$D$1

I am wondering what else in the Excel parameters that should be set that are not.
Example, not setting the "Accept labels in formulas." Can u send me a picture of all the settings I should set or not set? Btw, I haven't been able to create a new template, but I am still experimenting - I am sorry to say that Excel's Help is not very clear for novice users.

Again, thanks...