[Solved] Convert Excel Array Formula to ods

[thehoorse]

Hello. I have an array formula that works fine in excel, but doesn't work when converted to OpenOffice. I made sure all the , are changed to ;. I get the #DIV/0! message in OpenOffice. I know it means something's getting divided by 0 somewhere. Please help me get it to work just like it does in excel.

Here is the excel version of the formula: =SUM(--(IF(COLUMN(A1:M1)>MIN(LOOKUP(2,1/(A1:M1="W"),COLUMN(A1:M1)),LOOKUP(2,1/(A1:M1="L"),COLUMN(A1:M1))),A1:M1)=LOOKUP(2,1/((A1:M1<>"")*(A1:M1<>"P")),A1:M1)))

The array formula gives the result of most recent consecutive streak of W (wins) or L (loses) without being disrupted by P (push). In other words pushes are ignored and the most recent string is given.

I have attached the excel version of the array formula as well as an openoffice spreadsheet giving the aforementioned error message as a result.

Any help is greatly appreciated.

[acknak]

... LOOKUP(2,1/(A1:M1="W"),COLUMN(A1:M1)) ...

This depends on LOOKUP returning a value when the first column includes a divide-by-zero error--and Calc doesn't handle it the same way as Excel.

Sorry, I can't make any suggestions for working around the difference, but there is surely some more straightforward approach that will work in both.

Edit: PS:

I believe this is the relevant issue here (but I could be wrong): Issue 33295: error values in MATCH range match to 0

[thehoorse]

Thanks for the reply acknak. I wish I knew more about these things. Hope someone can come up with a solution, even if by different formula. Anyone know how to?

[Villeroy]

http://gnumeric.org handles this like Excel.

[keme]

Another issue is that LOOKUP() requires a sorted search vector, while the letters in your row 1 are unsorted. Behaviour of LOOKUP() with unsorted data is "undefined" (i.e. not predictable) in both Excel and Calc, and the results for identical formulas are frequently different between them. Not sure how Gnumeric handles that situation.

Trying to mimic an undefined/unpredictable situation from one application while using a different application is virtually impossible. You have here two elements evaluating to "indefinite" and "undefined", respectively. Please, instead of showing something that doesn't always work (and indeed shouldn't work at all), explain to us what you want that formula to tell you. That may enable us to create a formula "within spreadsheet semantics" (which will then make sense in all compatible software).

[Villeroy]

Another issue is that LOOKUP() requires a sorted search vector, while the letters in your row 1 are unsorted. Behaviour of LOOKUP() with unsorted data is "undefined" (i.e. not predictable) in both Excel and Calc, and the results for identical formulas are frequently different between them. Not sure how Gnumeric handles that situation.

As far as I can tell the LOOKUP function is the same in Excel, Gnumeric and Calc. The specific behaviour of sorted mode is part of the strategy to detect a streak of values within the unsorted array.

[eremmel]

So Excel ignores the DivByZero. But we can use an other method to get max column for any W or L:
MAX((A1:M1="W")* COLUMN(A1:M1) )
The latter LOOKUP can be replaced by (we take the proper index on a sorted list.:
LOOKUP(MAX((A1:M1<>"")*(A1:M1<>"P")*COLUMN(A1:M1)),COLUMN(A1:M1),A1:M1)
The double -- in the SUM is for Excel to translate boolean to numbers. The same can be done with 0+. Then the formula works in Excel and Calc:

Code: Select all

=SUM(0+(IF(COLUMN(A1:M1)>MIN( MAX((A1:M1="W")*COLUMN(A1:M1) ); MAX((A1:M1="L")*COLUMN(A1:M1) ) );A1:M1)=LOOKUP(MAX((A1:M1<>"")*(A1:M1<>"P")*COLUMN(A1:M1));COLUMN(A1:M1);A1:M1)))

P.S.
Five exclamation marks for the guy that construct this kind of formulas !!!!!

[ken johnson]

Check if this array formula follows the same logic...

Code: Select all

SUM(IF((COLUMN(A1:M1)>MIN(MAX(COLUMN(A1:M1)*(A1:M1="L"));MAX(COLUMN(A1:M1)*(A1:M1="W"))))*(COLUMN(A1:M1)<=MAX(MAX(COLUMN(A1:M1)*(A1:M1="L"));MAX(COLUMN(A1:M1)*(A1:M1="W"))));A1:M1;"")=INDEX(A1:M1;0;MAX(COLUMN(A1:M1)*(A1:M1<>"")*(A1:M1<>"P"))))

40 array formula problem.ods

(9.16 KiB) Downloaded 334 times

Ken Johnson

[thehoorse]

Another issue is that LOOKUP() requires a sorted search vector, while the letters in your row 1 are unsorted. Behaviour of LOOKUP() with unsorted data is "undefined" (i.e. not predictable) in both Excel and Calc, and the results for identical formulas are frequently different between them. Not sure how Gnumeric handles that situation.

Trying to mimic an undefined/unpredictable situation from one application while using a different application is virtually impossible. You have here two elements evaluating to "indefinite" and "undefined", respectively. Please, instead of showing something that doesn't always work (and indeed shouldn't work at all), explain to us what you want that formula to tell you. That may enable us to create a formula "within spreadsheet semantics" (which will then make sense in all compatible software).

