[libreoffice-users] PDF Creation

[Hylton Conacher (ZR1HPC)]

Hi,

Running LO 7.4.5.1 and am endeavouring to create a fillable PDF form 
using Writer. The file will be viewed using the free Adobe Reader software.


What I am battling with is the document saving the remote text that is 
entered so that when it is sent back to me via email as an attachment, 
whether the email sending be in Text or HTML, that the written text is 
not deleted.


Each time I open the PDF form on my side to view it and then close it, 
Adobe Reader always asks if I want to save changes, despite no changes 
being made.


I am endeavouring to find out from my remote users filling out the form 
but I wondered if there was a form control or other that needed to be 
set to save the inserted text on the form onto the form so that it and 
the saved text can be emailed.


What am I missing?

Regards
Hylton

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] INDEX MATCH to find MAX value?

[Hylton Conacher (ZR1HPC)]

Afternoon Remy,


Thank-you. I had thought of MAX but silly me had not gone further to 
investigate it's descendants. I had just thought that INDEX MATCH was 
THE lookup tool.


The formula has been amended for my sheet names and seems to be working 
well.


Now it seems that the data provided by the cloud provider has errors :(
The next email notifying them of this is on its way.

With the data being over 2.8Million records or 105120 rows and 27 
columns, time to move the data to Base.


Thanks again
Hylton

On 2022/12/19 15:37, Remy Gauthier wrote:

Greetings,
If you are looking for the maximum value, would the MAXIFS() function
work for you? If you are trying to populate the data in your secondary
sheet, with date values in column A, the formula to use in column B2 of
the secondary sheet would be:

=MAXIFS(Sheet1.M:M,Sheet1.C:C,"=")

Then just copy/paste this formula to the cells below.
I hope this helps.
Rémy.

Le dimanche 18 décembre 2022 à 21:18 +0200, Hylton Conacher (ZR1HPC) a
écrit :

Hi,

I have a RAW Data csv data sheet created which has several of the
same
date entries in column A and an increasing numerical value in another
column i.e.:
C   M
2022-01-01  0
2022-01-01  0
2022-01-01  0.1
2022-01-01  0.0
2022-01-02  0.0
2022-01-02  0.0
2022-01-03  0.5
2022-01-03  0.7

A secondary sheet has just a single date value and the maximum number
of
the other column i.e.:
2022-01-01 0.1
2022-01-02 0.0
2022-01-03 0.7
.
.

What I am needing help with, despite viewing multiple Index/Match
online
videos is a method to return the MAX value per date when there are
multiple instances of the search criteria result in 'M' still being
0.

Both 'date' fields are formatted as text.
Pointers appreciated.

I have not seen a tutorial online which covers a situation where
there
is repeating fields on the one hand but a maximum integer on the
other
column, although I am open to viewing one or possibly even creating a
tutorial.

Regards
Hylton







--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] INDEX MATCH to find MAX value?

[Hylton Conacher (ZR1HPC)]

Hi,

I have a RAW Data csv data sheet created which has several of the same 
date entries in column A and an increasing numerical value in another 
column i.e.:

C   M
2022-01-01  0
2022-01-01  0
2022-01-01  0.1
2022-01-01  0.0
2022-01-02  0.0
2022-01-02  0.0
2022-01-03  0.5
2022-01-03  0.7

A secondary sheet has just a single date value and the maximum number of 
the other column i.e.:

2022-01-01 0.1
2022-01-02 0.0
2022-01-03 0.7
.
.

What I am needing help with, despite viewing multiple Index/Match online 
videos is a method to return the MAX value per date when there are 
multiple instances of the search criteria result in 'M' still being 0.


Both 'date' fields are formatted as text.
Pointers appreciated.

I have not seen a tutorial online which covers a situation where there 
is repeating fields on the one hand but a maximum integer on the other 
column, although I am open to viewing one or possibly even creating a 
tutorial.


Regards
Hylton

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] [CLOSED] VLOOKUP Syntax to find MAX on a particular date

[Hylton Conacher (ZR1HPC)]

Dear fellow members,

My apologies for wasting your time and brain power, however the use of 
Calc and VLOOKUP is NOT the right tool for the job and that INDEX(MATCH) 
would have ben better, given that the downloaded .csv data file grows by 
approximately 1MB per month.


I have marked this thread as closed and shall be starting a new thread 
as I move the data file to Base. Expect the Base questions soon 0:)


Apologies again

Thank-you
Hylton

On 2022/08/08 21:25, Hylton Conacher (ZR1HPC) wrote:

Hi,

Using LO Calc 7.3.2.2 and I have a 28 column table RAW Data table in 
excess of 65k rows. A three column sample below:

Date    Simple Date    Outdoor Temp
2022-01-01T00:05:00+02:00    2022-01-01 00:05:00    17.5
2022-01-01T00:00:00+02:00    2022-01-01 00:00:00    17.4

I have a secondary table, called Amended RAW Data containing just the 10 
digit date and the corresponding value of the column value I am seeking, 
whether it be the Outdoor Temp, Daily Rainfall etc


What I need to do is query the following table, searching for a specific 
10 digit date, and find the max value on a specific date.


I have tried so many different itinerations on the VLOOKUP formula and 
browsed many elementary Google VLOOKUP pages that I need help. I have 
tried(all return N/A):


=MAX(VLOOKUP(LEFT($'RAW Data'.$B$2:$B$62037,10),$'RAW 
Data'.$B$2:$L$62037,1,FALSE))


=MAX(VLOOKUP($A11,$'RAW Data'.$B$2:$M$62037,11))

=MAX(VLOOKUP($A11,$'RAW Data'.$B$2:$M$62037,11))

=MAX(VLOOKUP(LEFT($'RAW Data'.$B$2:$B$62307,10)=$'Amended RAW 
Data'.$A$11,$'RAW Data'.$B$2:$L$62037,11,0))


References to 'Amended RAW Data'.$A$11 refer to a date that I know has 
greater than zero value, and the 11th column data I want to max, within 
a date.


Help appreciated
Hylton




--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] VLOOKUP Syntax to find MAX on a particular date

[Hylton Conacher (ZR1HPC)]

Hi Steve, et al,

On 2022/08/09 23:05, Steve Edmonds wrote:



...
Further to Johnny's question, how is the CSV delimited. Do you just have 
commas between values or are some values also quoted in " ".

There are no " "'s, all comma separated.

When I import a simple CSV using Sheet>Insert sheet from file I can set 
the simple date column as type date at import and using the MAXIFS 
solution proposed by Michael [ in my test 
=MAXIFS($C$2:$C$18,$B$2:$B$18,">=",$B2:$B$18,"<"&(G2+1)) ] works well 
in a test.


Steve, As a courtesy I have sent you a month's worth of data in a csv 
file downloaded from my Ambient Weather station cloud account. Try it, 
as by no means is it a simple one especially when all you want is the 
rainfall on each of the days of the month. into Base
My understanding is that VLOOKUP only returns 1 value, rather than a 
vector from which to determine a date.


I believe what I need to accomplish cannot be done with VLOOKUP and that 
a combination of INDEX(MATCH()) is a better solution especially given 
that the RAW Data file import is going to grow approximately 1MB per 
month, despite being just .csv type.


Moving on from this means that the whole file should preferably be 
manipulated in Base.


Sorry for wasting everyone's time, Base questions coming soon 0:)

Hylton

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] VLOOKUP Syntax to find MAX on a particular date

[Hylton Conacher (ZR1HPC)]

On 2022/08/09 14:53, Johnny Rosenberg wrote:



Den tis 9 aug. 2022 kl 12:26 skrev Hylton Conacher (ZR1HPC) 
mailto:hyl...@conacher.co.za>>:


Hi Johnny,

On 2022/08/08 22:19, Johnny Rosenberg wrote:
 > Den mån 8 aug. 2022 kl 21:47 skrev Hylton Conacher (ZR1HPC)
 > mailto:hyl...@conacher.co.za>
<mailto:hyl...@conacher.co.za <mailto:hyl...@conacher.co.za>>>:
 >
 >     Hi,
 >
 >     Using LO Calc 7.3.2.2 and I have a 28 column table RAW Data
table in
 >     excess of 65k rows. A three column sample below:
 >     Date                            Simple Date   
  Outdoor Temp

 >     2022-01-01T00:05:00+02:00       2022-01-01 00:05:00     17.5
 >     2022-01-01T00:00:00+02:00       2022-01-01 00:00:00     17.4
 >
 >
 > Are those dates numbers formatted as dates or just text?

The Date column is formatted as number and the simplified Date is
formatted as text.

Is VLOOKUP fussy about the format of the cells it must search? I
thought
it is just matching a string of characters?


It's not fussy about the format, but rather about the actual value in 
the cells, because if it's formatted as a date, it's really just a 
number (0.0 = 1899-12-30 00:00:00 and so on


The tab called Raw Data is actually a .csv import of an external file on 
which I have no ability to determine how the columns are formatted.
As time progresses so I will download another .csv file and overwrite 
the existing one with an updated file.


The bits that contain any formula will remain unchanged, apart from 
having their ranges extended due to the additional rows added.


But I wonder about the other column that is formatted as text. What do 
you mean by that?


I clicked on one of the cells in the Simple Date column and selected 
'Format->Cell' and it returned that is how it was formatted.


Tnx
Hylton

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] VLOOKUP Syntax to find MAX on a particular date

[Hylton Conacher (ZR1HPC)]

On 2022/08/08 23:55, Michael H wrote:
1. Vlookup can only return columns to the left of the column you search 
so you need to introduce the date into a leftward column (N2=B2, copy 
it, paste into column N, all rows)


All the examples I see on the net show that the left most column is the 
one used as the Search Criteria?


In my formulae all state the range from column B, being the left most 
column and include the columns to the right as the one who data I am 
seeking when a match is made in the left most column.



2. =vlookup(Max($rawdata.$B2:$M$62037),$rawdata.$b$2:$N63037,12,0)

SO NOW I HAVE A DATE IN THE LEFT MOST AND RIGHT MOST COLUMN?

??

Hylton

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] VLOOKUP Syntax to find MAX on a particular date

[Hylton Conacher (ZR1HPC)]

Hi Johnny,

On 2022/08/08 22:19, Johnny Rosenberg wrote:
Den mån 8 aug. 2022 kl 21:47 skrev Hylton Conacher (ZR1HPC) 
mailto:hyl...@conacher.co.za>>:


Hi,

Using LO Calc 7.3.2.2 and I have a 28 column table RAW Data table in
excess of 65k rows. A three column sample below:
Date                            Simple Date             Outdoor Temp
2022-01-01T00:05:00+02:00       2022-01-01 00:05:00     17.5
2022-01-01T00:00:00+02:00       2022-01-01 00:00:00     17.4


Are those dates numbers formatted as dates or just text?


The Date column is formatted as number and the simplified Date is 
formatted as text.


Is VLOOKUP fussy about the format of the cells it must search? I thought 
it is just matching a string of characters?


Tried formatting column B in Raw Data as both Number and Text, with no 
change in the formulae answers.


Hylton

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] VLOOKUP Syntax to find MAX on a particular date

[Hylton Conacher (ZR1HPC)]

Hi,

Using LO Calc 7.3.2.2 and I have a 28 column table RAW Data table in 
excess of 65k rows. A three column sample below:

DateSimple Date Outdoor Temp
2022-01-01T00:05:00+02:00   2022-01-01 00:05:00 17.5
2022-01-01T00:00:00+02:00   2022-01-01 00:00:00 17.4

I have a secondary table, called Amended RAW Data containing just the 10 
digit date and the corresponding value of the column value I am seeking, 
whether it be the Outdoor Temp, Daily Rainfall etc


What I need to do is query the following table, searching for a specific 
10 digit date, and find the max value on a specific date.


I have tried so many different itinerations on the VLOOKUP formula and 
browsed many elementary Google VLOOKUP pages that I need help. I have 
tried(all return N/A):


