selecting cells in calc sheet

classic Classic list List threaded Threaded
13 messages Options
pete nikolic pete nikolic
Reply | Threaded
Open this post in threaded view
|

selecting cells in calc sheet


Hi Folks ..


I need to be able to select  the last 3 in a series of cells that change on a weekly
basis



so i week 1 there would be 1 cell  no action
week 2                              2 cells  no action
week 3 3 cell sum three cells
week 4 4 cells sum last three
week 5 5 cells sum last three  

and so it goes on for 8 weeks always just summing the last 3 cells in the
series  then divide by 3 subtract 30 = result ..


Pete

     



--
Illegitimi non carborundum . ro for the purists out there
Noli nothis permittere te terere.

--
To unsubscribe e-mail to: [hidden email]
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

pietvOo pietvOo
Reply | Threaded
Open this post in threaded view
|

Re: selecting cells in calc sheet

pete nikolic wrote:

 >
 > Hi Folks ..
 >
 >
 > I need to be able to select  the last 3 in a series of cells that change on a weekly
 > basis
 >
 >
 >
 > so i week 1 there would be 1 cell  no action
 > week 2                              2 cells  no action
 > week 3 3 cell sum three cells
 > week 4 4 cells sum last three
 > week 5 5 cells sum last three  
 >
 > and so it goes on for 8 weeks always just summing the last 3 cells in the
 > series  then divide by 3 subtract 30 = result ..

That is rather vague. Where is the week number? How is the series represented? What do you want to do with the result?
--
Piet van Oostrum <[hidden email]>
WWW: http://pietvanoostrum.com/
PGP key: [8DAE142BE17999C4]

--
To unsubscribe e-mail to: [hidden email]
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

Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: selecting cells in calc sheet

In reply to this post by pete nikolic
At 21:00 24/04/2015 +0100, Pete Nikolic wrote:
>I need to be able to select the last 3 in a series of cells that
>change on a weekly basis so in
>week 1: there would be 1 cell - no action
>week 2: 2 cells - no action
>week 3: 3 cells - sum three cells
>week 4: 4 cells - sum last three
>week 5: 5 cells - sum last three
>and so it goes on for 8 weeks always just summing the last 3 cells
>in the series then divide by 3 subtract 30 = result ..

I'm guessing that you want separate results for each week, not just a
single result - so that in week 5, for example, you would have three
results, not one. Let's imagine your data is in column A, starting in
A1. In B3, insert:
=AVERAGE(A1:A3)-30
and fill down the column.

If you want to suppress the display in B cells for which there is not
yet an A-cell vale, try something like:
=IF(A3=0;"";AVERAGE(A1:A3)-30)

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: [hidden email]
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

pete nikolic pete nikolic
Reply | Threaded
Open this post in threaded view
|

Re: selecting cells in calc sheet

In reply to this post by pete nikolic
On Fri, 24 Apr 2015 21:52:47 +0100
Brian Barker <[hidden email]> wrote:

> At 21:00 24/04/2015 +0100, Pete Nikolic wrote:
> >I need to be able to select the last 3 in a series of cells that
> >change on a weekly basis so in
> >week 1: there would be 1 cell - no action
> >week 2: 2 cells - no action
> >week 3: 3 cells - sum three cells
> >week 4: 4 cells - sum last three
> >week 5: 5 cells - sum last three
> >and so it goes on for 8 weeks always just summing the last 3 cells
> >in the series then divide by 3 subtract 30 = result ..
>
> I'm guessing that you want separate results for each week, not just a
> single result - so that in week 5, for example, you would have three
> results, not one. Let's imagine your data is in column A, starting in
> A1. In B3, insert:
> =AVERAGE(A1:A3)-30
> and fill down the column.
>
> If you want to suppress the display in B cells for which there is not
> yet an A-cell vale, try something like:
> =IF(A3=0;"";AVERAGE(A1:A3)-30)
>
> I trust this helps.
>
> Brian Barker
>
>
Hi Brian