I'm looking for the formula which will count the current (most recent) streak of wins ("w") and losses ("l") while not allowing pushes ("p" - as in a draw) to break up the streak. In other words I would like for "p" (push) to be ignored. The result does not have to distinguish whether it is a win or loss. All I care about is the number of the current streak (regardless of whether it is a win or loss). Here are some examples...


W L W W L P L L W P W <-----The result I am looking for is 2 (since the current streak is 2 wins while ignoring pushes)

L P L L W W L L P L L <-----The result I am looking for here is 4

W L W L L W W W W L L <------The result is 2

W L W P L L P L P L L W W P <------The result is 2 (since last consecutive streak of non push is 2 wins)


Appreciate any suggestions.

[thehoorse]

Check if this array formula follows the same logic...

Code: Select all

SUM(IF((COLUMN(A1:M1)>MIN(MAX(COLUMN(A1:M1)*(A1:M1="L"));MAX(COLUMN(A1:M1)*(A1:M1="W"))))*(COLUMN(A1:M1)<=MAX(MAX(COLUMN(A1:M1)*(A1:M1="L"));MAX(COLUMN(A1:M1)*(A1:M1="W"))));A1:M1;"")=INDEX(A1:M1;0;MAX(COLUMN(A1:M1)*(A1:M1<>"")*(A1:M1<>"P"))))

40 array formula problem.ods

Ken Johnson

Ken, this seems to work on a new spreadsheet. However, when I apply it to the spreadsheet I already have made, it is adding up all the cells. I made sure to adjust each range. Instead of A1:M1, I made everything B2:BF2. It's a pretty long row hence up to BF2. Is it possible that it is not working because of the length of the row? Maybe too long? I would imagine it should still work, but not sure.

[thehoorse]

Ken, this seems to work on a new spreadsheet. However, when I apply it to the spreadsheet I already have made, it is adding up all the cells. I made sure to adjust each range. Instead of A1:M1, I made everything B2:BF2. It's a pretty long row hence up to BF2. Is it possible that it is not working because of the length of the row? Maybe too long? I would imagine it should still work, but not sure.

If it begins on cell A1 it works. It seems to stop working any time I change the range from starting on cell A1 (to something like B2:M2, for example).

[thehoorse]

Okay, the range length doesn't have anything to do with it. The problem seemed to be occurring anytime I didn't start it on column A. I've instead changed my spreadsheet to accommodate this. Sometimes you have to adapt as well instead of trying to force things, hehe. Thank you all for all the suggestions. Ken thank you for the winning formula!

Take care all and thanks again.

[MrProgrammer]

Here is the excel version of the formula:
=SUM(--(IF(COLUMN(A1:M1)>MIN(LOOKUP(2,1/(A1:M1="W"),COLUMN(A1:M1)),LOOKUP(2,1/(A1:M1="L"),COLUMN(A1:M1))),A1:M1)=LOOKUP(2,1/((A1:M1<>"")*(A1:M1<>"P")),A1:M1)))

This is the worst spreadsheet formula that I have ever seen:

• It is inscrutable.
• It deliberately attempts calculations with #DIV/0! error values.
• It uses LOOKUP in a way that both Excel's and Calc's documentation clearly says may give an incorrect value.
• It depends on the Excel-specific implemention of how that incorrect value is calculated.

I agree with keme's comments above. It would be far better to use some auxillary columns so that the answer can be calculated in a way that someone other than an Excel-array-formula geek can understand.

For LOOKUP, the search vector must be in ascending order. The sequence 1 #DIV/0! #DIV/0! #DIV/0! 1 1 … is not in ascending order. Even if we exclude the error values, 1 1 1 … is still not in ascending order because the adjective is "ascending" not "non-descending".

