Alternate row comparison
in [Functions]
|
From: GB3 on 26 Dec 2009 11:44 Thanks, Rick. That would work. But the issue there (a small one, granted) is that that would require my copying and pasting that formula multiple times, rather than simply extending the formula down the length of the spreadsheet. I was hoping to be able to write a single formula (with nested IFs as would seem to be necessary) that would allow me to write that same formula in every row of the spreadsheet. Rick Rothstein wrote: >Give the following a try. Put these formulas in the indicated cells... > >G1: =IF(G1>G2,"Greater","") >G2: =IF(G2>G1,"Greater","") > >Now select both G1 and G2 and copy that selection down as far as needed. > >> Thanks Don. Looks like I should start to learn to write macros. >> >[quoted text clipped - 34 lines] >>>> >>>> Thanks very much.
From: Rick Rothstein on 26 Dec 2009 11:47 He's comparing pairs of rows, not individual rows. Rows 1 and 2 are being compared, as a pair, separately from Rows 3 and 4, which are compared as their own pair... Rows 2 and 3 are not linked in any way as they belong to separate pairings. -- Rick (MVP - Excel) "T. Valko" <biffinpitt(a)comcast.net> wrote in message news:%23SaanokhKHA.1460(a)TK2MSFTNGP06.phx.gbl... > >Score 1 2 1 3 0 6 greater >>Score 2 0 0 0 0 0 >>Score 1 1 0 0 1 2 >>Score 2 0 1 2 0 3 greater > > 2 is greater than 0. Shouldn't that row say greater? > > -- > Biff > Microsoft Excel MVP > > > "GB3" <u57077(a)uwe> wrote in message news:a12bd3252ed3e(a)uwe... >> I've seen a few similar problems, but not quite what I'm seeking help for >> here. >> My goal is to write a formula to compare 2 rows of summed values for a >> lengthy spreadsheet - comparing the values of even-row scores and odd-row >> scores -- (Col G) here. >> So 5 is > 3 for rows 1&2; 4 is > 3 for rows 3&4, etc. >> >> Here's an example: >> >> Score 1 3 0 0 0 3 >> Score 2 0 3 1 1 5 greater >> Score 1 1 2 0 0 3 >> Score 2 0 0 2 2 4 greater >> Score 1 0 2 3 1 6 greater >> Score 2 1 0 0 4 5 >> Score 1 2 1 3 0 6 greater >> Score 2 0 0 0 0 0 >> Score 1 1 0 0 1 2 >> Score 2 0 1 2 0 3 greater >> >> I have used the MOD function to identify whether the row is even or odd, >> but >> I can't determine how to write the expression that in essence would say, >> that >> if the value of the Col G entry in row 3 is > than the value of the entry >> in >> Col G in row 4, then write "greater" in row 3, Col H. >> >> In a related vein, is it possible to subscript in Excel formulas? -- >> e.g., G >> [Row()] so as to refer to G3 when the formula is in Row 3? >> >> Thanks very much. >> > >
From: Don Guillett on 26 Dec 2009 11:50 Simply follow Ricks suggestion as presented in his post to you Now select both G1 and G2 and copy that selection down as far as needed. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1(a)austin.rr.com "GB3" <u57077(a)uwe> wrote in message news:a12c4d67ee641(a)uwe... > Thanks, Rick. > That would work. But the issue there (a small one, granted) is that that > would require my copying and pasting that formula multiple times, rather > than > simply extending the formula down the length of the spreadsheet. I was > hoping to be able to write a single formula (with nested IFs as would seem > to > be necessary) that would allow me to write that same formula in every row > of > the spreadsheet. > > Rick Rothstein wrote: >>Give the following a try. Put these formulas in the indicated cells... >> >>G1: =IF(G1>G2,"Greater","") >>G2: =IF(G2>G1,"Greater","") >> >>Now select both G1 and G2 and copy that selection down as far as needed. >> >>> Thanks Don. Looks like I should start to learn to write macros. >>> >>[quoted text clipped - 34 lines] >>>>> >>>>> Thanks very much. >
From: Rick Rothstein on 26 Dec 2009 12:07 No, you do not have to do them individually, over and over again... after putting the formulas in the cells I indicated, select **both** cells (G1:G2).... see the small, black square at the bottom right corner of the selection? Click drag it down as far as you need to... the formulas will adjust automatically. Alternately, you can select the two cells and press Ctrl+C (or click Edit/Copy in the menu), then select **all** the blank cells you want your formulas in and press Ctrl+V (or click Edit/Paste in the menu) and get the same result. -- Rick (MVP - Excel) "GB3" <u57077(a)uwe> wrote in message news:a12c4d67ee641(a)uwe... > Thanks, Rick. > That would work. But the issue there (a small one, granted) is that that > would require my copying and pasting that formula multiple times, rather > than > simply extending the formula down the length of the spreadsheet. I was > hoping to be able to write a single formula (with nested IFs as would seem > to > be necessary) that would allow me to write that same formula in every row > of > the spreadsheet. > > Rick Rothstein wrote: >>Give the following a try. Put these formulas in the indicated cells... >> >>G1: =IF(G1>G2,"Greater","") >>G2: =IF(G2>G1,"Greater","") >> >>Now select both G1 and G2 and copy that selection down as far as needed. >> >>> Thanks Don. Looks like I should start to learn to write macros. >>> >>[quoted text clipped - 34 lines] >>>>> >>>>> Thanks very much. >
From: T. Valko on 26 Dec 2009 12:11 Ok, got it. -- Biff Microsoft Excel MVP "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message news:OjNccskhKHA.4200(a)TK2MSFTNGP04.phx.gbl... > He's comparing pairs of rows, not individual rows. Rows 1 and 2 are being > compared, as a pair, separately from Rows 3 and 4, which are compared as > their own pair... Rows 2 and 3 are not linked in any way as they belong to > separate pairings. > > -- > Rick (MVP - Excel) > > > "T. Valko" <biffinpitt(a)comcast.net> wrote in message > news:%23SaanokhKHA.1460(a)TK2MSFTNGP06.phx.gbl... >> >Score 1 2 1 3 0 6 greater >>>Score 2 0 0 0 0 0 >>>Score 1 1 0 0 1 2 >>>Score 2 0 1 2 0 3 greater >> >> 2 is greater than 0. Shouldn't that row say greater? >> >> -- >> Biff >> Microsoft Excel MVP >> >> >> "GB3" <u57077(a)uwe> wrote in message news:a12bd3252ed3e(a)uwe... >>> I've seen a few similar problems, but not quite what I'm seeking help >>> for >>> here. >>> My goal is to write a formula to compare 2 rows of summed values for a >>> lengthy spreadsheet - comparing the values of even-row scores and >>> odd-row >>> scores -- (Col G) here. >>> So 5 is > 3 for rows 1&2; 4 is > 3 for rows 3&4, etc. >>> >>> Here's an example: >>> >>> Score 1 3 0 0 0 3 >>> Score 2 0 3 1 1 5 greater >>> Score 1 1 2 0 0 3 >>> Score 2 0 0 2 2 4 greater >>> Score 1 0 2 3 1 6 greater >>> Score 2 1 0 0 4 5 >>> Score 1 2 1 3 0 6 greater >>> Score 2 0 0 0 0 0 >>> Score 1 1 0 0 1 2 >>> Score 2 0 1 2 0 3 greater >>> >>> I have used the MOD function to identify whether the row is even or odd, >>> but >>> I can't determine how to write the expression that in essence would say, >>> that >>> if the value of the Col G entry in row 3 is > than the value of the >>> entry in >>> Col G in row 4, then write "greater" in row 3, Col H. >>> >>> In a related vein, is it possible to subscript in Excel formulas? -- >>> e.g., G >>> [Row()] so as to refer to G3 when the formula is in Row 3? >>> >>> Thanks very much. >>> >> >> >
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: Literal based on Week end Next: Calculating Elapsed Time Over a Set Period |