I believe I was able to follow how the results were obtained. Because I have typed the value 1 in cell D2, the database function counts the items wher Type = 1.Īs 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. Where D2 is equivalent to A14 in your 123 example. This is because a computed criteria effectively computes a new field for the list. Notice also that there is no header row for the criteria in column A. This is necessary so that the reference does not adjust when the database function evaluates the formula for each row in the list. Notice that I have used absolute references to cells outside the database list eg $D$1. The database function evaluates the formula for each row in the database - their initial values are irrelevant. In computed criteria the formula refers to the first item in the list or the header. Where D1 is equivalent to B13 in your 123 example. In my example I have used formulas to create the conditions:Ī2 =MID(Status,FIND($D$1,Status,1),LEN($D$1))=$D$1 The criteria are the same as those used by Advanced Filter. Then click the little icon at the end of the section on Criteria. For more information search Help for DataBase Functions. The range A1:B3 is the criteria range used by the DCOUNTA function in cell D3. 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. I am therefore still very puzzled on what to make of the data specified in A2:B3. Isn't #VALUE! an indication of an error with the MID formula in the "window?" If it is, I am not sure what that has to do with the formula you have in the "window.". I went to Help and found there is a FALSE function. 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 think A1:B3 is the "criteria table" for the MID statement showing in the "window." If I don't hear anything on this, then I'll asssume that my understanding is correct. In case, I've picked up the wrong idea, I am treating your example in A5:B17 as the database. I am, however, having problem grasping the "criteria table", so please bear with me as I am struggling real hard to grasp the knowledge. I'm okay with C1 to D3, because I can treat them as cells within a table (like my Sheet2 example). This message was edited by tn312c on 18:33 So, I am looking for help there too.Īgain, thank you all in advance for the help. The MID with the nested FIND is troubling me a lot, because I have tried different ways and none of them works. The #VALUE! error should, if coded properly, return a value of 2 for STATUS=AAA and TYPE=xyz.īecause 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 table, where the formula is specified, is in Sheet2: Note that the data is all 3 characters long. It has multiple columns, but for my example, the STATUS and TYPE columns are shown below with fictitiuos data: 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. Would it make a difference if the test arguments, like STATUS and TYPE are cell addresses in a table? For example: Well, now that I know, I don't feel too crazy. 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. Your asssitance will be greatly appreciated. Once I can get the hang of it, I think I should be fine. Hopefully your staff can help me through this initial roadblock. 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. It is very obvious that I am very new to Excel, even though I've had some expertise in using 123 database functions.
LOTUS 123 HELP HOW TO
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. Where H24=STATUS I24=TYPE H25=B13 and I25=A14.īut, what I think I've done is specify the following criteria: STATUS=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: The 123 formula tried using the following with no success: Can someone tell me how to convert a Lotus 123 formula to what must be implemented or coded in Excel?