ok    lets see ..


  if we start as will be the case  C3   will contain say 50  (the actual dates are
  of little importance )  a month later  C5 will be say 47  and so on every month
  the next cell would be C7 then C9 then C11 what i need is to be able to
  automatically pick the last 3 cells containing data  so when C9 is filled in then
  it needs to automatically choose  C9 C7 C5 and add them then divide by 3  then
  subtract  a variable defined on the day   ..

Hope this makes it a little clearer


Thanks Pete .

 



--
Illegitimi non carborundum . ro for the purists out there
Noli nothis permittere te terere.

--
To unsubscribe e-mail to: [hidden email]
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

pietvOo pietvOo
Reply | Threaded
Open this post in threaded view
|

Re: selecting cells in calc sheet

pete nikolic wrote:

 >   if we start as will be the case  C3   will contain say 50  (the actual dates are
 >   of little importance )  a month later  C5 will be say 47  and so on every month
 >   the next cell would be C7 then C9 then C11 what i need is to be able to
 >   automatically pick the last 3 cells containing data  so when C9 is filled in then
 >   it needs to automatically choose  C9 C7 C5 and add them then divide by 3  then
 >   subtract  a variable defined on the day   ..
 >
 > Hope this makes it a little clearer
 >
That would then be:

IF(ISBLANK(C7);"";AVERAGE(C3;C5;C7)-30)

--
Piet van Oostrum <[hidden email]>
WWW: http://pietvanoostrum.com/
PGP key: [8DAE142BE17999C4]

--
To unsubscribe e-mail to: [hidden email]
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

James E. Lang James E. Lang
Reply | Threaded
Open this post in threaded view
|

Re: selecting cells in calc sheet

Pete,

You still leave a lot unsaid such as whether the result is in a single fixed location, what if anything is in the even numbered rows of column C, whether any other column can be used for identification purposes, what if anything is below the most recent month's data, etc. You imply that there's no more than eight months worth of data so I guess C17 ends the series.

Without some of the information vacuum filled I'm not sure what we can do for you.

--
Jim

-----Original Message-----
From: Piet van Oostrum <[hidden email]>
To: pete nikolic <[hidden email]>
Cc: [hidden email]
Sent: Fri, 24 Apr 2015 14:20
Subject: Re: [libreoffice-users] selecting cells in calc sheet

pete nikolic wrote:

 >   if we start as will be the case  C3   will contain say 50  (the actual dates are
 >   of little importance )  a month later  C5 will be say 47  and so on every month
 >   the next cell would be C7 then C9 then C11 what i need is to be able to
 >   automatically pick the last 3 cells containing data  so when C9 is filled in then
 >   it needs to automatically choose  C9 C7 C5 and add them then divide by 3  then
 >   subtract  a variable defined on the day   ..
 >
 > Hope this makes it a little clearer
 >
That would then be:

IF(ISBLANK(C7);"";AVERAGE(C3;C5;C7)-30)

--
Piet van Oostrum <[hidden email]>
WWW: http://pietvanoostrum.com/
PGP key: [8DAE142BE17999C4]

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


--
To unsubscribe e-mail to: [hidden email]
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
James E. Lang James E. Lang
Reply | Threaded
Open this post in threaded view
|

Re: selecting cells in calc sheet

Pete,

Pardon me. I'm confused.

When you say C5 (for example) which do you mean, Row 5 in Column C or the fifth column?

Where is the calculation?

You speak of C1, C3, C5, C7, ... but what about C2, C4, C6, C8, ...?

Here is what I understand (my suppositions):

Row 1 is a ser of column headings.

Column A contains a set of names (of regions maybe).

Columns C, E, G, I, ... are headed by dates (e.g. Jan 1, Feb 14, etc.).

At the intersection of column C and row 2 is a number (maybe a counter of some kind) relating to the first region from Jan 1 through Feb 13. Other cells in columns C, E, G, I, ... get filled similarly.

