Re: [libreoffice-users] Calc multiplication & division formulas acting strangely (Mac 5.2.3)

[bunk3m]

Thanks again Brian.  Appreciate that you take time to comment and help!

Some additional comments inline below.

On 7 November 2016 at 23:53, Brian Barker  wrote:

> At 14:20 07/11/2016 -0500, Bonly Noname wrote:
>
>> On 2016-11-06 8:43 PM, "Brian Barker"  wrote:
>>
>>> At 20:06 06/11/2016 -0500, Bonly Noname wrote:
>>>
 Today I created a spreadsheet in 5.2.3 (Mac) and each time I try to do
 a multiplication with a decimal, Calc gives me a #NAME?. In cell A1R1
 =4*1.5 gives #NAME? In each spreadsheet that I've opened I get the same
 problem. When I do 4*150%, Calc changes the formula to =4*1.5 and gives 6.
 Then I try =4/1.5 and I get #NAME?
 [...]
 What's going on here? What should I be looking into?

>>>
>>> This is exactly what you will see if your settings are for a locale
>>> which uses the comma instead of the point as the fractional separator (as
>>> many do). You can confirm this easily by entering (to modify your first
>>> example):
>>> =4*1,5
>>> - which should work.
>>>
>>> You need to tell Calc to expect the point as the fractional separator.
>>> Before installing any application software, select appropriate locale
>>> settings in your operating system. Only if necessary, modify the settings
>>> in the application. In LibreOffice, go to Tools | Options... | Language
>>> Settings | Languages and make appropriate selections there. (Oh, on the
>>> Mac, for "Tools | Options..." read "LibreOffice | Preferences".)
>>>
>>
>> Thank you Brian. I thought that was the issue, but I've tried =4*1, 5 and
>> it gives me Err:509.
>>
>
> Well, it will - since you've inserted that unhelpful space! We need the
> number one-and-a-half, not the two separate numbers one and five. The test
> is =4*1,5 - as I suggested. I'm sticking with my suggestion that this will
> work!
>

This was just an additional space typo due to autocorrect or thumb typing
on my tablet.


> I can open old Calc spreadsheets with decimal inputs and have no problem
>> until I try to enter new numbers.
>>
>
> That's also consistent with my suggestion. Formulae in existing documents
> are reinterpreted in the light of your new settings.
>
> I've noticed that if the formula is multiply by less than 1.0, the
>> calculation works.  As soon as I try any decimal greater than 1.0, I get
>> the #NAME?. =0.99 * 0.99 works =1.1*0.99 gives #NAME? =2*0.99 works =2.1*1
>> #NAME? Very weird.
>>
>
> I don't immediately see what is happening here, but it has to be said that
> there are very many places where locales and languages are set. It's
> difficult to test every possibility without messing up my system. Note, for
> example, that the cell formatting has its own language setting (at Format |
> Cells... | Numbers | Language), though that ought to affect only the
> display of results, not the interpretation of input.
>

It is confusing.  As a summary, any number entered that is a decimal less
than 1.0 (one) can be entered OK and calculates OK.  Any decimal number
greater than 1.0 gives the $NAME# error.

I didn't realize that there was another language setting in the cell
format.  I've checked and it is set to the default which is "Canada
English".  So it is consistent (exactly the same) between the Mac OSX
10.10.5 and the various places in LO.


>
> You might want to get someone to look at a sample document to see what is
> happening.
>
> Brian Barker
>

I'm happy to provide a sample document.  How would I go about finding
someone who would look at it?

Thanks again,
B.

-- 
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Help with LOCalc formula (SOLVED)

[Pertti Rönnberg]

Hi,
Bruce, Remy, Brian and Jim
Thank you for your kindness and answers.

Tonight while following the election on TV I'll have a lot to do when 
giving the VLOOKUP() a new try according to your advice.

I must have done bad thinking -- perhaps getting old.
Anyway I again learn a lot.
Best regards
Pertti


On 8.11.2016 11.58, James E Lang wrote:

Correction: replace 25 with 0.


--
Jim

-Original Message-
From: "James E. Lang" 
To: "users@global.libreoffice.org" , 
"Pertti Rönnberg" 

Cc: "Pertti Rönnberg" 
Sent: Tue, 08 Nov 2016 1:52
Subject: Re: [libreoffice-users] Help with LOCalc formula

Try this.

=IF(B1="","",OFFSET($Z$20,MATCH(B1,$B$20:$B$35,0)-1,25))

