any help would be appreciated.....

  • Thread starter Thread starter cram81
  • Start date Start date
C

cram81

hi
i have a spread sheet with 10,000 sets of data ...ie: 10 sets of dat
labelled 1 to 100.

How can i get the minimum value for each value... i think i need t
write a macro but have no experience in this field...
my data would be like this...

1 0.045795
1 0.874388 need the minimum value for eac
set l labelled from 1 t
100.....
.....

.....
99 0.897473
99 0.847433
100 0.849737
100 0.983498

any help would be appreciated...
thank
 
Cram,
If you want to find the minimum value in each set,
something like this little macro may help. It plants
a "min" formula by the last cell of each set. i.e.
1 0.045795
1 0.874388 0.045795 <=min formula for set 1
.. 0.0798546
.. 0.849737 0.0798546 <=min for set ...
99 0.897473
99 0.847433 0.847433 <=min for set 99
100 0.849737
100 0.983498 0.849737 <=min for set 100

in this example, key in 2 to answer the prompt (that is,
there are only 2 numbers in a set)- modify that for the
number of values in each consecutive set.

Here's the macro: - just paste it in to the macros
section and run it. As always save your data off
first just in case....

Sub Minvals()
Dim r As Range
Set r = Selection
Dim x, cRow As Integer
Dim xSTR As String
xSTR = InputBox("How many numbers per set?")
x = Val(xSTR)
For Each c In r
If cRow Mod x = 0 Then beginrow = c.Row
cRow = cRow + 1
If cRow Mod x = 0 Then
c.Offset(0, 1).Value = "=MIN(" & Range(Cells
(beginrow, c.Column), Cells(c.Row, c.Column)).Address
& ")"
End If
Next c
End Sub

JR
 
I think I'd use a pivottable.

Add a single row of headers to your data.
Select your range (A1:B10000) and do
Data|Pivottable (wording varies with versions of excel)
Follow the wizard
Click next until you get to a dialog that has a Layout button on it
Click on Layout

Drag the category header to the row field.
Drag the quantity(?) header to the Data Field. But double click on it and
choose Min.

(you can drag it again and choose max, count, average--take a look at all the
options.)

Then ok/finish your way out of the pivottable wizard.

(One warning. If you change the raw data, the pivottable won't get updated
automatically. Just right click somewhere on that pivottable and click Refresh
Data to get it to, er, refresh.)
 
Another way.

Put the numbers 1:100 in D1:D100
(maybe =row() and copy down 100 rows)

then put this in E1:
=MIN(IF($A$2:$A$10001=D1,$B$2:$B$10001))
But instead of just hitting enter, I hit ctrl-shift-enter. This is an array
formula that will fill the selected cells with its results. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

(adjust the ranges to match the data)
 
Back
Top