=MAX(VLOOKUP(LEFT($'RAW Data'.$B$2:$B$62037,10),$'RAW 
Data'.$B$2:$L$62037,1,FALSE))


=MAX(VLOOKUP($A11,$'RAW Data'.$B$2:$M$62037,11))

=MAX(VLOOKUP($A11,$'RAW Data'.$B$2:$M$62037,11))

=MAX(VLOOKUP(LEFT($'RAW Data'.$B$2:$B$62307,10)=$'Amended RAW 
Data'.$A$11,$'RAW Data'.$B$2:$L$62037,11,0))


References to 'Amended RAW Data'.$A$11 refer to a date that I know has 
greater than zero value, and the 11th column data I want to max, within 
a date.


Help appreciated
Hylton

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] SUM, SUMIF, IF, INDEX, MATCH, VLOOKUP?

[Hylton Conacher (ZR1HPC)]

On 2021/12/15 21:11, Hylton Conacher (ZR1HPC) wrote:

Hi,

I am quickly losing patience with a task that requires a sum of values 
that occur on the same date, but at different times.


My raw data sheet is in 4 columns
Date/Time | Value 1| Value Y| Value z
2021-10-08 08:00:00| 5| 7| 0.15
2021-10-08 14:05:16| 3| 10| 5
2021-10-09 10:05:30| 15| 3| 25
2021-10-09 18:00:00| 15| 9| 6


After doing much reading of examples I thought the below formula would 
work, alas it does NOT WORK. Where is it wrong?


I copied the data I gave above onto a blank spreadsheet, formatted the 
first column as date YYY-MM-DD HH:MM:SS. I entered the below formula in 
column G1


=SUM(INDEX($A$2:$D$5,0,MATCH(A2,$B1:$D1,0)))

The MATCH i.e. A2 is proving troublesome as I have tried the following:
1. INT(A2)  formula result #N/A
2. 2021-10-8 The formula will not accept 08  formula result #N/A
3. LEFT(A2) although this gives me a function result of "44477." and 
formula result #N/A


So how the _ _ _ _ do you sum values for a particular column for 
multiple occurrences of the same date as above oh learned friends?


--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] SUM, SUMIF, IF, INDEX, MATCH, VLOOKUP?

[Hylton Conacher (ZR1HPC)]

On 2021/12/15 23:45, Michael D. Setzer II wrote:

On 15 Dec 2021 at 21:11, Hylton Conacher (ZR1HPC)
wrote:

Date sent:  Wed, 15 Dec 2021 21:11:55 +0200
To: LibreOffice Users

From:   "Hylton Conacher (ZR1HPC)"

Subject:[libreoffice-users] SUM, SUMIF, IF, INDEX,
MATCH, VLOOKUP?


Hi,

I am quickly losing patience with a task that requires a sum of values
that occur on the same date, but at different times.

My raw data sheet is in 4 columns
Date/Time | Value 1| Value Y| Value z
2021-10-08 08:00:00| 5| 7| 0.15
2021-10-08 14:05:16| 3| 10| 5
2021-10-09 10:05:30| 15| 3| 25
2021-10-09 18:00:00| 15| 9| 6


Don't know if this is what you are wanting, but I did this.
Copied the above data into a sheet using shift-ctrl-V use |
as delimiter. That date and time is pasted as a date/time
value versus a text string.


To Clarify: There are multiple duplicate dates in the first column of 
Sheet 1. the only difference being the adjacent time in the same field.


What is needed is to SUM all values for a particular date, regardless of 
time, either in column B, C, D.




Added a 5th column labled as Date Only and put formula
under it. =int(a2) and copied for the 4 data elements.


Thank you to you and Remy, the first formula on Sheet 2.A1 to strip the 
time function is INT(Sheet1.a1)





Then highlighted the 5 rows of Data.
Data/Subtotals
Group By "Date Only"
Under Calc Subtotals for: Checked value Y and Date Only
For value Y use SUM function
For Date Only used AVG function
Then OK.
That has it insert rows for each group with the totals for
each day. It also shows a final total of everything that
could be ignored.


I would prefer not to have to use the sort function. The main issue is 
amalgamating multiple values on duplicate dates i.e. sum all results in 
a column B/C/D regardless that there are multiple values are sorted by 
date and time.


I am happy to learn about the creation of the date in Sheet 2 from Sheet 
1 using INT.


If the base formulae are there to calculate what is needed, creating a 
Macro, I think, is overkill.


Posible extra step if you just want the subtotal data.
In cells F2 and G2 put the formulas
=if(a2=0,e2,"")  That gets date only for subtotal lines
=if(a2=0,c2,"")  That gets value y for subtotal lines.
Copy F2:g2 down to F7: (don't need row 8 for final total).
Then highlight F2:G7 use Ctrl-C
Go to H2 - Do ctrl-shift-V and paste text number date/time
The Click Data - Sort ascending.
That gives just the subtotal lines with date and total of
day?

Might be a better way to do it, seems to work. Perhaps a
macro to automate the whole thing.


--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] SUM, SUMIF, IF, INDEX, MATCH, VLOOKUP?

[Hylton Conacher (ZR1HPC)]

On 2021/12/15 23:29, John Kaufmann wrote:
> On 2021-12-15 14:11, Hylton Conacher (ZR1HPC) wrote:
>> ...
>> I am quickly losing patience with a task that requires a sum of 
values that occur on the same date, but at different times.

>>
>> My raw data sheet is in 4 columns [spaced for comprehension]
>>  Date/Time [stamp] | Value 1| Value Y| Value z
>> 2021-10-08 08:00:00|5   |7   |   0.15
>> 2021-10-08 14:05:16|3   |   10   |   5
>> 2021-10-09 10:05:30|   15   |3   |  25
>> 2021-10-09 18:00:00|   15   |9   |   6
>> ...
>> ..
>> .
>>
>> I have a secondary sheet with just the date i.e. 2010-10-08, 
acquired with formula `=LEFT($Data.A4,10)'. Column B of this sheet needs 
to be the SUM of all the numbers in the 3rd column, BUT only for the 
date in column A i.e.

>>
>> 2010-10-08| 17
>> 2010-10-09| 12
>> ...
>> ..
>> .
>
> Just trying to understand your question properly, so for clarification:
>   (1a) Your two sheets differ in column A, in that the first has a 
date/time stamp, the second date only (extracted from the first)?


Correct

>   (1b) Does each row of the second sheet reflect the date of the 
corresponding row in the first sheet (in other words, dates are repeated 
on the second sheet, as suggested by your formula ($Data.A4, $Data.A5, 
$Data.A6, ...), or is each date on the second sheet listed just once?


No, the second sheet merely has a single entry per date. I basically 
used LEFT(1st sheet date/time column, 10) then manually typed the 
following date in the cell below, clicked on bottom right of cell and 
dragged down to current date.


>   (2) We don't need to worry about columns B or D of the first sheet, 
just column C? [Any significance to columns B-C-D being indexed in three 
different formats (Numeric-UPPERCASE-lowercase)?]


Yes, do worry about columns B, D. I would like to do something similar 
to C, but figured I might try and bastardise the formula for C, for B +D.



>   (3a) Did you try confirming that conjecture using a test example 
with just the date, not a date/time stamp?


Yes

>   (3b) How is that date/time field acquired? Could it be 
acquired/input as two separate fields, date and time?

It is a combined field downloaded in a .csv file.



>   (4) [This is related to questions 1 and 3] Given that you acquire 
the date for your second sheet's column A as a text field, is there a 
possible data type mismatch?

The date column in both sheets is formatted as a date column
 Sheet 1: -mm-dd hh:mm:ss
 Sheet 2: -mm-dd


--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] SUM, SUMIF, IF, INDEX, MATCH, VLOOKUP?

[Hylton Conacher (ZR1HPC)]

Hi,

I am quickly losing patience with a task that requires a sum of values 
that occur on the same date, but at different times.


My raw data sheet is in 4 columns
Date/Time | Value 1| Value Y| Value z
2021-10-08 08:00:00| 5| 7| 0.15
2021-10-08 14:05:16| 3| 10| 5
2021-10-09 10:05:30| 15| 3| 25
2021-10-09 18:00:00| 15| 9| 6
...
..
.

I have a secondary sheet with just the date i.e. 2010-10-08, acquired 
with formula `=LEFT($Data.A4,10)'. Column B of this sheet needs to be 
the SUM of all the numbers in the 3rd column, BUT only for the date in 
column A i.e.


2010-10-08| 17
2010-10-09| 12
...
..
.

I have tried many Google searches and seen many videos on how this is 
supposed to work yet for some reason, being race, colour, creed, or 
stupidity, I cannot get the result I seek. It would seem that the 
summation of data with the same date, but different time component in 
the main data sheet is causing an issue.


I'd really appreciate some pointers here, even if not possible. If not 
possible what could be done. I just don't understand the 
INDEX(MATCH())/vlookup  argument.


--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc airithmetic snafu

[Hylton Conacher (ZR1HPC)]

HI ANDREW,

On 2021/11/26 16:38, Andrew Pitonyak wrote:
Left will return a string so convert to a number using value... 
Something like this


=Value(left())



THANK YOU Works as expected,now on to the next formula Challenge


Get BlueMail for Android <https://bluemail.me>
On Nov 26, 2021, at 9:12 AM, "Hylton Conacher (ZR1HPC)" 
mailto:hyl...@conacher.co.za>> wrote:


Hi,

Using LO7.2.2.2  <http://7.2.2.2>  Calc on iMac 11.6.1(Big Sur).

I have a data column that ends in alphabetic letters i.e.

    C      |  D
2| 2.4 km|    2.4
3| 4.8 km|    4.8

As the ' km' can't be added in column C, I used the LEFT formula to only
extract the first 3 digits, namely =LEFT(C2,3). This works wonderfully
in column D, BUT whilst not shown on the spreadsheet, the numbers are
shown in-between double quotes if one looks at the formula, in column D,
with the Function Wizard, which again does not enable the number to be
added, averaged etc.

Both C, D columns are formatted as Number with a single decimal point
and there will be no -ve number.

What am I missing, and where to rectify it?

Regards
Hylton




--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] Calc airithmetic snafu

[Hylton Conacher (ZR1HPC)]

Hi,

Using LO 7.2.2.2 Calc on iMac 11.6.1(Big Sur).

I have a data column that ends in alphabetic letters i.e.

  C      |  D
2| 2.4 km|    2.4
3| 4.8 km|    4.8

As the ' km' can't be added in column C, I used the LEFT formula to only 
extract the first 3 digits, namely =LEFT(C2,3). This works wonderfully 
in column D, BUT whilst not shown on the spreadsheet, the numbers are 
shown in-between double quotes if one looks at the formula, in column D, 
with the Function Wizard, which again does not enable the number to be 
added, averaged etc.


Both C, D columns are formatted as Number with a single decimal point 
and there will be no -ve number.


What am I missing, and where to rectify it?

Regards
Hylton

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Updating Data in Calc

[Hylton Conacher (ZR1HPC)]

On 2021/06/15 11:28, Dave Howorth wrote:

On Mon, 14 Jun 2021 21:35:49 +0200
"Hylton Conacher (ZR1HPC)"  wrote:


It has often puzzled me why when knowledgeable members of mailing
lists such as this, find fault in what someone, less qualified than
themselves, suggests. It is not taken via private mail but splashed
on the mailing list.


Don't take it personally. It's just a quirk of list netiquette


List netiquette quirk noted, apologies to Brian.

Hylton

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Updating Data in Calc

[Hylton Conacher (ZR1HPC)]

Dear Brian,

On 2021/06/13 18:52, Brian Barker wrote:

At 15:01 13/06/2021 +0200, Hylton Conacher wrote:

A simple sum will do it i.e.: In cell Y4 insert [...]
=SUM(M18-$W$4)


I'm often puzzled by suggestions such as this. In what way do you think 
that =SUM(Xm-Yn) differs from =Xm-Yn? The minus sign effects the 
subtraction between two values, generating a single value as its result. 
You then pass this single value to the SUM() function. What do you 
expect that function do with a single value? The sum of two and two is 
four; what do you think is the sum of just two?


I too was puzzled by the OP post however I assumed that it was the 
function that the OP was seeking, consequently my answer included the 
SUM option. The two formulae above do indeed calculate the same thing, 
but as you can see from the OP reply, he perhaps wanted  a more 
Index(Match()) query.


I will leave the explanation of the different parts of whatever function 
he chooses to someone who has knowledge of such.


It has often puzzled me why when knowledgeable members of mailing lists 
such as this, find fault in what someone, less qualified than 
themselves, suggests. It is not taken via private mail but splashed on 
the mailing list.


I have been very thankful for your help in years past, and despite an 
endeavour to understand the Index/Match formula I am still battling with 
what cell selections go where in the formula. Perhaps in showing the OP 
verbosely you will allow both him, I and perhaps a few others also 
understand the formula, if you think it is the one needed.


I, and many others, look forward to your continued support.

Warmest Regards
Hylton

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Updating Data in Calc

[Hylton Conacher (ZR1HPC)]

Hi Vince,

On 2021/06/12 17:20, Vince@Verizon wrote:

Hello:

Using LO vers 7.1.3.2 (x64) on desktop Windows 10 Home (64-bit).

I would greatly receiving an explanation of a formula or function in 
Calc that updates the data within cell $Y$4 each time the data in column 
[M] changes.  The formula in $Y$4 =$M18-$W$4.


[M]                                        [Y]

Row 18 -->  Row 4
Row 19 -->  Row 4
Row 20 -->  Row 4
Row 21 -->  Row 4
Row 21 -->  Row 4


 Where is column W in your layout? The below is done on assumption.

A simple sum will do it i.e.:

In cell Y4 insert everything between the "

"=SUM(M18-$W$4)"

In cell Y5

"=SUM(M19-$W$4)"

In theory you could just drag the formula from cell Y4 down to the 
bottom and then just change those M reference cell numbers.


Remember that putting a $ before either a letter or number reference of 
a cell LOCKS that formula i.e. dragging the formula down a column to 
populate cells below will keep whatever is prefixed with $.


Hope it helps
Hylton

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc Formula confusion an understanding needed Index/Match

[Hylton Conacher (ZR1HPC)]

Bravo Remy,

On 2021/05/29 20:29, Remy Gauthier wrote:

Hello,

I created something that ressembles what Johnny created, and I 
understand your data is formatted like so:


Data1 Data2 blank Data3 Data4 blank Data5 Data6

You want Data6 to be the maximum of Data2 and Data4 (and possibly more 
columns as well), and you want to have Data5 equal to the date on which 
the maximum occurred.


As Johnny indicated, the formula to place in Data6 is =MAX(Bx;Ex) where 
x is the row number.


If you want to use MATCH to find the date, then you shoud proceed like so:

The MATCH() function takes 3 arguments:
- Value to search
- Array where to search
- How to search
This function will return where the value you are looking for is located 
in the search array. The "how to search" argument tells the function if 
the values in the array are sorted (1 or -1) or not (0). In your case, 
the values are not sorted so you will need to use 0. Note that if you 
use 1 or -1, the search will return the closest match,and will not fail 
if the value you are looking for does not exist in your search array; if 
you use 0, however, the search will fail if your value is not in the 
array. I will usually always use zero (exact match) regardless of how 
the data is sorted. The only time I will make use of the 1 or -1 values 
is when I need to interpolate in a series of data points and I need to 
find where the interpolation will take place in the dataset.


The first argument will be Data6. The second argument will be Ax:Ex, 
where x is the row number. You can use the entire row like this because:

- Date values will always be greater than the rain values you have
- Empty cells do not count
The third argument will be zero since you want an exact match in an 
array that is not sorted.


The output of the MATCH() function will be the column number of the 
maximum (since the first cell of the search range is Ax). The date is 1 
to the left: one column less, so MATCH()-1 will give the column where we 
can find the date.


To extract the date with INDEX(), you must use the same range as used to 
MATCH() the value. The arguments are:

- Cell range
- Row in the range (1 if you only select one row of data)
- Column in the range

The cell range will be Ax:Ex (exactly what was used in the MATCH() 
function), the row will be 1 (only 1 row of data), and the column will 
be the result of the MATCH() we did minus 1. This means the formula in 
Gx will be:


=INDEX(Ax:Ex;1;MATCH(Hx;Ax:Ex;0)-1)

I hope this helps.
Rémy.


Works like a charm.  I mostly understood your explanation too. and will 
keep reviewing it until it sinks in


I copy and pasted your formula and then 'reinvented' it to cover the 
correct ranges and lines in the Data sheet.


Now the job is to incorporate the year into the date.

Regards
Hylton

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc Formula confusion an understanding needed Index/Match

[Hylton Conacher (ZR1HPC)]

On 2021/05/29 23:36, Johnny Rosenberg wrote:

Den lör 29 maj 2021 kl 21:58 skrev Hylton Conacher (ZR1HPC) <
hyl...@conacher.co.za>:


Hi Johnny,

On 2021/05/28 23:44, Johnny Rosenberg wrote:

Den fre 28 maj 2021 kl 22:49 skrev Hylton Conacher (ZR1HPC) <
hyl...@conacher.co.za>:


Hi,

I have LO 7.0.6.2 and am battling with understanding which formula to
use as well as the syntax for that formula.

I am aware of the availability of vlookup, hlookup, Index/Match formula
and have settled I think on the right one i.e. Index/Match

Below is a portion of my spreadsheet that is divided as below with a
blank column between each year. What I want to calculate is the date the
Max rain occurred. I am OK with the formula to obtain the MAX but I need
help in constructing a formula to get the corresponding date.

I had though the best would be Index and Match but no matter how I enter
it I cannot get the date listed under the Date column of 2020 or 2021,
never mind actually retrieving the year from the same column as the date
the originated.

=INDEX(a1:h14;MATCH(h3;a3:h3;0)) just gives me 01/01 for the first date
under Highest Monthly



Strange. I give a completely different result, so obviously my

spreadsheet

isn't identical to yours.
In which cell did you put this formula and what result did you expect?



I have looked aver tutorial and their mothers trying to find out what
ranges to insert into the Index(Match()) formula with ZERO success.

I am manually entering the dates under each year when the max value is
revealed by my formula. Would love to have it automated but my entire
spreadsheet covers over 400 rows and more than 52 columns resulting in
26 tabs of graphs from the Data sheet.

Is my data in the wrong order i.e. should the rainfall value column be
before the Date it occurred?

I do not understand what ranges need to consist of when using

Index/Match.


Can someone point me to a decent tutorial explaining the different terms
i.e. Reference, Row, Column, Range, Search Criterion, Lookup array.

20202021Highest Monthly
Date2020 Rain   Date2021 Rain   DateRain
01/19   9,5 01/15   3   2020/01/19  9,5
02/16   1,5 02/14   3,5 2021/02/14  3,5
03/25   3,5 03/14   19  2021/03/14  19
04/11   20  04/26   7   2020/04/11  20
05/28   27,505/20   43  2021/05/20  43
06/11   26  #N/A0   0
07/09   85,5#N/A0   0
08/28   35  #N/A0   0
09/02   21  #N/A0   0
10/28   15  #N/A0   0
11/06   25  #N/A0   0
12/26   2   #N/A0   0

If you want the entire spreadsheet it is available on direct request,
but ultimately I would like to understand how it works.

This will at least enable to use the formula successfully on newer
versions on LO.

Regards
Hylton



I'm not sure what you are trying to do here, so I'll just guess. Just
ignore me if I'm totally wrong.
So first, I tried to create a spreadsheet following the text above, so

this

is what my spreadsheet looks like:
Row 1 and two are just headers.
Column A is dates for 2020 formatted as Month/Day.
Column B is amount of rain in some unit, not sure which one, so I assume
mm, since that's what we use where I live, and it doesn't matter for this
question anyway.
Column C is empty.
Column D is dates for 2021 formatted as Month/Day.
Column E is the amount of rain for 2021.
Column F is empty.
Column G is the column that contains the dates for each month for the
respective year with the highest amount of rain, and it's also the column
that you wish to automate, is that right?
Column H is the highest value of rain in columns B and E for each row.

Right so far?

If so, you want column G to display the date for each maximum value in

the

B and E column per row, right? Column H  is already figured out, so for
instance, H3 contains the following:
=MAX(B3;E3)

Then you want to automatically display the datum of which this occured,

or

just the year? Well, date or year is only a question about formatting, so
let's just leave it to be formatted later. I guess you know how to do

cell

formats and styles anyway.
maybe I totally misunderstood the question, but if not, you don't need
neither INDEX nor MATCH for this. Here's my cell formula in G3, for
instance:
=IF(B3>E3;A3;D3) <
So it there was more rain in 2020 than in 2021, display the date in

column

A, otherwise display the date in column D.
If you want to fill further down to future dates and only show the values
when the rest of the row is completely entered, you could add another

test,

li

Re: [libreoffice-users] Calc Formula confusion an understanding needed Index/Match

[Hylton Conacher (ZR1HPC)]

Hi Johnny,

On 2021/05/28 23:44, Johnny Rosenberg wrote:

Den fre 28 maj 2021 kl 22:49 skrev Hylton Conacher (ZR1HPC) <
hyl...@conacher.co.za>:


Hi,

I have LO 7.0.6.2 and am battling with understanding which formula to
use as well as the syntax for that formula.

I am aware of the availability of vlookup, hlookup, Index/Match formula
and have settled I think on the right one i.e. Index/Match

Below is a portion of my spreadsheet that is divided as below with a
blank column between each year. What I want to calculate is the date the
Max rain occurred. I am OK with the formula to obtain the MAX but I need
help in constructing a formula to get the corresponding date.

I had though the best would be Index and Match but no matter how I enter
it I cannot get the date listed under the Date column of 2020 or 2021,
never mind actually retrieving the year from the same column as the date
the originated.

=INDEX(a1:h14;MATCH(h3;a3:h3;0)) just gives me 01/01 for the first date
under Highest Monthly



Strange. I give a completely different result, so obviously my spreadsheet
isn't identical to yours.
In which cell did you put this formula and what result did you expect?



I have looked aver tutorial and their mothers trying to find out what
ranges to insert into the Index(Match()) formula with ZERO success.

I am manually entering the dates under each year when the max value is
revealed by my formula. Would love to have it automated but my entire
spreadsheet covers over 400 rows and more than 52 columns resulting in
26 tabs of graphs from the Data sheet.

Is my data in the wrong order i.e. should the rainfall value column be
before the Date it occurred?

I do not understand what ranges need to consist of when using Index/Match.

Can someone point me to a decent tutorial explaining the different terms
i.e. Reference, Row, Column, Range, Search Criterion, Lookup array.

20202021Highest Monthly
Date2020 Rain   Date2021 Rain   DateRain
01/19   9,5 01/15   3   2020/01/19  9,5
02/16   1,5 02/14   3,5 2021/02/14  3,5
03/25   3,5 03/14   19  2021/03/14  19
04/11   20  04/26   7   2020/04/11  20
05/28   27,505/20   43  2021/05/20  43
06/11   26  #N/A0   0
07/09   85,5#N/A0   0
08/28   35  #N/A0   0
09/02   21  #N/A0   0
10/28   15  #N/A0   0
11/06   25  #N/A0   0
12/26   2   #N/A0   0

If you want the entire spreadsheet it is available on direct request,
but ultimately I would like to understand how it works.

This will at least enable to use the formula successfully on newer
versions on LO.

Regards
Hylton



I'm not sure what you are trying to do here, so I'll just guess. Just
ignore me if I'm totally wrong.
So first, I tried to create a spreadsheet following the text above, so this
is what my spreadsheet looks like:
Row 1 and two are just headers.
Column A is dates for 2020 formatted as Month/Day.
Column B is amount of rain in some unit, not sure which one, so I assume
mm, since that's what we use where I live, and it doesn't matter for this
question anyway.
Column C is empty.
Column D is dates for 2021 formatted as Month/Day.
Column E is the amount of rain for 2021.
Column F is empty.
Column G is the column that contains the dates for each month for the
respective year with the highest amount of rain, and it's also the column
that you wish to automate, is that right?
Column H is the highest value of rain in columns B and E for each row.

Right so far?

If so, you want column G to display the date for each maximum value in the
B and E column per row, right? Column H  is already figured out, so for
instance, H3 contains the following:
=MAX(B3;E3)

Then you want to automatically display the datum of which this occured, or
just the year? Well, date or year is only a question about formatting, so
let's just leave it to be formatted later. I guess you know how to do cell
formats and styles anyway.
maybe I totally misunderstood the question, but if not, you don't need
neither INDEX nor MATCH for this. Here's my cell formula in G3, for
instance:
=IF(B3>E3;A3;D3)
So it there was more rain in 2020 than in 2021, display the date in column
A, otherwise display the date in column D.
If you want to fill further down to future dates and only show the values
when the rest of the row is completely entered, you could add another test,
like this:
=IF(OR(A3="";B3="";D3="";E3="");"";IF(B3>E3;A3;D3))
Then you could just fill down and only the relevant cells will display
s

[libreoffice-users] Calc Formula confusion an understanding needed Index/Match

[Hylton Conacher (ZR1HPC)]

Hi,

I have LO 7.0.6.2 and am battling with understanding which formula to 
use as well as the syntax for that formula.


I am aware of the availability of vlookup, hlookup, Index/Match formula 
and have settled I think on the right one i.e. Index/Match


Below is a portion of my spreadsheet that is divided as below with a 
blank column between each year. What I want to calculate is the date the 
Max rain occurred. I am OK with the formula to obtain the MAX but I need 
help in constructing a formula to get the corresponding date.


I had though the best would be Index and Match but no matter how I enter 
it I cannot get the date listed under the Date column of 2020 or 2021, 
never mind actually retrieving the year from the same column as the date 
the originated.


=INDEX(a1:h14;MATCH(h3;a3:h3;0)) just gives me 01/01 for the first date 
under Highest Monthly


I have looked aver tutorial and their mothers trying to find out what 
ranges to insert into the Index(Match()) formula with ZERO success.


I am manually entering the dates under each year when the max value is 
revealed by my formula. Would love to have it automated but my entire 
spreadsheet covers over 400 rows and more than 52 columns resulting in 
26 tabs of graphs from the Data sheet.


Is my data in the wrong order i.e. should the rainfall value column be 
before the Date it occurred?


I do not understand what ranges need to consist of when using Index/Match.

Can someone point me to a decent tutorial explaining the different terms 
i.e. Reference, Row, Column, Range, Search Criterion, Lookup array.


20202021Highest Monthly 
Date2020 Rain   Date2021 Rain   DateRain
01/19   9,5 01/15   3   2020/01/19  9,5
02/16   1,5 02/14   3,5 2021/02/14  3,5
03/25   3,5 03/14   19  2021/03/14  19
04/11   20  04/26   7   2020/04/11  20
05/28   27,505/20   43  2021/05/20  43
06/11   26  #N/A0   0
07/09   85,5#N/A0   0
08/28   35  #N/A0   0
09/02   21  #N/A0   0
10/28   15  #N/A0   0
11/06   25  #N/A0   0
12/26   2   #N/A0   0

If you want the entire spreadsheet it is available on direct request, 
but ultimately I would like to understand how it works.


This will at least enable to use the formula successfully on newer 
versions on LO.


Regards
Hylton

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] LO 6.4.7.2 Calc: Where is a cell referenced elsewhere in s file?

