Excel Help

[jocko]

Hi there,

I have a column of 2500 numbers in Excel 2000. Starting from the
bottom of the list I have to check each number and see if it is higher
or lower than the number immediately below it in the column. I then
have to place a 'H' or a 'L' (standing for Higher or Lower) immediately
next to the checked number in the adjacent column as is shown in the
example below.


3906.3 H
3871.4 L
3967.7 L
3975.5 H
3964 L
3978.4 H
3945.4 H
3926.3 H
3908 H
3895.3

At the moment I am doing this manually which is quite laborious so any
help on how I can automate this would be would be greatly appreciated.
Unfortunately I know very little about Excel so i have no idea if this
is a difficult task or not.

Thanks again,

Jocko

 

[Gord Dibben]

Jocko

Assuming numbers are in Column A.

In B1 enter =IF(A1>A2,"H","L")

Drag/copy down to B2500 or double-click on the fill handle of B1 to fill down
to end of data in Column A.

Gord Dibben Excel MVP

 

[Don Guillett]

try this. Assumes col A from row 7 through row 12
Sub higerlower()
For i = 12 To 7 Step -1
If Cells(i - 1, "a") < Cells(i, "a") Then Cells(i, "a").Offset(, 1) = "L"
If Cells(i - 1, "a") > Cells(i, "a") Then Cells(i, "a").Offset(, 1) = "H"
Next i
End Sub

 

[jocko]

Gord & Don,

Thanks for your quick responce they were exactky what i was looking
for. You've saved me many hours of tedious data entry!

Thanks again,

Jocko

 

[Don Guillett]

If you use Gord's you might want to change the formulas to values
afterwards.

 

[RWN]

Especially if you sort the list(!).
(My "favorite error :-(O)

 

[David McRitchie]

Why ? (okay I see for sorting, method below also covers that)

If you intend to insert/delete numbers at a future time
coding as follows, so that each row identifies cells on the same row
or relative to the same row.

In B1 enter =IF(A1>OFFSET(A1,1,0),"H","L")

instead of
In B1 enter =IF(A1>A2,"H","L")

More information on use of OFFSET in
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

If you use Gord's you might want to change the formulas to values
afterwards.

 

[Guest]

I'm looking for the driving game easter egg,but can not seem to locate how to get it.