Starting with the third time interval you want to calculate the average of the most recent three vakues stored in those cilumns bur adjusted by some mystery constant for that region (column B maybe) and maybe store that result in columns D, F, H, J ....

If I'm right,  which is a mighty big IF, then the task is quite easily accomplished but I'll await your response to my suppositions before going any further. Besides that I need some sleep.

--
Jim

-----Original Message-----
From: pete nikolic <[hidden email]>
To: James E Lang <[hidden email]>
Sent: Fri, 24 Apr 2015 23:43
Subject: Re: [libreoffice-users] selecting cells in calc sheet

On Fri, 24 Apr 2015 20:36:10 -0700
James E Lang <[hidden email]> wrote:

> Pete,
>
> You still leave a lot unsaid such as whether the result is in a single fixed
> location, what if anything is in the even numbered rows of column C, whether any
> other column can be used for identification purposes, what if anything is below
> the most recent month's data, etc. You imply that there's no more than eight
> months worth of data so I guess C17 ends the series.
>
> Without some of the information vacuum filled I'm not sure what we can do for you.
>

Right   let me see how i can put this without attaching a spreadsheet


coloums..

C1 name C3  date1 C5 date2 C7 date3 C9 date4 C11
date5

in those colums  under the dates will be numbers of i would guess around the 40 to
60 region but only on or after the date ie under date 1 on row name 1 say 45  next
month it is date2 name1 say 48 then date3 name1  60, date4 name1  48 date5 name1 56
ect ect

Dates are columns rows are names there will be about 25 names  8 dates in total  so
for date1 date2 both name1 there would be no result , then fill date3 name 1 i then
need to do the add date1 date2 date3 divide by .

Thats is the easy bit  now when i fill date4 i need it to ignore date1 and add date
2 date3 date4 then divide  when i fill date 5 i need it to ignore date1 date 2  add
date3 date4 date5 divide by 3 and carry on like that always only picking the last
3 dates ignoring any earlier .

does that help any

                date1 data2 date3 date4 date5
  name1 48 60 48 56

Pete

                                                                                               


--
Illegitimi non carborundum . ro for the purists out there
Noli nothis permittere te terere.

--
To unsubscribe e-mail to: [hidden email]
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
James E. Lang James E. Lang
Reply | Threaded
Open this post in threaded view
|

Re: selecting cells in calc sheet

You did not correct any of my suppositions so here is my very detailed recommendation. Note that my quotation marks surrounding text are NOT to be keyed into the spreadsheet. For the time being I will limit my discussion to columns A through L and rows 1 through 5.

Starting from scratch with a new (empty) spreadsheet, select cell A1 and enter "Region"

In B1 enter "Constant"

In cell C1 enter the first date such as "01/01/2015"

Leave D1 empty but merge C1 and D1.

Continue with E1 & F1, G1 & H1, I1 & J1, and K1 & L1 in the same manner.

Now go to the data rows.

In cell A2 enter the name of the first region and in B2 that region's constant such as "30"

Prepare cells A3 through B5 similarly.

Leave cells C2 through G2 empty for now.

The long sought formula first appears in cell H2. It is
  =IF(G2="","",AVERAGE(C2,E2,G2)-$B2)

Now select cells G2 through H2 and copy (Ctrl+C).

Finally select cells G2 through L5 and paste (Ctrl+V). Of course this final step can be modified to cover up to 511 date ranges and over one million regions.

In production you might accidentally enter data where the formula is. Remember that undo (Ctrl+Z) is your friend when this happens. You can also use copy and paste or fill vertically to recover.

I hope this satisfies your requirements, If not then you'll have to explain your requirements better.

--
Jim

-----Original Message-----
From: pete nikolic <[hidden email]>
To: James E Lang <[hidden email]>
Sent: Sat, 25 Apr 2015 8:38
Subject: Re: [libreoffice-users] selecting cells in calc sheet