[Hylton Conacher (ZR1HPC)]

Hi,

I have a 134 column spreadsheet I am tidying up. An issue has arisen 
where if I delete a cell's content I do not know what other cell is 
referencing the deleted cell's content?


How can I determine this or should I raise a feature enhancement and if 
so where?


Regards
Hylton
I am a subscriber to the users list.

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] Con50: Quarantine errors

[Hylton Conacher (ZR1HPC)]

Hi Lizel,

Please add the following addresses onto the back-end of FortiMail.








Thanks
Hylton

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] LO 6.4.6.2 CALC : Appropriate formula?

[Hylton Conacher (ZR1HPC)]

HI,

USING

I have eight data values on a single row with corresponding dates next 
to each value in the format below i.e.


A   B   C   D   E   F   G   H
25  01/07   blank   60  09/05   blank   30  26/7

In the 2nd row I would like to show the highest number from the first 
row as well as the corresponding date value in the cell to the right of 
that value.


My A2 formula is MAX(A2,D2) = 60

The issue I am having is to get the corresponding date value from the 
adjacent column i.e. 09/05 in row B2.


The formula I have tried include:
=VLOOKUP(MAX(A1,D1,G1),B1:H1,FALSE) = #N/A
=MATCH(INDEX(A2),A1:H1,0) = #N/A
=HLOOKUP(MAX(A1,D1,G1),A1:H1,1,0)  =60 BEING THE SAME VALUE AS MAX().

I figured that as VLOOKUP looks for values below that HLOOKUP might be 
better suited. That said I have always battled understanding the 
terminology for the requirements for both vlookup and hlookup, even 
Index and Match needs understanding I don't have.


I read the LO manual online and Google has been some help but seems to 
always go the VLOOKUP route with all the data under each other. 
Unfortunately this sort of 'table' VLOOKUP requires is not feasible on 
my data set.


I should probably use BASE but have not delved into that especially as 
the data making up these figures is graphed.


Anyone's guidance appreciated.

Regards
Hylton
mailto:hyl...@conacher.co.za
P.S.: I am subscribed to the LO-Users mailing list


--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] [SOLVED] Re: Calc 6.0.6.2 IF criteria statement help

[Hylton Conacher (ZR1HPC)]

Hi Remy,

On 2018/11/13 01:22, remygauthier wrote:

Hi,
Unclaimed_slips.ods

I went back to the original post and downloaded the file you had uploaded to
Google Drive. I made what I think were the correct mods and the results on
the Summary sheet are consistent with what has been entered. I have attached
the modified file, so you should be able to download it and have a look (you
will probably need to access the post via the nabble site -
nabble.documentfoundation.org/). Once we know what is wrong with the
changes, it should be easier to provide information.

I hope this helps.


I have reviewed your file on Nabble and after reviewing and inserting 
the formula into the current file, am pleased to announce that the issue 
is SOLVED! :)


THANKYOU

Looking at the formula 
<=SUMIFS(Data.C$3:Data.C$1000,Data.$A$3:Data.$A$1000,CONCATENATE("=",$A3 
,"*"),Data.$F$3:Data.$F$1000,CONCATENATE("<>","Y"))>, I see how the 
Concatenate was used almost like an IF statement criteria, and could be 
stated as 'CONCATENATE("=",Summary.$A3 ,"*")'. However I do not 
understand the second CONCATENATE as I am looking for those with an "N" 
i.e. ones that have not been reconciled yet the SUMIFS yields the 
correct value?


Concatenate joins/amalgamates values from different cells or text i.e. 
as a Linux command $>cat filename would display the contents of the 
filename. My understanding is that a similar command of $>cat bee hive 
would result in the word beehive being displayed.


In the formula you state to SUM values in Data.c3:Data.c1000 if date 
value of column A equals a partial match of the actual Data column 
holding the date AND where the value is blank or Y, despite you only 
wanting to SUM the N values?


Basically the formula works and I am not complaining and very grateful 
but I would like to better understand the formula, so that I can use it 
in future.


I hope my explanation is sufficient.

Regards
Hylton

P.S.: To ALL owners of a Debit or Credit Card, establish a spreadsheet 
using the above formula, enter your transactions on the Data tab, 
reconcile them according to your bank statement and see what value of 
goods you are getting for FREE as sometimes it pays to shop at certain 
retailers, even if it is only occasional.


--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc 6.0.6.2 IF criteria statement help

[Hylton Conacher (ZR1HPC)]

My apologies Remy et al,

You are correct regarding the earlier post and despite the many 
possibilities, this issue still plagues me.


Unfortunately my Internet has been down a week so hence the late reply.

I did try your proposed solution however on further investigation into 
the formula used, you seemed to mis-understand the requirement, possibly 
due to poor explanation. I was therefore unable to get it to work but 
thank you for the effort.


