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.

Item ID	Price	Higher or Lower
1	10.00	Lower
4	54.00
3	23.00
2	14.00
4	78.00
1	41.00	Higher
2	74.00
3	33.00

<tbody>
</tbody>

 

[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
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

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:106px;"></colgroup><tbody>
</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.