Can Lotus 123 @DMAX be converted to Excel formula?

Not familiar with Lotus, but based on what you have the Excel version of DMAX works a bit differently.

What you do is set up criteria in a separate set of cells, then use those as criteria - similar to do what you do in QBE interfaces of database programs. Below is an example; I11:N26 is your POWER_TABLE, the cells above the table are the criteria. DMAX formula is in I2. Ignore the asterisks.

You'll probably have to explain what the original formula does, and/or post a sample of your data, if you want a better answer.

Morning iliace,

Thanks for the reply. I should have thought to place an example with my original post. Sorry about that. Let me try this again... I'm still taking examples from the 123 workbook since it works correctly.

This formula is from cell C14 and it is displaying "228".
@DMAX($POWER_TABLE,"DRAW",DRAW<@IF(C11=0,$D$3,C11)#AND#(FIRST=C$9#OR#SECOND=C$9#OR#THIRD=C$9#OR#FOURTH=C$9#OR#FIFTH=C$9)))

POWER_TABLE - I've placed a portion of the table below.

$D$3 equals the current draw number and is displaying "258".

C$9 is set to display the number "1". (Row 9 contains numbers from 1 thru 59.)

C11 has this formula in it: @IF(C12="YES",+$POWERBALL:$D$24," ") and would display "YES" if the current draw contained a "1". (The last draw to contain a "1" was draw number "228".)

What the formula does is to look at the top number in the "DRAW" column, then find the last time a "1" was displayed. When it finds the number "1", it then looks left to get the number from the "DRAW" column. As you can see below, the last draw that a "1" was displayed was in draw "228".


<table border="0" cellpadding="0" cellspacing="0" width="450"><col style="width: 56pt;" width="75" span="6"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 56pt;" width="75" height="20">DRAW</td> <td class="xl65" style="width: 56pt;" width="75">FIRST</td> <td class="xl65" style="width: 56pt;" width="75">SECOND</td> <td class="xl65" style="width: 56pt;" width="75">THIRD</td> <td class="xl65" style="width: 56pt;" width="75">FOURTH</td> <td class="xl65" style="width: 56pt;" width="75">FIFTH</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">259</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">258</td> <td class="xl65">18</td> <td class="xl65">39</td> <td class="xl65">57</td> <td class="xl65">36</td> <td class="xl65">41</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">257</td> <td class="xl65">59</td> <td class="xl65">12</td> <td class="xl65">46</td> <td class="xl65">19</td> <td class="xl65">15</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">256</td> <td class="xl65">22</td> <td class="xl65">38</td> <td class="xl65">21</td> <td class="xl65">12</td> <td class="xl65">41</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">255</td> <td class="xl65">41</td> <td class="xl65">43</td> <td class="xl65">20</td> <td class="xl65">19</td> <td class="xl65">38</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">254</td> <td class="xl65">50</td> <td class="xl65">16</td> <td class="xl65">18</td> <td class="xl65">36</td> <td class="xl65">27</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">253</td> <td class="xl65">53</td> <td class="xl65">37</td> <td class="xl65">14</td> <td class="xl65">44</td> <td class="xl65">45</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">252</td> <td class="xl65">17</td> <td class="xl65">58</td> <td class="xl65">39</td> <td class="xl65">19</td> <td class="xl65">41</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">251</td> <td class="xl65">56</td> <td class="xl65">38</td> <td class="xl65">46</td> <td class="xl65">8</td> <td class="xl65">18</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">250</td> <td class="xl65">43</td> <td class="xl65">12</td> <td class="xl65">20</td> <td class="xl65">51</td> <td class="xl65">55</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">249</td> <td class="xl65">50</td> <td class="xl65">4</td> <td class="xl65">31</td> <td class="xl65">42</td> <td class="xl65">23</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">248</td> <td class="xl65">8</td> <td class="xl65">49</td> <td class="xl65">2</td> <td class="xl65">40</td> <td class="xl65">50</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">247</td> <td class="xl65">7</td> <td class="xl65">12</td> <td class="xl65">42</td> <td class="xl65">49</td> <td class="xl65">13</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">246</td> <td class="xl65">40</td> <td class="xl65">18</td> <td class="xl65">17</td> <td class="xl65">8</td> <td class="xl65">44</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">245</td> <td class="xl65">43</td> <td class="xl65">32</td> <td class="xl65">9</td> <td class="xl65">45</td> <td class="xl65">17</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">244</td> <td class="xl65">11</td> <td class="xl65">55</td> <td class="xl65">47</td> <td class="xl65">2</td> <td class="xl65">27</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">243</td> <td class="xl65">15</td> <td class="xl65">41</td> <td class="xl65">29</td> <td class="xl65">27</td> <td class="xl65">3</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">242</td> <td class="xl65">41</td> <td class="xl65">15</td> <td class="xl65">6</td> <td class="xl65">13</td> <td class="xl65">32</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">241</td> <td class="xl65">40</td> <td class="xl65">44</td> <td class="xl65">55</td> <td class="xl65">24</td> <td class="xl65">4</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">240</td> <td class="xl65">58</td> <td class="xl65">11</td> <td class="xl65">3</td> <td class="xl65">48</td> <td class="xl65">47</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">239</td> <td class="xl65">9</td> <td class="xl65">24</td> <td class="xl65">43</td> <td class="xl65">34</td> <td class="xl65">36</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">238</td> <td class="xl65">21</td> <td class="xl65">55</td> <td class="xl65">33</td> <td class="xl65">45</td> <td class="xl65">44</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">237</td> <td class="xl65">4</td> <td class="xl65">49</td> <td class="xl65">39</td> <td class="xl65">50</td> <td class="xl65">23</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">236</td> <td class="xl65">53</td> <td class="xl65">5</td> <td class="xl65">56</td> <td class="xl65">14</td> <td class="xl65">32</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">235</td> <td class="xl65">56</td> <td class="xl65">18</td> <td class="xl65">55</td> <td class="xl65">10</td> <td class="xl65">41</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">234</td> <td class="xl65">43</td> <td class="xl65">22</td> <td class="xl65">34</td> <td class="xl65">6</td> <td class="xl65">45</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">233</td> <td class="xl65">58</td> <td class="xl65">56</td> <td class="xl65">20</td> <td class="xl65">19</td> <td class="xl65">42</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">232</td> <td class="xl65">10</td> <td class="xl65">4</td> <td class="xl65">11</td> <td class="xl65">33</td> <td class="xl65">19</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">231</td> <td class="xl65">15</td> <td class="xl65">28</td> <td class="xl65">5</td> <td class="xl65">26</td> <td class="xl65">32</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">230</td> <td class="xl65">11</td> <td class="xl65">20</td> <td class="xl65">46</td> <td class="xl65">27</td> <td class="xl65">3</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">229</td> <td class="xl65">53</td> <td class="xl65">28</td> <td class="xl65">48</td> <td class="xl65">39</td> <td class="xl65">40</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">228</td> <td class="xl65">4</td> <td class="xl65">41</td> <td class="xl65">1</td> <td class="xl65">12</td> <td class="xl65">47</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">227</td> <td class="xl65">12</td> <td class="xl65">20</td> <td class="xl65">28</td> <td class="xl65">40</td> <td class="xl65">48</td> </tr> </tbody></table>

I'm trying to recreate the formula in Excel so that I don't have to recreate the entire sheet. As I said, there are 100's of these formulas in this sheet.

I hope this explanation makes sense. it's gotten me turned around just trying to explain it. Thanks for devoting the time to attempt to figure it out.

OK, I think I gotcha. I don't think Excel's version of DMAX is going to help you, but maybe this will.

I have my data arranged as follows:

E22:E53 is the list of Draw numbers (from your example above) from 258 down.
Respectively F22:J53 is the 1st-5th number of each Draw.

In C9, I have numbers beginning with 1 like you indicated

In C14, I have a formula that will return the most recent time the number in C9 was drawn. The formula is then copied over to D14, E14, etc.

<b>sheet (3)</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:35px;" /><col style="width:35px;" /><col style="width:35px;" /><col style="width:35px;" /><col style="width:35px;" /><col style="width:35px;" /><col style="width:35px;" /><col style="width:35px;" /><col style="width:35px;" /><col style="width:35px;" /><col style="width:35px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td></tr><tr style="height:15px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">1</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">2</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">3</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">4</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">5</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">6</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">7</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">8</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">9</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">10</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">11</td></tr><tr style="height:15px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td></tr><tr style="height:15px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td></tr><tr style="height:15px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td></tr><tr style="height:15px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td><td style="font-family:Arial; font-size:10pt; "> </td></tr><tr style="height:15px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">228</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">248</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">243</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">249</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">236</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">242</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">247</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">251</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">245</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">235</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">244</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C14</td><td >{=INDEX($E$22:$E$53, MATCH<span style=' color:008000; '>(TRUE, MMULT<span style=' color:#0000ff; '>(--<span style=' color:#ff0000; '>($F$22:$J$53=C9)</span>, {1;1;1;1;1})</span>>0, 0)</span>)}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Formula Array:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Produce enclosing </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> by entering<br />formula with CTRL+SHIFT+ENTER!</span></td></tr></table>

Hello again iliace,

Sorry it took so long to get back to this post. It took me a while to figure out how to change the cell reference to a different sheet. Below is the working formula:

=INDEX(Sheet1!$D$22:$D$55, MATCH(TRUE, MMULT(--(Sheet1!$E$22:$I$55=C9), {1;1;1;1;1})>0, 0))

I discovered a different @DMAX formula in cell C17. It looks at the cell C14 results and and the "POWER_TABLE" data to find the next lowest draw number.
<c14#and#(first=c$9#or#second=c$9#or#third=c$9#or#fourth=c$9#or#fifth=c$9)))><c14#and#(first=c$9#or#second=c$9#or#third=c$9#or#fourth=c$9#or#fifth=c$9)))><c14#and#(first=c$9#or#second=c$9#or#third=c$9#or#fourth=c$9#or#fifth=c$9)))><c14#and#(first=c$9#or#second=c$9#or#third=c$9#or#fourth=c$9#or#fifth=c$9)))>
Your original solution works very well. Thank you. I'm attempting to understand how the construction works.

Would you mind taking at look at the second formula? I'll have to post separately, isn't pasting correctly.
</c14#and#(first=c$9#or#second=c$9#or#third=c$9#or#fourth=c$9#or#fifth=c$9)))></c14#and#(first=c$9#or#second=c$9#or#third=c$9#or#fourth=c$9#or#fifth=c$9)))></c14#and#(first=c$9#or#second=c$9#or#third=c$9#or#fourth=c$9#or#fifth=c$9)))></c14#and#(first=c$9#or#second=c$9#or#third=c$9#or#fourth=c$9#or#fifth=c$9)))>

Here's the other @DMAX formula I found. It's in cell C17. It looks at the result in cell C14 and the Power_Table and finds the next lower draw number.

@DMAX($POWER_TABLE,"DRAW",DRAW<c14#and#(first=c$9#or#second=c$9#or#third=c$9#or#fourth=c$9#or#fifth=c$9)))><c14#and#(first=c$9#or#second=c$9#or#third=c$9#or#fourth=c$9#or#fifth=c$9)))>

Not letting me paste the entire formula, had to type it out... and still didn't post properly....

Is this making any sense?</c14#and#(first=c$9#or#second=c$9#or#third=c$9#or#fourth=c$9#or#fifth=c$9)))></c14#and#(first=c$9#or#second=c$9#or#third=c$9#or#fourth=c$9#or#fifth=c$9)))>

I think that happens because the forum treats < symbols as HTML tags... try putting spaces after them...

I'm thinking of a solution, however just for your information, the first formula hinges on MMULT returning a single-column array with non-zero values for each row where the required number exists. Using MATCH/INDEX is fairly easy to find the first occurrence, but subsequent occurrences are somewhat more difficult... but that bit of information should help you figure out how the original solution works.

Here's another attempt. Don't know yet if I'll work....

This formula is in cell C17 and C20 and goes across the rows. Now that I look further, it's in all of the other cells downward that display the @DMAX formula.

@DMAX($POWER_TABLE,"DRAW",DRAW

The formula above and below is 1 formula with "<" between the 2.

C14#AND(FIRST=C$9#OR#SECOND=C$9#OR#THIRD=C$9#OR#FOURTH=C$9#OR#FIFTH=C$9)))

The formula in C17 looks for the next "1" to appear in a lower draw number, then when it finds it, it gets the draw number as before.

An example would be the number "18". Cell T14 is displaying draw number 254. The next draw number is 251 and it's in cell T17. Then in cell T20, draw number 246 is displayed. If you'll notice the previous example table, the number "18" is in different columns in each draw.

Getting back to your previous solution, I can create some simple formulas, but when it comes to complex formula construction, I'm lost. I also don't understand how data base formulas work. I've have been able to create formulas with several =IF's nested within them that work real well.

I looked at your previous formula and went Bada Bada...Huh?

Thanks again for the assistance. It's very much appreciated.

iliace,

Per post #4 in this topic.

I went back and rechecked the Lotus 123 file with the results from the Excel file that I'm attempting to recreate. Although the formula works, it did not provide the same results.

I posted a different question regarding the =DMAX function multiple criteria construction. This is the link:

http://www.mrexcel.com/forum/showthread.php?p=2773104#post2773104

Thanks for your assistance.

It's not really a question of whether the answer is the same, it's more an issue of what the correct one should be.

In either case, best of luck to you.