My issue now is that whilst I have identified a formula that `works', I 
need to get the right method of specifying that the INT value must be 
EQUAL to a value on another sheet BEFORE advancing with the next formula 
argument i.e.


IF an INT formula value of data, on EVERY cell in a column on Data 
sheet, equals a cell from the Summary sheet, then sum the values located 
in a corresponding column on the Data sheet, provided the value in 
another column is either "N" or blank.


vlookup comes to mind as does IF, INT and AND but despite numerous tries 
the syntax still gets in the way.


Regards
Hylton

On 2018/11/10 17:55, Remy Gauthier wrote:

Hi,

I believe you had a similar (if not identical question) which you posted 
back on September 27th (topic: IF doesn't respect AND - 
http://document-foundation-mail-archive.969070.n3.nabble.com/IF-doesn-t-respect-AND-td4249584.html). 
At the time you had uploaded a file to Google and I had offered a 
possible solution to your problem which had worked in my environment. I 
am curious to know if you have tried it and if it worked.


Rgds,

Rémy.

Le samedi 10 novembre 2018 à 08:16 -0600, Michael H a écrit :

I'm not sure I follow your logic, but if your source range contains a list
of values that includes nulls, and non-numbers, you need to logically
exclude those from a math statement, or you get the 'VALUE' error (tried to
do math on a non-numeric cell.) ISBLANK, and ISNUMBER are functions that
provide the way around the VALUE.

If you're trying to make a list of values out of a column of values,
investigate CONCATENATE.  The problem with concatenate is that it doesn't
auto-insert separators and whitespace.  You'll either need to include a new
column with ", " in each row and concatenate 2 columns, or have the
appropriate whitespace in each cell.

If you only have a couple cells to concatenate, you can be precise with the
& operator.  that is [="Hello "&"there!"] allows multiple strings in a
single cell, and each string can be cell references or functions with
string or numeric output.  [="I've seen "&", "&" cells today."] is a
nice way to summarize your data in human readable form.

If you're trying to only pick a few values, investigate LOOKUP.  I'm an
excel refugee, (and used Lotus, and 8bit (GEOS) spreadsheets before that.)
LibreOffice version of LOOKUP is not very well done, but if you're
patient with it, it gets the job done.  I frequently have to insert dummy
rows containing null cells when I want 'exact match only', and libreoffice
can't deal with 'non-sorted' indexes, and they aren't clearly or obvious
what the sorting pattern should be, so it's best to sort your data within
LibreOffice before you use lookup.  (for lookups, a ==A or not, sometimes
NBSP == space, but sometimes not.  This affects matches, but also sorting..
and sort failures breaks many responses, not just one.  These sorting
issues are constant for a single edition of libreoffice, but seem to change
without any notice on updates.)

On Sat, Nov 10, 2018 at 7:44 AM Hylton Conacher (ZR1HPC) <
hyl...@conacher.co.za <mailto:hyl...@conacher.co.za>> wrote:

Hi, What I need help with is the formula to find any row from 
Data.$A$3 to Data$A$1000 and after shortening the 8 digit date value 
to 6 digit value, and having the same 6 digit value as Summary.$A4, 
forward ONLY those rows onto the next part of the formula i.e. SUMIF. 
I have a formula shown below but currently is showing me #VALUE! as 
my result. 
"=IF(INT(Data.$A$31:Data.$A$50/100)=Summary.$A4,SUMIF(Data.$F$31:Data.$F$50,"N",Data.$C$31:Data.$C$50))" 
I have identified the problem where the INT value = Summary.$A4 What 
would the correct syntax be to have a criteria equal a specified 
value as a criteria in a formula? I tried encasing 
"INT...=Summary.A4", but no luck. SUMIFS does not assist as the 
criteria to find, after INT shortening must equal another value 
before the next step can be carried out. Appreciate any help. Where 
should I upload a file Nabble, GDrive, other? Regards Hylton -- To 
unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org 
<mailto:users+unsubscr...@global.libreoffice.org> Problems? 
https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting 
guidelines + more: https://wiki.documentfoundation.org/Netiquette 
List archive: https://listarchives.libreoffice.org/global/users/ 
Privacy Policy: h

[libreoffice-users] Calc 6.0.6.2 IF criteria statement help

[Hylton Conacher (ZR1HPC)]

Hi,

What I need help with is the formula to find any row from Data.$A$3 to 
Data$A$1000 and after shortening the 8 digit date value to 6 digit 
value, and having the same 6 digit value as Summary.$A4, forward ONLY 
those rows onto the next part of the formula i.e. SUMIF.


I have a formula shown below but currently is showing me #VALUE! as my 
result.


"=IF(INT(Data.$A$31:Data.$A$50/100)=Summary.$A4,SUMIF(Data.$F$31:Data.$F$50,"N",Data.$C$31:Data.$C$50))"

I have identified the problem where the INT value = Summary.$A4

What would the correct syntax be to have a criteria equal a specified 
value as a criteria in a formula? I tried encasing "INT...=Summary.A4", 
but no luck.


SUMIFS does not assist as the criteria to find, after INT shortening 
must equal another value before the next step can be carried out.


Appreciate any help. Where should I upload a file Nabble, GDrive, other?

Regards
Hylton

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] IF doesn't respect AND?

[Hylton Conacher (ZR1HPC)]

Hi,

I have a spreadsheet of two tabs with tab 1 named 'Data' and the other 
named 'Summary'. The purpose of this spreadsheet group is to record all 
credit card account transactions on the 'Data' sheet and then see what 
value or transaction slips have not bee submitted by suppliers i.e. the 
goods were acquired FREE.


'Data' tab
The data on the 'Data' tab consists of 5 columns labelled in Row 1 as 
Date,Description,Bank1,Bank2, Reconciled


Format of the date values are 8 digit mmdd values. The Description 
field holds text, either bank 1 or bank2 hold a currency value to two 
decimal places.


'Data' Tab Explanation:
Purchases are recorded in the 'Data' tab with a 8 digit date value and a 
currency value in either bank1 or bank2. When each bank account is 
reconciled a Y is placed in the reconcile column showing that the 
corresponding transaction reflects on the bank statement.


'Summary' Tab:
The data on the summary tab consists of 5 columns labelled in row 1 as 
Date, Description, Bank1, Bank2, Total


The format of the Date value is a 6 digit value i.e. mm. The 
Description is text and does not change. The values listed under Bank 1 
or Bank2 are the SUM total of the values listed in the 'Data' sheet, for 
the particular month/6-digit date under a corresponding Bank1 or Bank2.


'Summary' Tab Explanation:
This includes 6 digit dates(MM) for each month with the same text 
Description for each. The value listed in the Bank1 column in the same 
row as the month concerned is a Sum of all the transaction values from 
the bank1 column on the 'Data' tab for the same month.
The value listed in the Bank2 column in the same row as the month 
concerned is a Sum of all the transaction values from the 'Data' tab for 
the same month under the Bank2 column.


Both the 'Data' and 'Summary' sheets have the 2nd row used to Sum the 
values below just for interest.


The issue I am having identifying a formula that would Sum all the 
values from the 'Data' tab under Bank1 for a specific month shown in the 
'Summary' sheet AND that have NOT been reconciled.


I have managed, after going through many, many formulae to find one that 
'works' but a letter N needs to be placed in the reconciled column  as 
opposed to just being blank, or meeting the criteria by having a Y in it.



 that have not been reconciled and also only have the the first 6 
digits of the 8 digit date matching the year and month on the 'Summary' 
sheet.


Consequently a formula that would Sum all the values from the 'Data' tab 
under Bank2 that have also NOT been reconciled and also match the 6 
digit date on the 'Summary' tab eludes me.


I have Googled silly and tried so many formulae that I ask for help.

Here are some of the formulae I tried:


To search among the 8 digit dates in the 'Data' tab for a 6 digit date 
value I have in the 'Summary' tab, I am using:

INT($Data.$A$3:$Data.$A$1000/100)=Summary.$A3

=IF(INT($Data.$A$3:$Data.$A$998/100)=$A3,SUMIF($Data.F$3:$Data.F$1000,$Data.F$3:$Data.F$100<>"Y",$Data.C$3:$Data.C$1000),0)

=SUMIF(SUMPRODUCT(INT($Data.$A$3:$Data.$A$1000/100)=$A4,$Data.C$3:$Data.C$1000) 
,$Data.F3:$Data.F34<>"Y",$Data.C3:$Data.C1000)


=SUMIF($Data.F$3:$Data.F$1000,AND(INT($Data.A$3:$Data.A$1000)=A3,VLOOKUP($Data.F$3:$Data.F$1000<>"Y",$Data.A$3:$Data.D$1000,4,0)),$Data.D$3:$Data.D$1000)

=SUMIFS($Data.D3:$Data.D32,$Data.A$3:$Data.A$1000,INT($Data.$A$3:$Data.$A$1000/100)=$A4,$Data.F$3:$Data.F$1000,$Data.F$3:$Data.F$1000<>"Y")

=IF(INT($Data.$A$3:$Data.$A$998/100)=$A3,SUM(SUMIF($Data.$C$3:$Data.$C998,"$Data.$F$3:$F1000<>Y"),SUMIF($Data.$D$3:$Data.$D998,"$Data.$F$3:$F1000<>Y")),0)

=IF(INT($Data.$A$3:$Data.$A$998/100)=$A4,SUM(SUMIFS($Data.$C$3:$Data.$C999,$Data.$F$3:$Data.$F999,"<>*Y*"),SUMIFS($Data.$D$3:$Data.$D999,$Data.$F$3:$Data.$F999,"<>*Y*")),0)

=IF(INT($Data.$A$3:$Data.$A$998/100)=$A3,SUMIF($Data.F$3:$Data.F$1000,VLOOKUP("Y",$Data.F$3:$Data.F$100,4,0),$Data.C$3:$Data.C$1000))

=IF(INT($Data.$A$3:$Data.$A$998/100)=$A3,SUM(SUMIF($Data.$C$3:$Data.$C998,"$Data.$F$3:$F1000<>Y"),SUMIF($Data.$D$3:$Data.$D998,"$Data.$F$3:$F1000<>Y")),0)

=VLOOKUP(INT($Data.$A$3:$Data.$A$998/100)=$A3,$Data.B3:F1000,3,1)

=SUMIFS($Data.C3:C50,$Data.A3:A50,INT($Data.$A$3:$Data.$A$1000/100)=$Summary.$A3,$Data.F3:F50,"N")

I am using the formula of

=IF(INT($Data.$A$3:$Data.$A$1000/100)=$A3,SUMIF($Data.F$3:F$1000,"N",$Data.C$3:C$1000),0)

This formula works but I would like to have a blank field i.e. "" 
instead of "N". Converting "N" to "" makes the formula inaccurate as it 
SUMS all months where the field is blank, not just the the date 
specified by '$A3' in the above formula.


Changing the formula to read: 
=IF(AND(INT($Data.$A$3:$Data.$A$1000/100)=$A3,$Data.F$3:F$1000=""),SUM($Data.D$3:D$1000),0) 
instead of: 
=IF(AND(INT($Data.$A$3:$Data.$A$1000/100)=$A3,$Data.F$3:F$1000="N"),SUM($Data.D$3:D$1000),0)
also provides an incorrect value i.e. the rounding done seems to be 
ignored despite being a condition under AND?


You can view 

Re: [libreoffice-users] Add column values according to part of date value

[Hylton Conacher (ZR1HPC)]

Greetings Brian,

To assist I redo my example data:

Date:Text  :A  :B
20180803:Cattle Baron:430.00:BLANK
20180805:Dischem  :BLANK :1233.39
20180805:Checkers :BLANK:606.71
20180901:PNP :2000.00   :BLANK
20181001:WW  : BLANK   :150.00

Dates are plain 8 digit numbers and the field separator is a colon.

> Try =SUMPRODUCT(INT(A2:A99/100)=201809;D2:D99)

Formula worked perfectly, HATS off to you Brian.

I noticed that dragging the formula onto lower cells ie 201810 and
201811 didn't increase te INT= value so I made that equal a cell on my
summary data.

My final formula:
=SUMPRODUCT(INT($Data.$A$3:$A$30/100)=$A4,$Data.D$3:D$30) to display
it a sheet different to data

Tnx, I'll definitely be using the "INT" section alot more.

Regards
Hylton

On 24 August 2018 at 15:28, Brian Barker  wrote:
> At 08:59 24/08/2018 +0200, Hylton Conacher wrote:
>>
>> I have data in 4 columns titled Date:Text:A:B. A typical row example is:
>> 20180822:PNP:blank:500.00 OR 20180822:PNP:500.00:blank
>>
>> A row will only have a value in column A or B, never both on the same row.
>> There may be multiple rows with the same date but different Text and A or B
>> value.
>>
>> Example Data:
>> 20180803:Cattle Baron:430.00:
>> 20180805:Dischem::1233.39
>> 20180805:Checkers::606.71
>> 20180901:PNP:2000.00:
>> 20181001:WW::150.00
>>
>> 1) How to SUM the values in column B ...
>
>
> Er, that's column D, then?!
>
>> ... for September i.e. 201809
>
>
> That's easy: there are no such values, so the answer is zero!
>
>> Pointers and solutions GREATLY appreciated.
>
>
> I'm having to guess that your date values are actually plain eight-digit
> numbers; if they are date values formatted similarly, you will need to
> modify my suggestion.
>
> Try =SUMPRODUCT(INT(A2:A99/100)=201809;D2:D99)
>
> Explanation:
> o A2:A99 is the array of date values (as integers).
> o Dividing those by 100 and taking the integral part of the result -
> INT(A2:A99/100) - gives the year and month values only.
> o The expression INT(A2:A99/100)=201809 then gives an array of boolean
> values, TRUE for September 2018 and FALSE otherwise.
> o The SUMPRODUCT function then sums the numerical products of members of the
> array of boolean values and corresponding members in column D (your "B"). In
> this context, the boolean values TRUE and FALSE are interpreted as 1 and 0
> respectively, so September 2018 values will be included but others not.
>
> Note: although SUMPRODUCT() handles arrays it returns a single value, so it
> is not necessary to enter it as an array function.
>
> I trust this helps.
>
> Brian Barker
>

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] Add column values according to part of date value

[Hylton Conacher (ZR1HPC)]

Using LibreOffice Calc 6.0.6.2 on macOS High Sierra.

I have data in 4 columns titled Date:Text:A:B

A typical row example is: 20180822:PNP:blank:500.00 OR 
20180822:PNP:500.00:blank


A row will only have a value in column A or B, never both on the same 
row. There may be multiple rows with the same date but different Text 
and A or B value.


Example Data:

20180803:Cattle Baron:430.00:
20180805:Dischem::1233.39
20180805:Checkers::606.71
20180901:PNP:2000.00:
20181001:WW::150.00

1) How to SUM the values in column B for September i.e. 201809

I have tried vlookup coupled with IF and RANDBETWEEN, SUMIF. I have 
Googled myself silly looking for a way to SUM multiple values that meet 
a criteria i.e. 201808 or 201809. Vlookup and Hlookup have also not 
assisted as whilst they can return a single value, the addition of 
multiple values is an issue.


My thinking goes " SUM those values in the array a1:d5 that have the 
first column beginning with "201808" Putting that into a formula is 
what's getting me.


Pointers and solutions GREATLY appreciated.

Hylton

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] Spellcheck/Find text selection colour?

[Hylton Conacher (ZR1HPC)]

Hi,

I am using Libreoffice 5.4.7 and deal with large volumes of text and 
data between Writer and Calc.


Very often I need to search for a particular string of text or number 
and using Ctrl-F allows me to do this.


What I am battling with and would like to be able to change is the 
colour indicating a text or number match.


I went into my installed version Preferences and did not find a 
reference to changing the selection colour, which currently seems to be 
a feint pink when identifying a match with the search criteria.


I have reviewed the release notes on the LO download page for both the 
stable and cutting edge versions and none mention a change that would 
assist.


I also visited 
 
from 2014 but could not find the 'Transparency' setting, as obviously 
the app version has changed. Besides I would prefer to select my own 
colour i.e. green, not just have the text match inverted.


I also tried to find something in the app at:
Libreoffice>Preferences>Libreoffice>Application colours

Suggestions?

Regards
Hylton
P.S: I am on the Libreoffice users mailing list
Else mailto:hyl...@conacher.co.za

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc: Calculate number of days until yesterday IE TODAY-1 to assist in array determination for COUNTIF?

[Hylton Conacher (ZR1HPC)]

Morning Brian,

Thanks for the reply.

On 2018/04/27 20:20, Brian Barker wrote:

At 18:04 27/04/2018 +0200, Hylton Conacher wrote:
I am running LibreOffice 5.4.6.2 and am using a spreadsheet to enable 
me to draw graphs based on rainfall received per year, up until the 
date prior to today. The figures also allow me to compare current 
rainfall to date to the same period in a previous year. I agree I 
should probably use Base but I do not think it would have solved the 
issue.


**Example Data***
,
Month,Day,M/D,2016,2017,2018,Average('16->'17)
Apr,1.04/01,3,0,1,1.5
Apr,2,04/02,0,8,0,4
,,
,,
,,
Apr,26,04/26,0,0,20,0

Assuming today is Apr 2nd, a formula to count how many days of rain 
there were in the current month would be =COUNTIF($F$2:$F2,">0").


Surely not? Rather something like =COUNTIF($F$94:$F94,">0").


Correctly extrapolated to the real data set as opposed to the example 
data set. Sorry, I just checked the example data set and I seemed to 
have mucked it up.




The Total amount of rain that fell on that number of days, being 1, 
was 1mm, ...


If we are talking 2018, that seems to be zero in your data.


You are correct and have extrapolated the formula to the REAL data set 
correctly.


... or taking it further 60% of the average rainfall to date for the 
month.


I don't know what fraction zero is of zero - as I'm not allowed to 
divide by zero. Does your mathematics allow this?


:) Apologies. 0 of 0 is still 0. I was using the example data so folk 
did not have to download the spreadsheet.


Assuming today is Apr 27, a formula to count how many days of rain 
there were in the current month is unknown, ...


Surely not? The current month (I think it's still 2018, unless some 
catastrophe has happened without my knowing) has apparently had six 
rainy days in your data.


Again you are correct, just using a different data set.


... especially as there are undoubtedly values in the other un-shown 
81 cells i.e. 3yrs and 27 remaining days?


Where are these "unshown" cells - somewhere else, and not in this 
spreadsheet at all? And how can the "current month" - surely April 2018 
- contain over three years' worth of days?


Disregard as I was referring to my example data as instead if writing 
values for 26/27 days across three years. You are looking at the REAL 
data set and there are NO missing values.



Basically take the starting cell reference $F$2 ...


Perhaps $F$3?

Again wrong data set. The F3 was based on the example data not REAL data.

Take the starting cell reference as you correctly identified as $F94...


... and add the number of events that were ">0" to get the end 
reference for the COUNTIF formula.


Now you've lost me ...
If my starting reference is F94 SUM onto 94 the number of rows in column 
F those rows that are ">0", but only add them if the date is between the 
date of F94 i.e. 2018-04-01 and Yesterday's date.




=COUNTIF($F$2:Add number of items ">0" to the starting line reference 
2, to get the end of the COUNTIF range) i.e. being Apr 27, whilst 
there may be 26 days between the beginning of Apr and today i.e. 
Today()-1, there are only two instances in the above table where ">0" 
for a particular year, ...


Are you working with the current month - one month? Or with all the 
Aprils in three years - three non-contiguous months? Or with the period 
from 1 January 2016 up to yesterday? Or what?

I am working in a month for a year i.e. April in 2018



... thus making 2 the number of days it rained, ...


That's still six in your spreadsheet.

Correct




... to receive the SUM of the 2018 rainfall being 21mm


Er, 67 mm?

Correct


Incidentally, you have the value 119 in L2 to indicate yesterday's row 
number, but this has evidently been added manually and also must be 
updated so. It should be fairly easy to establish this automatically, 
and even to embed any such calculation in any formula.


Now this sounds promising. Can I reference a cell with a manually 
entered line number i.e. L2 on the REAL data  has a value of 120 and use 
that value to complete a formula i.e. COUNTIF($F$94:F(Value in L2), 
">0") i.e. COUNTIF($F$94:F120, ">0")


Sorry for the confusion. Next time I raise a query I am only going to 
refer to my REAL data.


Regards
Hylton

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

[libreoffice-users] Calc: Calculate number of days until yesterday IE TODAY-1 to assist in array determination for COUNTIF?

[Hylton Conacher (ZR1HPC)]

Hi,

I am running LibreOffice 5.4.6.2 and am using a spreadsheet to enable me 
to draw graphs based on rainfall received per year, up until the date 
prior to today. The figures also allow me to compare current rainfall to 
date to the same period in a previous year. I agree I should probably 
use Base but I do not think it would have solved the issue.


**Example Data***,
Month,Day,M/D,2016,2017,2018,Average('16->'17)
Apr,1.04/01,3,0,1,1.5
Apr,2,04/02,0,8,0,4
,,
,,
,,
Apr,26,04/26,0,0,20,0

Assuming today is Apr 2nd, a formula to count how many days of rain 
there were in the current month would be =COUNTIF($F$2:$F2,">0"). The 
Total amount of rain that fell on that number of days, being 1, was 1mm, 
or taking it further 60% of the average rainfall to date for the month.


Assuming today is Apr 27, a formula to count how many days of rain there 
were in the current month is unknown, especially as there are 
undoubtedly values in the other un-shown 81 cells i.e. 3yrs and 27 
remaining days?


Basically take the starting cell reference $F$2 and add the number of 
events that were ">0" to get the end reference for the COUNTIF formula.


=COUNTIF($F$2:Add number of items ">0" to the starting line reference 2, 
to get the end of the COUNTIF range) i.e. being Apr 27, whilst there may 
be 26 days between the beginning of Apr and today i.e. Today()-1, there 
are only two instances in the above table where ">0" for a particular 
year, thus making 2 the number of days it rained, to receive the SUM of 
the 2018 rainfall being 21mm


I have Googled silly but my GFu is lacking something as the solution 
evades me, even after reading the LibreOffice help files.


For reference the formula to count the number of days from 1st Apr to 
Yesterday:

=DAYS(TODAY(),"2018-04-01")

I have uploaded the file to 
 
for folk to view. quite willing to upload the spreadsheet


I will need to re-write at least 36 formulae being one for each month of 
three years, but then hopefully I can leave column AC and concentrate on 
entering the data correctly and optimising the graphs.


Ideally I think I need a formula to give me the number of days between 
$F$2 and 'TODAY() -1' and add that number to the cell reference to 
create the end of the COUNTIF range.


I hope you can point me in a direction that will help.

Regards
Hylton

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

Re: [libreoffice-users] Calc: Export as PDF

[Hylton Conacher (ZR1HPC)]

bugzilla report 116667 has been resolved/fixed.

 On 2018/03/27 22:20, Steve Edmonds wrote:

Hi.
Have you checked Format>Print ranges and checked View>Page break to see 
that the graphs fit on the page.

Does File>Prit preview show all graphs.

Steve

On 28/03/18 09:01, Hylton Conacher (ZR1HPC) wrote:

Hi,

I have a large spreadsheet of 23 sheets of which all but two contain 
full page graphs i.e. at 200% magnification the graph stretches until 
column K and down to row 25.


On a monthly basis I need to select 11 of the sheets, 10 of which hold 
graphs and Export as PDF into a single PDF. The 'export' works but 
only on 6 pages, whilst the others are lost in the ether. The PDF 
created is an Archive PDF/A, duplicate field names are allowed and 
View PDF after export is selected with lossless compression of the 
selected sheets.
I have tried creating the PDF as a Hybrid PDF with the same results 
although no warning about transparency.


Viewing in Adobe Reader afterwards only produces some of the sheets 
and does not begin with the first sheet selected.


I am using Version: 5.4.6.2
Build ID: 4014ce260a04f1026ba855d3b8d91541c224eab8
CPU threads: 4; OS: Mac OS X 10.13.3; UI render: default;
Locale: en-ZA (en_ZA.UTF-8); Calc: group

Please advise as there are approximately 1500 people who receive the 
graphs and could therefore want to try Libreoffice themselves.


I have searched the Bugzilla and not found one that matches so have 
created a new one titled: Bug 116667 - FILESAVE: Export as PDF


Any comments?

Hylton
P.S.:I am on the Libreoffice users mailing list and can be reached 
privately via mailto:hyl...@conacher.co.za








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

Re: [libreoffice-users] Calc: Export as PDF

[Hylton Conacher (ZR1HPC)]

Hi Steve,

On 2018/03/27 22:20, Steve Edmonds wrote:

Hi.
Have you checked Format>Print ranges and checked View>Page break to see 
that the graphs fit on the page.

Does File>Prit preview show all graphs.


Checked the Format of the page being correctly orientated etc and Print 
Preview did NOT show all images.
So I defined the print range as the whole page, which I thought would 
have been standard printing practice i.e. if I say print sheets 1,3,4,5 
then the entire sheets of 1, 3, 4, 5 should be selected and printed but 
apparently Libreoffice didn't think like I did.
I amended all the sheets and defined the print range as the entire sheet 
and then all graphs showed on Print Preview. I Exported as PDF and 
viola' all graphs appeared.


Thanks, I'll certainly remember to check print range in future.

Regards
Hylton


Steve

On 28/03/18 09:01, Hylton Conacher (ZR1HPC) wrote:

Hi,

I have a large spreadsheet of 23 sheets of which all but two contain 
full page graphs i.e. at 200% magnification the graph stretches until 
column K and down to row 25.


On a monthly basis I need to select 11 of the sheets, 10 of which hold 
graphs and Export as PDF into a single PDF. The 'export' works but 
only on 6 pages, whilst the others are lost in the ether. The PDF 
created is an Archive PDF/A, duplicate field names are allowed and 
View PDF after export is selected with lossless compression of the 
selected sheets.
I have tried creating the PDF as a Hybrid PDF with the same results 
although no warning about transparency.


Viewing in Adobe Reader afterwards only produces some of the sheets 
and does not begin with the first sheet selected.


I am using Version: 5.4.6.2
Build ID: 4014ce260a04f1026ba855d3b8d91541c224eab8
CPU threads: 4; OS: Mac OS X 10.13.3; UI render: default;
Locale: en-ZA (en_ZA.UTF-8); Calc: group

Please advise as there are approximately 1500 people who receive the 
graphs and could therefore want to try Libreoffice themselves.


I have searched the Bugzilla and not found one that matches so have 
created a new one titled: Bug 116667 - FILESAVE: Export as PDF


Any comments?

Hylton
P.S.:I am on the Libreoffice users mailing list and can be reached 
privately via mailto:hyl...@conacher.co.za








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

[libreoffice-users] Calc: Export as PDF

[Hylton Conacher (ZR1HPC)]

Hi,

I have a large spreadsheet of 23 sheets of which all but two contain 
full page graphs i.e. at 200% magnification the graph stretches until 
column K and down to row 25.


On a monthly basis I need to select 11 of the sheets, 10 of which hold 
graphs and Export as PDF into a single PDF. The 'export' works but only 
on 6 pages, whilst the others are lost in the ether. The PDF created is 
an Archive PDF/A, duplicate field names are allowed and View PDF after 
export is selected with lossless compression of the selected sheets.
I have tried creating the PDF as a Hybrid PDF with the same results 
although no warning about transparency.


Viewing in Adobe Reader afterwards only produces some of the sheets and 
does not begin with the first sheet selected.


I am using Version: 5.4.6.2
Build ID: 4014ce260a04f1026ba855d3b8d91541c224eab8
CPU threads: 4; OS: Mac OS X 10.13.3; UI render: default;
Locale: en-ZA (en_ZA.UTF-8); Calc: group

Please advise as there are approximately 1500 people who receive the 
graphs and could therefore want to try Libreoffice themselves.


I have searched the Bugzilla and not found one that matches so have 
created a new one titled: Bug 116667 - FILESAVE: Export as PDF


Any comments?

Hylton
P.S.:I am on the Libreoffice users mailing list and can be reached 
privately via mailto:hyl...@conacher.co.za


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

Re: [libreoffice-users] Calc: Filtering formula

[Hylton Conacher (ZR1HPC)]

On 01/12/2017 21:57, T. R. Valentine wrote:

I messed up. Should be:
Thank you for the correction. The formula, with a little amendment works 
flawlessly.


=SUMIFS($D$7:$D$1000,$C$7:$C$1000,"=Sold",$D$7:$D$1000,"<0")

With this formula I can now search for a certain string in column C and 
SUM the corresponding values in column D.


Thank you, I will certainly be kept in my personal FAQ file.


Assuming labels are in A1:A30, values in B1:B30
=SUMIFS(B1:B30,A1:A30,"=",B1:B30,">0")

replace  with the 'certain text string'


- unless you are looking for a text string within the contents of
the cells with text. That would be more complicated. I'd go for a (if
necessary, hidden) column:

Assuming original labels in A1:A30, values in C1:C30
cell B1 formula: =LEN(IF(ISERR(FIND("",A1)),"",A1))
(copy this formulas through B30)

then
=SUMIFS(C1:C30,B1:B30,">0",C1:C30,">0")






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

[libreoffice-users] Calc: Filtering formula

[Hylton Conacher (ZR1HPC)]

Hi,

I have two adjacent  columns where the first contains text and the 
second contains 9 digit numbers both above and below zero.


I would like to SUM the contents of the second column provided it meets 
two criteria, namely that the first column contains a certain text 
string and the number corresponding to that text string in the second 
column is greater than zero.


I have tried SUMIFS, AND with SUMIF and Vlookup but cannot seem to get 
the formula correct i.e. the error seems to always be that there is a 
missing operator in the string yet checking back I see all commas are in 
the right places. I am placing the text to search for in quotation marks 
as well as the "0<" or "0>"


Any help appreciated

Regards
Hylton


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

Re: [libreoffice-users] libreoffice 5.3.5.2 display issue

[Hylton Conacher (ZR1HPC)]

Hi,

On 20/08/2017 23:23, FHDATA wrote:


hello,

I use the Still version and it's been a couple of days
I switched to new Still release 5.3.5.x and I see
the row numbers getting smooshed together when
I scroll up-and-down (slow or fast does not matter).
makes it difficult to see row  numbers or in general
to work on spreadsheet 


small screen shot attached.

any thing can be done about this?
As you may or may not be aware, any attachments to this mailing list are 
stripped. If you want to add those niceties, it is better to raise a 
Bugzilla report, which does accept attachments.


Whilst I initially also experienced this issue, I found a work-around by 
selecting the column of cells that was not displaying correctly and 
Formatting the Cells i.e. no the column to keep the cell contents 
aligned in the centre(H + V) and also to enable Word Wrap.


This has so fat cleared the problem for my application.

If it should happen again I will raise the necessary feature request on 
Bugzilla with screen shots.


Regards
Hylton

--
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] libreoffice 5.3.5.2 display issue

[Hylton Conacher (ZR1HPC)]

Hi,

On 20/08/2017 23:23, FHDATA wrote:


hello,

I use the Still version and it's been a couple of days
I switched to new Still release 5.3.5.x and I see
the row numbers getting smooshed together when
I scroll up-and-down (slow or fast does not matter).
makes it difficult to see row  numbers or in general
to work on spreadsheet 


I have this issue as well with the Version: 5.3.5.2
Build ID: 50d9bf2b0a79cdb85a3814b592608037a682059d

If I insert details comprising text and numbers into a cell with centre 
alignment and word wrap ON the row height does NOT increase to 
accommodate the increased row height used for text that does not fit 
into the standard cell size.


Please advise as it is most annoying to keep resizing row height to 
accommodate the increased space taken by the wrapped input.


What gives as even if I format the entire column to have alignment 
centre and with wrapped text, there are still cells that do NOT increase 
in height to accommodate their content.


Regards
Hylton
P.S: I am on the mailing list.

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

[libreoffice-users] Calc 5.3.4.2 Data into cell on 'Frozen' column error

[Hylton Conacher (ZR1HPC)]

Hi,

I have recently downloaded and installed LO i.e.:

Version: 5.3.4.2
Build ID: f82d347ccc0be322489bf7da61d7e4ad13fe2ff3
CPU Threads: 4; OS Version: Mac OS X 10.12.6; UI Render: default; Layout 
Engine: new;

Locale: en-ZA (en_GB.UTF-8); Calc: group

In previous versions of Calc I have many spreadsheets where I drag the 
thick 'freeze column' line from the bottom left and move it towards the 
left margin so that only the number of columns I want to freeze is 
shown. I can then move the columns to the right of the 'freeze' line as 
I want and the columns to the left of the 'freeze' line stay frozen.


As of version 5.3.4.2, the frozen columns remain frozen however should 
the columns to the right of the 'freeze' line not have the first column 
showing the 'frozen' column, entering data into the 'frozen' column 
forces the columns on the right of the 'freeze' line to re-arrange 
themselves so that the 'frozen' column is the first column after the 
'freeze' line.


This is MOST annoying when needing to enter data only in specific 
columns once the 'frozen' column has been updated with new data.


There is a workaround with View->Freeze Cells however if you do not want 
to only 'freeze' the first column, this workaround fails.


Please bring back the functionality of just moving the 'freeze' line and 
not having the other columns re-arrange if data is entered into the 
'frozen' zone.


Can anyone confirm if this also occurs/doesn't occur on the 2.3.5 
download release.


Appreciated
Hylton
P.S: I am subscribed to the users mailing list

--
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] COUNTIF / DAYS / ?

[Hylton Conacher (ZR1HPC)]

Michael,

Thank-you for the time and response

On 08/05/2017 08:54, Michael D. Setzer II wrote:

Not sure if I fully understand what you are looking at,
but here is what I have attempted.
Assuming you column data is:
A is month
B is day of Month
C is the rain on day in 2016
D is the rain on day in 2017


Correct :)


I've added Column E and Column F to be real date value.
E2 =DATE(2016,MATCH(A2,$G$1:$G$12),B2)
F2 =DATE(2017,MATCH(A2,$G$1:$G$12),B2)


This turns out to be a problem as I am working with five(5) years of 
data and whilst my example did not shot it there are multiple values 
under each month, even if it is zero.


I don't think five columns of dates is a smart way to do this.
I am rather going to investigate making my date column a rolling year 
numeric month/day for 365 rows, with the columns being divided into years.


Example:

Date201620172018
01010   1   5
01021   0   0
01030   5   10
05011   0   0
09225   1   1
31120   1   0

I then reckon use of MATCH, DAYS, COUNTIF and SUM should deliver what I 
seek.


Your thoughts on how to get the rows in a column formatted as Date to 
show a sequential month and day number?


I have Bcc'd you as the message contain an attachment of my existing sheet.

Regards
Hylton

--
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] COUNTIF / DAYS / ?

[Hylton Conacher (ZR1HPC)]

On 07/05/2017 23:48, Remy Gauthier wrote:

Hi,

If you have one row per day of the year, you can find out easily where 
the row corresponding to "TODAY()-1" is located by finding out the 
number of days between Jan 1st of that year and "yesterday". This would 
be something like (you may need to adjust based on the heading of your 
column(s)):


=DAYS(YEAR(TODAY()-1)-1;MONTH(TODAY()-1);DAY(TODAY()-1);YEAR(TODAY()-1)-1;1;1)

The formula above calculates the number of days between yesterday last 
year and Jan 1st of last year. Now, you need to find in which column to 
add. Since you seem to have a heading providing the year, you can use 
the MATCH() function to locate the column:


=MATCH(YEAR(TODAY()-1)-1;$a$1:$z$1;0)

This should give you the column where last year's data is located (I 
used a1 to z1, but any row reference will work).


To add the values, you now just need to use a combination of SUM() and 
OFFSET():


=SUM(OFFSET($A$2;0;MATCH(YEAR(TODAY()-1)-1;$a$1:$z$1;0)-1;DAYS(YEAR(TODAY()-1)-1;MONTH(TODAY()-1);DAY(TODAY()-1);YEAR(TODAY()-1)-1;1;1);1))

What that does:

OFFSET() allows you to create dynamic arrays for functions like SUM(). I 
used the anchor point $A$2 (I supposed your heading is in row 1), and 
told OFFSET to move from that reference 0 rows down and 
"MATCH(YEAR(TODAY()-1)-1;$a$1:$z$1;0)-1" columns to the right; then, 
from that new point, make an array that is 
"DAYS(YEAR(TODAY()-1)-1;MONTH(TODAY()-1);DAY(TODAY()-1);YEAR(TODAY()-1)-1;1;1)" 
rows high and 1 column wide. The resulting array is then used by SUM() 
to calculate the total rainfall.


This will probably need to be adjusted a bit to take into account the 
position of your column heading and to correctly find the row where to 
stop, but you should now have something to play with.


I hope this helps.

Rémy Gauthier.


Thank you for the in-depth reply! I will need to play with this  and the 
functions you mention to get it right but hopefully I will come right. I 
will post my final formula here, for anyone else wanting to do something 
similar.



Regards
Hylton

Le dimanche 07 mai 2017 à 21:55 +0200, Hylton Conacher (ZR1HPC) a écrit :

Hi,

I have a spreadsheet I use to draw graphs and extract information about
the rainfall in my area for the last two years.

I need to create a formula that will count how many days it rained
between the start of the year, both last year and this year, so I can
compare the amount of rainfall that was received during that time frame.

The example below is a sample dataset that shows how much rainfall was
received in 2016 or 2017, on each of the four days

Example:
Month Day   20162017
Jan  1  0   15
Feb  29 1   0   
May  6  0   1
Sept 22 15  5

  From this we can determine that 16mm of rain fell in 2016 over 2 days.
Likewise we can determine that 21mm of rain fell in 2017 but over 3 days.

Today is the 7th of May 2017 and I would like a formula to work out how
many days the rainfall received between(and incl) 2016/01/01 and the day
before TODAY last year. The answer for 2016 is 1 i.e. it only rained
once between the dates specified, however the answer for 2017 is 16 as
it rained twice between TODAY-1 and 2017/1/1. I had thought of using
COUNTIF or DAYS or =COUNTIF(C2:SUM(TODAY()-1,">0")), however the problem
is that I do not have a single date column, but three.

This formula will allow me to create a graph showing how much rainfall
had fallen last year compared to this year between the beginning of the
year(01/01) and the day prior to Today.

I had thought of converting the first two columns into a single and
having the text name of the month with each successive line being a new
date in that month until the month changed, however I was unable to get
it to work i.e. automatically change month after 31 days in January to
read February 01.
As you can imagine I have 367 rows of data per year, mostly with 0 as a
value, however there are odd days it does rain.

Any comments, and if you want the original spreadsheet, just yell where
to put it i.e. Nabble etc.

Regards
Hylton




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

[libreoffice-users] COUNTIF / DAYS / ?

[Hylton Conacher (ZR1HPC)]

Hi,

I have a spreadsheet I use to draw graphs and extract information about 
the rainfall in my area for the last two years.


I need to create a formula that will count how many days it rained 
between the start of the year, both last year and this year, so I can 
compare the amount of rainfall that was received during that time frame.


The example below is a sample dataset that shows how much rainfall was 
received in 2016 or 2017, on each of the four days


Example:
Month   Day 20162017
Jan 1   0   15
Feb 29  1   0   
May 6   0   1
Sept22  15  5

From this we can determine that 16mm of rain fell in 2016 over 2 days. 
Likewise we can determine that 21mm of rain fell in 2017 but over 3 days.


Today is the 7th of May 2017 and I would like a formula to work out how 
many days the rainfall received between(and incl) 2016/01/01 and the day 
before TODAY last year. The answer for 2016 is 1 i.e. it only rained 
once between the dates specified, however the answer for 2017 is 16 as 
it rained twice between TODAY-1 and 2017/1/1. I had thought of using 
COUNTIF or DAYS or =COUNTIF(C2:SUM(TODAY()-1,">0")), however the problem 
is that I do not have a single date column, but three.


This formula will allow me to create a graph showing how much rainfall 
had fallen last year compared to this year between the beginning of the 
year(01/01) and the day prior to Today.


I had thought of converting the first two columns into a single and 
having the text name of the month with each successive line being a new 
date in that month until the month changed, however I was unable to get 
it to work i.e. automatically change month after 31 days in January to 
read February 01.
As you can imagine I have 367 rows of data per year, mostly with 0 as a 
value, however there are odd days it does rain.


Any comments, and if you want the original spreadsheet, just yell where 
to put it i.e. Nabble etc.


Regards
Hylton

--
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] Base and Java

[Hylton Conacher (ZR1HPC)]

On 28/11/2016 18:52, Keith Barber wrote:

I've created a smallish (200-odd members) membership database in Base.
Just in case it's relevant, I populated it with data from a Calc
spreadsheet which itself originated as a MS Excel spreadsheet created by
my predecessor.

On trying to open the table, I got a no JRE error.  I managed to cure
that by uninstalling and reinstalling Java, and all went swimmingly
until...


Hi Keith,

I have a similar problem however my Base just will not let me do 
anything until the JRE is installed.


I REALLY NEED Java to use Base as I am endeavouring to managed a 500 
strong membership list over 9 zones with an index common to all. I have 
tried to install and Java sits in my "System Preferences" but linking LO 
to it is another mission as it doesn't seem you can re-initiate a search 
by LO for a JRE?


Uninstalling it can't be done by dragging icon to Trash and only thing I 
have found is on the net to do it via cli.


I could use Calc and import data sets from a main sheet into a smaller 
table but it is really time intensive so really using Base is a no 
brainer. I also refuse to pay money for MS Access to run on my Apple Mac 
Sierra 10.12.1 with LO 5.2.3.3.


Wonder when LO is going to follow Minecraft's example for items such as 
Base? 



Regards
Hylton

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

[libreoffice-users] Calc formula syntax assistance

[Hylton Conacher (ZR1HPC)]

Hi,

Running :O 5.0.3.2 on IMac and have seen there is a 5.0.4 update, 
however, perhaps my issue does not require an update.


Anyway, the formula involves COUNTIF with the search range on another 
sheet and the search criteria in a cell adjacent to the countif formula.


=COUNTIF(Data.$C$4:C$492,$B17)

Each search criteria (B17), is text consisting of spaces, numbers and 
special characters i.e. BLUE GUM LANE (NO'S 1 – 17)


On my version of LO this formula displays a zero as the count of the 
search criteria however on reviewing the Data sheet, I can identify at 
least one exact replica within range, yet it is not counted?


Any ideas to get the correct answer of the number of times a field appears?

Regards
Hylton

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

[libreoffice-users] Calc: Searching for values in a certain range string

[Hylton Conacher (ZR1HPC)]

Hi,

I am looking for a pointer or two to enable me to accomplish what I seek.
What I seek is to be able to have a sheet of Lottery results downloaded 
from the Internet and pasted into a Calc sheet. On another Calc sheet I 
will have a listing of the 6 numbers held for each specific draw date.


Each populated cell  of my ticket sheet will indicate if the number in 
that cell is the same as a number in a range of cells from the results 
sheet, on a particular date.


As an example:

Assume the draw results below dated  drew the following numbers:
04/02/2014 16, 03, 19, 21, 23, 06   
11/02/2014 17, 05, 02, 20, 22, 04
18/02/2014 09, 40, 05, 09, 42, 03

Also assume that draw numbers I chose for a draw on a date were:
11/02/2014  02, 05, 18, 20, 22, 04

In theory looking at the data, if I submitted my ticket for prize 
redemption, I might score very well considering my ticket matches 5 out 
of 6 of the numbers from the draw. If any number on my ticket matches a 
corresponding date value, I want the cell to change background colour.


Now the trick is to put this in practice. :)

After looking into the usage of vlookup and given I want the background 
colour to change if the conditions are met, I selected the cell 
containing the first number on my ticket (02) and then selected 
'Format', Conditional Formatting'.


I selected that the cell value is equal to and entered 
vlookup($LottoResults.$B$39,$LottoResults.$C$39:$D$39,1,0) into the 
field, chose the new background style and said OK.


I checked the first number of my ticket on the specified date and 
noticed that the background had not changed as instructed to by the 
conditional formatting. :(


Using the other option on Conditional Formatting I selected 'Formula is' 
and entered the same vlookup formula wit the same disastrous results, 
i.e. no colour change on the first cell of my ticket number :(


Doing a second trial I said cell value must equal and entered 
$LottoResults.$C$39.
The cell on my ticket came out the requested colour however the formula 
did not allow me to check if on the ticket date and corresponding ticket 
draw were identical. :(


The purchased ticket has 6 numbers on it, with the first 5 being in 
numerical order and the 6th being a bonus random ball( 01-49).

The draw results are not in numerical order.

So close yet so far, what am I missing?

pointers?

Regards
Hylton

--
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] Calc cross-sheet issue with COUNTIF and AVERAGEIF...

[Hylton Conacher (ZR1HPC)]

On 05/01/14 02:10, Ryan Ashley wrote:

I have developed a spreadsheet which contains a sheet for the cover and
basic information as well as one sheet for every month. There is a cell
with a number on every sheet at location H35. I want the average of all
of those which are not zero on the first sheet. I initially tried using
AVERAGEIF(January.H35:December.H35, 0), but it keeps giving me
error 504. I then tried the formula below, which uses COUNTIF, but the
part with COUNTIF in it causes a 504 also. If I cannot use COUNTIF or
AVERAGEIF, how do I get an average of cell H35 on each sheet where H35
is greater than zero?


It certainly sounds like a bug however that being said, but perhaps your 
AVERAGEIF formula needs work.


Unfortunately my older version of LO does not have AVERAGEIF so I cannot 
check the syntax. Perhaps 
http://www.libreoffice.org/get-help/documentation/ will have the 
answer for you.


Post the answer here or the bug number on this thread.

Hope you come right.

Hylton
--

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://www.fishhoeklionsclub.org.za being part of the worlds
largest non profit NGO




--
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] Calc Function Wizard - IF Result differences

[Hylton Conacher (ZR1HPC)]

On 20/10/13 01:15, Paul wrote:

As I noticed you're practically around the corner from me, I got
interested, and had a look at your spreadsheet :)

[tl;dr: I got solutions, but need some help uploading the file. Also,
it may be that you could work it out in a *much* simpler way, using
what you already have, but a) something is broken in your other
formulas, and b) that's if they are supposed to do what I think they
are.]

trimmed

I have replied to Paul privately and am looking forward to learning a 
bit more on the user formula he mentions. Any other pointers would be 
appreciated especially as he also indicates that using VLOOKUP would not 
work, in this case. MIN and MAX are still under testing here :)


I am however still waiting for someone to indicate to me which part of 
the formula in the Formula Wizard the boxes apply i.e. what is the 
difference between the 'Result' and 'Function Result' answer box.



Regards
Hylton

--

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://www.fishhoeklionsclub.org.za being part of the worlds
largest non profit NGO




--
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] Calc IF problem

[Hylton Conacher (ZR1HPC)]

On 19/10/13 16:46, Brian Barker wrote:

At 19:11 10/10/2013 +0200, Hylton Conacher wrote:

I have a spreadsheet with four pricing ranges for a certain number of
units.

G4 = 601
H4 = calculated by below formula = 751.25 but should be 751.52
.
F20 = 1.25
G20 = 150 i.e. 0-150 units
h20 = F*G

I20 = 1.25
J20 = 200 i.e. 150.0001 - 350 units
K20 = I*J

L20 = 1.25
M20 = 250 i.e. 350.0001 - 600 units
N20 = L*M

O20 = 1.52
P20 = 600 i.e. 600.0001 - infinity units
Q20 = O*P

The below formula calculates H4
=IF(0G4=$G$20,G4*$F$20,IF($G$20G4=$J$20,(G4-$G$20)*$I$20+($F$20*$G$20),IF($J$20G4=$M$20,(G4-$G$20-$J$20)*$L$20+(($G$20*$F$20)+($J$20*$I$20),IF($M$20G4$P$20,(G4-$G$20-$J$20-$M$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20),IF(G4=$P$20,((G4-$G$20-$J$20-$M$20-$P$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20)+($O$20*$P$20))


Is there a simpler way to achieve the right answer i.e. 751.52 as I
have checked all the cell values and they all read as indicated here.

Where am I making a mistake?


Sorry if this appears critical, but there are a number of things to say
here.


Criticism is not bad, it is a method of proposing different or better 
solution to a problem. Your thoughts are welcome and appreciated.



o To get the best help from others, you really do need to explain your
problem in detail - and that means words, not formulae or values.  Once
you have defined your problem, it is very likely someone can help you.
Indeed, explaining your problem concisely and completely may well help
you to see how to solve it yourself!


The spreadsheet is what I use to calculate the exact units purchased and 
used each month for a prepaid electricity meter. Those same unit values 
could build a trend and so additional units could be bought in the 
colder months, without knowing what additional currency purchase value( 
i.e. ZAR200 or ZAR500) would be needed e.g. Assuming there was say 100 
units of electricity left at the end of the month and based on an 
average the house will probably use 750 units of electricity next month. 
Therefore an amount of 650 units needs purchasing. Based on the pricing 
structure on the number of units required being provided by the local 
electricity authority, what is the currency value that must be taken to 
an electricity vendor to purchase the required 650 electricity units?



o You have laid your values out as a row (in row 20) - as if it's a
vector, a twelve by one array.  But surely it is a four by three array?
Then you need another column to show what number of units were purchased 
in each given month for each of the four price brackets



Setting this out as four rows of three columns would help you see what
the likely solution was.  And you have not used those products (H20,
K20, etc.) in your formula: instead, you have effectively recalculated
them all, so they are just distracting.

o I always hope it's obvious (but it's clearly not) that you cannot
define what you are trying to calculate by quoting a formula that you
know doesn't work.  This must indicate what you *don't* want!  Please,
please omit all the detail and say what you do want.  (Forcing yourself
to do this is a good first step in solving any problem.)


I agree, however the end value I want is determinant on the remaining 
electricity unit balance at the end of the month, which can be highly 
variable.



o Any formula as complicated and repetitive as this cannot be the right
way to do things.  Apart from anything else, you might not notice any
slip you might have made in the middle of the formula, and it would be
quite easy to miss it during testing.  Anything repetitive should be
reduced to a simpler formula that nevertheless deals with all situations.


I agree this is a complicated formula and so far during testing I have 
managed to ascertain that despite the 350.1 - 600 being 250, for some 
reason my formula is not taking heed of the 250 Max unit value i.e. 251 
units instead of being 250 units plus 1 unit at 1.52 being the pricing 
range for the next number of units.



o You have apparent logical expressions such as 0G4=$G$20.  Whilst
ABC is meaningful in mathematics, where in the documentation did you
find any suggestion that it makes sense in spreadsheet lingo?!


Actually in the Formula Wizard as it mentions the first field needs to 
be the test value, the next field needs to be the action if the test 
field is true and the third field in the IF formula needs to be applied 
if the test field evaluation is false.



Since
it's undefined, I'm having to guess what this will mean to Calc.  First,
0G4 will be evaluated, giving the result TRUE or FALSE.  Now the rest
of the expression reduces to TRUE=$G$20 or FALSE=$G$20 - which make no
sense, of course.  In practice, it seems that the logical values are
interpreted as numbers - with FALSE being zero and TRUE being one - and
this numerical value is then compared with $G$20.  But these values are
150, 200, and so on, which will always be 

Re: [libreoffice-users] Calc Function Wizard - IF Result differences

[Hylton Conacher (ZR1HPC)]

Jay,

On 19/10/13 19:23, Jay Lozier wrote:

Hylton,

If you can breakup the formula into smaller units you might find the
problem.

Often when I have had a similar problem I found the my problem
was I reversed test in an IF clause.


Thanks, I have checked all the IF tests and mathematically they are correct.
--

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://www.fishhoeklionsclub.org.za being part of the worlds
largest non profit NGO




--
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] Calc Function Wizard - IF Result differences

[Hylton Conacher (ZR1HPC)]

On 19/10/13 19:32, Jean-Francois Nifenecker wrote:

Hi,

Le 19/10/2013 14:36, Hylton Conacher (ZR1HPC) a écrit :


The initial formula is:
=IF(0G4=$G$20,G4*$F$20,IF($G$20G4=$J$20,(G4-$G$20)*$I$20+($F$20*$G$20),IF($J$20G4=$M$20,(G4-$G$20-$J$20)*$L$20+(($G$20*$F$20)+($J$20*$I$20),IF($M$20G4=$P$20,(G4-$G$20-$J$20-$M$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20),IF(G4$P$20,((G4-$G$20-$J$20-$M$20-$P$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20)+($O$20*$P$20))


this is waaay too convoluted to be easily checked.


I would agree, but it should still work.



There must be an answer and or  my formula syntax is wrong unbeknown to me.



Sure, there is. As your request looks very much like your previous one,
I highly encourage you to read Brian's answer to that one: it's filled
will good advice. IOW, when you can write your problem down using common
speech (not some spreadsheet language), I'm sure you'll turn having
found the solution by yourself.

- I share Brian's idea about using VLOOKUP() through a 3 lines set of data.


I have no problem using VLOOKUP but this data does not lend itself 
towards using VLOOKUP, to my minor knowledge.


If someone could give me more of an explained example of VLOOKUP using 
the pricing structure and unit limits on each, that can be expanded 
across 12 months, I will look into it further.

--

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://www.fishhoeklionsclub.org.za being part of the worlds
largest non profit NGO




--
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] Calc Function Wizard - IF Result differences

[Hylton Conacher (ZR1HPC)]

On 19/10/13 20:25, Brian Barker wrote:

At 14:36 19/10/2013 +0200, Hylton Conacher wrote:

Entering a formula into the Function Wizard in Calc shows up two
different answers in the 'Result' and 'Function Result' answer box.


Certainly: there would otherwise be no point in having two boxes.
Suppose you use the Function Wizard to construct =3+SQRT(4).  The
function result is 2 but the formula result is 5.


Tnx, Now I see the difference i.e. the Function result is of the 
function/formula the cursor is on whilst the result is the end answer.



In addition I have tried changing the formula without success to try
and reduce the multiple nested IF functions:
i.e.:
=sum((IF(0G4=$G$20,G4*$F$20,0),(IF($G$20G4=$J$20,(G4-$G$20)*$I$20+($F$20*$G$20),0),(IF($J$20G4=$M$20,(G4-$G$20-$J$20)*$L$20+(($G$20*$F$20)+($J$20*$I$20),0),(IF($M$20G4=$P$20,(G4-$G$20-$J$20-$M$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20),0),(IF(G4$P$20,((G4-$G$20-$J$20-$M$20-$P$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20)+($O$20*$P$20)),0)


I cannot see how the VLOOKUP function will help either as I am not
looking up values from cells, I am calculating a single value in H4
based in a variable input in G4.


But your formula refers to the values in row 20, which are thus the
values you need to look up.  Your IF conditions are your current way of
selecting the appropriate values from that row.


Have a peak at the uploaded spreadsheet on Nabble:


Will do - but I'll need more than a peek!


You will see that provided you enter a value for G4 below 600 the
correct answer is shown i.e. for 600 in G4 H4=750.


I think this may be more by luck that judgment, as the prices for your
first three ranges are identical - which doesn't test your formula
effectively.


This minor statement about the price ranges being identical caused me to 
test by starting the first at 1 and incrementing each range by one until 
4. Changing the value in G4 made NO change to my calculated field H4, 
so guess its time to revisit which formulae to use :(



There must be an answer and or my formula syntax is wrong unbeknown to
me.


Oh, the formula is certainly wrong.  There will be a right one ...


Still searching for the needle in the haystack...
--

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://www.fishhoeklionsclub.org.za being part of the worlds
largest non profit NGO




--
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] Calc Function Wizard - IF Result differences

[Hylton Conacher (ZR1HPC)]

Hi Paul,

On 21/10/13 00:26, Paul wrote:

Ok, so I just used a file hosting service found via Google search.
Here's the file I modified with my two solutions:

http://www.filedropper.com/electricity_1

It didn't work the first time or two that I tried to download the file
to check, not sure why, but it worked the third time, so maybe
persevere if it doesn't work first time for you.

When I tried to open the file again, I got told the security settings
prevented macros from running, and the cells that used the user
function to calculate your answer showed #VALUE, I think it was.


Ditto but ignored it. :(



You
can reduce the security settings to allow macros to run, of course, but
perhaps this is not the best way forward after all, although it is
still an elegant solution for some values of elegant.


There is always sending it as an email attachment :)


So if using just formulas, I liked Brian's use of MAX(0, value) instead
of my IF(value  0, value, 0), so I would probably substitute that
first off, to make the formulas I've used more legible. I would still
strongly recommend intermediate steps for each line of Brian's formula,
and then one simple SUM to get the final answer.

=MIN(G4;$A$72)*$B$71
+MAX(0;(MIN(G4;$A$73)-$A$72)*$B$72)
+MAX(0;(MIN(G4;$A$74)-$A$73)*$B$73)
+MAX(0;(G4-$A$74)*$B$74)


Also, while Brian has, as I understand it (without actually trying it
out), combined the information into a simple table, much as I first felt
should be done, this does mean the price brackets can't change per
month, while my solution does allow for this. Your choice.


I like Brian's MIN/MAX idea too but the price might well need to change 
on a month, it just depends on when the local authority increases prices.



Hope this helps, and feel free to ask me for any clarifications of what
I've done.


Time for coffee. My brain is getting frazzled  going thru each of the 
replies. I am very thankful for them though.


Hylton
P.S: Like the idea of Macro security.
--

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://www.fishhoeklionsclub.org.za being part of the worlds
largest non profit NGO




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

[libreoffice-users] Calc Function Wizard - IF Result differences

[Hylton Conacher (ZR1HPC)]

Hi,

Entering a formula into the Function Wizard in Calc shows  up two 
different answers in the 'Result' and 'Function Result' answer box.


If the editing cursor is placed in a certain section of the formula, 
only the 'Function Result' answer box holds the correct answer i.e. 
751.52004. Pressing 'OK' puts the 'Result' answer box value in the 
calculated cell, which unfortunately is the wrong answer i.e. 751.25.


I have looked at the help file to see if there was a difference between 
the Result answer boxes but found none.


The initial formula is:
=IF(0G4=$G$20,G4*$F$20,IF($G$20G4=$J$20,(G4-$G$20)*$I$20+($F$20*$G$20),IF($J$20G4=$M$20,(G4-$G$20-$J$20)*$L$20+(($G$20*$F$20)+($J$20*$I$20),IF($M$20G4=$P$20,(G4-$G$20-$J$20-$M$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20),IF(G4$P$20,((G4-$G$20-$J$20-$M$20-$P$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20)+($O$20*$P$20))

In addition I have tried changing the formula without success to try and 
reduce the multiple nested IF functions:


i.e.:
=sum((IF(0G4=$G$20,G4*$F$20,0),(IF($G$20G4=$J$20,(G4-$G$20)*$I$20+($F$20*$G$20),0),(IF($J$20G4=$M$20,(G4-$G$20-$J$20)*$L$20+(($G$20*$F$20)+($J$20*$I$20),0),(IF($M$20G4=$P$20,(G4-$G$20-$J$20-$M$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20),0),(IF(G4$P$20,((G4-$G$20-$J$20-$M$20-$P$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20)+($O$20*$P$20)),0)

The SUMIF function was also tried but my brain just gave up.

I cannot see how the VLOOKUP function will help either as I am not 
looking up values from cells, I am calculating a single value in H4 
based in a variable input in G4.


Have a peak at the uploaded spreadsheet on Nabble:

http://nabble.documentfoundation.org/file/n4077843/Electricity.ods

You will see that provided you enter a value for G4 below 600 the 
correct answer is shown i.e. for 600 in G4 H4=750. The moment G4 exceeds 
600, even by 1, there is a problem.


There must be an answer and or  my formula syntax is wrong unbeknown to me.

Help would be very much appreciated

Sincerely
Hylton
--

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://www.fishhoeklionsclub.org.za being part of the worlds
largest non profit NGO



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

[libreoffice-users] Calc IF problem

[Hylton Conacher (ZR1HPC)]

Hi,

I have a spreadsheet with four pricing ranges for a certain number of units.

G4 = 601
H4 = calculated by below formula = 751.25 but should be 751.52
.
F20 = 1.25
G20 = 150 i.e. 0-150 units
h20 = F*G

I20 = 1.25
J20 = 200 i.e. 150.0001 - 350 units
K20 = I*J

L20 = 1.25
M20 = 250 i.e. 350.0001 - 600 units
N20 = L*M

O20 = 1.52
P20 = 600 i.e. 600.0001 - infinity units
Q20 = O*P

The below formula calculates H4
=IF(0G4=$G$20,G4*$F$20,IF($G$20G4=$J$20,(G4-$G$20)*$I$20+($F$20*$G$20),IF($J$20G4=$M$20,(G4-$G$20-$J$20)*$L$20+(($G$20*$F$20)+($J$20*$I$20),IF($M$20G4$P$20,(G4-$G$20-$J$20-$M$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20),IF(G4=$P$20,((G4-$G$20-$J$20-$M$20-$P$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20)+($O$20*$P$20))

Is there a simpler way to achieve the right answer i.e. 751.52 as I have 
checked all the cell values and they all read as indicated here.


Where am I making a mistake?

Regards
Hylton
--

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://e-clubhouse.org/sites/fishhoek/index.php being part of the worlds
largest non profit NGO



--
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] Change Y axis labels in Calc chart

[Hylton Conacher (ZR1HPC)]

On 25/02/13 17:48, PeterTheBike wrote:

In this chart I want to use the names in the legend to be labels in the Y
axis instead of the numeric values. Is there a way this can be done please?

Sample_position_chart.ods
http://nabble.documentfoundation.org/file/n4039977/Sample_position_chart.ods


Have you received assistance with this issue yet?

Regards
Hylton

--
For unsubscribe instructions e-mail to: users+h...@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] Re: Calc scale formula conundrum/ logic confusion

[Hylton Conacher (ZR1HPC)]

Hi Tom,

On 14/02/13 16:41, Tom wrote:

Hi :)
Yes, of course you can ask different questions.  I just thought it was
exactly the same question again in which case my answer wouldn't have been
so wrong.


Thanks , I was hoping so.

snip


I wonder if there is a really good Maths forum for getting help with this
sort of thing.  It's a bit sideways from our normal ways of thinking so we
might not be much good at it.  A Maths forum like that might even be good
place for students or kids to get unusual but real-life questions to figure
out.  A chance for teachers to escape irrelevant ancient dusty text-books
and get real.


Whilst I agree a good Maths/Calc forum wouldn't be a bad idea, I think 
considering the software in use on LibreOffice Calc, then the users 
should mostly be able to find a resolution to their problem regarding 
formula syntax and entry. This problem is complex but hopefully once 
solved will help others with scale calculations.


Given the subscribers to the list are the folk who would be most likely 
to use the formula, its correct usage and syntax is imperative or 
perhaps it might indicate a problem with a formula in Calc's repertoire.



Of course an obvious answer is to avoid using a spreadsheet for this
initially and just use the back of an envelope or scrap of paper from
elsewhere.  When the question states the amount of unit i have left that
suggests an upper limit that can't be exceeded such that
Total amount of units allowed = Used + amount left
So i think i need to know which of these 2 following statements are true and
which isn't (or am i completely off-track?)
G2= Total amount allowed, the limit
or
G2 = amount left
In the question it seems to be being used in both ways at different times.
Well, to me anyway.
Scraps of paper are sometimes a handy quickie solution but if the 
results need graphing over a few readings, a spreadsheet really is 
better as I am sure you'll agree :) , mind you that said a database 
would be the best, but that gets REALLY complicated. :)


G2 in this example is the total electricity unit reading taken at the 
end of the month that indicated how many units are left or available. 
What I am aiming to work out is the currency value of those units(H2) 
using the same scale that is used to purchase the units.


Using my Purchase scale and changing the purchase value in c22 for e.g., 
I can see that I would need to layout ZAR574.61 to be allocated 150 
units on the first scale and 282.99 on the second scale totalling 
R574.60,5 - vat= R504.05 to give me the value of 433 units.


The question is to work out the value obtained in c22 auto-magically for 
H2, without using C22 to give the ZAR value of G2 or 432.9 units.


The formula in H2 incorporates the first two scales i.e. 
`=IF(G2=$G$18,$H$18,G2*$F$18)+IF(G2-$G$18=$J$18,$K$18,(G2-$G$18)*$I$18)'
Provided G2 is 433 the correct answer for H2 of R504.05 is received. 
HOWEVER using the same formula, if you make G2 =1. The answer in H2 
should be R1.13 yet shows an answer of R -174.85?


