Alternate row comparison

[GB3]

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.

 

[Don Guillett]

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

 

[GB3]

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.

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.

 

[Rick Rothstein]

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.

--
Rick (MVP - Excel)

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.

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.

 

[T. Valko]

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

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.

 

[GB3]

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.

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]

 

[Rick Rothstein]

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)

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

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.

 

[Don Guillett]

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
(e-mail address removed)

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.

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.

 

[Rick Rothstein]

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)

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.

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.

 

[T. Valko]

Ok, got it.

--
Biff
Microsoft Excel MVP

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)

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

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.

 

[GB3]

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.

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.

 

[Arvi Laanemets]

Hi

Start your data from row 2 (you can have column headers in 1st row). Then
the formula will be:
=IF(IF(ISODD(ROW()),F2>F1,F2>F3),"greater","")


Arvi Laanemets