You are viewing a plain text version of this content. The canonical link for it is here.
Posted to api@openoffice.apache.org by Павел <pa...@list.ru.INVALID> on 2018/03/02 21:57:30 UTC
OpenOffice Calc
Good afternoon!
Sorry for my bad english.
I propose a new formula for the OpenOffice Calc application.
= FINDN (A; B; C; D), where
FINDN (A; B; C; D) - displays the number of the character from which the desired, repeating text begins.
A - is the text you want to find;
B - the text in which the text A is searched;
C - the number, position number of the text B, with which to search;
D - is the number, the ordinal number of the text A to be found.
Example:
Cell A1 contains the text ";"
Cell B1 contains the text «154,8;641,5486;461,654;315,4135;135,454;574,42452;874,35;157,4;713»
The FINDN formula (A1; B1; 1; 6) gives the result = 50
Will explain:
I work a lot in OpenOffice Calc, I work every day with tens of thousands of different numbers. They are difficult to systematize, store and process due to the fact that they occupy a large number of cells. On my computer dozens of large tables with data that are complexly processed, new values are calculated. As a result I get a huge array of digital values for other company specialists.
I have to generate text containing values in this format:
«154,80000;641,54860;461,65400;315,41350;135,45400;574,42452;874,35000;157,40000;713,00000»
That is, here each value takes exactly 9 characters, plus 10 delimiter characters.
Cells with this text I copy to another table - a report from which, according to the query of other specialists, the first, second, third ... hundredth and so on value is displayed.
At the moment, I needed to use more than 120 such values to display parameters for one point of accounting for one day (actual resource consumption for 24 hours, planned flow for 24 hours, calculation factor for 24 hours [for example steam enthalpy], average temperature for 24 hours , average pressure for 24 hours and other data). And you need to have an archive not only for the current month, but also from the beginning of the year: for every day for several hundred accounting items.
It would be much easier to save data in this form:
«154,8;641,5486;461,654;315,4135;135,454;574,42452;874,35;157,4;713»
But in this case it is difficult to describe several algorithms by the user of the data, displaying the text, enclosed between some delimiters. For example, the sixth value is between the fifth and the sixth separator (574.42452). If this is the way to display text between 100 and 101 separators, it takes a lot of space and computer memory (if you have to work with thousands of such cells at the same time).
Sincerely, Kolomiets Pavel
pavel220@list.ru
Re: OpenOffice Calc
Posted by Martin Groenescheij <Ma...@Groenescheij.COM>.
Why don't you save the data in a CSV file first and then import it into
your spreadsheet?
Set the delimiter to ';" and make sure you change a locale that use the
"," as a decimal separator.
Much easier to calculate averages, sums etc.
On 03/03/18 8:57 AM, Павел wrote:
> Good afternoon!
> Sorry for my bad english.
> I propose a new formula for the OpenOffice Calc application.
> = FINDN (A; B; C; D), where
> FINDN (A; B; C; D) - displays the number of the character from which the desired, repeating text begins.
> A - is the text you want to find;
> B - the text in which the text A is searched;
> C - the number, position number of the text B, with which to search;
> D - is the number, the ordinal number of the text A to be found.
> Example:
> Cell A1 contains the text ";"
> Cell B1 contains the text «154,8;641,5486;461,654;315,4135;135,454;574,42452;874,35;157,4;713»
> The FINDN formula (A1; B1; 1; 6) gives the result = 50
> Will explain:
> I work a lot in OpenOffice Calc, I work every day with tens of thousands of different numbers. They are difficult to systematize, store and process due to the fact that they occupy a large number of cells. On my computer dozens of large tables with data that are complexly processed, new values are calculated. As a result I get a huge array of digital values for other company specialists.
> I have to generate text containing values in this format:
> «154,80000;641,54860;461,65400;315,41350;135,45400;574,42452;874,35000;157,40000;713,00000»
> That is, here each value takes exactly 9 characters, plus 10 delimiter characters.
> Cells with this text I copy to another table - a report from which, according to the query of other specialists, the first, second, third ... hundredth and so on value is displayed.
> At the moment, I needed to use more than 120 such values to display parameters for one point of accounting for one day (actual resource consumption for 24 hours, planned flow for 24 hours, calculation factor for 24 hours [for example steam enthalpy], average temperature for 24 hours , average pressure for 24 hours and other data). And you need to have an archive not only for the current month, but also from the beginning of the year: for every day for several hundred accounting items.
> It would be much easier to save data in this form:
> «154,8;641,5486;461,654;315,4135;135,454;574,42452;874,35;157,4;713»
> But in this case it is difficult to describe several algorithms by the user of the data, displaying the text, enclosed between some delimiters. For example, the sixth value is between the fifth and the sixth separator (574.42452). If this is the way to display text between 100 and 101 separators, it takes a lot of space and computer memory (if you have to work with thousands of such cells at the same time).
>
> Sincerely, Kolomiets Pavel
> pavel220@list.ru
---------------------------------------------------------------------
To unsubscribe, e-mail: api-unsubscribe@openoffice.apache.org
For additional commands, e-mail: api-help@openoffice.apache.org
Re: OpenOffice Calc
Posted by Антон Борисов <an...@gmail.com>.
You can define custom formula
https://wiki.openoffice.org/wiki/Custom_functions
Anton Borisov
сб, 3 марта 2018 г., 1:57 Павел <pa...@list.ru.invalid>:
>
> Good afternoon!
> Sorry for my bad english.
> I propose a new formula for the OpenOffice Calc application.
> = FINDN (A; B; C; D), where
> FINDN (A; B; C; D) - displays the number of the character from which the
> desired, repeating text begins.
> A - is the text you want to find;
> B - the text in which the text A is searched;
> C - the number, position number of the text B, with which to search;
> D - is the number, the ordinal number of the text A to be found.
> Example:
> Cell A1 contains the text ";"
> Cell B1 contains the text
> «154,8;641,5486;461,654;315,4135;135,454;574,42452;874,35;157,4;713»
> The FINDN formula (A1; B1; 1; 6) gives the result = 50
> Will explain:
> I work a lot in OpenOffice Calc, I work every day with tens of thousands
> of different numbers. They are difficult to systematize, store and process
> due to the fact that they occupy a large number of cells. On my computer
> dozens of large tables with data that are complexly processed, new values
> are calculated. As a result I get a huge array of digital values for other
> company specialists.
> I have to generate text containing values in this format:
>
> «154,80000;641,54860;461,65400;315,41350;135,45400;574,42452;874,35000;157,40000;713,00000»
> That is, here each value takes exactly 9 characters, plus 10 delimiter
> characters.
> Cells with this text I copy to another table - a report from which,
> according to the query of other specialists, the first, second, third ...
> hundredth and so on value is displayed.
> At the moment, I needed to use more than 120 such values to display
> parameters for one point of accounting for one day (actual resource
> consumption for 24 hours, planned flow for 24 hours, calculation factor for
> 24 hours [for example steam enthalpy], average temperature for 24 hours ,
> average pressure for 24 hours and other data). And you need to have an
> archive not only for the current month, but also from the beginning of the
> year: for every day for several hundred accounting items.
> It would be much easier to save data in this form:
> «154,8;641,5486;461,654;315,4135;135,454;574,42452;874,35;157,4;713»
> But in this case it is difficult to describe several algorithms by the
> user of the data, displaying the text, enclosed between some delimiters.
> For example, the sixth value is between the fifth and the sixth separator
> (574.42452). If this is the way to display text between 100 and 101
> separators, it takes a lot of space and computer memory (if you have to
> work with thousands of such cells at the same time).
>
> Sincerely, Kolomiets Pavel
> pavel220@list.ru
>