As the above formula only incorporates 2 of the 3 scales, the full 
formula is 
`=IF(G2=$G$18,$H$18,(G2*$F$18))+IF(G2-$G$18)=$J$18,$K$18,(G2-$G$18)*$I$18)+IF(G2-$G$18-$J$18)=$M$18,$N$18+(G2-$G$18-$J$18-$M$18)*$L$19,(G2-$G$18-$J$18-$M$18)*$L$19)' 
yet for some reason I am getting a 508 error yet I have checked the 
number of brackets and all is OK?



zr1hpc wrote

Using Calc 3.4.5 on openSuse 11.2

I am trying to work out the currency value of the amount of electricity
units I have left at the end of the month (G2).

This same formula will be used for the other 11 months of the year with
only the G2 reference changing to G3, G4...etc.
For those who wish to stretch their grey matter as mine just ain't
stretching any more.

The pricing scale the energy supplier has given me is:

Unit Range  Value per unit
0-150   1.1320
150.01 - 6001.1811
600.01 -1.4018

Therefore the first 150 units are priced at 1.132 ea. any units still
available need to be calculated at the next rate of 1.1811, but only up to
449.90 units at the second rate. Anything more than that must be at the
highest rate of 1.4018.

Assuming my unit balance of G2 = 433 I can work out the currency value of
G2 by following the following logic, and using the scale above:

433  150 therefore the initial 150 units need to be priced at 1.1320 i.e.
150* 1.132= ZAR169.80. To this the balance of the units over 150 need to
be multiplied by the next rate of 1.1811 i.e. 433-150= 283. Since 283 is
below the max of 449.9 units allowed for this scale, therefore 283*1.1811=
ZAR334.25. The third scale is not needed as all the units have been
accounted for, however it must be available should G2 exceed 600 units
e.g. taking 601 units; the first 150 would be costed at 1.132, the next
449.9 costed at 1.1811 and the balance of 1.1 units costed at 1.4018 per
unit.

Therefore total value of 433 units is:
   150*1.1320 = 169.80
+ 283*1.1811 = 

Re: [libreoffice-users] Calc scale formula conundrum/ logic confusion

[Hylton Conacher (ZR1HPC)]

Hi Tom, Brian,

On 14/02/13 10:52, Brian Barker wrote:

At 07:45 14/02/2013 +, Tom Davies wrote:

Someone else asked a very similar question about a month ago ...


That someone else was (not surprisingly) the same person!


... and i think someone created a neat calculation or spreadsheet to
help figure it out.  Now the trick is trying to find it in the archives!


He probably doesn't need to ...


Yes its the same person ;) I had hoped I was able to ask more than one 
question about a problem, I mean it is even a different subject/thread.


The value required is a currency value gathered from the sums of the 
different IF values according to a scale.


I have the replies from the previous discussion we had via the list but 
the same formula does not seem to work in 'reverse'? I believe I have 
the right formula, but I think the logic in the IF functions needs checking.


Its not a 'Do my homework please', its a genuine logic oops I think.

Dare I say, that perhaps you also won't spot the error and let me know 
about it. I am sure you would be able to spot the error though? :)


Regards
Hylton
--

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://e-clubhouse.org/sites/fishhoek/index.php being part of the worlds
largest non profit NGO




--
For unsubscribe instructions e-mail to: users+h...@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] Calc SUM Error 502 - How to add results of three IF formulae? - SOLVED

[Hylton Conacher (ZR1HPC)]

A BIG THANK YOU to Brian Barker and Johnny Rosenberg as being 
instrumental in solving the problem by giving me the knowledge I needed 
to sort out the issue.


Bravo guys and Thanks again

Hylton
--

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://e-clubhouse.org/sites/fishhoek/index.php being part of the worlds
largest non profit NGO




--
For unsubscribe instructions e-mail to: users+h...@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] Calc SUM Error 502 - How to add results of three IF formulae?

[Hylton Conacher (ZR1HPC)]

Brian,

Thank you, and sorry for the double post of this message.

The calculation you provided worked perfectly after correctly 
identifying the necessary cells to be used. funny though the same 
formula does not work just 2 columns to the left? See below and I am 
sure identify the cause as I have spent too many hours trying to solve it.


On 02/02/13 17:22, Brian Barker wrote:

At 16:35 02/02/2013 +0200, Hylton Conacher wrote:

I have a formula as corrected below(except spaces):

=IF(B6C2;D2;0) + IF(B6-C2C3;D3;0) + IF(B6-C2-C3C4;(B6-C2-C3)*B4;0)


I did not realise that the formula could be simplified that much :|
The extra brackets and 'SUM's were to keep to the BODMAS(Brackets OF 
Division Multiplication Addition Subtraction) maths rule as I did not 
want the greater than sign to only work on a single cell but on the sum 
of a few cells.


The formula worked flawlessly and gave me the same answer when compared 
to a calculator.


HOWEVER,

Needing to use the same scale and rules on a different column gave me an 
incorrect answer?


I have amalgamated the data sheets onto a single sheet so many of the 
cell references are the same, except the cell I need to use as the base.


Using a copy and paste of the formulae below:
The working one:
Cell 
J2=IF(I2G18,H18,0)+IF(I2-G18J18,K18,0)+IF(I2-G18-J18M18,(I2-G18-J18)*L18,0)


The non-working one:
Cell 
H2=IF(G2G18,H18,0)+IF((G2-G18)J18,K18,0)+IF((G2-G18-J18)M18,(G2-G18-J18)*L18,0)


As you can see the block being used has changed from I2 to G2, the rest 
of the formula is much the same, bar the addition of an odd bracket 
pair. The Non-working formula provides the same answer for H2 whether 
the extra bracket pairs are there or not.


FYI:G2=433 and is manually entered
I2=1349.31 with formula =SUM(C2,D2)-G2
C2=1479
D2=303.31 with formula =O19
O19=303.31 with formula =SUM(G19:G20,J19:J20, M19:M20)
The answers I seek is for H2.
Using the non-working formula. the only answer I have been able to get 
is R 169.80 (which is =H18). This is incorrect as according to the 
sliding scale the answer should be R 504.05.


What is wrong with the non-working formula as it does not seem to 
continue past the first IF argument?


I have attached the file and hope it will appear on Nabble, as even 
after registering for Nabble, and confirming membership, I saw no 
instructions/options to upload files. Maybe its automatic? Google was as 
usual cryptic.


--

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://e-clubhouse.org/sites/fishhoek/index.php being part of the worlds
largest non profit NGO




--
For unsubscribe instructions e-mail to: users+h...@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

[libreoffice-users] Calc SUM Error 502 - How to add results of three IF formulae?

[Hylton Conacher (ZR1HPC)]

Hi,

I have a formula below which is giving me an Err: 502 on Calc 3.4.5 when 
I try and calculate a currency total using a sliding scale.


=SUMIF(b6c2,d2,0),(IF(b6-c2)c3,d3,0),(IF(b6-c2-c3)c4,sum((b6-c2)-c3)*b4,0

Each IF statement outs only a single answer and all I want to do is SUM 
the 'IF' answers


I have Googled and tried the above IF formula in a few ways, but despite 
giving me a correct answer after two IF's, adding the third IF gives me 
a 502 error which wrecks the entire formula i.e. removing 3rd IF gives 
no answer. Pasting the above formula back into Calc turns many of the 
commas(,) into a tilde (~).


My data is so:

Units to convert to currency value(B6): 1349.31
First avail 150 units(C2) are $1.1311 each
The next avail 450 units(C3) are $1.811 each
Any units more than 600(C4) cost $1.4018 each

Work out the currency value of the units available i.e. B6 in a single cell?

This follows on from my earlier posting about calculating the total unit 
value obtained when units are purchased on the same sliding scale with a 
subject of 'Calc sliding scale formulae?' whose spreadsheet you will 
find on Nabble.


Help appreciated after you have stretched the brain matter. Does the 
same happen in newer versions of Calc?


Regards
Hylton
--

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://e-clubhouse.org/sites/fishhoek/index.php being part of the worlds
largest non profit NGO



--
For unsubscribe instructions e-mail to: users+h...@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

[libreoffice-users] Re: Calc sliding scale formulae? - SOLVED Part 1

[Hylton Conacher (ZR1HPC)]

On 10/01/13 17:38, Hylton Conacher (ZR1HPC) wrote:

Hi,

I am using Calc 3.4.5 on openSuSe Linux 11.2

I am working on a spreadsheet to cater for the monthly measurement of
household electricity consumption, new unit purchase and costing of used
units.

The electricity supplier gives me the first 150 units of electricity at
R1.29 per unit. The next 350 units are costed at R1.35 per unit.
Thereafter the units cost me R1.60 per unit. The monetary values per set
amount of unit changes when the electricity provider increases prices
i.e. the first 150 units might increase to R1.50 per purchased unit, the
next 350 units might be costed at R1.55 and thereafter units will cost
R2.00.

Each time I purchase electricity the units I am allocated are based on
the above sliding scale starting from 0.

How can I work out the number of units I will receive for a given
currency value i.e. R2000.00? i.e. x @ R1.29, y @ R1.35 and z @ R1.60=
R2000

In addition how could I work out the value of units consumed using the
above scale?

I am assuming the R values in the scale will change over time and these
can each be averaged to calculate the cost of units used, but the
problem is still to work out the formula.

Anybody have an idea if this is possible and where to start?

I am investigating results found on Google but would appreciate a
pointer or three.


The calculation of the number of unit of electricity across a sliding 
scale has been solved.


A member of this mailing list suggested using the IF formula. With some 
formula tweaking with , it proved to be the right answer. Tnx to that 
member. I only wish they had corresponded via the mailing list as 
opposed to directly, as perhaps there would have been greater learning 
for all subscribers.


I have attached the completed to date file, via BCc email, for those who 
showed an interest in solving the problem, as I know the ML doesn't 
accept attachments. . I anyone else wants it, feel free to contact me 
directly at hyl...@conacher.co.za and request it.


I have tested the Purchases tab in the spreadsheet using LibreOffice 
3.4.5 and using common currency values, it works.


Next are the other tabs etc.

Thanks again to those that helped
Hylton
--

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://e-clubhouse.org/sites/fishhoek/index.php being part of the worlds
largest non profit NGO




--
For unsubscribe instructions e-mail to: users+h...@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

Fwd: Re: [libreoffice-users] Calc sliding scale formulae?

[Hylton Conacher (ZR1HPC)]

This message never seemed to get thru :(


 Original Message 
Subject: Re: [libreoffice-users] Calc sliding scale formulae?
Date: Fri, 18 Jan 2013 14:00:19 +0200
From: Hylton Conacher (ZR1HPC) hyl...@conacher.co.za
Reply-To: LibreOffice Users users@global.libreoffice.org
To: users@global.libreoffice.org

It has been privately suggested to me that I want my work done for me,
however, whilst I have had theory from the list on how the formulae
should work, I have not had anyone indicating how I can get Calc to only
use a particular formula range until its result exceeds the amount
allowed for that formula, and to then use a different range for the
remaining value.

My email below indicates the scales used, and please see below for the
calculation of the number of units per pricing bracket obtained, using a
calculator.

What is the main problem is getting Calc to only use a formula up until
a max value before moving onto the next formula.

Assuming I purchased R2000 of the unit(electricity). As per the scale I
would get 150 units at R1.29 which would equate to R193.50, which leaves
a further R1806.50 that was used to buy electricity, but at different
rate per unit.

Taking the remaining R value and dividing it by 1.35(being the next
value in the sliding scale) provides an answer of 1338.1 units. Only 450
units are allowed to be purchased at this price however, so
450*1.35=R607.50.

So all in all we have only spent 193.50 and 607.50 = R801.00 out of R2000.

Therefore the remaining R1199.00 divided by 1.60(being the next value in
the sliding scale) = 749.38 units.

Therefore our R2000 purchased 150+450+749.38=1349.38 units.

I trust the above better explains what I need Calc to do. Perhaps it is
not possible, but I challenge you all to solve this one.


On 01/10/2013 10:38 AM, Hylton Conacher (ZR1HPC) wrote:

Hi,

I am using Calc 3.4.5 on openSuSe Linux 11.2

I am working on a spreadsheet to cater for the monthly measurement of
household electricity consumption, new unit purchase and costing of
used units.

The electricity supplier gives me the first 150 units of electricity
at R1.29 per unit. The next 450 units are costed at R1.35 per unit.
Thereafter the units cost me R1.60 per unit. The monetary values per
set amount of unit changes when the electricity provider increases
prices i.e. the first 150 units might increase to R1.50 per purchased
unit, the next 450 units might be costed at R1.55 and thereafter units
will cost R2.00.

Each time I purchase electricity the units I am allocated are based on
the above sliding scale starting from 0.

How can I work out the number of units I will receive for a given
currency value i.e. R2000.00? i.e. x @ R1.29, y @ R1.35 and z @ R1.60=
R2000

In addition how could I work out the value of units consumed using the
above scale?

I am assuming the R values in the scale will change over time and
these can each be averaged to calculate the cost of units used, but
the problem is still to work out the formula.

Anybody have an idea if this is possible and where to start?

I am investigating results found on Google but would appreciate a
pointer or three.

Thanks
Hylton

If I understand your problem, you have a general equation of ax + by +
cz = d where a = 150, b = 350, c is currently unknown, d = 2000 (or any
value you choose), x = 1.29, y = 1.35, and c = 1.60. Solving for c, you
get c = (d - ax - by)/z.

Average cost per unit, ave, is ave = (a + b + c)/d., if I understand
your question The value c must be calculated prior to this step. In
terms of a Calc formula the c value would reference the cell with the
previous calculation.

In your spreadsheet I would set a table with named cells for each value
(a1 is Base_Rate and is 1.29, etc) so you can easily change the values
and see the values used.

--

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://e-clubhouse.org/sites/fishhoek/index.php being part of the worlds
largest non profit NGO






--
For unsubscribe instructions e-mail to: users+h...@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] Calc sliding scale formulae?

[Hylton Conacher (ZR1HPC)]

Hi,

On 18/01/13 18:04, Tom Davies wrote:

Hi :)
That was my approach too but then i forgot to send my spreadsheet to Hylton and 
by the time i remembered Jay had produced an answer that took it to the next 
level.  I think it's good to see these sorts of problems occasionally because 
it pushes us outside of our normal thought processes.
Regards from
Tom :)


I still await that spreadsheet Tom :)

I hope the further explanation I sent the list makes it thru this time.

Please forward it to me via pvt email.

Tnx

Hylton





From: Dan Lewis elderdanle...@gmail.com
To: users@global.libreoffice.org
Sent: Friday, 18 January 2013, 15:34
Subject: Re: [libreoffice-users] Calc sliding scale formulae?

   Perhaps the private suggestion was correct. Perhaps your understanding 
of Algebra is not as good as it needs to be. After all this is an Algebra 
problem.
   Now for some questions:
1. How much will 1 to 150 units cost? What is the formula to determine this?
2. How much will 151 to 599 units cost you? What is the formula for this?
3. How much will 600 units cost? What is the formula to determine this?
4. How much will 601 or more units cost you? What is the formula for this?
5. What is the cost per unit for any given number of units? What is the formula 
for this? (Hint: this is a very simple division problem.)
Hint: I have asked you for 5 formula, and that is also the number of cells you 
will need: one cell per formula. You will be using a result in some cells in 
the formula in another cell.

--Dan

On 01/18/2013 07:00 AM, Hylton Conacher (ZR1HPC) wrote:

It has been privately suggested to me that I want my work done for me, however, 
whilst I have had theory from the list on how the formulae should work, I have 
not had anyone indicating how I can get Calc to only use a particular formula 
range until its result exceeds the amount allowed for that formula, and to then 
use a different range for the remaining value.

My email below indicates the scales used, and please see below for the 
calculation of the number of units per pricing bracket obtained, using a 
calculator.

What is the main problem is getting Calc to only use a formula up until a max 
value before moving onto the next formula.

Assuming I purchased R2000 of the unit(electricity). As per the scale I would 
get 150 units at R1.29 which would equate to R193.50, which leaves a further 
R1806.50 that was used to buy electricity, but at different rate per unit.

Taking the remaining R value and dividing it by 1.35(being the next value in 
the sliding scale) provides an answer of 1338.1 units. Only 450 units are 
allowed to be purchased at this price however, so 450*1.35=R607.50.

So all in all we have only spent 193.50 and 607.50 = R801.00 out of R2000.

Therefore the remaining R1199.00 divided by 1.60(being the next value in the 
sliding scale) = 749.38 units.

Therefore our R2000 purchased 150+450+749.38=1349.38 units.

I trust the above better explains what I need Calc to do. Perhaps it is not 
possible, but I challenge you all to solve this one.


On 01/10/2013 10:38 AM, Hylton Conacher (ZR1HPC) wrote:

Hi,

I am using Calc 3.4.5 on openSuSe Linux 11.2

I am working on a spreadsheet to cater for the monthly measurement of
household electricity consumption, new unit purchase and costing of
used units.

The electricity supplier gives me the first 150 units of electricity
at R1.29 per unit. The next 450 units are costed at R1.35 per unit.
Thereafter the units cost me R1.60 per unit. The monetary values per
set amount of unit changes when the electricity provider increases
prices i.e. the first 150 units might increase to R1.50 per purchased
unit, the next 450 units might be costed at R1.55 and thereafter units
will cost R2.00.

Each time I purchase electricity the units I am allocated are based on
the above sliding scale starting from 0.

How can I work out the number of units I will receive for a given
currency value i.e. R2000.00? i.e. x @ R1.29, y @ R1.35 and z @ R1.60=
R2000

In addition how could I work out the value of units consumed using the
above scale?

I am assuming the R values in the scale will change over time and
these can each be averaged to calculate the cost of units used, but
the problem is still to work out the formula.

Anybody have an idea if this is possible and where to start?

I am investigating results found on Google but would appreciate a
pointer or three.

Thanks
Hylton

If I understand your problem, you have a general equation of ax + by +
cz = d where a = 150, b = 350, c is currently unknown, d = 2000 (or any
value you choose), x = 1.29, y = 1.35, and c = 1.60. Solving for c, you
get c = (d - ax - by)/z.

Average cost per unit, ave, is ave = (a + b + c)/d., if I understand
your question The value c must be calculated prior to this step. In
terms of a Calc formula the c value would reference the cell with the
previous calculation.

In your spreadsheet I would set a table

Re: [libreoffice-users] Calc sliding scale formulae?

[Hylton Conacher (ZR1HPC)]

It has been privately suggested to me that I want my work done for me, 
however, whilst I have had theory from the list on how the formulae 
should work, I have not had anyone indicating how I can get Calc to only 
use a particular formula range until its result exceeds the amount 
allowed for that formula, and to then use a different range for the 
remaining value.


My email below indicates the scales used, and please see below for the 
calculation of the number of units per pricing bracket obtained, using a 
calculator.


What is the main problem is getting Calc to only use a formula up until 
a max value before moving onto the next formula.


Assuming I purchased R2000 of the unit(electricity). As per the scale I 
would get 150 units at R1.29 which would equate to R193.50, which leaves 
a further R1806.50 that was used to buy electricity, but at different 
rate per unit.


Taking the remaining R value and dividing it by 1.35(being the next 
value in the sliding scale) provides an answer of 1338.1 units. Only 450 
units are allowed to be purchased at this price however, so 
450*1.35=R607.50.


So all in all we have only spent 193.50 and 607.50 = R801.00 out of R2000.

Therefore the remaining R1199.00 divided by 1.60(being the next value in 
the sliding scale) = 749.38 units.


Therefore our R2000 purchased 150+450+749.38=1349.38 units.

I trust the above better explains what I need Calc to do. Perhaps it is 
not possible, but I challenge you all to solve this one.


 On 01/10/2013 10:38 AM, Hylton Conacher (ZR1HPC) wrote:

Hi,

I am using Calc 3.4.5 on openSuSe Linux 11.2

I am working on a spreadsheet to cater for the monthly measurement of
household electricity consumption, new unit purchase and costing of
used units.

The electricity supplier gives me the first 150 units of electricity
at R1.29 per unit. The next 450 units are costed at R1.35 per unit.
Thereafter the units cost me R1.60 per unit. The monetary values per
set amount of unit changes when the electricity provider increases
prices i.e. the first 150 units might increase to R1.50 per purchased
unit, the next 450 units might be costed at R1.55 and thereafter units
will cost R2.00.

Each time I purchase electricity the units I am allocated are based on
the above sliding scale starting from 0.

How can I work out the number of units I will receive for a given
currency value i.e. R2000.00? i.e. x @ R1.29, y @ R1.35 and z @ R1.60=
R2000

In addition how could I work out the value of units consumed using the
above scale?

I am assuming the R values in the scale will change over time and
these can each be averaged to calculate the cost of units used, but
the problem is still to work out the formula.

Anybody have an idea if this is possible and where to start?

I am investigating results found on Google but would appreciate a
pointer or three.

Thanks
Hylton

If I understand your problem, you have a general equation of ax + by +
cz = d where a = 150, b = 350, c is currently unknown, d = 2000 (or any
value you choose), x = 1.29, y = 1.35, and c = 1.60. Solving for c, you
get c = (d - ax - by)/z.

Average cost per unit, ave, is ave = (a + b + c)/d., if I understand
your question The value c must be calculated prior to this step. In
terms of a Calc formula the c value would reference the cell with the
previous calculation.

In your spreadsheet I would set a table with named cells for each value
(a1 is Base_Rate and is 1.29, etc) so you can easily change the values
and see the values used.

--

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://e-clubhouse.org/sites/fishhoek/index.php being part of the worlds
largest non profit NGO




--
For unsubscribe instructions e-mail to: users+h...@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] Calc sliding scale formulae?

[Hylton Conacher (ZR1HPC)]

Jay,

Thank you. Your input is appreciated. My comments below.

On 10/01/13 19:36, Jay Lozier wrote:

On 01/10/2013 10:38 AM, Hylton Conacher (ZR1HPC) wrote:

Hi,

I am using Calc 3.4.5 on openSuSe Linux 11.2

I am working on a spreadsheet to cater for the monthly measurement of
household electricity consumption, new unit purchase and costing of
used units.

The electricity supplier gives me the first 150 units of electricity
at R1.29 per unit. The next 350 units are costed at R1.35 per unit.
Thereafter the units cost me R1.60 per unit. The monetary values per
set amount of unit changes when the electricity provider increases
prices i.e. the first 150 units might increase to R1.50 per purchased
unit, the next 350 units might be costed at R1.55 and thereafter units
will cost R2.00.

Each time I purchase electricity the units I am allocated are based on
the above sliding scale starting from 0.

How can I work out the number of units I will receive for a given
currency value i.e. R2000.00? i.e. x @ R1.29, y @ R1.35 and z @ R1.60=
R2000

In addition how could I work out the value of units consumed using the
above scale?

I am assuming the R values in the scale will change over time and
these can each be averaged to calculate the cost of units used, but
the problem is still to work out the formula.

Anybody have an idea if this is possible and where to start?

I am investigating results found on Google but would appreciate a
pointer or three.

Thanks
Hylton

If I understand your problem, you have a general equation of ax + by +
cz = d where a = 150, b = 350, c is currently unknown, d = 2000 (or any
value you choose), x = 1.29, y = 1.35, and c = 1.60. Solving for c, you
get c = (d - ax - by)/z.


Correct


Average cost per unit, ave, is ave = (a + b + c)/d., if I understand
your question The value c must be calculated prior to this step. In
terms of a Calc formula the c value would reference the cell with the
previous calculation.


What if there was no previous calculation i.e. first month of year?


In your spreadsheet I would set a table with named cells for each value
(a1 is Base_Rate and is 1.29, etc) so you can easily change the values
and see the values used.


My problem is letting the formula know on which scale to work on i.e. 
ax, by,cz.


To explain:

The rates are indicated like so:

Units   Cost per unit
0 - 150 1.29
150.1 - 600 1.35
600.1 - infinity1.60

Single units of any tier can be bought provided the previous tier 
pricing has been used i.e. 601 units would cost R811.60.


So given a currency value purchase of R2000 would equate to:
150 units @ 1.29 =  R 193.50
450 units @ 1.35 =  R 607.50 i.e. =SUM(R2k, -193.50) is 1.35 buy
749.38 units @ 1.60 =   R 1199.00 i.e. =SUM(R2k, -193.50, -607.5)/1.60

The above all worked out via calculator, BUT how can I get Calc to do it 
i.e. how is Calc going to know to only calculate on a +ve value meaning 
there is still money available for the purchase of electricity?


Further to this is working out the cost of the electricity used using 
the same sliding scale having only the number of units units used i.e. 
500 units usage?


It seems to be a case of an IF, Then argument i.e. if the units listed 
are greater than what the scale allows then the remaining units must use 
the next unit of pricing up the scale.


Anyone who wants to play can request my spreadsheet. Pick a new sheet 
and play to your hearts content. Of sending answers off list to to me is 
also OK. I'll sum them all up and give a solution on the list, if one is 
worked out.


Thanks again
Hylton
--

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://e-clubhouse.org/sites/fishhoek/index.php being part of the worlds
largest non profit NGO




--
For unsubscribe instructions e-mail to: users+h...@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

[libreoffice-users] Calc sliding scale formulae?

[Hylton Conacher (ZR1HPC)]

Hi,

I am using Calc 3.4.5 on openSuSe Linux 11.2

I am working on a spreadsheet to cater for the monthly measurement of 
household electricity consumption, new unit purchase and costing of used 
units.


The electricity supplier gives me the first 150 units of electricity at 
R1.29 per unit. The next 350 units are costed at R1.35 per unit. 
Thereafter the units cost me R1.60 per unit. The monetary values per set 
amount of unit changes when the electricity provider increases prices 
i.e. the first 150 units might increase to R1.50 per purchased unit, the 
next 350 units might be costed at R1.55 and thereafter units will cost 
R2.00.


Each time I purchase electricity the units I am allocated are based on 
the above sliding scale starting from 0.


How can I work out the number of units I will receive for a given 
currency value i.e. R2000.00? i.e. x @ R1.29, y @ R1.35 and z @ R1.60= R2000


In addition how could I work out the value of units consumed using the 
above scale?


I am assuming the R values in the scale will change over time and these 
can each be averaged to calculate the cost of units used, but the 
problem is still to work out the formula.


Anybody have an idea if this is possible and where to start?

I am investigating results found on Google but would appreciate a 
pointer or three.


Thanks
Hylton
--

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://e-clubhouse.org/sites/fishhoek/index.php being part of the worlds
largest non profit NGO



--
For unsubscribe instructions e-mail to: users+h...@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] SOLVED - pdf creation issue on a single file?

[Hylton Conacher (ZR1HPC)]

My thanks go to Roger Barker and Miguel Angel.

On 28/07/12 15:58, webmaster-Kracked_P_P wrote:


What is you OS?
Windows, Linux, Mac?  Which version.
What version of LO are you using?  Your statement of using 3.3 since
3.4.5 is confusing.  Are you using 3.5.5, 3.5.4, 3.5.3?

while someone tries to figure out the issue, if you use Windows, please
download doPDF - free PDF printer software.  Linux - CUPS-PDF.

With the PDF printer installed print the document to the PDF printer,
like you would a paper printer.

Then see if creating a PDF that way works.  If it does, then it is the
PDF Exporting option.  If not it may be in some generic printing option
or some other issue.

I tend to use a PDF printer more than Exporting to PDF, since I print
PDFs from many different packages and forget to use the Export
option.  I have PDF printing as my default printer.




On 07/28/2012 09:42 AM, Hylton Conacher (ZR1HPC) wrote:

Hi,

I have an rainfall.ods document that has 24 tabs. Two of the tabs
contain figures and the balance contain graphs of the figures.

I recently experienced a problem, it would seem after my last tab
addition, that prevents me from creating a PDF file of the selected
sheets(12).

The PDF export works fine on other files regarding `Export as PDF',
why not on this file?

