PDA

View Full Version : Can you use the min & average function with cells and not ranges?



nyteflyte
06-17-2007, 01:41 PM
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
06-17-2007, 02:12 PM
Something like
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


or
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

nyteflyte
06-17-2007, 02:21 PM
awesome now how would i take out the lowest value and calculate the average after taht?

mdmackillop
06-17-2007, 02:23 PM
Are you wanting cell formulae or results?

nyteflyte
06-17-2007, 02:27 PM
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
06-17-2007, 02:29 PM
I understood that, but are you looking for formuale or results to be entered on the spreadsheet?

nyteflyte
06-17-2007, 02:31 PM
i want the results in the cells

mdmackillop
06-17-2007, 02:43 PM
Range(Cells(2, 3), Cells(LR, 3)).FormulaR1C1 = _
"=(SUM(RC[1]:RC[5])-MIN(RC[1]:RC[5]))/4"

Bob Phillips
06-17-2007, 03:13 PM
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