I would like to have a function who finds the first or last occurrence of an item in a cell area. Something like FIND.FIRST(B1:C20,A1) and FIND.LAST(B1:C20,A1) where A1 contains the needle (what should be found), C1:C20 the search area (the heystack) and if found in C1:C20 returns the value of the corresponding row from B1:B20. Also FIND.FIRST(B1:B20,A1) where the returned value is the address to the actual cell in B1:B20 will do. A1 could contain a number value or something like "<5" to find the first occurrence of a number less than 5. Including text would be top. What could this function be used for? If I for example are running a store opening at 7 in the morning with a few employers. (I assume the cash register is connected to Calc). What time of the day is the first sale? If I never sell anything before 10 o'clock, I could save some wages, and consider sleeping 3 hours more in the morning.:-) Or I could find out what day was the first rainy day in June or the first frosty day in the autumn the latest 20 years, or when my wildlife cameras first recognized an elk, and many others like these. I have not found this function in other spreadsheets, so perhaps Calc will be the first one? I am now using a walk around, so I am not asking for how to do this calculations. I am only asking for a function to do this in a simpler way. Kolbjørn Stuestøl
Please expose your workaround.
I'm tempted to close this as WONTFIX. Opening the floodgate to functions that already have workable solutions but users want "an easier way" seems hugely problematic. I can think of dozens (maybe even hundreds) of nested functions that I use somewhat routinely that I could argue "this three nested function could be done with one new function" but I think that's unrealistic and problematic in that it adds yet more extraneous code to the base. My suggestion is close as WONTFIX but I suppose let's wait for the workaround (a really simple attachment would give us what we need)
A simple example: Using it for weather statistics to find the first occurrence of frost in autumn. In a spreadsheet A contains the date of the month starting from A1, B contains the temperature measured the corresponding day. My workaround in this example is: Copy the dates from A to D. In C1 write “= IF(B1<0;1;0)” and fill this down to the end of the month (row 31). In A33 insert “VLOOKUP(1;C1:D31;2;0)”. To get the last day with frost I have to sort a copy of my tables descending (dates 31 to 1) before using the same procedure as mentioned above. (Of course I have to do a bit more coding to avoid “#N/A” in A33 if no frost the particular month). Download simplified example from: http://www.stuestoel.no/temp/FIND.FIRST.ods I know that the developers have plenty to do, but I hope someone will give this suggestion a thought. It is no problem to write (simple) workarounds for many of the functions already in use, so I do not think a function is unnecessary only because it is possible to write workarounds. (For example the first function in the function list: ABS(A1) can be written as IF(A1<0;A1*-1;A1)). But if I am the only one in the world in need for such a function, it is of course a good reason to close it. Kolbjørn
Created attachment 125848 [details] Find_first_v2.ods Can be done without sort or additional column. {=MIN(A4:A34*IF(B4:B34<0;1;99))}, needs {Ctrl+Enter] to enter the formula as array or =SUMPRODUCT(MIN(A4:A34*IF(B4:B34<0;1;99))), to avoid enter as array. or with a simple pivot table as showed in the sample. But I agree with Joel, not enough for a new function, I haven't seen asking for it in forums. In fact lately only functions for compatibility reasons have been added, and it is being a large effort.
Thank you for your formulas. “In fact lately only functions for compatibility reasons have been added …”. So I have to wait until Excel comes up with a function like this ;-) . It was a proposal only, and as I said before, if no other people finds the function useful, do not waste time on it. Thank you for using time on this suggestion. Kolbjørn
Consulted devs on IRC and the conclusion is to close this.