Compare values between rows

[Rosadocc]

Hi,

Im looking to compare the price of items in a spreadsheet.

In the example below, Im looking for a function for the Higher or Lower column that compares Item ID "1" with itself and identifies which is the higher and lower price of the two.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Item ID
[/TD]
[TD]Price[/TD]
[TD]Higher or Lower[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10.00
[/TD]
[TD]Lower[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]54.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]23.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]14.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]78.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]41.00[/TD]
[TD]Higher[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]74.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]33.00[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

Copy C2 down. Note this is an array formula: confirm with ctrl+shift+enter.

Excel Workbook
	A	B	C
1	Item ID	Price	Higher or Lower
2	1	10	Lower
3	4	54	Lower
4	3	23	Lower
5	2	14	Lower
6	4	78	Higher
7	1	41	Higher
8	2	74	Higher
9	3	33	Higher
A

 

[Rosadocc]

Copy C2 down. Note this is an array formula: confirm with ctrl+shift+enter.
A

	A	B	C
Item ID	Price	Higher or Lower
Lower
Lower
Lower
Lower
Higher
Higher
Higher
Higher

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:106px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]54[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]23[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]78[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]41[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]74[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]33[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C2 {=IF(COUNTIF($A$2:$A$9,A2)<2,"",IF(MAX(IF($A$2:$A$9=A2,$B$2:$B$9))=B2,"Higher","Lower"))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Worked Great!

 

[JoeMo]

Worked Great!

Thanks for the reply.