--
Jim

-Original Message-
From: "Pertti Rönnberg" >
To: "users@global.libreoffice.org 
" >

Cc: "Pertti Rönnberg" >
Sent: Mon, 07 Nov 2016 8:00
Subject: [libreoffice-users] Help with LOCalc formula

Best Spreadsheet Experts,
May I ask for help having a more elegant formula in LOCalc (v.5.0.3; 
win10)


Description
I try to get a little extra to my pension savings by experimenting on
the share market and follow up the results using Calc (not a database).
I have a spreadsheet with two ranges – range(1) directly above range(2).

r(1) specifies per row every specific purchase/sell event (date, company
name, amount, etc).
To calculate the actual "to-day"-result for this specific event (row) we
need the "to-day"-value for this company's share (in e.g. column X),
which value is taken from respective company's row in col.Z in r(2).
r(1) has yearly 100-130 rows (events)

Range(2) lists each company named in the above Range(1) and has in col.Z
the corresponding share's "to-day"-value. These inserted share values
varies from one day to the next.
There are now 10-15 companies (rows) listed in r(2) – not in alphabetic
order.
r(2) sums the up-to-date result per company and the total result.

Problem
The belowe example simplified to 5 events concerning 3 companies may
clearify the problem with the formula in X1 copied down col.X:
=if(B1=$B$20;$Z$20;if(B1=$B$21;$Z$21;(ifB1=$B$22;$Z$22;0)))

As you can see: with 15 (or more) companies (and >100 events) the
formula will grow and become very long and sensitive for mistakes.
VLOOKUP does not seem to be an option, at least it requires a completely
new setup of the page.

Range(1) - events
A   B C…. X
1. nokia   5,o
2. kone 41,o
3. nokia   5,o
4. fiskars 12,o
5. kone41,o

Range(2) - companies
A B C... Z
20 nokia   5,o
21 fiskars   12,o
22 kone 41,o

Any suggestion will be very interesting.
Thank you in advance
Pertti Rönnberg/Finland


--
To unsubscribe e-mail to: users+unsubscr...@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


--
To unsubscribe e-mail to: users+unsubscr...@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



--
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Help with LOCalc formula

[James E. Lang]

Try this.

=IF(B1="","",OFFSET($Z$20,MATCH(B1,$B$20:$B$35,0)-1,25))

-- 
Jim

-Original Message-
From: "Pertti Rönnberg" 
To: "users@global.libreoffice.org" 
Cc: "Pertti Rönnberg" 
Sent: Mon, 07 Nov 2016 8:00
Subject: [libreoffice-users] Help with LOCalc formula

Best Spreadsheet Experts,
May I ask for help having a more elegant formula in LOCalc (v.5.0.3; win10)

Description
I try to get a little extra to my pension savings by experimenting on 
the share market and follow up the results using Calc (not a database).
I have a spreadsheet with two ranges – range(1) directly above range(2).

r(1) specifies per row every specific purchase/sell event (date, company 
name, amount, etc).
To calculate the actual "to-day"-result for this specific event (row) we 
need the "to-day"-value for this company's share (in e.g. column X), 
which value is taken from respective company's row in col.Z in r(2).
r(1) has yearly 100-130 rows (events)

Range(2) lists each company named in the above Range(1) and has in col.Z 
the corresponding share's "to-day"-value. These inserted share values 
varies from one day to the next.
There are now 10-15 companies (rows) listed in r(2) – not in alphabetic 
order.
r(2) sums the up-to-date result per company and the total result.

Problem
The belowe example simplified to 5 events concerning 3 companies may 
clearify the problem with the formula in X1 copied down col.X:
=if(B1=$B$20;$Z$20;if(B1=$B$21;$Z$21;(ifB1=$B$22;$Z$22;0)))

As you can see: with 15 (or more) companies (and >100 events) the 
formula will grow and become very long and sensitive for mistakes.
VLOOKUP does not seem to be an option, at least it requires a completely 
new setup of the page.

Range(1) - events
A   B C…. X
1. nokia   5,o
2. kone 41,o
3. nokia   5,o
4. fiskars 12,o
5. kone41,o

Range(2) - companies
A B C... Z
20 nokia   5,o
21 fiskars   12,o
22 kone 41,o

Any suggestion will be very interesting.
Thank you in advance
Pertti Rönnberg/Finland


-- 
To unsubscribe e-mail to: users+unsubscr...@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

-- 
To unsubscribe e-mail to: users+unsubscr...@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