I'm looking for the formula which will count the current (most recent) streak of wins ("w") and losses ("l") while not allowing pushes ("p" - as in a draw) to break up the streak. In other words I would like for "p" (push) to be ignored. The result does not have to distinguish whether it is a win or loss. All I care about is the number of the current streak (regardless of whether it is a win or loss).

In my attachment I use columns AA1:AN1 to determine the columns containing L or W, columns BA1:BN1 to determine if the item matches the last L or W, columns CA1:CN1 to determine if it also matches P, and columns DA1:DN1 to perform the incrementing count.

Because it is difficult to view the formulas in these columns without a lot of scrolling, I have created a second "Simplified" sheet which uses the same formulas in A2:N5 instead of AA1:DN1. They are quite simple: =IF(OR(A1="L";A1="W");COLUMN(A1)), =INDEX(A1:M1;1;MAX(A2:M2)), =A1=$N$2, =OR(A3;A1="P"), =IF(B5*A4;B5+A3), and =MAX(A5:M5)-$N$5. That's all you need.

For future reference in case we see this bogus function usage again, it apprears that in Excel =LOOKUP(2,1/logicalarray,…) returns the item corresponding to the last TRUE value in logicalarray. The corresponding formulas in Calc would be =INDEX(…;1;MAX(IF(logicalarray;COLUMN(logicalarray)))) if logicalarray is a row vector and =INDEX(…;MAX(IF(logicalarray;ROW(logicalarray)));1) if logicalarray is a column vector.

Edit: My attachment seems to have disappeared, so I'm restoring it.

[acknak]

Actually, (ab)using LOOKUP this way seems to be a rather well-known trick idiom in Excel. Here's a good description of how it works, from 2003: http://www.mrexcel.com/forum/excel-ques ... -data.html

[ken johnson]

The solution provided by eremmel is the one you should have chosen.
I suspect that when you pasted it into your document it immediately threw the #VALUE! error. This will always happen when you paste a string for an array formula into a cell when that string includes the starting equals sign. (I never include the starting equals sign when the formula I am suggesting is an array formula)
Had you copied eremmel's formula immediately after the starting equals sign, pasted that into your doc, typed the starting equals sign back in, then array-entered the result you would have seen a numerical result applicable to A1:M1 (which would be #N/A if A1:M1 is empty).
eremmel's formula is readily adaptable to B2:BF2 by simply changing all references to A1:M1 to references to B2:BF2.
To make my formula equally adaptable the INDEX function's column reference parameter needs to be changed from...

Code: Select all

MAX(COLUMN(B2:BF2)*(B2:BF2<>"")*(B2:BF2<>"P"))

to...

Code: Select all

MAX(COLUMN(B2:BF2)*(B2:BF2<>"")*(B2:BF2<>"P"))-MIN(COLUMN(B2:BF2))+1

resulting in this final array formula...

Code: Select all

SUM(IF((COLUMN(B2:BF2)>MIN(MAX(COLUMN(B2:BF2)*(B2:BF2="L"));MAX(COLUMN(B2:BF2)*(B2:BF2="W"))))*(COLUMN(B2:BF2)<=MAX(MAX(COLUMN(B2:BF2)*(B2:BF2="L"));MAX(COLUMN(B2:BF2)*(B2:BF2="W"))));B2:BF2;"")=INDEX(B2:BF2;0;MAX(COLUMN(B2:BF2)*(B2:BF2<>"")*(B2:BF2<>"P"))-MIN(COLUMN(B2:BF2))+1))

Also, for use in Calc and Excel, the IF needs to be wrapped in parentheses and prefaced with the double unary "--" or "0+"...

Code: Select all

SUM(--(IF((COLUMN(B2:BF2)>MIN(MAX(COLUMN(B2:BF2)*(B2:BF2="L"));MAX(COLUMN(B2:BF2)*(B2:BF2="W"))))*(COLUMN(B2:BF2)<=MAX(MAX(COLUMN(B2:BF2)*(B2:BF2="L"));MAX(COLUMN(B2:BF2)*(B2:BF2="W"))));B2:BF2;"")=INDEX(B2:BF2;0;MAX(COLUMN(B2:BF2)*(B2:BF2<>"")*(B2:BF2<>"P"))-MIN(COLUMN(B2:BF2))+1)))

Ken Johnson