Can you use the min & average function with cells and not ranges?

[nyteflyte]

hey guys, i am having trouble again, this time it is with excel's built in mid and average function, i need them to use the cells property as opposed to the range property, so i can loop through them... is there a way to do this? or can you do that with the range? attached is my files for my code.. any help would be appreciated. thanks

[mdmackillop]

Something like
[vba]Private Sub cmdCalculate_Click()
Dim LR As Long, i As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LR
Cells(i, 3) = Application.Average(Cells(i, 4).Resize(, 5))
Next
End Sub

[/vba]
or
[VBA]Private Sub cmdCalculate_Click()
Dim LR As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(2, 3), Cells(LR, 3)).FormulaR1C1 = "=AVERAGE(RC[1]:RC[5])"
End Sub

[/VBA]

[nyteflyte]

awesome now how would i take out the lowest value and calculate the average after taht?

[mdmackillop]

Are you wanting cell formulae or results?

[nyteflyte]

well i want to make it so that it loops through and finds the lowest mark, then takes out that lowest mark and calculates the average after the lowest mark is taken out

[mdmackillop]

I understood that, but are you looking for formuale or results to be entered on the spreadsheet?

[nyteflyte]

i want the results in the cells

[mdmackillop]

[vba]Range(Cells(2, 3), Cells(LR, 3)).FormulaR1C1 = _
"=(SUM(RC[1]:RC[5])-MIN(RC[1]:RC[5]))/4"

[/vba]

[Bob Phillips]

[vba]

Private Sub cmdCalculate_Click()
Dim LR As Long
LR = Cells(Rows.Count, 1).End(xlUp).row
ActiveCell.Value = ActiveSheet.Evaluate( _
"AVERAGE(IF(A1:A" & LR & "<>MIN(A1:A" & LR & "),A1:A" & LR & "))")
End Sub
[/vba]