Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Alternate row comparison
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Alternate row comparison
Thanks Don. Looks like I should start to learn to write macros.
Anyway, my second question was related to an aspect writing formulas. So if the formula, =Row(), returns the value "3" when used in row 3, and "4" when used in Row 4, etc., I was wondering if there were a way to specify G3 when in Row 3 by using a formula like ... G[=Row()] -- in essence, someway to get the equivalent of a pointer to cell G3. Thanks again for your help. Don Guillett wrote: A macro solution. If you have a header row change to 2 to cells Don't understand the second question. Sub largeroftworows() Dim mc As String mc = "g" Dim i As Long For i = 1 To Cells(Rows.Count, mc) _ .End(xlUp).Row Step 2 If Cells(i, "g") Cells(i + 1, "g") Then Cells(i, "h") = "greater" Else Cells(i + 1, "h") = "greater" End If Next i End Sub I've seen a few similar problems, but not quite what I'm seeking help for here. [quoted text clipped - 29 lines] Thanks very much. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Alternate row comparison
Give the following a try. Put these formulas in the indicated cells...
G1: =IF(G1G2,"Greater","") G2: =IF(G2G1,"Greater","") Now select both G1 and G2 and copy that selection down as far as needed. -- Rick (MVP - Excel) "GB3" <u57077@uwe wrote in message news:a12c0b39f293c@uwe... Thanks Don. Looks like I should start to learn to write macros. Anyway, my second question was related to an aspect writing formulas. So if the formula, =Row(), returns the value "3" when used in row 3, and "4" when used in Row 4, etc., I was wondering if there were a way to specify G3 when in Row 3 by using a formula like ... G[=Row()] -- in essence, someway to get the equivalent of a pointer to cell G3. Thanks again for your help. Don Guillett wrote: A macro solution. If you have a header row change to 2 to cells Don't understand the second question. Sub largeroftworows() Dim mc As String mc = "g" Dim i As Long For i = 1 To Cells(Rows.Count, mc) _ .End(xlUp).Row Step 2 If Cells(i, "g") Cells(i + 1, "g") Then Cells(i, "h") = "greater" Else Cells(i + 1, "h") = "greater" End If Next i End Sub I've seen a few similar problems, but not quite what I'm seeking help for here. [quoted text clipped - 29 lines] Thanks very much. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Alternate row comparison
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@uwe wrote in message news:a12bd3252ed3e@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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Alternate row comparison
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(G1G2,"Greater","") G2: =IF(G2G1,"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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Alternate row comparison
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" wrote in message ... 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@uwe wrote in message news:a12bd3252ed3e@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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Alternate row comparison
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 "GB3" <u57077@uwe wrote in message news:a12c4d67ee641@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(G1G2,"Greater","") G2: =IF(G2G1,"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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Alternate row comparison
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@uwe wrote in message news:a12c4d67ee641@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(G1G2,"Greater","") G2: =IF(G2G1,"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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Alternate row comparison
Ok, got it.
-- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... 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" wrote in message ... 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@uwe wrote in message news:a12bd3252ed3e@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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Alternate row comparison
Thanks to all. Now my problem is overcoming rigidity of thinking. I had
some monstrosity of nested IFs and Row() functions -- none of which worked -- and resulted in my growing that creature with layers of twisted logic. Don, you've got me thinking about macros now anyway. Thanks again. Rick, and thank you too -- your solution is simple. I just didn't believe Excel would be able to reapply the 2-row logic with only 2 rows as an example. But I didn't check either. Rick Rothstein wrote: 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. Score 1 2 1 3 0 6 greater Score 2 0 0 0 0 0 [quoted text clipped - 36 lines] Thanks very much. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Alternate row comparison
Hi
Start your data from row 2 (you can have column headers in 1st row). Then the formula will be: =IF(IF(ISODD(ROW()),F2F1,F2F3),"greater","") Arvi Laanemets "GB3" <u57077@uwe wrote in message news:a12bd3252ed3e@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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum of Alternate Cells | Excel Discussion (Misc queries) | |||
Help w/ alternate to Countif | Excel Worksheet Functions | |||
Alternate to Lookup? | Excel Worksheet Functions | |||
alternate blank row | Excel Worksheet Functions | |||
Add alternate rows | Excel Worksheet Functions |