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 <wa.two...@verizon.net.invalid> 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 <wa2rs...@gmail.com> 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 <wa2rs...@gmail.com> > 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
