Calc suggestion

kolbjoern · June 20, 2016, 7:54pm

I have not found an active forum for developers so I am trying this forum. Perhaps someone know where to post it?

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 shop 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.

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

Paolo_Debortoli · June 20, 2016, 8:13pm

It is not the wrong place, but you may directly talk to developers in other places:

https://www.libreoffice.org/community/developers/
https://www.libreoffice.org/get-help/mailing-lists/

https://lists.freedesktop.org/mailman/listinfo/libreoffice
Suggestions, requests, contributions and new contributors are always welcome.

Paolo

kolbjoern · June 21, 2016, 8:55am

Den 20.06.2016 22:10, Paolo Debortoli skreiv:

It is not the wrong place, but you may directly talk to developers in other places:

https://www.libreoffice.org/community/developers/
https://www.libreoffice.org/get-help/mailing-lists/

https://lists.freedesktop.org/mailman/listinfo/libreoffice
Suggestions, requests, contributions and new contributors are always welcome.

Paolo

Thank you to you and Brian.
I'll give them a try.
I know the bug site, but thought it was for bugs only.

Kolbjørn

kolbjoern · June 26, 2016, 7:01pm

I asked for this as an enhancement. (Bug125848 <http://bugs.documentfoundation.org/attachment.cgi?id=125848>).
It looks like the developers has plenty to do adding functions to be compatible with other spreadsheets (read: Excel?). So most likely it will be marked WONTFIX.
I just asked for this function because
1) I needed it (instead of using workarounds), and
2) it would be nice if Calc sometimes was ahead of Excel.
But, as I said, if I am the only one in need for such function(s), it is undesirable to waste time on it.

Kolbjørn

Joel_Madero · June 26, 2016, 8:14pm

Hi Kolbjørn,

I just want to make sure expectations here are realistic (I believe I was one who commented on the bug). Our project works by the "doers deciding" - so even if 100 users say they'd like the feature, *at most* this would result in the enhancement going to NEW as a valid enhancement request and then *very likely* would sit there for a long time (until a developer with the skills required *volunteered* to implement it.

I've pointed out a couple issues with adding entirely new functions:

1) We (The Document Foundation) does not control ods standards, I suggested to you that you poke them to see if they're at all interested in adding a new standards;

2) When there is currently a workable formula (even if requiring some nested functions) - the reason is that there are almost infinite combinations of nested functions that people use - if we start taking enhancements based on "well one function could replace this x(y(z(#,#))) function" than we almost have no limit. I could almost immediately file probably two dozen such requests.

Again, just trying to keep things realistic. Best case is that this goes to "NEW" just as a "fine it's a valid enhancement" but then it'll almost definitely sit there (possibly indefinitely).

Warmest Regards,

Joel

kolbjoern · June 27, 2016, 10:32am

Hi Joel
Thank you for using your time to explain the workflow and "philosophy" of this project. I have learned a lot .
I know that LibreOffice depends on volunteers. (I am just one of the many translators using some spare time to get LibreOffice available all over the world in peoples own languages).
The first spreadsheets had only a few functions and workarounds was a necessity in almost every spreadsheet, so I am used to write workarounds. I was filing my suggestion because I do not have any others to ask whether this function would be useful to others or not.
So again, thank you for answering.
Kolbjørn

Den 26.06.2016 22:14, Joel Madero skreiv:

toki · June 28, 2016, 5:01pm

What is the bug # at https://bugs.documentfoundation.org/

jonathon

V_Stuart_Foote · June 28, 2016, 5:22pm

toki wrote

I was filing my suggestion because I do not have any others to ask
whether this function would be useful to others or not.

What is the bug # at https://bugs.documentfoundation.org/

tdf#100529 <https://bugs.documentfoundation.org/show_bug.cgi?id=100529>

kolbjoern · June 28, 2016, 5:48pm

Den 28.06.2016 19:00, toki skreiv:

I was filing my suggestion because I do not have any others to ask whether this function would be useful to others or not.

What is the bug # at https://bugs.documentfoundation.org/

jonathon

100529 <https://bugs.documentfoundation.org/show_bug.cgi?id=100529>
or search for FIND.FIRST

Kolbjørn

toki · June 28, 2016, 6:00pm

Thanks

jonathon

Tom_Davies1 · June 29, 2016, 1:06pm

Hi
The bug-report system is good for suggesting new ideas too. This is also
true in many other projects too. In the LibreOffice's bug-tracker it used
to be that one of the drop-downs (severity, urgency or whatever) had
"feature request" somewhere in it's options.

Bug-reports look a lot like emails, in that they have a "Subject-line" and
a "main message" section, and that you can add more information to them
later through additional posts.

So for a lot of projects it is enough to write "[feature request]" in the
subject-line and i think the LibreOffice devs would recognise that too. So
if you can't find the option in the drop-downs or are shy about using them
then just putting something like that in the subject-line is plenty good
enough. It may even help to put it in even if you did manage to use the
drop-downs.

Many other projects have many other ways of putting forwards new
suggestions but i don't think any have found a perfect system and all have
advantages and disadvantages.

"Easy Hacks"
The Document Foundation were brilliant in introducing the "Easy Hacks"
tag/category so that new devs can try their hand at writing code for the
LibreOffice project. This is great for;
1. experienced devs who just haven't worked with LibreOffice before, so
that they can quickly show off and/or find out about "in-house styles" (I
can't think what the coding equivalent is but there is surely something
like that).
2. for noobish devs who have only just learned how to code and need
real-world experience.
For this 2nd option TDF tried to set-up a mentoring system. I'm not sure
if it really "took off" but even if it didn't it is likely that other devs
might be willing (or even happy) to give their opinions about the quality
of the code and hopefully give ideas about "best practice" or neat ideas
worth trying, or 'rules' used within the project.

So, don't worry about putting forwards ideas. Even if they don't get a
great reception initially they may later prove to grab someone's interest
or/and lead on to other things.
Regards from
Tom