On Sat, 25 Apr 2015 01:30:53 -0700
James E Lang <[hidden email]> wrote:

> Pete,
>
> Pardon me. I'm confused.
>
> When you say C5 (for example) which do you mean, Row 5 in Column C or the fifth
> column?
>
> Where is the calculation?
>
> You speak of C1, C3, C5, C7, ... but what about C2, C4, C6, C8, ...?
>
> Here is what I understand (my suppositions):
>
> Row 1 is a ser of column headings.
>
> Column A contains a set of names (of regions maybe).
>
> Columns C, E, G, I, ... are headed by dates (e.g. Jan 1, Feb 14, etc.).
>
> At the intersection of column C and row 2 is a number (maybe a counter of some
> kind) relating to the first region from Jan 1 through Feb 13. Other cells in
> columns C, E, G, I, ... get filled similarly.
>
> Starting with the third time interval you want to calculate the average of the
> most recent three vakues stored in those cilumns bur adjusted by some mystery
> constant for that region (column B maybe) and maybe store that result in columns
> D, F, H, J ....
>
> If I'm right,  which is a mighty big IF, then the task is quite easily
> accomplished but I'll await your response to my suppositions before going any
> further. Besides that I need some sleep.
>



Hi right   ok lets see


take an ever increasing  list of values  for the first 2 values do nothing  when a
third value gets added add the three together  then add anoter value to the list
ignore the first 2 values add the next three  add another value  then ignore first 3
values  add the remaining three  but every time a value is added discard the
previous sum of three  ...



Pete .


--
Illegitimi non carborundum . ro for the purists out there
Noli nothis permittere te terere.

--
To unsubscribe e-mail to: [hidden email]
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
James E. Lang James E. Lang
Reply | Threaded
Open this post in threaded view
|

Re: selecting cells in calc sheet

The main thing I'm not understanding is whether the formula is to be one fixed place or if after 3 data points it is in cell X but after 4 data points it is in cell Y. The solution I gave you applies to the latter case.

For the former case I would use a more complex formula like this using your latest specification.