I was using LibreOffice 3.3 and have since updated to LibreOffice 3.4.5
OOO340m1 (Build:1505). Unfortunately the newer version is unable to
`Export as PDF' on the selected TABS either!!

What could be wrong? There are no special characters in the sheet
names bar possibly a space.

I will gladly send this file to anyone who can assist as I need to
create and post to a public user mailing list a PDF at the end of the
month.

Help is much appreciated
Hylton






--

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://www.fishhoeklionsclub.org.za being part of the worlds largest NGO




--
For unsubscribe instructions e-mail to: users+h...@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] pdf creation issue on a single file?

[Hylton Conacher (ZR1HPC)]

Brian and Miguel,

On 31/07/12 03:03, Brian Barker wrote:

At 15:42 28/07/2012 +0200, Hylton Conacher wrote:

I have an rainfall.ods document that has 24 tabs. Two of the tabs
contain figures and the balance contain graphs of the figures.  I
recently experienced a problem, it would seem after my last tab
addition, that prevents me from creating a PDF file of the selected
sheets(12).  The PDF export works fine on other files regarding
`Export as PDF', why not on this file?


At 18:12 29/07/2012 +0200, Hylton Conacher wrote:

Further to this issue it would seem that only the last created graph
is being exported (but this is NOT the last selected Tab) as when I
view the exported PDF, only a single page shows, instead of approx 12.


