Prev: Literal based on Week end
Next: Calculating Elapsed Time Over a Set Period
From: GB3 on 26 Dec 2009 10:49
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:01
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1(a)austin.rr.com
"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: GB3 on 26 Dec 2009 11:14
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.

From: Rick Rothstein on 26 Dec 2009 11:25
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)


"GB3" <u57077(a)uwe> wrote in message news:a12c0b39f293c(a)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.
>

From: T. Valko on 26 Dec 2009 11:40
>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.
>


 |  Next  |  Last
Pages: 1 2 3
Prev: Literal based on Week end
Next: Calculating Elapsed Time Over a Set Period