=IF(COUNT(A1:A50)>2,AVERAGE(OFFSET(A1,0,COUNT(A1:A50)-3,1,3)-30,"")

That says, if there are more than two values then define a horizontal range composed of the last three filled cells, average that range, and subtract 30. This works as long as they are no empty or text cells before the values of interest in A1:A50. This process gets a little bit (to say the least) more hairy if there are intervening cells.


--
Jim

-----Original Message-----
From: pete nikolic <[hidden email]>
To: James E Lang <[hidden email]>, [hidden email]
Sent: Sat, 25 Apr 2015 11:11
Subject: Re: [libreoffice-users] selecting cells in calc sheet

On Sat, 25 Apr 2015 10:53:38 -0700
James E Lang <[hidden email]> wrote:

> You did not correct any of my suppositions so here is my very detailed
> recommendation. Note that my quotation marks surrounding text are NOT to be keyed
> into the spreadsheet. For the time being I will limit my discussion to columns A
> through L and rows 1 through 5.
>
> Starting from scratch with a new (empty) spreadsheet, select cell A1 and enter
> "Region"
>
> In B1 enter "Constant"
>
> In cell C1 enter the first date such as "01/01/2015"
>
> Leave D1 empty but merge C1 and D1.
>
> Continue with E1 & F1, G1 & H1, I1 & J1, and K1 & L1 in the same manner.
>
> Now go to the data rows.
>
> In cell A2 enter the name of the first region and in B2 that region's constant
> such as "30"
>
> Prepare cells A3 through B5 similarly.
>
> Leave cells C2 through G2 empty for now.
>
> The long sought formula first appears in cell H2. It is
>   =IF(G2="","",AVERAGE(C2,E2,G2)-$B2)
>
> Now select cells G2 through H2 and copy (Ctrl+C).
>
> Finally select cells G2 through L5 and paste (Ctrl+V). Of course this final step
> can be modified to cover up to 511 date ranges and over one million regions.
>
> In production you might accidentally enter data where the formula is. Remember
> that undo (Ctrl+Z) is your friend when this happens. You can also use copy and
> paste or fill vertically to recover.
>
> I hope this satisfies your requirements, If not then you'll have to explain your
> requirements better.
>

Hi James .


It may in a round about fashion  lets just totally forget about dates .

Lets imagine a row that has number entered in cells on that row at intervals (the
interval is totally unimportant) i enter a number  in A1 then another in A2  with
just input in A1 & A2 no action  enter a number in A3 then sum the numbers  (easy so
far) the enter a number in A4  then i want it to forget the value in A1 and sum A2
A3 &A4  then enter a number in A5 i then want it to forget A1 & A2 and Sum A3 A4 &A5
and so on  does this help any


  PS thanks so far this is one aspect of spread sheets that i find confusing
to say the least


Pete .
 
--
Illegitimi non carborundum . ro for the purists out there
Noli nothis permittere te terere.

--
To unsubscribe e-mail to: [hidden email]
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
James E. Lang James E. Lang
Reply | Threaded
Open this post in threaded view
|

Re: selecting cells in calc sheet

You're in business. The AVERAGE() function does the same thing as the SUM() function divided by the COUNT() function. I would use AVERAGE() as it is cleaner (more streamlined).

I'm glad I could help. I use the OFFSET() function myself to dynamically define ranges of cells. If somebody knows a better technique I hope they will chime in. I've never seen anyone else post a practical use for this function but for me, any spreadsheet program that lacks support for it fails to gain my acceptance.

--
Jim

-----Original Message-----
From: pete nikolic <[hidden email]>
To: James E Lang <[hidden email]>
Sent: Sat, 25 Apr 2015 12:17
Subject: Re: [libreoffice-users] selecting cells in calc sheet

On Sat, 25 Apr 2015 11:46:23 -0700
James E Lang <[hidden email]> wrote:

> The main thing I'm not understanding is whether the formula is to be one fixed
> place or if after 3 data points it is in cell X but after 4 data points it is in
> cell Y. The solution I gave you applies to the latter case.
>
> For the former case I would use a more complex formula like this using your latest
> specification.
>
> =IF(COUNT(A1:A50)>2,AVERAGE(OFFSET(A1,0,COUNT(A1:A50)-3,1,3)-30,"")
>
> That says, if there are more than two values then define a horizontal range
> composed of the last three filled cells, average that range, and subtract 30. This
> works as long as they are no empty or text cells before the values of interest in
> A1:A50. This process gets a little bit (to say the least) more hairy if there are
> intervening cells.
>
>

Hi Jim ..


Right this is looking promissing   the actual location of the formula /result will
be in a separate cell on the same row but outside the range of entered values it
will also be copied to the next 30 or so rows below and edited to suit where
needed   . one other thing you have it averaging the cells they need to be added
together then divided by 3 unless the average function does the same thing



Thanks      

Pete .
 

--
Illegitimi non carborundum . ro for the purists out there
Noli nothis permittere te terere.

--
To unsubscribe e-mail to: [hidden email]
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
James E Lang-2 James E Lang-2
Reply | Threaded
Open this post in threaded view
|

Re: selecting cells in calc sheet

Correction. I ran across an old message from Mr Barker that suggested using the OFFSET() function for something.

--
Jim

-----Original Message-----
From: James E Lang <[hidden email]>
To: pete nikolic <[hidden email]>, LibreOffice Users <[hidden email]>
Sent: Sat, 25 Apr 2015 15:03
Subject: Re: [libreoffice-users] selecting cells in calc sheet

You're in business. The AVERAGE() function does the same thing as the SUM() function divided by the COUNT() function. I would use AVERAGE() as it is cleaner (more streamlined).

I'm glad I could help. I use the OFFSET() function myself to dynamically define ranges of cells. If somebody knows a better technique I hope they will chime in. I've never seen anyone else post a practical use for this function but for me, any spreadsheet program that lacks support for it fails to gain my acceptance.

--
Jim

-----Original Message-----
From: pete nikolic <[hidden email]>
To: James E Lang <[hidden email]>
Sent: Sat, 25 Apr 2015 12:17
Subject: Re: [libreoffice-users] selecting cells in calc sheet

On Sat, 25 Apr 2015 11:46:23 -0700
James E Lang <[hidden email]> wrote:

> The main thing I'm not understanding is whether the formula is to be one fixed
> place or if after 3 data points it is in cell X but after 4 data points it is in
> cell Y. The solution I gave you applies to the latter case.
>
> For the former case I would use a more complex formula like this using your latest
> specification.
>
> =IF(COUNT(A1:A50)>2,AVERAGE(OFFSET(A1,0,COUNT(A1:A50)-3,1,3)-30,"")
>
> That says, if there are more than two values then define a horizontal range
> composed of the last three filled cells, average that range, and subtract 30. This
> works as long as they are no empty or text cells before the values of interest in
> A1:A50. This process gets a little bit (to say the least) more hairy if there are
> intervening cells.
>
>

Hi Jim ..


Right this is looking promissing   the actual location of the formula /result will
be in a separate cell on the same row but outside the range of entered values it
will also be copied to the next 30 or so rows below and edited to suit where
needed   . one other thing you have it averaging the cells they need to be added
together then divided by 3 unless the average function does the same thing



Thanks      

Pete .
 

--
Illegitimi non carborundum . ro for the purists out there
Noli nothis permittere te terere.

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

--
To unsubscribe e-mail to: [hidden email]
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
James E. Lang James E. Lang
Reply | Threaded
Open this post in threaded view
|

Re: CORRECTED SOLUTION selecting cells in calc sheet

In reply to this post by James E. Lang
For anyone who might have taken an interest in this discussion, I omitted a right parenthesis when I first posted this formula. It is now correct.

--
Jim

-----Original Message-----
From: James E Lang <[hidden email]>
To: [hidden email], pete nikolic <[hidden email]>
Sent: Sat, 25 Apr 2015 11:47
Subject: Re: [libreoffice-users] selecting cells in calc sheet

The main thing I'm not understanding is whether the formula is to be one fixed place or if after 3 data points it is in cell X but after 4 data points it is in cell Y. The solution I gave you applies to the latter case.

For the former case I would use a more complex formula like this using your latest specification.

=IF(COUNT(A1:A50)>2,AVERAGE(OFFSET(A1,0,COUNT(A1:A50)-3,1,3))-30,"")

That says, if there are more than two values then define a horizontal range composed of the last three filled cells, average that range, and subtract 30. This works as long as they are no empty or text cells before the values of interest in A1:A50. This process gets a little bit (to say the least) more hairy if there are intervening cells.

--
Jim

--
To unsubscribe e-mail to: [hidden email]
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
Tim Deaton Tim Deaton
Reply | Threaded
Open this post in threaded view
|

uses for OFFSET() (was Re: [libreoffice-users] selecting cells in calc sheet)

In reply to this post by James E Lang-2
I find OFFSET() quite useful whenever I build a table with a running
balance (for instance where the balance in X10 = X9+W10).  Without the
OFFSET() function, if I needed to insert a row between rows 9 & 10, the
new row would not be included in the running balance unless I edited the
formula.  But if the original X10's formula is "=OFFSET(X10,-1,0)+W10"
then the new row is automatically included in the running balance.

-- Tim
===========================


On 4/26/2015 9:15 AM, [hidden email] wrote:

> Correction. I ran across an old message from Mr Barker that suggested using the OFFSET() function for something.
>


--
To unsubscribe e-mail to: [hidden email]
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