Easy - now that I've looked at the document.  You have a print range
defined on sheet 20 of 24 (Ann Monthly Comp).  You may be assuming
that - since this print range is set to entire sheet - that it will
have no effect on the output, but that is not so.  If you have a print
range set anywhere in your document, it affects the whole document, and
no other sheets will print except where they, too, have print ranges
defined.  And print ranges, it seems, apply equally to Export as PDF as
to Print itself.

Remove the print range from the rogue sheet (Format | Print Ranges  |
Remove) and everything is hunky-dory.


My heartfelt thanks on resolving the issue of non export of tabs in a 
PDF. Your solution and instructions on how to remove worked flawlessly. 
I have no idea how the print range got there in the first place as I 
have never used them and only upgraded from LO 3.3 to LO 3.4.5


What made you or how did you check for print ranges i.e. what alerted 
you that that might be causing the problem?


Thank you again and I will definitely pass this onto bug 32771 for 
comment #13 and others.


May your keyboards always type true

Regards
Hylton
--

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://www.fishhoeklionsclub.org.za being part of the worlds largest NGO




--
For unsubscribe instructions e-mail to: users+h...@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] pdf creation issue on a single file?

[Hylton Conacher (ZR1HPC)]

On 29/07/12 23:50, MiguelAngel wrote:

