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 must have posed a very difficult question or problem.

I do not feel too bad then, because I was of the impression that my problem is one that a lot of people may have encountered, especially those that had converted from using Lotus 123 to Microsoft Excel. I am one these people that are switching to Excel and could use some help getting off the ground, so to speak.

Hope I get a solution to the 123 formula, since it represents a majority of my formulas that I must convert.

Thank you in advance...

 

[Andrew Poulsom]

I remember being very please when 123 introduced text criteria like that. And very irritated when I moved to Excel and you had to specify the criteria range.

Anyway, when you use a formula to create a criterion, do not use a column label for a criteria label; either keep the criteria label blank or use a label that is not a column label in the list. See below:

DCOUNT.xls
	A	B	C	D
1		Type	Status	Done
2	#VALUE!	1	Type	1
3	FALSE	1	Count	4
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

 

[Aladin Akyurek]

On 2002-11-11 12:53, tn312c wrote:
I must have posed a very difficult question or problem.

I do not feel too bad then, because I was of the impression that my problem is one that a lot of people may have encountered, especially those that had converted from using Lotus 123 to Microsoft Excel. I am one these people that are switching to Excel and could use some help getting off the ground, so to speak.

Hope I get a solution to the 123 formula, since it represents a majority of my formulas that I must convert.

Thank you in advance...

Lotus database-functions have a larger scope than the ones in Excel. My guess is that the one you want to convert needs using SumProduct (see: http://www.mrexcel.com/wwwboard/messages/8961.html).

Additionally...

http://makeashorterlink.com/?P26B21962

http://makeashorterlink.com/?Y48B23962

 

[tn312c]

Thank you all for your responses. Your responses brought a bit of sanity back to my thinking, because I couldn't at first envision that a Microsoft product functions not as well as one of its competitors. Well, now that I know, I don't feel too crazy.

If I may refer to Andrew's post...

Would it make a difference if the test arguments, like STATUS and TYPE are cell addresses in a table? For example:

I have a lot of sheets referring to the database, so I have the database in a sheet of its own and it is called Sheet1. It has multiple columns, but for my example, the STATUS and TYPE columns are shown below with fictitiuos data:

Status Type
AAA xyz
BBB def
CCC rst
AAA xyz
BBB rst
CCC def
AAA klm
BBB klm
CCC def
Note that the data is all 3 characters long.

The table, where the formula is specified, is in Sheet2:

AAA BBB CCC
xyz #VALUE!
def

The #VALUE! error should, if coded properly, return a value of 2 for STATUS=AAA and TYPE=xyz..

Because the table I am trying to get the data filled is a table, how would I specify the criteria that has 3 criteria? Namely, 1) MID with a nested FIND; 2) LEFT and 3) a simple compare of "TYPE" not equal to what is in A14.

The MID with the nested FIND is troubling me a lot, because I have tried different ways and none of them works. So, I am looking for help there too.

Again, thank you all in advance for the help...
Again, thank you all in advance...
This message was edited by tn312c on 2002-11-11 18:33

 

[Andrew Poulsom]

On 2002-11-11 18:31, tn312c wrote:
If I may refer to Andrew's post...

... how would I specify the criteria that has 3 criteria? Namely, 1) MID with a nested FIND; 2) LEFT and 3) a simple compare of "TYPE" not equal to what is in A14.

Look at my formulas in A2:A3 - they contain MID and LEFT functions. To make Type not equal to D2 use the formula

=B6<>$D$2

in cell B2 and leave cell B3 blank.

 

[tn312c]

I'm okay with C1 to D3, because I can treat them as cells within a table (like my Sheet2 example).

I am, however, having problem grasping the "criteria table", so please bear with me as I am struggling real hard to grasp the knowledge.

In case, I've picked up the wrong idea, I am treating your example in A5:B17 as the database. If I don't hear anything on this, then I'll asssume that my understanding is correct.

I think A1:B3 is the "criteria table" for the MID statement showing in the "window."

Why or what is the purpose of "1" in B2? It is not the type value of "1" that is in the database and in D2, is it?

I am also confused with "FALSE" in A3. I went to Help and found there is a FALSE function. If it is, I am not sure what that has to do with the formula you have in the "window.".

Lastly, I am unsure about #VALUE! in A2. Isn't #VALUE! an indication of an error with the MID formula in the "window?"

I am therefore still very puzzled on what to make of the data specified in A2:B3. Is there a place I can go to get more educated? I looked under Excel Help, but didn't find any or if I did I got more confused than before I went in.

Sorry, for being so naive. Hope you'll be patient with me. Again, thank you in advance...

 

[Andrew Poulsom]

The range A1:B3 is the criteria range used by the DCOUNTA function in cell D3. For more information search Help for DataBase Functions. Then click the little icon at the end of the section on Criteria. The criteria are the same as those used by Advanced Filter.

In my example I have used formulas to create the conditions:

A2 =MID(Status,FIND($D$1,Status,1),LEN($D$1))=$D$1
A3 =LEFT(Status,LEN($D$1))=$D$1

where D1 is equivalent to B13 in your 123 example. In computed criteria the formula refers to the first item in the list or the header.

The database function evaluates the formula for each row in the database - their initial values are irrelevant. Notice that I have used absolute references to cells outside the database list eg $D$1. This is necessary so that the reference does not adjust when the database function evaluates the formula for each row in the list.

Notice also that there is no header row for the criteria in column A. This is because a computed criteria effectively computes a new field for the list.

Cells B2 and B3 contain the formula:

=$D$2

where D2 is equivalent to A14 in your 123 example. Because I have typed the value 1 in cell D2, the database function counts the items wher Type = 1.

Hope this helps.

 

[tn312c]

As per your suggestion, I went to "the little icon at the end of the section on Criteria" and copied the examples to Excel to see how the examples work. I believe I was able to follow how the results were obtained.

I tried an experiment, where I blanked A1 to remove the "Tree" from the criteria row. When I did that many of the formulas blew up. I must have misunderstood when the header row must be specified in the criteria box versus when it must be blank. What it is that I should have done, but did not do?

As to converting the 123 formula, I also put your example in Excel. I am, however, unable to duplicate the count of 4 for D3.

I have the following formulas in:
A2 has =MID(Status,FIND($D$2,Status,1),LEN($D$2))=$D$2
A3 has =LEFT(Status,LEN($D$2))=$D$2
D3 has =DCOUNTA(A5:B17,"Status",A1:B3)
The result I got for the count in D3 is zero.

May be my notion that you had broken my 123 formula into 3 separate formulas, where each is put in D3, A2 and A3. Is my understanding incorrect?

Again, thank you in advance Andrew.

 

[Andrew Poulsom]

On 2002-11-13 14:42, tn312c wrote:
As to converting the 123 formula, I also put your example in Excel. I am, however, unable to duplicate the count of 4 for D3.

I have the following formulas in:
A2 has =MID(Status,FIND($D$2,Status,1),LEN($D$2))=$D$2
A3 has =LEFT(Status,LEN($D$2))=$D$2
D3 has =DCOUNTA(A5:B17,"Status",A1:B3)
The result I got for the count in D3 is zero.

That's because in my example cell D1 contains the status test, not cell D2.