Calc Help

classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

Calc Help

Vince@Gmail
AOO 4.1.5 on Win10 Home desktop

_________________________________

Note: Cells with a yellow background identify required data must be
entered.  Cells with a (darker) gray background identify cells which
show calculated results.

I have a Calc sheet that I enter data on a regular basis, as shown below:

[D] [E] [F]    [G]            [H] <-------- Column headings

*/_Today is Week #_/* */_Today's Date Is_/* */_Game #1_/* */_Game #2
_/* */_Game #3_/* */_Match Series_/* */_Match Average_/*
*1* 6-Sep 152 146 187 485 161.7
*2* 13-Sep 133 159 141 433 144.3
*3* 20-Sep 147 155 148 450 150.0
*4* 27-Sep 161 151 142 454 151.3
*5* 4-Oct 130 139 116 385 128.3
*6* 11-Oct *Did Not Bowl. *
*7* 18-Oct 148 187 119 454 151.3
*8* 25-Oct 155 167 169 491 163.7
*9* 1-Nov 140 141 157 438 146.0
*10* 8-Nov 147 135 155 437 145.7
*11* 15-Nov 139 133 155 427 142.3
*12* 22-Nov 164 162 164 490 163.3
*13* 29-Nov 120 110 155 385 128.3
*14* 6-Dec 150 136 125 411 137.0
*15* 13-Dec 170 187 132 489 163.0
*16* 20-Dec 160 168 146 474 158.0
*17* 27-Dec 160 144 150 454 151.3
*18* 3-Jan 151 141 138 430 143.3
*/_---Half Season---_/*
*19* 10-Jan *Did Not Bowl. *
*20* 17-Jan 161 134 147 442 147.3
*21* 24-Jan
       
       
       
       

I use the results generated in columns G and H in another section of the
sheet, as shown:

                     [AL] [AN] <-------- Column headings

*Team #2- ACHES & PAINS v2.0
Post Match Play*
*Russ: * 458 *Player's Average: * 152.67
*Vince: * 442 147.33
*Dave: * 555 185.00
*Bob: * 600 200.00


Cells AL7 and AN7 are set to = the results calculated and provided
within cells G26 and H26, i.e., they show: 442 and 147.3.

My problem is that after calculating an update result within columns G
and H, e.g., on 24 Jan, (week # 21's data), the results based on entry
of data in D27:F27, there will be new results in the next row, at cells
G27 and H27. Therefore, I am forced to manually change the formulae
within AL7 and AN7 to correctly reflect the updated results provided in
columns AL and AN, by changing =G26 to =G27 and by changing =H26 to =H27.

Is there an easier method of handling this task, through use of a
function or a formula?

I hope this is described clearly enough.  If needed, I will attempt to
store the complete calc sheet at a Dropbox link. Let me know.

Regards,

VinceB.


Reply | Threaded
Open this post in threaded view
|

Re: Calc Help

Alan Boba-2
Hello Vince,

I'm intrigued but not able to follow what you're trying to do. Columns D
through H appear to be the games bowled in any given week, rows (or should
I say weeks) 1 through 21 are the results of the games in those weeks. That
seems clear to me. But whose games, Russ, Vince, Dave, or Bob's?

The disconnect comes with "using the results in [AL] and [AN]".

How does a single row shown as 1 - 21 and columns [D] though [H] in your
example relate to multiple individuals [AL] through [AN]?

And more importantly, what are you trying to achieve? If I understood that
I might be able to offer some useful help.

On Sat, Jan 18, 2020 at 12:48 PM Vince@GMAIL <[hidden email]> wrote:

> AOO 4.1.5 on Win10 Home desktop
>
> _________________________________
>
> Note: Cells with a yellow background identify required data must be
> entered.  Cells with a (darker) gray background identify cells which
> show calculated results.
>
> I have a Calc sheet that I enter data on a regular basis, as shown below:
>
> [D] [E] [F]    [G]            [H] <-------- Column headings
>
> */_Today is Week #_/*   */_Today's Date Is_/*   */_Game #1_/*   */_Game #2
> _/*     */_Game #3_/*   */_Match Series_/*      */_Match Average_/*
> *1*     6-Sep   152     146     187     485     161.7
> *2*     13-Sep  133     159     141     433     144.3
> *3*     20-Sep  147     155     148     450     150.0
> *4*     27-Sep  161     151     142     454     151.3
> *5*     4-Oct   130     139     116     385     128.3
> *6*     11-Oct  *Did Not Bowl. *
> *7*     18-Oct  148     187     119     454     151.3
> *8*     25-Oct  155     167     169     491     163.7
> *9*     1-Nov   140     141     157     438     146.0
> *10*    8-Nov   147     135     155     437     145.7
> *11*    15-Nov  139     133     155     427     142.3
> *12*    22-Nov  164     162     164     490     163.3
> *13*    29-Nov  120     110     155     385     128.3
> *14*    6-Dec   150     136     125     411     137.0
> *15*    13-Dec  170     187     132     489     163.0
> *16*    20-Dec  160     168     146     474     158.0
> *17*    27-Dec  160     144     150     454     151.3
> *18*    3-Jan   151     141     138     430     143.3
> */_---Half Season---_/*
> *19*    10-Jan  *Did Not Bowl. *
> *20*    17-Jan  161     134     147     442     147.3
> *21*    24-Jan
>
>
>
>
>
> I use the results generated in columns G and H in another section of the
> sheet, as shown:
>
>                      [AL] [AN] <-------- Column headings
>
> *Team #2- ACHES & PAINS v2.0
> Post Match Play*
> *Russ: *        458     *Player's Average: *    152.67
> *Vince: *       442     147.33
> *Dave: *        555     185.00
> *Bob: *         600     200.00
>
>
> Cells AL7 and AN7 are set to = the results calculated and provided
> within cells G26 and H26, i.e., they show: 442 and 147.3.
>
> My problem is that after calculating an update result within columns G
> and H, e.g., on 24 Jan, (week # 21's data), the results based on entry
> of data in D27:F27, there will be new results in the next row, at cells
> G27 and H27. Therefore, I am forced to manually change the formulae
> within AL7 and AN7 to correctly reflect the updated results provided in
> columns AL and AN, by changing =G26 to =G27 and by changing =H26 to =H27.
>
> Is there an easier method of handling this task, through use of a
> function or a formula?
>
> I hope this is described clearly enough.  If needed, I will attempt to
> store the complete calc sheet at a Dropbox link. Let me know.
>
> Regards,
>
> VinceB.
>
>
>

--
Alan Boba
CISSP, CCENT, ITIL v3 Foundations 2011
Reply | Threaded
Open this post in threaded view
|

Re: Calc Help

Vince@Gmail
Hello Alan B:

TNX for reading my query about Calc Help-updating Data.ods.

For reasons, unknown to me, my pasted example seems to be
plain/unformatted text, although I had sent it out via Mozilla
Thunderbird email client using both HTML and plain text to the AOO
mailing list, and appeared properly displayed in my compose window.  I
realize that w/o the full formatting, it is difficult to read and
understand what is displayed at your end.

This Dropbox link, I think, will allow you to download the full AOO-Calc
file:

https://www.dropbox.com/scl/fi/2ibk2u5elew1y4mx9b583/Calc-Help-updating-Data.ods?dl=0&rlkey=zeujgqduhbuo82jmxryylinvd 
.

I am trying to "harvest", via a formula or function, the resulting
_Match Series_ (column G) data, which is data only relevant to Vince's
game scores. Sorry, I neglected to include the _Vince's Individual
Record_ heading over columns B:L in my original example.  The game
scores for Vince (only) are being tracked on a weekly basis to generate
the result that is given in column $G.

At present, Cell AL7 contains the formula =G26. This value changes each
week, as Vince's new scores are poked into columns D, E, and F each week
(i.e., on the next week's row). So, at present, I am manually changing
the formula within AL7 to reflect the current week's row.

(The _Match Series_ data for my teammates Russ, Dave, and Bob are being
poked manually into AL6, AL8, and AL9, weekly. )

In another thread, dated 1/18/2020,  Brian Barker has offered
suggestions using =MATCH, =OFFSET, and =INDIRECT functions.  I am not
familiar with those functions, but will likely attempt their use when
time permits me to learn more about them. Brian also prefers the use of
minimal examples; therefore, I fear that I am verbose in my queries and
comments. However, I am hopeful that my comments above have been helpful
in clarifying my OP.

Again, thanks for your interest in solving my problem.

Regards,

VinceB.


On 1/19/2020 10:00 PM, Alan B wrote:

> Hello Vince,
>
> I'm intrigued but not able to follow what you're trying to do. Columns D
> through H appear to be the games bowled in any given week, rows (or should
> I say weeks) 1 through 21 are the results of the games in those weeks. That
> seems clear to me. But whose games, Russ, Vince, Dave, or Bob's?
>
> The disconnect comes with "using the results in [AL] and [AN]".
>
> How does a single row shown as 1 - 21 and columns [D] though [H] in your
> example relate to multiple individuals [AL] through [AN]?
They do not .
> And more importantly, what are you trying to achieve? If I understood that
> I might be able to offer some useful help.
See my comments above. HTH.

> On Sat, Jan 18, 2020 at 12:48 PM Vince@GMAIL <[hidden email]> wrote:
>
>> AOO 4.1.5 on Win10 Home desktop
>>
>> _________________________________
>>
>> Note: Cells with a yellow background identify required data must be
>> entered.  Cells with a (darker) gray background identify cells which
>> show calculated results.
>>
>> I have a Calc sheet that I enter data on a regular basis, as shown below:
>>
>> [D] [E] [F]    [G]            [H] <-------- Column headings
>>
>> */_Today is Week #_/*   */_Today's Date Is_/*   */_Game #1_/*   */_Game #2
>> _/*     */_Game #3_/*   */_Match Series_/*      */_Match Average_/*
>> *1*     6-Sep   152     146     187     485     161.7
>> *2*     13-Sep  133     159     141     433     144.3
>> *3*     20-Sep  147     155     148     450     150.0
>> *4*     27-Sep  161     151     142     454     151.3
>> *5*     4-Oct   130     139     116     385     128.3
>> *6*     11-Oct  *Did Not Bowl. *
>> *7*     18-Oct  148     187     119     454     151.3
>> *8*     25-Oct  155     167     169     491     163.7
>> *9*     1-Nov   140     141     157     438     146.0
>> *10*    8-Nov   147     135     155     437     145.7
>> *11*    15-Nov  139     133     155     427     142.3
>> *12*    22-Nov  164     162     164     490     163.3
>> *13*    29-Nov  120     110     155     385     128.3
>> *14*    6-Dec   150     136     125     411     137.0
>> *15*    13-Dec  170     187     132     489     163.0
>> *16*    20-Dec  160     168     146     474     158.0
>> *17*    27-Dec  160     144     150     454     151.3
>> *18*    3-Jan   151     141     138     430     143.3
>> */_---Half Season---_/*
>> *19*    10-Jan  *Did Not Bowl. *
>> *20*    17-Jan  161     134     147     442     147.3
>> *21*    24-Jan
>>
>>
>>
>>
>>
>> I use the results generated in columns G and H in another section of the
>> sheet, as shown:
>>
>>                       [AL] [AN] <-------- Column headings
>>
>> *Team #2- ACHES & PAINS v2.0
>> Post Match Play*
>> *Russ: *        458     *Player's Average: *    152.67
>> *Vince: *       442     147.33
>> *Dave: *        555     185.00
>> *Bob: *         600     200.00
>>
>>
>> Cells AL7 and AN7 are set to = the results calculated and provided
>> within cells G26 and H26, i.e., they show: 442 and 147.3.
>>
>> My problem is that after calculating an update result within columns G
>> and H, e.g., on 24 Jan, (week # 21's data), the results based on entry
>> of data in D27:F27, there will be new results in the next row, at cells
>> G27 and H27. Therefore, I am forced to manually change the formulae
>> within AL7 and AN7 to correctly reflect the updated results provided in
>> columns AL and AN, by changing =G26 to =G27 and by changing =H26 to =H27.
>>
>> Is there an easier method of handling this task, through use of a
>> function or a formula?
>>
>> I hope this is described clearly enough.  If needed, I will attempt to
>> store the complete calc sheet at a Dropbox link. Let me know.
>>
>> Regards,
>>
>> VinceB.
>>
>>
>>
Reply | Threaded
Open this post in threaded view
|

Re: Calc Help

Alan Boba-2
I've found a method that can work, providing the spreadsheet design is
modified somewhat.
It comes from a formula provided by RusselB in the OpenOffice forums.

=index(range;count(range);1)

For your sample spreadsheet this formula in cell AL7
=INDEX($Sheet1.G6:G42,COUNTA($Sheet1.G6:G42),1)
will return the Match Series value for the last value entered. When week#20
is completed the number in AL7 will update. When week#21 is completed it
will update again, and so on.

REQUIRED design changes
1. There must not be any merged cells that span column G. A merged cell in
the column breaks the formula. Update the =INDEX formula for column H and
the same is true, no merged cells.
2. The formulas in the Match Average and Total Pins columns should change.
The IF test should be whether the tested cell contains a number, not
whether it is blank. This will prevent #value! errors being displayed in
those cells.
3. Last, any blank rows in a column must be BELOW the currently completed
week number.

To meet design change #1 the "Did not bowl" in week 6 cannot be merged
cells. And to meet condition #3 they cannot be empty either. As an
alternative you could enter DNB in each cell in the row.

The row of merged cells that demark "half season" needs to change because
of #1. If the row is deleted and the season half is indicated by a heavy
border between week 18 and 19, that would suffice.

This is the simplest solution I could find. More complex formulas might be
possible that would meet your goal but I'm not familiar enough with Calc to
readily conceive of them.


On Mon, Jan 20, 2020 at 1:31 PM Vince@GMAIL <[hidden email]> wrote:

> Hello Alan B:
>
> TNX for reading my query about Calc Help-updating Data.ods.
>
> For reasons, unknown to me, my pasted example seems to be
> plain/unformatted text, although I had sent it out via Mozilla
> Thunderbird email client using both HTML and plain text to the AOO
> mailing list, and appeared properly displayed in my compose window.  I
> realize that w/o the full formatting, it is difficult to read and
> understand what is displayed at your end.
>
> This Dropbox link, I think, will allow you to download the full AOO-Calc
> file:
>
>
> https://www.dropbox.com/scl/fi/2ibk2u5elew1y4mx9b583/Calc-Help-updating-Data.ods?dl=0&rlkey=zeujgqduhbuo82jmxryylinvd
> .
>
> I am trying to "harvest", via a formula or function, the resulting
> _Match Series_ (column G) data, which is data only relevant to Vince's
> game scores. Sorry, I neglected to include the _Vince's Individual
> Record_ heading over columns B:L in my original example.  The game
> scores for Vince (only) are being tracked on a weekly basis to generate
> the result that is given in column $G.
>
> At present, Cell AL7 contains the formula =G26. This value changes each
> week, as Vince's new scores are poked into columns D, E, and F each week
> (i.e., on the next week's row). So, at present, I am manually changing
> the formula within AL7 to reflect the current week's row.
>
> (The _Match Series_ data for my teammates Russ, Dave, and Bob are being
> poked manually into AL6, AL8, and AL9, weekly. )
>
> In another thread, dated 1/18/2020,  Brian Barker has offered
> suggestions using =MATCH, =OFFSET, and =INDIRECT functions.  I am not
> familiar with those functions, but will likely attempt their use when
> time permits me to learn more about them. Brian also prefers the use of
> minimal examples; therefore, I fear that I am verbose in my queries and
> comments. However, I am hopeful that my comments above have been helpful
> in clarifying my OP.
>
> Again, thanks for your interest in solving my problem.
>
> Regards,
>
> VinceB.
>
>
> On 1/19/2020 10:00 PM, Alan B wrote:
> > Hello Vince,
> >
> > I'm intrigued but not able to follow what you're trying to do. Columns D
> > through H appear to be the games bowled in any given week, rows (or
> should
> > I say weeks) 1 through 21 are the results of the games in those weeks.
> That
> > seems clear to me. But whose games, Russ, Vince, Dave, or Bob's?
> >
> > The disconnect comes with "using the results in [AL] and [AN]".
> >
> > How does a single row shown as 1 - 21 and columns [D] though [H] in your
> > example relate to multiple individuals [AL] through [AN]?
> They do not .
> > And more importantly, what are you trying to achieve? If I understood
> that
> > I might be able to offer some useful help.
> See my comments above. HTH.
> > On Sat, Jan 18, 2020 at 12:48 PM Vince@GMAIL <[hidden email]> wrote:
> >
> >> AOO 4.1.5 on Win10 Home desktop
> >>
> >> _________________________________
> >>
> >> Note: Cells with a yellow background identify required data must be
> >> entered.  Cells with a (darker) gray background identify cells which
> >> show calculated results.
> >>
> >> I have a Calc sheet that I enter data on a regular basis, as shown
> below:
> >>
> >> [D] [E] [F]    [G]            [H] <-------- Column headings
> >>
> >> */_Today is Week #_/*   */_Today's Date Is_/*   */_Game #1_/*   */_Game
> #2
> >> _/*     */_Game #3_/*   */_Match Series_/*      */_Match Average_/*
> >> *1*     6-Sep   152     146     187     485     161.7
> >> *2*     13-Sep  133     159     141     433     144.3
> >> *3*     20-Sep  147     155     148     450     150.0
> >> *4*     27-Sep  161     151     142     454     151.3
> >> *5*     4-Oct   130     139     116     385     128.3
> >> *6*     11-Oct  *Did Not Bowl. *
> >> *7*     18-Oct  148     187     119     454     151.3
> >> *8*     25-Oct  155     167     169     491     163.7
> >> *9*     1-Nov   140     141     157     438     146.0
> >> *10*    8-Nov   147     135     155     437     145.7
> >> *11*    15-Nov  139     133     155     427     142.3
> >> *12*    22-Nov  164     162     164     490     163.3
> >> *13*    29-Nov  120     110     155     385     128.3
> >> *14*    6-Dec   150     136     125     411     137.0
> >> *15*    13-Dec  170     187     132     489     163.0
> >> *16*    20-Dec  160     168     146     474     158.0
> >> *17*    27-Dec  160     144     150     454     151.3
> >> *18*    3-Jan   151     141     138     430     143.3
> >> */_---Half Season---_/*
> >> *19*    10-Jan  *Did Not Bowl. *
> >> *20*    17-Jan  161     134     147     442     147.3
> >> *21*    24-Jan
> >>
> >>
> >>
> >>
> >>
> >> I use the results generated in columns G and H in another section of the
> >> sheet, as shown:
> >>
> >>                       [AL] [AN] <-------- Column headings
> >>
> >> *Team #2- ACHES & PAINS v2.0
> >> Post Match Play*
> >> *Russ: *        458     *Player's Average: *    152.67
> >> *Vince: *       442     147.33
> >> *Dave: *        555     185.00
> >> *Bob: *         600     200.00
> >>
> >>
> >> Cells AL7 and AN7 are set to = the results calculated and provided
> >> within cells G26 and H26, i.e., they show: 442 and 147.3.
> >>
> >> My problem is that after calculating an update result within columns G
> >> and H, e.g., on 24 Jan, (week # 21's data), the results based on entry
> >> of data in D27:F27, there will be new results in the next row, at cells
> >> G27 and H27. Therefore, I am forced to manually change the formulae
> >> within AL7 and AN7 to correctly reflect the updated results provided in
> >> columns AL and AN, by changing =G26 to =G27 and by changing =H26 to
> =H27.
> >>
> >> Is there an easier method of handling this task, through use of a
> >> function or a formula?
> >>
> >> I hope this is described clearly enough.  If needed, I will attempt to
> >> store the complete calc sheet at a Dropbox link. Let me know.
> >>
> >> Regards,
> >>
> >> VinceB.
> >>
> >>
> >>
>


--
Alan Boba
CISSP, CCENT, ITIL v3 Foundations 2011
Reply | Threaded
Open this post in threaded view
|

Re: Calc Help

WA.TWORSX via AOL-2
Hi Alan:

I am trying to work my way through your suggestions. Changes to satisfy
Design Change  Requirements #1 and #3 are not a problem.

However, Required Design Change #2 is problematic for me. :-[

I am not successful with IF-testing in the Match Average and Total Pins
columns to satisfy Required Design Change #2.

I have tried:

 1. When I use =IF(G27>0;G27/3);"", the returned result is 0. (Not
    acceptable.)
 2. When I use =IF(D27>0;G27/3);"", cell H27 returns a blank
    (acceptable??).


How is a cell's content IF-tested for a number? Is IFNUMBER() needed
here? If, yes, I will try using >0 within the parenthesis.

I inserted: =INDEX($Sheet1.G6:G42,COUNTA($Sheet1.G6:G42),1) into cell
AL7; am getting Error 508.  Please verify the punctuation used in your
suggested formula; I am expecting to see semicolons. I have changed the
commas to semicolons, but that produces a blank cell in AL7, which does
not change after inputting additional scores in columns D, E, and F.   I
am stumped. :-[


TNX for your inputs, Alan.

Regards,

VinceB.


On 1/20/2020 5:58 PM, Alan B wrote:

> I've found a method that can work, providing the spreadsheet design is
> modified somewhat.
> It comes from a formula provided by RusselB in the OpenOffice forums.
>
> =index(range;count(range);1)
>
> For your sample spreadsheet this formula in cell AL7
> =INDEX($Sheet1.G6:G42,COUNTA($Sheet1.G6:G42),1)
> will return the Match Series value for the last value entered. When week#20
> is completed the number in AL7 will update. When week#21 is completed it
> will update again, and so on.
>
> REQUIRED design changes
> 1. There must not be any merged cells that span column G. A merged cell in
> the column breaks the formula. Update the =INDEX formula for column H and
> the same is true, no merged cells.
> 2. The formulas in the Match Average and Total Pins columns should change.
> The IF test should be whether the tested cell contains a number, not
> whether it is blank. This will prevent #value! errors being displayed in
> those cells.
> 3. Last, any blank rows in a column must be BELOW the currently completed
> week number.
>
> To meet design change #1 the "Did not bowl" in week 6 cannot be merged
> cells. And to meet condition #3 they cannot be empty either. As an
> alternative you could enter DNB in each cell in the row.
>
> The row of merged cells that demark "half season" needs to change because
> of #1. If the row is deleted and the season half is indicated by a heavy
> border between week 18 and 19, that would suffice.
>
> This is the simplest solution I could find. More complex formulas might be
> possible that would meet your goal but I'm not familiar enough with Calc to
> readily conceive of them.
>
>
> On Mon, Jan 20, 2020 at 1:31 PM Vince@GMAIL <[hidden email]> wrote:
>
>> Hello Alan B:
>>
>> TNX for reading my query about Calc Help-updating Data.ods.
>>
>> For reasons, unknown to me, my pasted example seems to be
>> plain/unformatted text, although I had sent it out via Mozilla
>> Thunderbird email client using both HTML and plain text to the AOO
>> mailing list, and appeared properly displayed in my compose window.  I
>> realize that w/o the full formatting, it is difficult to read and
>> understand what is displayed at your end.
>>
>> This Dropbox link, I think, will allow you to download the full AOO-Calc
>> file:
>>
>>
>> https://www.dropbox.com/scl/fi/2ibk2u5elew1y4mx9b583/Calc-Help-updating-Data.ods?dl=0&rlkey=zeujgqduhbuo82jmxryylinvd
>> .
>>
>> I am trying to "harvest", via a formula or function, the resulting
>> _Match Series_ (column G) data, which is data only relevant to Vince's
>> game scores. Sorry, I neglected to include the _Vince's Individual
>> Record_ heading over columns B:L in my original example.  The game
>> scores for Vince (only) are being tracked on a weekly basis to generate
>> the result that is given in column $G.
>>
>> At present, Cell AL7 contains the formula =G26. This value changes each
>> week, as Vince's new scores are poked into columns D, E, and F each week
>> (i.e., on the next week's row). So, at present, I am manually changing
>> the formula within AL7 to reflect the current week's row.
>>
>> (The _Match Series_ data for my teammates Russ, Dave, and Bob are being
>> poked manually into AL6, AL8, and AL9, weekly. )
>>
>> In another thread, dated 1/18/2020,  Brian Barker has offered
>> suggestions using =MATCH, =OFFSET, and =INDIRECT functions.  I am not
>> familiar with those functions, but will likely attempt their use when
>> time permits me to learn more about them. Brian also prefers the use of
>> minimal examples; therefore, I fear that I am verbose in my queries and
>> comments. However, I am hopeful that my comments above have been helpful
>> in clarifying my OP.
>>
>> Again, thanks for your interest in solving my problem.
>>
>> Regards,
>>
>> VinceB.
>>
>>
>> On 1/19/2020 10:00 PM, Alan B wrote:
>>> Hello Vince,
>>>
>>> I'm intrigued but not able to follow what you're trying to do. Columns D
>>> through H appear to be the games bowled in any given week, rows (or
>> should
>>> I say weeks) 1 through 21 are the results of the games in those weeks.
>> That
>>> seems clear to me. But whose games, Russ, Vince, Dave, or Bob's?
>>>
>>> The disconnect comes with "using the results in [AL] and [AN]".
>>>
>>> How does a single row shown as 1 - 21 and columns [D] though [H] in your
>>> example relate to multiple individuals [AL] through [AN]?
>> They do not .
>>> And more importantly, what are you trying to achieve? If I understood
>> that
>>> I might be able to offer some useful help.
>> See my comments above. HTH.
>>> On Sat, Jan 18, 2020 at 12:48 PM Vince@GMAIL <[hidden email]> wrote:
>>>
>>>> AOO 4.1.5 on Win10 Home desktop
>>>>
>>>> _________________________________
>>>>
>>>> Note: Cells with a yellow background identify required data must be
>>>> entered.  Cells with a (darker) gray background identify cells which
>>>> show calculated results.
>>>>
>>>> I have a Calc sheet that I enter data on a regular basis, as shown
>> below:
>>>> [D] [E] [F]    [G]            [H] <-------- Column headings
>>>>
>>>> */_Today is Week #_/*   */_Today's Date Is_/*   */_Game #1_/*   */_Game
>> #2
>>>> _/*     */_Game #3_/*   */_Match Series_/*      */_Match Average_/*
>>>> *1*     6-Sep   152     146     187     485     161.7
>>>> *2*     13-Sep  133     159     141     433     144.3
>>>> *3*     20-Sep  147     155     148     450     150.0
>>>> *4*     27-Sep  161     151     142     454     151.3
>>>> *5*     4-Oct   130     139     116     385     128.3
>>>> *6*     11-Oct  *Did Not Bowl. *
>>>> *7*     18-Oct  148     187     119     454     151.3
>>>> *8*     25-Oct  155     167     169     491     163.7
>>>> *9*     1-Nov   140     141     157     438     146.0
>>>> *10*    8-Nov   147     135     155     437     145.7
>>>> *11*    15-Nov  139     133     155     427     142.3
>>>> *12*    22-Nov  164     162     164     490     163.3
>>>> *13*    29-Nov  120     110     155     385     128.3
>>>> *14*    6-Dec   150     136     125     411     137.0
>>>> *15*    13-Dec  170     187     132     489     163.0
>>>> *16*    20-Dec  160     168     146     474     158.0
>>>> *17*    27-Dec  160     144     150     454     151.3
>>>> *18*    3-Jan   151     141     138     430     143.3
>>>> */_---Half Season---_/*
>>>> *19*    10-Jan  *Did Not Bowl. *
>>>> *20*    17-Jan  161     134     147     442     147.3
>>>> *21*    24-Jan
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> I use the results generated in columns G and H in another section of the
>>>> sheet, as shown:
>>>>
>>>>                        [AL] [AN] <-------- Column headings
>>>>
>>>> *Team #2- ACHES & PAINS v2.0
>>>> Post Match Play*
>>>> *Russ: *        458     *Player's Average: *    152.67
>>>> *Vince: *       442     147.33
>>>> *Dave: *        555     185.00
>>>> *Bob: *         600     200.00
>>>>
>>>>
>>>> Cells AL7 and AN7 are set to = the results calculated and provided
>>>> within cells G26 and H26, i.e., they show: 442 and 147.3.
>>>>
>>>> My problem is that after calculating an update result within columns G
>>>> and H, e.g., on 24 Jan, (week # 21's data), the results based on entry
>>>> of data in D27:F27, there will be new results in the next row, at cells
>>>> G27 and H27. Therefore, I am forced to manually change the formulae
>>>> within AL7 and AN7 to correctly reflect the updated results provided in
>>>> columns AL and AN, by changing =G26 to =G27 and by changing =H26 to
>> =H27.
>>>> Is there an easier method of handling this task, through use of a
>>>> function or a formula?
>>>>
>>>> I hope this is described clearly enough.  If needed, I will attempt to
>>>> store the complete calc sheet at a Dropbox link. Let me know.
>>>>
>>>> Regards,
>>>>
>>>> VinceB.
>>>>
>>>>
>>>>
>
Reply | Threaded
Open this post in threaded view
|

Re: Calc Help

Alan Boba-2
I tested by modifying the formula in cell H7 in your sample spreadsheet.
The modified formula is...

=IF((ISNUMBER(G7));(G7/3);"")

The test is whether or not the value in G7 is a number, ISNUMBER(G7). Text
or a blank returns false, any number returns true. Replace the existing
formula in column H with this. The same changes should be applied in the
formula in column J.

The if test logic is reversed from your original formula. Now if the test
is true because the cell in column G contains a number then value1, Match
Series / 3, should be the result.

So IF(test; value1; value2) changes the order of your values. Instead of
IF(test;"";(G7/3)) the formula is IF(test;(G7/3);"").

On Wed, Jan 22, 2020 at 9:45 AM WA.TWORSX via AOL
<[hidden email]> wrote:

> Hi Alan:
>
> I am trying to work my way through your suggestions. Changes to satisfy
> Design Change  Requirements #1 and #3 are not a problem.
>
> However, Required Design Change #2 is problematic for me. :-[
>
> I am not successful with IF-testing in the Match Average and Total Pins
> columns to satisfy Required Design Change #2.
>
> I have tried:
>
>  1. When I use =IF(G27>0;G27/3);"", the returned result is 0. (Not
>     acceptable.)
>  2. When I use =IF(D27>0;G27/3);"", cell H27 returns a blank
>     (acceptable??).
>
>
> How is a cell's content IF-tested for a number? Is IFNUMBER() needed
> here? If, yes, I will try using >0 within the parenthesis.
>
> I inserted: =INDEX($Sheet1.G6:G42,COUNTA($Sheet1.G6:G42),1) into cell
> AL7; am getting Error 508.  Please verify the punctuation used in your
> suggested formula; I am expecting to see semicolons. I have changed the
> commas to semicolons, but that produces a blank cell in AL7, which does
> not change after inputting additional scores in columns D, E, and F.   I
> am stumped. :-[
>
>
> TNX for your inputs, Alan.
>
> Regards,
>
> VinceB.
>
>
> On 1/20/2020 5:58 PM, Alan B wrote:
> > I've found a method that can work, providing the spreadsheet design is
> > modified somewhat.
> > It comes from a formula provided by RusselB in the OpenOffice forums.
> >
> > =index(range;count(range);1)
> >
> > For your sample spreadsheet this formula in cell AL7
> > =INDEX($Sheet1.G6:G42,COUNTA($Sheet1.G6:G42),1)
> > will return the Match Series value for the last value entered. When
> week#20
> > is completed the number in AL7 will update. When week#21 is completed it
> > will update again, and so on.
> >
> > REQUIRED design changes
> > 1. There must not be any merged cells that span column G. A merged cell
> in
> > the column breaks the formula. Update the =INDEX formula for column H and
> > the same is true, no merged cells.
> > 2. The formulas in the Match Average and Total Pins columns should
> change.
> > The IF test should be whether the tested cell contains a number, not
> > whether it is blank. This will prevent #value! errors being displayed in
> > those cells.
> > 3. Last, any blank rows in a column must be BELOW the currently completed
> > week number.
> >
> > To meet design change #1 the "Did not bowl" in week 6 cannot be merged
> > cells. And to meet condition #3 they cannot be empty either. As an
> > alternative you could enter DNB in each cell in the row.
> >
> > The row of merged cells that demark "half season" needs to change because
> > of #1. If the row is deleted and the season half is indicated by a heavy
> > border between week 18 and 19, that would suffice.
> >
> > This is the simplest solution I could find. More complex formulas might
> be
> > possible that would meet your goal but I'm not familiar enough with Calc
> to
> > readily conceive of them.
> >
> >
> > On Mon, Jan 20, 2020 at 1:31 PM Vince@GMAIL <[hidden email]> wrote:
> >
> >> Hello Alan B:
> >>
> >> TNX for reading my query about Calc Help-updating Data.ods.
> >>
> >> For reasons, unknown to me, my pasted example seems to be
> >> plain/unformatted text, although I had sent it out via Mozilla
> >> Thunderbird email client using both HTML and plain text to the AOO
> >> mailing list, and appeared properly displayed in my compose window.  I
> >> realize that w/o the full formatting, it is difficult to read and
> >> understand what is displayed at your end.
> >>
> >> This Dropbox link, I think, will allow you to download the full AOO-Calc
> >> file:
> >>
> >>
> >>
> https://www.dropbox.com/scl/fi/2ibk2u5elew1y4mx9b583/Calc-Help-updating-Data.ods?dl=0&rlkey=zeujgqduhbuo82jmxryylinvd
> >> .
> >>
> >> I am trying to "harvest", via a formula or function, the resulting
> >> _Match Series_ (column G) data, which is data only relevant to Vince's
> >> game scores. Sorry, I neglected to include the _Vince's Individual
> >> Record_ heading over columns B:L in my original example.  The game
> >> scores for Vince (only) are being tracked on a weekly basis to generate
> >> the result that is given in column $G.
> >>
> >> At present, Cell AL7 contains the formula =G26. This value changes each
> >> week, as Vince's new scores are poked into columns D, E, and F each week
> >> (i.e., on the next week's row). So, at present, I am manually changing
> >> the formula within AL7 to reflect the current week's row.
> >>
> >> (The _Match Series_ data for my teammates Russ, Dave, and Bob are being
> >> poked manually into AL6, AL8, and AL9, weekly. )
> >>
> >> In another thread, dated 1/18/2020,  Brian Barker has offered
> >> suggestions using =MATCH, =OFFSET, and =INDIRECT functions.  I am not
> >> familiar with those functions, but will likely attempt their use when
> >> time permits me to learn more about them. Brian also prefers the use of
> >> minimal examples; therefore, I fear that I am verbose in my queries and
> >> comments. However, I am hopeful that my comments above have been helpful
> >> in clarifying my OP.
> >>
> >> Again, thanks for your interest in solving my problem.
> >>
> >> Regards,
> >>
> >> VinceB.
> >>
> >>
> >> On 1/19/2020 10:00 PM, Alan B wrote:
> >>> Hello Vince,
> >>>
> >>> I'm intrigued but not able to follow what you're trying to do. Columns
> D
> >>> through H appear to be the games bowled in any given week, rows (or
> >> should
> >>> I say weeks) 1 through 21 are the results of the games in those weeks.
> >> That
> >>> seems clear to me. But whose games, Russ, Vince, Dave, or Bob's?
> >>>
> >>> The disconnect comes with "using the results in [AL] and [AN]".
> >>>
> >>> How does a single row shown as 1 - 21 and columns [D] though [H] in
> your
> >>> example relate to multiple individuals [AL] through [AN]?
> >> They do not .
> >>> And more importantly, what are you trying to achieve? If I understood
> >> that
> >>> I might be able to offer some useful help.
> >> See my comments above. HTH.
> >>> On Sat, Jan 18, 2020 at 12:48 PM Vince@GMAIL <[hidden email]>
> wrote:
> >>>
> >>>> AOO 4.1.5 on Win10 Home desktop
> >>>>
> >>>> _________________________________
> >>>>
> >>>> Note: Cells with a yellow background identify required data must be
> >>>> entered.  Cells with a (darker) gray background identify cells which
> >>>> show calculated results.
> >>>>
> >>>> I have a Calc sheet that I enter data on a regular basis, as shown
> >> below:
> >>>> [D] [E] [F]    [G]            [H] <-------- Column headings
> >>>>
> >>>> */_Today is Week #_/*   */_Today's Date Is_/*   */_Game #1_/*
>  */_Game
> >> #2
> >>>> _/*     */_Game #3_/*   */_Match Series_/*      */_Match Average_/*
> >>>> *1*     6-Sep   152     146     187     485     161.7
> >>>> *2*     13-Sep  133     159     141     433     144.3
> >>>> *3*     20-Sep  147     155     148     450     150.0
> >>>> *4*     27-Sep  161     151     142     454     151.3
> >>>> *5*     4-Oct   130     139     116     385     128.3
> >>>> *6*     11-Oct  *Did Not Bowl. *
> >>>> *7*     18-Oct  148     187     119     454     151.3
> >>>> *8*     25-Oct  155     167     169     491     163.7
> >>>> *9*     1-Nov   140     141     157     438     146.0
> >>>> *10*    8-Nov   147     135     155     437     145.7
> >>>> *11*    15-Nov  139     133     155     427     142.3
> >>>> *12*    22-Nov  164     162     164     490     163.3
> >>>> *13*    29-Nov  120     110     155     385     128.3
> >>>> *14*    6-Dec   150     136     125     411     137.0
> >>>> *15*    13-Dec  170     187     132     489     163.0
> >>>> *16*    20-Dec  160     168     146     474     158.0
> >>>> *17*    27-Dec  160     144     150     454     151.3
> >>>> *18*    3-Jan   151     141     138     430     143.3
> >>>> */_---Half Season---_/*
> >>>> *19*    10-Jan  *Did Not Bowl. *
> >>>> *20*    17-Jan  161     134     147     442     147.3
> >>>> *21*    24-Jan
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>> I use the results generated in columns G and H in another section of
> the
> >>>> sheet, as shown:
> >>>>
> >>>>                        [AL] [AN] <-------- Column headings
> >>>>
> >>>> *Team #2- ACHES & PAINS v2.0
> >>>> Post Match Play*
> >>>> *Russ: *        458     *Player's Average: *    152.67
> >>>> *Vince: *       442     147.33
> >>>> *Dave: *        555     185.00
> >>>> *Bob: *         600     200.00
> >>>>
> >>>>
> >>>> Cells AL7 and AN7 are set to = the results calculated and provided
> >>>> within cells G26 and H26, i.e., they show: 442 and 147.3.
> >>>>
> >>>> My problem is that after calculating an update result within columns G
> >>>> and H, e.g., on 24 Jan, (week # 21's data), the results based on entry
> >>>> of data in D27:F27, there will be new results in the next row, at
> cells
> >>>> G27 and H27. Therefore, I am forced to manually change the formulae
> >>>> within AL7 and AN7 to correctly reflect the updated results provided
> in
> >>>> columns AL and AN, by changing =G26 to =G27 and by changing =H26 to
> >> =H27.
> >>>> Is there an easier method of handling this task, through use of a
> >>>> function or a formula?
> >>>>
> >>>> I hope this is described clearly enough.  If needed, I will attempt to
> >>>> store the complete calc sheet at a Dropbox link. Let me know.
> >>>>
> >>>> Regards,
> >>>>
> >>>> VinceB.
> >>>>
> >>>>
> >>>>
> >
>


--
Alan Boba
CISSP, CCENT, ITIL v3 Foundations 2011
Reply | Threaded
Open this post in threaded view
|

Re: Calc Help

WA.TWORSX via AOL-2
In reply to this post by Alan Boba-2

On 1/20/2020 5:58 PM, Alan B wrote:

> I've found a method that can work, providing the spreadsheet design is
> modified somewhat.
> It comes from a formula provided by RusselB in the OpenOffice forums.
>
> =index(range;count(range);1)
>
> For your sample spreadsheet this formula in cell AL7
> =INDEX($Sheet1.G6:G42,COUNTA($Sheet1.G6:G42),1)
> will return the Match Series value for the last value entered. When week#20
> is completed the number in AL7 will update. When week#21 is completed it
> will update again, and so on.
>
> REQUIRED design changes
> 1. There must not be any merged cells that span column G. A merged cell in
> the column breaks the formula. Update the =INDEX formula for column H and
> the same is true, no merged cells.
> 2. The formulas in the Match Average and Total Pins columns should change.
> The IF test should be whether the tested cell contains a number, not
> whether it is blank. This will prevent #value! errors being displayed in
> those cells.
> 3. Last, any blank rows in a column must be BELOW the currently completed
> week number.
>
> To meet design change #1 the "Did not bowl" in week 6 cannot be merged
> cells. And to meet condition #3 they cannot be empty either. As an
> alternative you could enter DNB in each cell in the row.

AlanB:   What is meant by "DNB" ?

> The row of merged cells that demark "half season" needs to change because
> of #1. If the row is deleted and the season half is indicated by a heavy
> border between week 18 and 19, that would suffice.
>
> This is the simplest solution I could find. More complex formulas might be
> possible that would meet your goal but I'm not familiar enough with Calc to
> readily conceive of them.
>
>
> On Mon, Jan 20, 2020 at 1:31 PM Vince@GMAIL <[hidden email]> wrote:
>
>> Hello Alan B:
>>
>> TNX for reading my query about Calc Help-updating Data.ods.
>>
>> For reasons, unknown to me, my pasted example seems to be
>> plain/unformatted text, although I had sent it out via Mozilla
>> Thunderbird email client using both HTML and plain text to the AOO
>> mailing list, and appeared properly displayed in my compose window.  I
>> realize that w/o the full formatting, it is difficult to read and
>> understand what is displayed at your end.
>>
>> This Dropbox link, I think, will allow you to download the full AOO-Calc
>> file:
>>
>>
>> https://www.dropbox.com/scl/fi/2ibk2u5elew1y4mx9b583/Calc-Help-updating-Data.ods?dl=0&rlkey=zeujgqduhbuo82jmxryylinvd
>> .
>>
>> I am trying to "harvest", via a formula or function, the resulting
>> _Match Series_ (column G) data, which is data only relevant to Vince's
>> game scores. Sorry, I neglected to include the _Vince's Individual
>> Record_ heading over columns B:L in my original example.  The game
>> scores for Vince (only) are being tracked on a weekly basis to generate
>> the result that is given in column $G.
>>
>> At present, Cell AL7 contains the formula =G26. This value changes each
>> week, as Vince's new scores are poked into columns D, E, and F each week
>> (i.e., on the next week's row). So, at present, I am manually changing
>> the formula within AL7 to reflect the current week's row.
>>
>> (The _Match Series_ data for my teammates Russ, Dave, and Bob are being
>> poked manually into AL6, AL8, and AL9, weekly. )
>>
>> In another thread, dated 1/18/2020,  Brian Barker has offered
>> suggestions using =MATCH, =OFFSET, and =INDIRECT functions.  I am not
>> familiar with those functions, but will likely attempt their use when
>> time permits me to learn more about them. Brian also prefers the use of
>> minimal examples; therefore, I fear that I am verbose in my queries and
>> comments. However, I am hopeful that my comments above have been helpful
>> in clarifying my OP.
>>
>> Again, thanks for your interest in solving my problem.
>>
>> Regards,
>>
>> VinceB.
>>
>>
>> On 1/19/2020 10:00 PM, Alan B wrote:
>>> Hello Vince,
>>>
>>> I'm intrigued but not able to follow what you're trying to do. Columns D
>>> through H appear to be the games bowled in any given week, rows (or
>> should
>>> I say weeks) 1 through 21 are the results of the games in those weeks.
>> That
>>> seems clear to me. But whose games, Russ, Vince, Dave, or Bob's?
>>>
>>> The disconnect comes with "using the results in [AL] and [AN]".
>>>
>>> How does a single row shown as 1 - 21 and columns [D] though [H] in your
>>> example relate to multiple individuals [AL] through [AN]?
>> They do not .
>>> And more importantly, what are you trying to achieve? If I understood
>> that
>>> I might be able to offer some useful help.
>> See my comments above. HTH.
>>> On Sat, Jan 18, 2020 at 12:48 PM Vince@GMAIL <[hidden email]> wrote:
>>>
>>>> AOO 4.1.5 on Win10 Home desktop
>>>>
>>>> _________________________________
>>>>
>>>> Note: Cells with a yellow background identify required data must be
>>>> entered.  Cells with a (darker) gray background identify cells which
>>>> show calculated results.
>>>>
>>>> I have a Calc sheet that I enter data on a regular basis, as shown
>> below:
>>>> [D] [E] [F]    [G]            [H] <-------- Column headings
>>>>
>>>> */_Today is Week #_/*   */_Today's Date Is_/*   */_Game #1_/*   */_Game
>> #2
>>>> _/*     */_Game #3_/*   */_Match Series_/*      */_Match Average_/*
>>>> *1*     6-Sep   152     146     187     485     161.7
>>>> *2*     13-Sep  133     159     141     433     144.3
>>>> *3*     20-Sep  147     155     148     450     150.0
>>>> *4*     27-Sep  161     151     142     454     151.3
>>>> *5*     4-Oct   130     139     116     385     128.3
>>>> *6*     11-Oct  *Did Not Bowl. *
>>>> *7*     18-Oct  148     187     119     454     151.3
>>>> *8*     25-Oct  155     167     169     491     163.7
>>>> *9*     1-Nov   140     141     157     438     146.0
>>>> *10*    8-Nov   147     135     155     437     145.7
>>>> *11*    15-Nov  139     133     155     427     142.3
>>>> *12*    22-Nov  164     162     164     490     163.3
>>>> *13*    29-Nov  120     110     155     385     128.3
>>>> *14*    6-Dec   150     136     125     411     137.0
>>>> *15*    13-Dec  170     187     132     489     163.0
>>>> *16*    20-Dec  160     168     146     474     158.0
>>>> *17*    27-Dec  160     144     150     454     151.3
>>>> *18*    3-Jan   151     141     138     430     143.3
>>>> */_---Half Season---_/*
>>>> *19*    10-Jan  *Did Not Bowl. *
>>>> *20*    17-Jan  161     134     147     442     147.3
>>>> *21*    24-Jan
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> I use the results generated in columns G and H in another section of the
>>>> sheet, as shown:
>>>>
>>>>                        [AL] [AN] <-------- Column headings
>>>>
>>>> *Team #2- ACHES & PAINS v2.0
>>>> Post Match Play*
>>>> *Russ: *        458     *Player's Average: *    152.67
>>>> *Vince: *       442     147.33
>>>> *Dave: *        555     185.00
>>>> *Bob: *         600     200.00
>>>>
>>>>
>>>> Cells AL7 and AN7 are set to = the results calculated and provided
>>>> within cells G26 and H26, i.e., they show: 442 and 147.3.
>>>>
>>>> My problem is that after calculating an update result within columns G
>>>> and H, e.g., on 24 Jan, (week # 21's data), the results based on entry
>>>> of data in D27:F27, there will be new results in the next row, at cells
>>>> G27 and H27. Therefore, I am forced to manually change the formulae
>>>> within AL7 and AN7 to correctly reflect the updated results provided in
>>>> columns AL and AN, by changing =G26 to =G27 and by changing =H26 to
>> =H27.
>>>> Is there an easier method of handling this task, through use of a
>>>> function or a formula?
>>>>
>>>> I hope this is described clearly enough.  If needed, I will attempt to
>>>> store the complete calc sheet at a Dropbox link. Let me know.
>>>>
>>>> Regards,
>>>>
>>>> VinceB.
>>>>
>>>>
>>>>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Calc Help

WA.TWORSX via AOL-2
Ooops!

DNB=Did not Bowl.

I am presently troubleshooting why the result at cell AL7 (i.e.,
=INDEX($Sheet1.G6:G42,COUNTA($Sheet1.G6:G42),1) is not correct.

The difference between COUNT and COUNTA is yet to sink into this
author's gray matter.


On 1/24/2020 10:16 AM, WA.TWORSX via AOL wrote:

>
> On 1/20/2020 5:58 PM, Alan B wrote:
>> I've found a method that can work, providing the spreadsheet design is
>> modified somewhat.
>> It comes from a formula provided by RusselB in the OpenOffice forums.
>>
>> =index(range;count(range);1)
>>
>> For your sample spreadsheet this formula in cell AL7
>> =INDEX($Sheet1.G6:G42,COUNTA($Sheet1.G6:G42),1)
>> will return the Match Series value for the last value entered. When
>> week#20
>> is completed the number in AL7 will update. When week#21 is completed it
>> will update again, and so on.
>>
>> REQUIRED design changes
>> 1. There must not be any merged cells that span column G. A merged
>> cell in
>> the column breaks the formula. Update the =INDEX formula for column H
>> and
>> the same is true, no merged cells.
>> 2. The formulas in the Match Average and Total Pins columns should
>> change.
>> The IF test should be whether the tested cell contains a number, not
>> whether it is blank. This will prevent #value! errors being displayed in
>> those cells.
>> 3. Last, any blank rows in a column must be BELOW the currently
>> completed
>> week number.
>>
>> To meet design change #1 the "Did not bowl" in week 6 cannot be merged
>> cells. And to meet condition #3 they cannot be empty either. As an
>> alternative you could enter DNB in each cell in the row.
>
> AlanB:   What is meant by "DNB" ?
>
>> The row of merged cells that demark "half season" needs to change
>> because
>> of #1. If the row is deleted and the season half is indicated by a heavy
>> border between week 18 and 19, that would suffice.
>>
>> This is the simplest solution I could find. More complex formulas
>> might be
>> possible that would meet your goal but I'm not familiar enough with
>> Calc to
>> readily conceive of them.
>>
>>
>> On Mon, Jan 20, 2020 at 1:31 PM Vince@GMAIL <[hidden email]> wrote:
>>
>>> Hello Alan B:
>>>
>>> TNX for reading my query about Calc Help-updating Data.ods.
>>>
>>> For reasons, unknown to me, my pasted example seems to be
>>> plain/unformatted text, although I had sent it out via Mozilla
>>> Thunderbird email client using both HTML and plain text to the AOO
>>> mailing list, and appeared properly displayed in my compose window.  I
>>> realize that w/o the full formatting, it is difficult to read and
>>> understand what is displayed at your end.
>>>
>>> This Dropbox link, I think, will allow you to download the full
>>> AOO-Calc
>>> file:
>>>
>>>
>>> https://www.dropbox.com/scl/fi/2ibk2u5elew1y4mx9b583/Calc-Help-updating-Data.ods?dl=0&rlkey=zeujgqduhbuo82jmxryylinvd 
>>>
>>> .
>>>
>>> I am trying to "harvest", via a formula or function, the resulting
>>> _Match Series_ (column G) data, which is data only relevant to Vince's
>>> game scores. Sorry, I neglected to include the _Vince's Individual
>>> Record_ heading over columns B:L in my original example.  The game
>>> scores for Vince (only) are being tracked on a weekly basis to generate
>>> the result that is given in column $G.
>>>
>>> At present, Cell AL7 contains the formula =G26. This value changes each
>>> week, as Vince's new scores are poked into columns D, E, and F each
>>> week
>>> (i.e., on the next week's row). So, at present, I am manually changing
>>> the formula within AL7 to reflect the current week's row.
>>>
>>> (The _Match Series_ data for my teammates Russ, Dave, and Bob are being
>>> poked manually into AL6, AL8, and AL9, weekly. )
>>>
>>> In another thread, dated 1/18/2020,  Brian Barker has offered
>>> suggestions using =MATCH, =OFFSET, and =INDIRECT functions.  I am not
>>> familiar with those functions, but will likely attempt their use when
>>> time permits me to learn more about them. Brian also prefers the use of
>>> minimal examples; therefore, I fear that I am verbose in my queries and
>>> comments. However, I am hopeful that my comments above have been
>>> helpful
>>> in clarifying my OP.
>>>
>>> Again, thanks for your interest in solving my problem.
>>>
>>> Regards,
>>>
>>> VinceB.
>>>
>>>
>>> On 1/19/2020 10:00 PM, Alan B wrote:
>>>> Hello Vince,
>>>>
>>>> I'm intrigued but not able to follow what you're trying to do.
>>>> Columns D
>>>> through H appear to be the games bowled in any given week, rows (or
>>> should
>>>> I say weeks) 1 through 21 are the results of the games in those weeks.
>>> That
>>>> seems clear to me. But whose games, Russ, Vince, Dave, or Bob's?
>>>>
>>>> The disconnect comes with "using the results in [AL] and [AN]".
>>>>
>>>> How does a single row shown as 1 - 21 and columns [D] though [H] in
>>>> your
>>>> example relate to multiple individuals [AL] through [AN]?
>>> They do not .
>>>> And more importantly, what are you trying to achieve? If I understood
>>> that
>>>> I might be able to offer some useful help.
>>> See my comments above. HTH.
>>>> On Sat, Jan 18, 2020 at 12:48 PM Vince@GMAIL <[hidden email]>
>>>> wrote:
>>>>
>>>>> AOO 4.1.5 on Win10 Home desktop
>>>>>
>>>>> _________________________________
>>>>>
>>>>> Note: Cells with a yellow background identify required data must be
>>>>> entered.  Cells with a (darker) gray background identify cells which
>>>>> show calculated results.
>>>>>
>>>>> I have a Calc sheet that I enter data on a regular basis, as shown
>>> below:
>>>>> [D] [E] [F]    [G]            [H] <-------- Column headings
>>>>>
>>>>> */_Today is Week #_/*   */_Today's Date Is_/*   */_Game #1_/*  
>>>>> */_Game
>>> #2
>>>>> _/*     */_Game #3_/*   */_Match Series_/*      */_Match Average_/*
>>>>> *1*     6-Sep   152     146     187     485     161.7
>>>>> *2*     13-Sep  133     159     141     433     144.3
>>>>> *3*     20-Sep  147     155     148     450     150.0
>>>>> *4*     27-Sep  161     151     142     454     151.3
>>>>> *5*     4-Oct   130     139     116     385     128.3
>>>>> *6*     11-Oct  *Did Not Bowl. *
>>>>> *7*     18-Oct  148     187     119     454     151.3
>>>>> *8*     25-Oct  155     167     169     491     163.7
>>>>> *9*     1-Nov   140     141     157     438     146.0
>>>>> *10*    8-Nov   147     135     155     437     145.7
>>>>> *11*    15-Nov  139     133     155     427     142.3
>>>>> *12*    22-Nov  164     162     164     490     163.3
>>>>> *13*    29-Nov  120     110     155     385     128.3
>>>>> *14*    6-Dec   150     136     125     411     137.0
>>>>> *15*    13-Dec  170     187     132     489     163.0
>>>>> *16*    20-Dec  160     168     146     474     158.0
>>>>> *17*    27-Dec  160     144     150     454     151.3
>>>>> *18*    3-Jan   151     141     138     430     143.3
>>>>> */_---Half Season---_/*
>>>>> *19*    10-Jan  *Did Not Bowl. *
>>>>> *20*    17-Jan  161     134     147     442     147.3
>>>>> *21*    24-Jan
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> I use the results generated in columns G and H in another section
>>>>> of the
>>>>> sheet, as shown:
>>>>>
>>>>>                        [AL] [AN] <-------- Column headings
>>>>>
>>>>> *Team #2- ACHES & PAINS v2.0
>>>>> Post Match Play*
>>>>> *Russ: *        458     *Player's Average: *    152.67
>>>>> *Vince: *       442     147.33
>>>>> *Dave: *        555     185.00
>>>>> *Bob: *         600     200.00
>>>>>
>>>>>
>>>>> Cells AL7 and AN7 are set to = the results calculated and provided
>>>>> within cells G26 and H26, i.e., they show: 442 and 147.3.
>>>>>
>>>>> My problem is that after calculating an update result within
>>>>> columns G
>>>>> and H, e.g., on 24 Jan, (week # 21's data), the results based on
>>>>> entry
>>>>> of data in D27:F27, there will be new results in the next row, at
>>>>> cells
>>>>> G27 and H27. Therefore, I am forced to manually change the formulae
>>>>> within AL7 and AN7 to correctly reflect the updated results
>>>>> provided in
>>>>> columns AL and AN, by changing =G26 to =G27 and by changing =H26 to
>>> =H27.
>>>>> Is there an easier method of handling this task, through use of a
>>>>> function or a formula?
>>>>>
>>>>> I hope this is described clearly enough.  If needed, I will
>>>>> attempt to
>>>>> store the complete calc sheet at a Dropbox link. Let me know.
>>>>>
>>>>> Regards,
>>>>>
>>>>> VinceB.
>>>>>
>>>>>
>>>>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
Reply | Threaded
Open this post in threaded view
|

Re: Calc Help

Alan Boba-2
In reply to this post by WA.TWORSX via AOL-2
DNB is meant to note someone did not bowl. It is just text entered in each
cell rather than merging the cells and entering "Did not bowl".

Now, some bad news. After reviewing what I suggested I realized the formula
given for cell AL7 will always show the value in G36 if the column G
formula is copied to every row at the start of each season. If it is added
week by week to each row in column G, then no problem. That doesn't make
for an easy to maintain sheet though.

My idea was enter three scores each week and everything else updates.

I did a little more thinking and came up with the following which meets the
goal above (this all started so AL7 and AN7 will update without being
modified by hand)...

* cells in the range D6:F41 must always be a score, the text "DNB", or be
empty
* Rows 6 through 41 must always have the games entered week by week. IOW,
no entering week 7 before week 6.
* the row for half way in the season must be removed. Only rows for bowling
weeks are allowed

* The formula in cell G6 for "Match Series" column is...
=IF(SUM(D6:F6)=0,IF(D6="DNB","DNB",""),SUM(D6:F6))
The formula displays the total for the games, DNB, or "".
SUM is 0 if columns D-F have DNB or "".
This formula can be copied to all the rows in the column and it will update
correctly for each row it is in.

* The formula in cell H6 for "Match Average" column is...
=IF(ISNUMBER(G6),G6/3,G6)
This formula can be copied to all the rows in the column and it will update
correctly for each row it is in.

* The formula in cell J6 for "Total Pins" column calculates total pins for
rows 6 through row the formula is in.
=IF(ISNUMBER(I6),DSUM($G$5:INDIRECT(CONCATENATE("$G",ROW())),1,$BB$5:$BB$6),"")
This formula should be copied to all week# rows in column J. It updates
correctly for each row it is in.
So for row 10 it calculates the total of rows 6 through 10, For row 15 the
total of row 6 through 15 and so on. If there is no number in League
Average it displays "".
Buried in the formula is the DSUM function. It requires a "Criteria Range"
to work. As written here the criteria range is cells BB5 and BB6. Cell BB5
contains the formula =G5. Cell BB6 contains >=0. For a criteria range to
work the column titles of the "database" and the "criteria range" must
match exactly (not accounting for formatting). If column title G, "Match
Series", is ever changed having cell BB5 be a formula that refers to it
prevents needing to remember to update the criteria.
The INDIRECT function enables the formula to identify the ROW() it is in so
the DSUM database range doesn't have to be entered individually for each
row, e.g. $G$5:$G$5, $G$5:$G$6, $G$5:$G$7 ... , $G$5:$G$10 and so on.

* The formula in cell AL7 for Vince post match play is...
=INDEX($Sheet1.G6:G41,36-COUNTBLANK($G$6:$G$41),1)
The column G formula returns one of three values, the sum of the games,
"DNB", or "". The season has 36 weeks. The formula presumes that as weeks
go by the Match Series column will be either a number, "DNB", or "" and
that "" are always the last rows in the column. Provided these conditions
are true the formula always returns the most recently entered week's Match
Series result.

* The formula in cell AN7 for Vince post match average is...
=INDEX($Sheet1.H6:H41,36-COUNTBLANK($H$6:$H$41),1)
This works the same as the above formula except for Match Average.




On Fri, Jan 24, 2020 at 10:16 AM WA.TWORSX via AOL
<[hidden email]> wrote:

>
> On 1/20/2020 5:58 PM, Alan B wrote:
> > I've found a method that can work, providing the spreadsheet design is
> > modified somewhat.
> > It comes from a formula provided by RusselB in the OpenOffice forums.
> >
> > =index(range;count(range);1)
> >
> > For your sample spreadsheet this formula in cell AL7
> > =INDEX($Sheet1.G6:G42,COUNTA($Sheet1.G6:G42),1)
> > will return the Match Series value for the last value entered. When
> week#20
> > is completed the number in AL7 will update. When week#21 is completed it
> > will update again, and so on.
> >
> > REQUIRED design changes
> > 1. There must not be any merged cells that span column G. A merged cell
> in
> > the column breaks the formula. Update the =INDEX formula for column H and
> > the same is true, no merged cells.
> > 2. The formulas in the Match Average and Total Pins columns should
> change.
> > The IF test should be whether the tested cell contains a number, not
> > whether it is blank. This will prevent #value! errors being displayed in
> > those cells.
> > 3. Last, any blank rows in a column must be BELOW the currently completed
> > week number.
> >
> > To meet design change #1 the "Did not bowl" in week 6 cannot be merged
> > cells. And to meet condition #3 they cannot be empty either. As an
> > alternative you could enter DNB in each cell in the row.
>
> AlanB:   What is meant by "DNB" ?
>
> > The row of merged cells that demark "half season" needs to change because
> > of #1. If the row is deleted and the season half is indicated by a heavy
> > border between week 18 and 19, that would suffice.
> >
> > This is the simplest solution I could find. More complex formulas might
> be
> > possible that would meet your goal but I'm not familiar enough with Calc
> to
> > readily conceive of them.
> >
> >
> > On Mon, Jan 20, 2020 at 1:31 PM Vince@GMAIL <[hidden email]> wrote:
> >
> >> Hello Alan B:
> >>
> >> TNX for reading my query about Calc Help-updating Data.ods.
> >>
> >> For reasons, unknown to me, my pasted example seems to be
> >> plain/unformatted text, although I had sent it out via Mozilla
> >> Thunderbird email client using both HTML and plain text to the AOO
> >> mailing list, and appeared properly displayed in my compose window.  I
> >> realize that w/o the full formatting, it is difficult to read and
> >> understand what is displayed at your end.
> >>
> >> This Dropbox link, I think, will allow you to download the full AOO-Calc
> >> file:
> >>
> >>
> >>
> https://www.dropbox.com/scl/fi/2ibk2u5elew1y4mx9b583/Calc-Help-updating-Data.ods?dl=0&rlkey=zeujgqduhbuo82jmxryylinvd
> >> .
> >>
> >> I am trying to "harvest", via a formula or function, the resulting
> >> _Match Series_ (column G) data, which is data only relevant to Vince's
> >> game scores. Sorry, I neglected to include the _Vince's Individual
> >> Record_ heading over columns B:L in my original example.  The game
> >> scores for Vince (only) are being tracked on a weekly basis to generate
> >> the result that is given in column $G.
> >>
> >> At present, Cell AL7 contains the formula =G26. This value changes each
> >> week, as Vince's new scores are poked into columns D, E, and F each week
> >> (i.e., on the next week's row). So, at present, I am manually changing
> >> the formula within AL7 to reflect the current week's row.
> >>
> >> (The _Match Series_ data for my teammates Russ, Dave, and Bob are being
> >> poked manually into AL6, AL8, and AL9, weekly. )
> >>
> >> In another thread, dated 1/18/2020,  Brian Barker has offered
> >> suggestions using =MATCH, =OFFSET, and =INDIRECT functions.  I am not
> >> familiar with those functions, but will likely attempt their use when
> >> time permits me to learn more about them. Brian also prefers the use of
> >> minimal examples; therefore, I fear that I am verbose in my queries and
> >> comments. However, I am hopeful that my comments above have been helpful
> >> in clarifying my OP.
> >>
> >> Again, thanks for your interest in solving my problem.
> >>
> >> Regards,
> >>
> >> VinceB.
> >>
> >>
> >> On 1/19/2020 10:00 PM, Alan B wrote:
> >>> Hello Vince,
> >>>
> >>> I'm intrigued but not able to follow what you're trying to do. Columns
> D
> >>> through H appear to be the games bowled in any given week, rows (or
> >> should
> >>> I say weeks) 1 through 21 are the results of the games in those weeks.
> >> That
> >>> seems clear to me. But whose games, Russ, Vince, Dave, or Bob's?
> >>>
> >>> The disconnect comes with "using the results in [AL] and [AN]".
> >>>
> >>> How does a single row shown as 1 - 21 and columns [D] though [H] in
> your
> >>> example relate to multiple individuals [AL] through [AN]?
> >> They do not .
> >>> And more importantly, what are you trying to achieve? If I understood
> >> that
> >>> I might be able to offer some useful help.
> >> See my comments above. HTH.
> >>> On Sat, Jan 18, 2020 at 12:48 PM Vince@GMAIL <[hidden email]>
> wrote:
> >>>
> >>>> AOO 4.1.5 on Win10 Home desktop
> >>>>
> >>>> _________________________________
> >>>>
> >>>> Note: Cells with a yellow background identify required data must be
> >>>> entered.  Cells with a (darker) gray background identify cells which
> >>>> show calculated results.
> >>>>
> >>>> I have a Calc sheet that I enter data on a regular basis, as shown
> >> below:
> >>>> [D] [E] [F]    [G]            [H] <-------- Column headings
> >>>>
> >>>> */_Today is Week #_/*   */_Today's Date Is_/*   */_Game #1_/*
>  */_Game
> >> #2
> >>>> _/*     */_Game #3_/*   */_Match Series_/*      */_Match Average_/*
> >>>> *1*     6-Sep   152     146     187     485     161.7
> >>>> *2*     13-Sep  133     159     141     433     144.3
> >>>> *3*     20-Sep  147     155     148     450     150.0
> >>>> *4*     27-Sep  161     151     142     454     151.3
> >>>> *5*     4-Oct   130     139     116     385     128.3
> >>>> *6*     11-Oct  *Did Not Bowl. *
> >>>> *7*     18-Oct  148     187     119     454     151.3
> >>>> *8*     25-Oct  155     167     169     491     163.7
> >>>> *9*     1-Nov   140     141     157     438     146.0
> >>>> *10*    8-Nov   147     135     155     437     145.7
> >>>> *11*    15-Nov  139     133     155     427     142.3
> >>>> *12*    22-Nov  164     162     164     490     163.3
> >>>> *13*    29-Nov  120     110     155     385     128.3
> >>>> *14*    6-Dec   150     136     125     411     137.0
> >>>> *15*    13-Dec  170     187     132     489     163.0
> >>>> *16*    20-Dec  160     168     146     474     158.0
> >>>> *17*    27-Dec  160     144     150     454     151.3
> >>>> *18*    3-Jan   151     141     138     430     143.3
> >>>> */_---Half Season---_/*
> >>>> *19*    10-Jan  *Did Not Bowl. *
> >>>> *20*    17-Jan  161     134     147     442     147.3
> >>>> *21*    24-Jan
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>> I use the results generated in columns G and H in another section of
> the
> >>>> sheet, as shown:
> >>>>
> >>>>                        [AL] [AN] <-------- Column headings
> >>>>
> >>>> *Team #2- ACHES & PAINS v2.0
> >>>> Post Match Play*
> >>>> *Russ: *        458     *Player's Average: *    152.67
> >>>> *Vince: *       442     147.33
> >>>> *Dave: *        555     185.00
> >>>> *Bob: *         600     200.00
> >>>>
> >>>>
> >>>> Cells AL7 and AN7 are set to = the results calculated and provided
> >>>> within cells G26 and H26, i.e., they show: 442 and 147.3.
> >>>>
> >>>> My problem is that after calculating an update result within columns G
> >>>> and H, e.g., on 24 Jan, (week # 21's data), the results based on entry
> >>>> of data in D27:F27, there will be new results in the next row, at
> cells
> >>>> G27 and H27. Therefore, I am forced to manually change the formulae
> >>>> within AL7 and AN7 to correctly reflect the updated results provided
> in
> >>>> columns AL and AN, by changing =G26 to =G27 and by changing =H26 to
> >> =H27.
> >>>> Is there an easier method of handling this task, through use of a
> >>>> function or a formula?
> >>>>
> >>>> I hope this is described clearly enough.  If needed, I will attempt to
> >>>> store the complete calc sheet at a Dropbox link. Let me know.
> >>>>
> >>>> Regards,
> >>>>
> >>>> VinceB.
> >>>>
> >>>>
> >>>>
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

--
Alan Boba
CISSP, CCENT, ITIL v3 Foundations 2011
Reply | Threaded
Open this post in threaded view
|

Re: Calc Help

WA.TWORSX via AOL-2

And, here I was about to claim that I have a satisfactory *work-around
solution*, including cell AL7, and was about to incorporate the changes
into my actual .ods file that is being used for the present bowling
season. I'll come up with a DropBox link to what I think is a workable
solution, soon.

Will take a closer look at the G36 issue, perhaps, later today, because
I do hope to have a template of this calc sheet for use during future
bowling seasons, i.e., covering G6:G41.


On 1/24/2020 7:17 PM, Alan B wrote:

> DNB is meant to note someone did not bowl. It is just text entered in each
> cell rather than merging the cells and entering "Did not bowl".
>
> Now, some bad news. After reviewing what I suggested I realized the formula
> given for cell AL7 will always show the value in G36 if the column G
> formula is copied to every row at the start of each season. If it is added
> week by week to each row in column G, then no problem. That doesn't make
> for an easy to maintain sheet though.
>
> My idea was enter three scores each week and everything else updates.
>
> I did a little more thinking and came up with the following which meets the
> goal above (this all started so AL7 and AN7 will update without being
> modified by hand)...
>
> * cells in the range D6:F41 must always be a score, the text "DNB", or be
> empty
> * Rows 6 through 41 must always have the games entered week by week. IOW,
> no entering week 7 before week 6.
> * the row for half way in the season must be removed. Only rows for bowling
> weeks are allowed
>
> * The formula in cell G6 for "Match Series" column is...
> =IF(SUM(D6:F6)=0,IF(D6="DNB","DNB",""),SUM(D6:F6))
> The formula displays the total for the games, DNB, or "".
> SUM is 0 if columns D-F have DNB or "".
> This formula can be copied to all the rows in the column and it will update
> correctly for each row it is in.
>
> * The formula in cell H6 for "Match Average" column is...
> =IF(ISNUMBER(G6),G6/3,G6)
> This formula can be copied to all the rows in the column and it will update
> correctly for each row it is in.
>
> * The formula in cell J6 for "Total Pins" column calculates total pins for
> rows 6 through row the formula is in.
> =IF(ISNUMBER(I6),DSUM($G$5:INDIRECT(CONCATENATE("$G",ROW())),1,$BB$5:$BB$6),"")
> This formula should be copied to all week# rows in column J. It updates
> correctly for each row it is in.
> So for row 10 it calculates the total of rows 6 through 10, For row 15 the
> total of row 6 through 15 and so on. If there is no number in League
> Average it displays "".
> Buried in the formula is the DSUM function. It requires a "Criteria Range"
> to work. As written here the criteria range is cells BB5 and BB6. Cell BB5
> contains the formula =G5. Cell BB6 contains >=0. For a criteria range to
> work the column titles of the "database" and the "criteria range" must
> match exactly (not accounting for formatting). If column title G, "Match
> Series", is ever changed having cell BB5 be a formula that refers to it
> prevents needing to remember to update the criteria.
> The INDIRECT function enables the formula to identify the ROW() it is in so
> the DSUM database range doesn't have to be entered individually for each
> row, e.g. $G$5:$G$5, $G$5:$G$6, $G$5:$G$7 ... , $G$5:$G$10 and so on.
>
> * The formula in cell AL7 for Vince post match play is...
> =INDEX($Sheet1.G6:G41,36-COUNTBLANK($G$6:$G$41),1)
> The column G formula returns one of three values, the sum of the games,
> "DNB", or "". The season has 36 weeks. The formula presumes that as weeks
> go by the Match Series column will be either a number, "DNB", or "" and
> that "" are always the last rows in the column. Provided these conditions
> are true the formula always returns the most recently entered week's Match
> Series result.
>
> * The formula in cell AN7 for Vince post match average is...
> =INDEX($Sheet1.H6:H41,36-COUNTBLANK($H$6:$H$41),1)
> This works the same as the above formula except for Match Average.
>
>
>
>
> On Fri, Jan 24, 2020 at 10:16 AM WA.TWORSX via AOL
> <[hidden email]> wrote:
>
>> On 1/20/2020 5:58 PM, Alan B wrote:
>>> I've found a method that can work, providing the spreadsheet design is
>>> modified somewhat.
>>> It comes from a formula provided by RusselB in the OpenOffice forums.
>>>
>>> =index(range;count(range);1)
>>>
>>> For your sample spreadsheet this formula in cell AL7
>>> =INDEX($Sheet1.G6:G42,COUNTA($Sheet1.G6:G42),1)
>>> will return the Match Series value for the last value entered. When
>> week#20
>>> is completed the number in AL7 will update. When week#21 is completed it
>>> will update again, and so on.
>>>
>>> REQUIRED design changes
>>> 1. There must not be any merged cells that span column G. A merged cell
>> in
>>> the column breaks the formula. Update the =INDEX formula for column H and
>>> the same is true, no merged cells.
>>> 2. The formulas in the Match Average and Total Pins columns should
>> change.
>>> The IF test should be whether the tested cell contains a number, not
>>> whether it is blank. This will prevent #value! errors being displayed in
>>> those cells.
>>> 3. Last, any blank rows in a column must be BELOW the currently completed
>>> week number.
>>>
>>> To meet design change #1 the "Did not bowl" in week 6 cannot be merged
>>> cells. And to meet condition #3 they cannot be empty either. As an
>>> alternative you could enter DNB in each cell in the row.
>> AlanB:   What is meant by "DNB" ?
>>
>>> The row of merged cells that demark "half season" needs to change because
>>> of #1. If the row is deleted and the season half is indicated by a heavy
>>> border between week 18 and 19, that would suffice.
>>>
>>> This is the simplest solution I could find. More complex formulas might
>> be
>>> possible that would meet your goal but I'm not familiar enough with Calc
>> to
>>> readily conceive of them.
>>>
>>>
>>> On Mon, Jan 20, 2020 at 1:31 PM Vince@GMAIL <[hidden email]> wrote:
>>>
>>>> Hello Alan B:
>>>>
>>>> TNX for reading my query about Calc Help-updating Data.ods.
>>>>
>>>> For reasons, unknown to me, my pasted example seems to be
>>>> plain/unformatted text, although I had sent it out via Mozilla
>>>> Thunderbird email client using both HTML and plain text to the AOO
>>>> mailing list, and appeared properly displayed in my compose window.  I
>>>> realize that w/o the full formatting, it is difficult to read and
>>>> understand what is displayed at your end.
>>>>
>>>> This Dropbox link, I think, will allow you to download the full AOO-Calc
>>>> file:
>>>>
>>>>
>>>>
>> https://www.dropbox.com/scl/fi/2ibk2u5elew1y4mx9b583/Calc-Help-updating-Data.ods?dl=0&rlkey=zeujgqduhbuo82jmxryylinvd
>>>> .
>>>>
>>>> I am trying to "harvest", via a formula or function, the resulting
>>>> _Match Series_ (column G) data, which is data only relevant to Vince's
>>>> game scores. Sorry, I neglected to include the _Vince's Individual
>>>> Record_ heading over columns B:L in my original example.  The game
>>>> scores for Vince (only) are being tracked on a weekly basis to generate
>>>> the result that is given in column $G.
>>>>
>>>> At present, Cell AL7 contains the formula =G26. This value changes each
>>>> week, as Vince's new scores are poked into columns D, E, and F each week
>>>> (i.e., on the next week's row). So, at present, I am manually changing
>>>> the formula within AL7 to reflect the current week's row.
>>>>
>>>> (The _Match Series_ data for my teammates Russ, Dave, and Bob are being
>>>> poked manually into AL6, AL8, and AL9, weekly. )
>>>>
>>>> In another thread, dated 1/18/2020,  Brian Barker has offered
>>>> suggestions using =MATCH, =OFFSET, and =INDIRECT functions.  I am not
>>>> familiar with those functions, but will likely attempt their use when
>>>> time permits me to learn more about them. Brian also prefers the use of
>>>> minimal examples; therefore, I fear that I am verbose in my queries and
>>>> comments. However, I am hopeful that my comments above have been helpful
>>>> in clarifying my OP.
>>>>
>>>> Again, thanks for your interest in solving my problem.
>>>>
>>>> Regards,
>>>>
>>>> VinceB.
>>>>
>>>>
>>>> On 1/19/2020 10:00 PM, Alan B wrote:
>>>>> Hello Vince,
>>>>>
>>>>> I'm intrigued but not able to follow what you're trying to do. Columns
>> D
>>>>> through H appear to be the games bowled in any given week, rows (or
>>>> should
>>>>> I say weeks) 1 through 21 are the results of the games in those weeks.
>>>> That
>>>>> seems clear to me. But whose games, Russ, Vince, Dave, or Bob's?
>>>>>
>>>>> The disconnect comes with "using the results in [AL] and [AN]".
>>>>>
>>>>> How does a single row shown as 1 - 21 and columns [D] though [H] in
>> your
>>>>> example relate to multiple individuals [AL] through [AN]?
>>>> They do not .
>>>>> And more importantly, what are you trying to achieve? If I understood
>>>> that
>>>>> I might be able to offer some useful help.
>>>> See my comments above. HTH.
>>>>> On Sat, Jan 18, 2020 at 12:48 PM Vince@GMAIL <[hidden email]>
>> wrote:
>>>>>> AOO 4.1.5 on Win10 Home desktop
>>>>>>
>>>>>> _________________________________
>>>>>>
>>>>>> Note: Cells with a yellow background identify required data must be
>>>>>> entered.  Cells with a (darker) gray background identify cells which
>>>>>> show calculated results.
>>>>>>
>>>>>> I have a Calc sheet that I enter data on a regular basis, as shown
>>>> below:
>>>>>> [D] [E] [F]    [G]            [H] <-------- Column headings
>>>>>>
>>>>>> */_Today is Week #_/*   */_Today's Date Is_/*   */_Game #1_/*
>>   */_Game
>>>> #2
>>>>>> _/*     */_Game #3_/*   */_Match Series_/*      */_Match Average_/*
>>>>>> *1*     6-Sep   152     146     187     485     161.7
>>>>>> *2*     13-Sep  133     159     141     433     144.3
>>>>>> *3*     20-Sep  147     155     148     450     150.0
>>>>>> *4*     27-Sep  161     151     142     454     151.3
>>>>>> *5*     4-Oct   130     139     116     385     128.3
>>>>>> *6*     11-Oct  *Did Not Bowl. *
>>>>>> *7*     18-Oct  148     187     119     454     151.3
>>>>>> *8*     25-Oct  155     167     169     491     163.7
>>>>>> *9*     1-Nov   140     141     157     438     146.0
>>>>>> *10*    8-Nov   147     135     155     437     145.7
>>>>>> *11*    15-Nov  139     133     155     427     142.3
>>>>>> *12*    22-Nov  164     162     164     490     163.3
>>>>>> *13*    29-Nov  120     110     155     385     128.3
>>>>>> *14*    6-Dec   150     136     125     411     137.0
>>>>>> *15*    13-Dec  170     187     132     489     163.0
>>>>>> *16*    20-Dec  160     168     146     474     158.0
>>>>>> *17*    27-Dec  160     144     150     454     151.3
>>>>>> *18*    3-Jan   151     141     138     430     143.3
>>>>>> */_---Half Season---_/*
>>>>>> *19*    10-Jan  *Did Not Bowl. *
>>>>>> *20*    17-Jan  161     134     147     442     147.3
>>>>>> *21*    24-Jan
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> I use the results generated in columns G and H in another section of
>> the
>>>>>> sheet, as shown:
>>>>>>
>>>>>>                         [AL] [AN] <-------- Column headings
>>>>>>
>>>>>> *Team #2- ACHES & PAINS v2.0
>>>>>> Post Match Play*
>>>>>> *Russ: *        458     *Player's Average: *    152.67
>>>>>> *Vince: *       442     147.33
>>>>>> *Dave: *        555     185.00
>>>>>> *Bob: *         600     200.00
>>>>>>
>>>>>>
>>>>>> Cells AL7 and AN7 are set to = the results calculated and provided
>>>>>> within cells G26 and H26, i.e., they show: 442 and 147.3.
>>>>>>
>>>>>> My problem is that after calculating an update result within columns G
>>>>>> and H, e.g., on 24 Jan, (week # 21's data), the results based on entry
>>>>>> of data in D27:F27, there will be new results in the next row, at
>> cells
>>>>>> G27 and H27. Therefore, I am forced to manually change the formulae
>>>>>> within AL7 and AN7 to correctly reflect the updated results provided
>> in
>>>>>> columns AL and AN, by changing =G26 to =G27 and by changing =H26 to
>>>> =H27.
>>>>>> Is there an easier method of handling this task, through use of a
>>>>>> function or a formula?
>>>>>>
>>>>>> I hope this is described clearly enough.  If needed, I will attempt to
>>>>>> store the complete calc sheet at a Dropbox link. Let me know.
>>>>>>
>>>>>> Regards,
>>>>>>
>>>>>> VinceB.
>>>>>>
>>>>>>
>>>>>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>>
Reply | Threaded
Open this post in threaded view
|

Re: Calc Help-INTERIM SOLUTION

WA.TWORSX via AOL-2
In reply to this post by Vince@Gmail

I believe that I am successful with an interim solution to my original
problem.

This DropBox link, I think, will allow you to download my interim
solution, which comes with some caveats and compromises:

https://www.dropbox.com/s/05gk2h8zt7lbua2/Calc%20Help-updating%20Data_INTERIM%20SOLUTION.ods?dl=0 


The caveats and compromises are presented in the referenced .ods file by
way of annotated comments on the content of cells as used in my solution.

I am very appreciative to AlanB for the time he has spent in an effort
to lead me toward the correct Calc sheet.  Believe me, I have learned
from his thoughtfulness and patience to explain why he made specific
suggestions for changing my approach.  This is how I am able to learn.
Thank you Alan!  If it were not for your assistance, I would not have
gotten to this point.

In his 1/24/2020 posted reply, Alan mentioned a glitch involving cell
G36.  I have not been able to duplicate the problem.

Alan's logic for the formula in cell J6:

> * The formula in cell J6 for "Total Pins" column calculates total pins for
> rows 6 through row the formula is in.
> =IF(ISNUMBER(I6),DSUM($G$5:INDIRECT(CONCATENATE("$G",ROW())),1,$BB$5:$BB$6),"")
> This formula should be copied to all week# rows in column J. It updates
> correctly for each row it is in.
is far above my present skill level. Thus, I may return to it in the
future when I attempt to set up a calc sheet for a new WINTER SR LEAGUE
bowling season.

Yes, this all started with my goal of AL7 and AN7 being updated
automatically, without first changing the enclosed formula or being
modified by hand.  My interim solution allows for auto-updating of AL7.
Cell AN7 is simply: AN7=(AL7/3).

Regards,

VinceB.


P.S. The PBA Oklahoma Open Bowing Tournament stepladder finals are
presently being televised on FS1 channel. I'm gone!




Reply | Threaded
Open this post in threaded view
|

Re: Calc Help-INTERIM SOLUTION

Alan Boba-2
Hello Vince,

Thank you for your kind words.

In your sheet notes you describe needing to "segment a formula" into
several ranges. Unnecessary work, I think.

Try the following formulas instead...
In H43, =MAXIFS(G6:G41,G6:G41,">0")
In H44, =MINIFS(G6:G41,G6:G41,">0")

They appear to me to work as desired in your example sheet.

On Sun, Jan 26, 2020 at 2:40 PM WA.TWORSX via AOL
<[hidden email]> wrote:

>
> I believe that I am successful with an interim solution to my original
> problem.
>
> This DropBox link, I think, will allow you to download my interim
> solution, which comes with some caveats and compromises:
>
>
> https://www.dropbox.com/s/05gk2h8zt7lbua2/Calc%20Help-updating%20Data_INTERIM%20SOLUTION.ods?dl=0
>
>
> The caveats and compromises are presented in the referenced .ods file by
> way of annotated comments on the content of cells as used in my solution.
>
> I am very appreciative to AlanB for the time he has spent in an effort
> to lead me toward the correct Calc sheet.  Believe me, I have learned
> from his thoughtfulness and patience to explain why he made specific
> suggestions for changing my approach.  This is how I am able to learn.
> Thank you Alan!  If it were not for your assistance, I would not have
> gotten to this point.
>
> In his 1/24/2020 posted reply, Alan mentioned a glitch involving cell
> G36.  I have not been able to duplicate the problem.
>
> Alan's logic for the formula in cell J6:
>
> > * The formula in cell J6 for "Total Pins" column calculates total pins
> for
> > rows 6 through row the formula is in.
> >
> =IF(ISNUMBER(I6),DSUM($G$5:INDIRECT(CONCATENATE("$G",ROW())),1,$BB$5:$BB$6),"")
> > This formula should be copied to all week# rows in column J. It updates
> > correctly for each row it is in.
> is far above my present skill level. Thus, I may return to it in the
> future when I attempt to set up a calc sheet for a new WINTER SR LEAGUE
> bowling season.
>
> Yes, this all started with my goal of AL7 and AN7 being updated
> automatically, without first changing the enclosed formula or being
> modified by hand.  My interim solution allows for auto-updating of AL7.
> Cell AN7 is simply: AN7=(AL7/3).
>
> Regards,
>
> VinceB.
>
>
> P.S. The PBA Oklahoma Open Bowing Tournament stepladder finals are
> presently being televised on FS1 channel. I'm gone!
>
>
>
>
>

--
Alan Boba
CISSP, CCENT, ITIL v3 Foundations 2011
Reply | Threaded
Open this post in threaded view
|

Re: Calc Help-INTERIM SOLUTION

WA.TWORSX via AOL-2
Alan:

I tried in cell H44: =MINIFS(G6:G41,G6:G41,">0"), but it is not working
for me. Error508: Error in bracketing is shown.

I also tried with use of semicolons =MINIFS(G6:G41;G6:G41;">0") rather
than commas; #NAME? error is returned.

No joy here.

I could not find anything  as =MINFS(  ) funtion within the built-in
(F1) Help file. Can you point me to a discussion elsewhere?


On 1/27/2020 3:01 PM, Alan B wrote:

> Hello Vince,
>
> Thank you for your kind words.
>
> In your sheet notes you describe needing to "segment a formula" into
> several ranges. Unnecessary work, I think.
>
> Try the following formulas instead...
> In H43, =MAXIFS(G6:G41,G6:G41,">0")
> In H44, =MINIFS(G6:G41,G6:G41,">0")
>
> They appear to me to work as desired in your example sheet.
>
> On Sun, Jan 26, 2020 at 2:40 PM WA.TWORSX via AOL
> <[hidden email]> wrote:
>
>> I believe that I am successful with an interim solution to my original
>> problem.
>>
>> This DropBox link, I think, will allow you to download my interim
>> solution, which comes with some caveats and compromises:
>>
>>
>> https://www.dropbox.com/s/05gk2h8zt7lbua2/Calc%20Help-updating%20Data_INTERIM%20SOLUTION.ods?dl=0
>>
>>
>> The caveats and compromises are presented in the referenced .ods file by
>> way of annotated comments on the content of cells as used in my solution.
>>
>> I am very appreciative to AlanB for the time he has spent in an effort
>> to lead me toward the correct Calc sheet.  Believe me, I have learned
>> from his thoughtfulness and patience to explain why he made specific
>> suggestions for changing my approach.  This is how I am able to learn.
>> Thank you Alan!  If it were not for your assistance, I would not have
>> gotten to this point.
>>
>> In his 1/24/2020 posted reply, Alan mentioned a glitch involving cell
>> G36.  I have not been able to duplicate the problem.
>>
>> Alan's logic for the formula in cell J6:
>>
>>> * The formula in cell J6 for "Total Pins" column calculates total pins
>> for
>>> rows 6 through row the formula is in.
>>>
>> =IF(ISNUMBER(I6),DSUM($G$5:INDIRECT(CONCATENATE("$G",ROW())),1,$BB$5:$BB$6),"")
>>> This formula should be copied to all week# rows in column J. It updates
>>> correctly for each row it is in.
>> is far above my present skill level. Thus, I may return to it in the
>> future when I attempt to set up a calc sheet for a new WINTER SR LEAGUE
>> bowling season.
>>
>> Yes, this all started with my goal of AL7 and AN7 being updated
>> automatically, without first changing the enclosed formula or being
>> modified by hand.  My interim solution allows for auto-updating of AL7.
>> Cell AN7 is simply: AN7=(AL7/3).
>>
>> Regards,
>>
>> VinceB.
>>
>>
>> P.S. The PBA Oklahoma Open Bowing Tournament stepladder finals are
>> presently being televised on FS1 channel. I'm gone!
>>
>>
>>
>>
>>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Calc Help-INTERIM SOLUTION

David Robley
Those functions are not available in AOO - but exist in LibreOffice
since around version 5.2

On 28/1/20 7:50 am, WA.TWORSX via AOL wrote:

> Alan:
>
> I tried in cell H44: =MINIFS(G6:G41,G6:G41,">0"), but it is not
> working for me. Error508: Error in bracketing is shown.
>
> I also tried with use of semicolons =MINIFS(G6:G41;G6:G41;">0") rather
> than commas; #NAME? error is returned.
>
> No joy here.
>
> I could not find anything  as =MINFS(  ) funtion within the built-in
> (F1) Help file. Can you point me to a discussion elsewhere?
>
>
Cheers
--
David Robley

The cat that ate the ball of yarn....had mittens!
 


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Calc Help-INTERIM SOLUTION

WA.TWORSX via AOL-2

TNX David; I have just been visiting the LibreOffice help
https://help.libreoffice.org/6.2/he/text/scalc/01/func_minifs.html?DbPAR=CALC 


and came to that same conclusion.


On 1/27/2020 4:46 PM, David Robley wrote:

> Those functions are not available in AOO - but exist in LibreOffice
> since around version 5.2
>
> On 28/1/20 7:50 am, WA.TWORSX via AOL wrote:
>> Alan:
>>
>> I tried in cell H44: =MINIFS(G6:G41,G6:G41,">0"), but it is not
>> working for me. Error508: Error in bracketing is shown.
>>
>> I also tried with use of semicolons =MINIFS(G6:G41;G6:G41;">0")
>> rather than commas; #NAME? error is returned.
>>
>> No joy here.
>>
>> I could not find anything  as =MINFS(  ) funtion within the built-in
>> (F1) Help file. Can you point me to a discussion elsewhere?
>>
>>
> Cheers

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Calc Help-INTERIM SOLUTION

Alan Boba-2
In reply to this post by WA.TWORSX via AOL-2
See David Robeley's response.

I was jumping back and forth between virtual machines and inadvertently
used the one with LibreOffice without remembering you're using OpenOffice.
My bad.

Sorry about that.

On Mon, Jan 27, 2020 at 4:20 PM WA.TWORSX via AOL
<[hidden email]> wrote:

> Alan:
>
> I tried in cell H44: =MINIFS(G6:G41,G6:G41,">0"), but it is not working
> for me. Error508: Error in bracketing is shown.
>
> I also tried with use of semicolons =MINIFS(G6:G41;G6:G41;">0") rather
> than commas; #NAME? error is returned.
>
> No joy here.
>
> I could not find anything  as =MINFS(  ) funtion within the built-in
> (F1) Help file. Can you point me to a discussion elsewhere?
>
>
> On 1/27/2020 3:01 PM, Alan B wrote:
> > Hello Vince,
> >
> > Thank you for your kind words.
> >
> > In your sheet notes you describe needing to "segment a formula" into
> > several ranges. Unnecessary work, I think.
> >
> > Try the following formulas instead...
> > In H43, =MAXIFS(G6:G41,G6:G41,">0")
> > In H44, =MINIFS(G6:G41,G6:G41,">0")
> >
> > They appear to me to work as desired in your example sheet.
> >
> > On Sun, Jan 26, 2020 at 2:40 PM WA.TWORSX via AOL
> > <[hidden email]> wrote:
> >
> >> I believe that I am successful with an interim solution to my original
> >> problem.
> >>
> >> This DropBox link, I think, will allow you to download my interim
> >> solution, which comes with some caveats and compromises:
> >>
> >>
> >>
> https://www.dropbox.com/s/05gk2h8zt7lbua2/Calc%20Help-updating%20Data_INTERIM%20SOLUTION.ods?dl=0
> >>
> >>
> >> The caveats and compromises are presented in the referenced .ods file by
> >> way of annotated comments on the content of cells as used in my
> solution.
> >>
> >> I am very appreciative to AlanB for the time he has spent in an effort
> >> to lead me toward the correct Calc sheet.  Believe me, I have learned
> >> from his thoughtfulness and patience to explain why he made specific
> >> suggestions for changing my approach.  This is how I am able to learn.
> >> Thank you Alan!  If it were not for your assistance, I would not have
> >> gotten to this point.
> >>
> >> In his 1/24/2020 posted reply, Alan mentioned a glitch involving cell
> >> G36.  I have not been able to duplicate the problem.
> >>
> >> Alan's logic for the formula in cell J6:
> >>
> >>> * The formula in cell J6 for "Total Pins" column calculates total pins
> >> for
> >>> rows 6 through row the formula is in.
> >>>
> >>
> =IF(ISNUMBER(I6),DSUM($G$5:INDIRECT(CONCATENATE("$G",ROW())),1,$BB$5:$BB$6),"")
> >>> This formula should be copied to all week# rows in column J. It updates
> >>> correctly for each row it is in.
> >> is far above my present skill level. Thus, I may return to it in the
> >> future when I attempt to set up a calc sheet for a new WINTER SR LEAGUE
> >> bowling season.
> >>
> >> Yes, this all started with my goal of AL7 and AN7 being updated
> >> automatically, without first changing the enclosed formula or being
> >> modified by hand.  My interim solution allows for auto-updating of AL7.
> >> Cell AN7 is simply: AN7=(AL7/3).
> >>
> >> Regards,
> >>
> >> VinceB.
> >>
> >>
> >> P.S. The PBA Oklahoma Open Bowing Tournament stepladder finals are
> >> presently being televised on FS1 channel. I'm gone!
> >>
> >>
> >>
> >>
> >>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

--
Alan Boba
CISSP, CCENT, ITIL v3 Foundations 2011
Reply | Threaded
Open this post in threaded view
|

Re: Calc Help-INTERIM SOLUTION

WA.TWORSX via AOL-2

Not a problem, Alan.


On 1/27/2020 5:43 PM, Alan B wrote:

> See David Robeley's response.
>
> I was jumping back and forth between virtual machines and inadvertently
> used the one with LibreOffice without remembering you're using OpenOffice.
> My bad.
>
> Sorry about that.
>
> On Mon, Jan 27, 2020 at 4:20 PM WA.TWORSX via AOL
> <[hidden email]> wrote:
>
>> Alan:
>>
>> I tried in cell H44: =MINIFS(G6:G41,G6:G41,">0"), but it is not working
>> for me. Error508: Error in bracketing is shown.
>>
>> I also tried with use of semicolons =MINIFS(G6:G41;G6:G41;">0") rather
>> than commas; #NAME? error is returned.
>>
>> No joy here.
>>
>> I could not find anything  as =MINFS(  ) funtion within the built-in
>> (F1) Help file. Can you point me to a discussion elsewhere?
>>
>>
>>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]