El 29/07/12 18:12, Hylton Conacher (ZR1HPC) escribió:

Hi Miguel,

On 28/07/12 18:07, MiguelAngel wrote:

El 28/07/12 15:42, Hylton Conacher (ZR1HPC) escribió:

Hi,

I have an rainfall.ods document that has 24 tabs. Two of the tabs
contain figures and the balance contain graphs of the figures.

I recently experienced a problem, it would seem after my last tab
addition, that prevents me from creating a PDF file of the selected
sheets(12).

The PDF export works fine on other files regarding `Export as PDF', why
not on this file?

I was using LibreOffice 3.3 and have since updated to LibreOffice 3.4.5
OOO340m1 (Build:1505). Unfortunately the newer version is unable to
`Export as PDF' on the selected TABS either!!

What could be wrong? There are no special characters in the sheet names
bar possibly a space.

I will gladly send this file to anyone who can assist as I need to
create and post to a public user mailing list a PDF at the end of the
month.

Help is much appreciated
Hylton


Thank you for commenting on the OP's initial problem, and not a Linux
Windows word flame-war that bears no relation to the subject line,
despite some valid suggestions being valid.


Verify you have not marked in the same tab the option:
Embed OpenDocument file.


How could I check this via the GUI? I recreated the last graph and made
sure there were no 'embeds' of any kind. Could non-standard fonts i.e.
not Arial, also play a part?

Further to this, issue it would seem that only the last created graph is
being exported( but this is NOT the last selected Tab)  as when I view
the exported PDF, only a single page shows, instead of approx 12.

Hylton


Hi Hylton,
sure I have not explained with the required detail.

The option is in:
Menu/File/Export as PDF/[General] - General - Embed OpenDocument file.


This option is not available on my version of LO 3.4.5 only 'Embed 
Standard fonts' is available, which is not selected.


If this option is enable the only chance is to export the complete
document.

This option embed the ods document in the pdf file, and if you later
open this pdf in LibreOffice the ods document is open. IMO a very
interesting option to share a document, an accurate view plus an edit
file option.

About other non related things with your question, you are right, IMHO
people would be better answer what it is asked, and if they like to
comment about other things start their own thread.
Too much blah blah blah, with few helping answers.


Thanks for the idea, but alas the problem persists.

I have Cc'd you and include the file as an attachment, that I know will 
be stripped by TDF for the mailing list but you should receive it. Once 
you open the file in Calc, select all the tabs from July to General, 
Choose File: Export to PDF, Choose only selection, save the file on your 
system and then re open it with Adobe Acrobat to check all pages exported.


If they did what version of LO are you running?

Regards
Hylton
--

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://www.fishhoeklionsclub.org.za being part of the worlds largest NGO




--
For unsubscribe instructions e-mail to: users+h...@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] pdf creation issue on a single file?

[Hylton Conacher (ZR1HPC)]

On 28/07/12 15:58, webmaster-Kracked_P_P wrote:

Replies below


What is you OS?
Windows, Linux, Mac?  Which version.
What version of LO are you using?  Your statement of using 3.3 since
3.4.5 is confusing.  Are you using 3.5.5, 3.5.4, 3.5.3?

while someone tries to figure out the issue, if you use Windows, please
download doPDF - free PDF printer software.  Linux - CUPS-PDF.

With the PDF printer installed print the document to the PDF printer,
like you would a paper printer.

Then see if creating a PDF that way works.  If it does, then it is the
PDF Exporting option.  If not it may be in some generic printing option
or some other issue.

I tend to use a PDF printer more than Exporting to PDF, since I print
PDFs from many different packages and forget to use the Export
option.  I have PDF printing as my default printer.


The OS is openSuSe 11.2
LibreOffice version is as stated i.e. LibreOffice 3.4.5 OOO340m1 
(Build:1505)


A newer edition of LO has not appeared via my Yast software manager, 
despite there being a few updates.




On 07/28/2012 09:42 AM, Hylton Conacher (ZR1HPC) wrote:

Hi,

I have an rainfall.ods document that has 24 tabs. Two of the tabs
contain figures and the balance contain graphs of the figures.

I recently experienced a problem, it would seem after my last tab
addition, that prevents me from creating a PDF file of the selected
sheets(12).

The PDF export works fine on other files regarding `Export as PDF',
why not on this file?

I was using LibreOffice 3.3 and have since updated to LibreOffice 3.4.5
OOO340m1 (Build:1505). Unfortunately the newer version is unable to
`Export as PDF' on the selected TABS either!!

What could be wrong? There are no special characters in the sheet
names bar possibly a space.

I will gladly send this file to anyone who can assist as I need to
create and post to a public user mailing list a PDF at the end of the
month.

Help is much appreciated
Hylton






--

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://www.fishhoeklionsclub.org.za being part of the worlds largest NGO




--
For unsubscribe instructions e-mail to: users+h...@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] pdf creation issue on a single file?

[Hylton Conacher (ZR1HPC)]

 another version of MSO. LO
is not the default for most Linux versions. MSO is the package most
Windows user know as their only office package option.  We need to get
the word out there to these Windows users that LO is there and we want
them to try it.  We do not want them to think we are stuck-up
egg-headed Linux users that do not accept that there is anything wrong
with them using Windows.   I prefer to use Ubuntu, but I do not think
most Windows users will be able to switch.  I know of some Linux users
who had to make one of their system run Windows due to the fact that
need to use a certain type of software and Linux does not have the
software they need.  One young fellow needs some really good
post-production video editing software.  He prefers Linux for
everything, but he just cannot find the software he needs for Linux.

So
LO runs on many different OSs.  Then be a support to all of those
different OS users for LO and not blame anything on their choice of
OS.  IF we do, then we might as well start telling people that their
version of Linux is laughable over our version of Linux. Now where
would you think that would get us?  Nothing but trouble and in-fighting.


On 07/28/2012 11:40 AM, Lynne Stevens wrote:


*Don't you have the ability to print to a PDF file ?  Linux gives me
the choice to print the file or print to a PDF file and asks where to
save it and a name for it . . You must be a windows user LOL ! I
really feel sorry for windows users ROFLMAO

On 07/28/2012 06:42 AM, Hylton Conacher (ZR1HPC) wrote:

Hi,

I have an rainfall.ods document that has 24 tabs. Two of the tabs
contain figures and the balance contain graphs of the figures.

I recently experienced a problem, it would seem after my last tab
addition, that prevents me from creating a PDF file of the selected
sheets(12).

The PDF export works fine on other files regarding `Export as PDF',
why not on this file?

I was using LibreOffice 3.3 and have since updated to LibreOffice 3.4.5
OOO340m1 (Build:1505). Unfortunately the newer version is unable to
`Export as PDF' on the selected TABS either!!

What could be wrong? There are no special characters in the sheet
names bar possibly a space.

I will gladly send this file to anyone who can assist as I need to
create and post to a public user mailing list a PDF at the end of
the month.

Help is much appreciated
Hylton











--

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://www.fishhoeklionsclub.org.za being part of the worlds largest NGO




--
For unsubscribe instructions e-mail to: users+h...@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] pdf creation issue on a single file?

[Hylton Conacher (ZR1HPC)]

Hi Miguel,

On 28/07/12 18:07, MiguelAngel wrote:

El 28/07/12 15:42, Hylton Conacher (ZR1HPC) escribió:

Hi,

I have an rainfall.ods document that has 24 tabs. Two of the tabs
contain figures and the balance contain graphs of the figures.

I recently experienced a problem, it would seem after my last tab
addition, that prevents me from creating a PDF file of the selected
sheets(12).

The PDF export works fine on other files regarding `Export as PDF', why
not on this file?

I was using LibreOffice 3.3 and have since updated to LibreOffice 3.4.5
OOO340m1 (Build:1505). Unfortunately the newer version is unable to
`Export as PDF' on the selected TABS either!!

What could be wrong? There are no special characters in the sheet names
bar possibly a space.

I will gladly send this file to anyone who can assist as I need to
create and post to a public user mailing list a PDF at the end of the
month.

Help is much appreciated
Hylton


Thank you for commenting on the OP's initial problem, and not a Linux 
Windows word flame-war that bears no relation to the subject line, 
despite some valid suggestions being valid.



Verify you have not marked in the same tab the option:
Embed OpenDocument file.


How could I check this via the GUI? I recreated the last graph and made 
sure there were no 'embeds' of any kind. Could non-standard fonts i.e. 
not Arial, also play a part?


Further to this, issue it would seem that only the last created graph is 
being exported( but this is NOT the last selected Tab)  as when I view 
the exported PDF, only a single page shows, instead of approx 12.


Hylton
--

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://www.fishhoeklionsclub.org.za being part of the worlds largest NGO




--
For unsubscribe instructions e-mail to: users+h...@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

[libreoffice-users] pdf creation issue on a single file?

[Hylton Conacher (ZR1HPC)]

Hi,

I have an rainfall.ods document that has 24 tabs. Two of the tabs 
contain figures and the balance contain graphs of the figures.


I recently experienced a problem, it would seem after my last tab 
addition, that prevents me from creating a PDF file of the selected 
sheets(12).


The PDF export works fine on other files regarding `Export as PDF', why 
not on this file?


I was using LibreOffice 3.3 and have since updated to LibreOffice 3.4.5
OOO340m1 (Build:1505). Unfortunately the newer version is unable to 
`Export as PDF' on the selected TABS either!!


What could be wrong? There are no special characters in the sheet names 
bar possibly a space.


I will gladly send this file to anyone who can assist as I need to 
create and post to a public user mailing list a PDF at the end of the month.


Help is much appreciated
Hylton
--

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://www.fishhoeklionsclub.org.za being part of the worlds largest NGO



--
For unsubscribe instructions e-mail to: users+h...@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

[libreoffice-users] Calc: Identifying duplicate cell values with conditional formatting?

[Hylton Conacher (ZR1HPC)]

Hi,

Using LibreOffice 3.3.1 and am in the process of editing a 68000 row, 10
column file.

There are two main columns that contain the data to be cleaned up with
multiple  instances of duplication i.e. the same text but only the text
case differs between two rows or the text is totally different in column
A row 1 and row 2 but the text in column B rows 1 and 2 is identical i.e.

Col A   Col B   OR  Col A   Col B
a   hx  a   hx
A   hx  a   hx

OR

a   hx  a   hx
a   hy  A   hy

etc for the other combinations

I am doing the alphabetical sort via Col A.

I can use find to search for the duplicate record row once I know what I
am looking for however determining what test is different when the
values in the Col A are the same and vice versa/

On 136000 cells this is a FAIR mission!

I would like to know if there is a conditional formula I could use that
could highlight the differences in one column when cells in the other
column are the same. I am thinking of a formula that says if the cell
contents are the same as any other cell in a range, apply the
conditional format. Of course this conditional would need to be added
onto all 136000 cells. :(

That way I can highlight the 'error' cells and find them easily and
correct them or add a new row of data.

Any pointers would be appreciated for doing this in Calc as an external
database is not available. What elements of the formula can I investigate?

Many thanks
Hylton
-- 

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://www.fishhoeklionsclub.org.za being part of the worlds largest NGO


-- 
For unsubscribe instructions e-mail to: users+h...@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

[libreoffice-users] Calc row filter extraction

[Hylton Conacher (ZR1HPC)]

Hi all,

I have a 'data' spreadsheet sheet for my family medical aid on LO 3.3.1
with multiple columns and different information in each column. Please
see the text example I include below.

What I would ideally like to do is have rows on sheet A, where Column D
is the same, linked onto another sheet(B). My next requirement is to
group all the like column B on sheet B, C and link them onto sheet D but
group them according to column B and SUM the amounts from sheets B, C.

I have included a brief text example below:

Sheet A
A   B   C   D   E
20-July SVP SBP Robert  200
20-July STP SPG Hazel   100
21-July STP SBP Robert  180
22-July SVP SBP Robert  50
23-July STP SBP Hazel   400

Sheet B
A   B   C   D   E
20-July SVP SBP Robert  200
21-July STP SBP Robert  180
22-July SVP SBP Robert  50

Sheet C
A   B   C   D   E
20-July STP SPG Hazel   100
23-July STP SBP Hazel   400

Sheet D
A   B   C   D   E
STP SBP Robert  100
STP SBP Hazel   500
SVP SBP Robert  250

I have done some googling regarding row extraction and have also
investigated the SUMIF command. and whilst I can get a total of all the
STP's, I cannot SUM it by user at STP.
The SUMD command has also raised its head but I cannot figure out how to
get it to work in conjunction with SUMIF.

Any help appreciated, even telling me MySQL is a better option. My
problem is that the data is Sheet A is ever growing and being edited by
a basic Excel user, and sheets b-D need to reflect those changes made
on sheet A.

Again, Any Help Appreciated
-- 

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://www.fishhoeklionsclub.org.za being part of the worlds largest NGO


-- 
Unsubscribe instructions: E-mail to users+h...@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] Conditional formatting cell copying? - SOLVED

[Hylton Conacher (ZR1HPC)]

Tnx all,

The solution was to reference the cells without the $ sign before them.
If I copied that cell and selected the cells I wanted to apply the
conditional format to, selected Paste Special and selected Formats and
Formula, it worked a treat.

Thanks to whoever thought of the relative/absolute references as well as
the Paste Special.

Regards
Hylton
-- 

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://www.fishhoeklionsclub.org.za being part of the worlds largest NGO


-- 
Unsubscribe instructions: E-mail to users+h...@global.libreoffice.org
In case of problems unsubscribing, write to postmas...@documentfoundation.org
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] [Calc] Create and export chart

[Hylton Conacher (ZR1HPC)]

Hi Thorsten,

On 13/07/11 19:18, Thorsten Kampe wrote:
 Hi,
 
 I have a Calc file which contains multiple sheets.
 
 - every sheet contains one month of data: from May 2010 to July 2011.
 
 - every sheet has two columes: in column A the days and in column B the 
 actual data.

This sounds very much like the rainfall spreadsheet I use currently.
Currently it has 19 sheets and it covers the entire year as well as 5
years history.

I have attached it for you to view and adapt to your need.

I hope it assists you

Regards
Hylton
-- 

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://www.fishhoeklionsclub.org.za being part of the worlds largest NGO


-- 
Unsubscribe instructions: E-mail to users+h...@global.libreoffice.org
In case of problems unsubscribing, write to postmas...@documentfoundation.org
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] Re: Conditional formatting cell copying?

[Hylton Conacher (ZR1HPC)]

Hi Dave,

Thanks for the answer, and sorry for the belated reply, however it
doesn't work using LibreOffice 3.3.1
OOO330m19 (Build:8)
tag libreoffice-3.3.1.2 On OpenSuse 11.0

You are quite correct that the conditional formulae are copied too,
however the formulae still refer to the same cell references given in
the copied cell.

In other words if you copied cell C3 and pasted special on C4:c10, the
conditional formatting would be copied but the cell references in cell
C10 for the conditional formatting would point to the same cell as those
referenced in cell C3.

Please confirm if this is the case.

Regards
Hylton
-- 

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://www.fishhoeklionsclub.org.za being part of the worlds largest NGO


-- 
Unsubscribe instructions: E-mail to users+h...@global.libreoffice.org
In case of problems unsubscribing, write to postmas...@documentfoundation.org
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] Conditional formatting cell copying?

[Hylton Conacher (ZR1HPC)]

Hi Regina,

See my earlier reply to Dave in addition to the below:.

Copying and writing the conditions are not the problem.

Getting the condition to automatically increment is the problem e.g. I
have cell A1 that has the formula to add the contents of cells B1, C1
and D1. Cell A1 also has 3 conditional formulas that relate its value to
cells B1, C1 and D1 and apply certain colour coding to the A1 cell.

The formula is cell A1 is the same until cell a10, with just the
respective cell references changing i.e. on cell A5 the formula would
add cells B5, C5 and D5. What I require is that the conditional
formatting applied to cell A1 also be applied to cell A5 but with the
conditional formulae having been incremented i.e. so that the correct
formatting is shown, as per the conditional formulae for the values on
that row i.e. b5, C5 and D5, when the value in A5 is compared to cells
B5, C5 and D5.

I trust that answers the query.

Regards
Hylton
-- 

Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://www.fishhoeklionsclub.org.za being part of the worlds largest NGO


-- 
Unsubscribe instructions: E-mail to users+h...@global.libreoffice.org
In case of problems unsubscribing, write to postmas...@documentfoundation.org
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