Re: [libreoffice-users] a LibO/Calc problem (SOLVED)

Thank you Paul and Brian
for your interesting answers and for your kindness to send them so soon. 
I have not checked them yet but I am sure that both of them give me what 
I was looking for.
I see thepros and cons in Paul's method: by splitting the calculation in 
details you can follow both the process and the reliability of the 
result, but at cost of space.
But, there is more fun in Brian's solution; I have always liked to 
create such complex formulas partly to be familiar with the spreadsheet 
program and it's functions but now a days mostly because it is very good 
exercise for an old man's brain --it is a challenge and then a good 
reason to award yourself with a drink when it finally works.
I think I was quite near Brian's solution, but something went wrong, so 
I had to shout for help.

Paul's answer is interesting from another point of view also -- he 
changes the decimal sign from a comma to a period and replaces the 
periods replaced by commas to get the "correct" thousand separator.
If you Google (e.g.) "decimal dot vs. comma" you will get a map that 
shows "How the world separates its decimals". We in Finland, like all 
European countries -- with the U.K. as the only exception -- use a comma 
as decimal sign.

Another page "Decimal and Thousand Separators" says: "Great Britain and 
the United States are two of the few places in the world that use a 
period to indicate the decimal place. Many other countries use a comma 
instead... Likewise, while the U.K. and U.S. use a comma to separate 
groups of thousands, many other countries use a period instead, and some 
countries separate thousands groups with a thin space."
Personally I have never understood the need or benefits of any thousand 
separator -- I have never used it neither private, in technical planning 
nor in (international)business.
Thanks again and
best regards
Pertti Rönnberg




On 12.1.2014 19:22, Paul wrote:
> I haven't so much tried to answer your question directly, instead I
> attempted to solve the same problem you did. For me, this gives a
> simpler answer. Given:
>
> Col A: The initial bank value as text
> Col B: =IF(RIGHT(A1,1)="-","Debit","Credit")
> Col C: =LEN(A1)
> Col D: =LEFT(A1,C1-1)
> Col E: =FIND(",",D1)
> Col F: =SUBSTITUTE(D1,".",",")
> Col G: =REPLACE(F1,E1,1,".")
> Col H: =VALUE(G1)
> Col I: =IF(B1="Debit", 0-H1, H1)
>
> Col A holds the initial value as given by your bank, as text.
>
> Col B then looks at the last character of that value, and displays
> either "Debit", if the character was a minus sign, or "Credit" for
> anything else.
>
> Col C gives the total length of the initial text value
>
> Col D gives the initial value stripped of the last character, the plus
> or minus sign.
>
> Col E gives us the position of the comma, so that we can turn it into a
> period later.
>
> Col F gives us the stripped value (from col D) with the periods
> replaced by commas, for correct thousand separators.
>
> Col G gives us the value from col F with the final comma changed to a
> period, for correct decimal separator. This text string should now be
> correctly formatted for interpreting as a number.
>
> Col H gives us the value from col G interpreted as a number.
>
> Col I gives us the final value we want. This is derived from col
> H, which has the initial value as a number, without the correct plus or
> minus sign, and we use the value in col B to determine if we should use
> the number as is, or subtract it from zero to get a negative number. I
> formatted this column as currency.
>
> I tested it with the following:
>
> "11.200,33+" ==> "R 11,200.3"
> "2.500.236,65-" ==> "R-2,500,236.65"
> "11,25+" ==> "R 11.25"
> "0,96-" ==> "R-0.96"
>
> If I understand you correctly, this is exactly what you want. No need
> to mess around with numbers under or over "9.999,99".
>
> Of course, you can combine some of those formulas to use less extra
> cells, but the more you combine stuff, the more complex it becomes, and
> I don't know where you'd like to draw the line for complexity of
> formulas vs complexity of page layout, so I leave that part up to you.
>
> Hope this helps you out.
>
> Paul
>
>
>
> On Sun, 12 Jan 2014 17:55:17 +0200
> Pertti Rönnberg <ptjr@elisanet.fi> wrote:
>
> > Happy New Year Dear LibO experts,
> >
> > Since years back I have copied my bank's digital listing of my bank
> > account(s); first using MSOWord and later on MSOExcel.
> > My intension is to transform these listings so I can calculate with
> > the currency values in LibO-Calc.
> > The bank's table has four cols: colA=date, colB&colC= text and colD
> > is the currency as text.
> > Each listing consists of several hundreds of events (rows).
> >
> > The problem is that the damn bank -- against all standards -- gives
> > the currency values with a dot (".") as thousand separator and "+" or
> > "-" chars (plus or minus) in the right end of each number (e.g.
> > "987,65+", "1.234,56-", "23.456,78+") -- which is against Calc's will.
> >
> > By now I have managed to get LibO/Calc to accept all values less than
> > 9.999,99:
> >   > first dragged (copied) the table from MSWord => MSExcel;
> >   > MSExcel-file
> > saved in LibO/Calc as "ods"
> >   > in Calc => function TRIM(D5) to get rid of non-printable chars
> >
> >   > formula (in E5) :
> > removes the separator/dot and the "+" sign and changes the value from
> > text to number
> > /=VALUE(IF(RIGHT($E4;1)="+";IF(MID($E4;2;1)=".";CONCATENATE(MID($E4;1;1);MID($E4;3;LEN($E4)-3));MID($E4;1;LEN($E4)-1));0))/
> >   > formula (in F5):
> > removes the separator/dot and the "-" sign and changes the value from
> > text to number
> > /=VALUE(IF(RIGHT(//E4//;1)="-";IF(MID(//E4//;2;1)=".";CONCATENATE(MID(//E4//;1;1);MID(//E4//;3;LEN(//E4//)-3));MID(//E4//;1;LEN(//E4//)-1));0))/
> >
> >   > copy(dragged) down the 100-300 rows
> >   > Copy(cols E&F) => PasteSpecial to colG:H gives the desired list of
> > incomes and expences separated in their own cols as acceptable numbers
> >
> > _Question:_
> > What formula/function gives the same result for the bank's currency
> > values bigger than 9.999,99?
> > e.g. "11.222,33+", "11.222.333,44-"
> > I have tried to define the "." using the Find() - but have had
> > trouble with it when nested in another function/formula (e.g. an IF()
> > function); the definition of it in Calc/Help is obviously at least
> > unclear if not wrong.
> > Cannot get the Fixed() -- when trying to eliminate the separator/dot
> > that way -- working in this 'project' either.
> >
> > Any advice and help is greatly appreciated.
> > Best regards
> > Pertti Rönnberg
> > computer: PC, win7prof/64bit; LO4.0.4.2


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted