Average If Macro

[Kris Taylor]

Hi all!

I'm not quite sure whether this is feasible or not, however I guess I
can give it a shot.

I have a worksheet with 3 different tabs: i) Jan-Jun03 ii) Jul-Dec03
iii) Jan-Apr04

In each of these tabs I need to average a range of criteria in column
"D". The entries in column D range from -5000 to 6000 or so and it
goes on for about 50000 rows in each tab. Here's the catch; I need
to average based on intervals. I need it to give me an average for
the range of data between entries that go below 15. So basically,
everytime the data goes below 15, I should get an average based on all
prior data greater than 15. When an entry goes below 15 is totally
random.

If all the average intervals could go in column "M", that would be
great!!!

If further information is required, do not hesitate to tell me!

Feel free to post or email me!!!

Thanks in advance,

Kris Taylor

 

[Tom Ogilvy]

=sumif(range,"<15",range)/countif(range,"<15")

 

[Ron Rosenfeld]

Hi all!

I'm not quite sure whether this is feasible or not, however I guess I
can give it a shot.

I have a worksheet with 3 different tabs: i) Jan-Jun03 ii) Jul-Dec03
iii) Jan-Apr04

In each of these tabs I need to average a range of criteria in column
"D". The entries in column D range from -5000 to 6000 or so and it
goes on for about 50000 rows in each tab. Here's the catch; I need
to average based on intervals. I need it to give me an average for
the range of data between entries that go below 15. So basically,
everytime the data goes below 15, I should get an average based on all
prior data greater than 15. When an entry goes below 15 is totally
random.

If all the average intervals could go in column "M", that would be
great!!!

If further information is required, do not hesitate to tell me!

Feel free to post or email me!!!

Thanks in advance,

Kris Taylor

Your description excludes values that are equal to 15 from being averaged. Is
that what you want? I thought not, so they are included in the VBA solution
I've posted below; but could easily be excluded:

=================================
Option Explicit
Sub AvgGT15()
Dim AOI As Range
Dim StoreResult As Range
Dim c As Range
Dim i As Long
Dim Result()

Set StoreResult = [M1]
Set AOI = [D1500]
[M1:M5000].ClearContents

i = 0
ReDim Preserve Result(i)

For Each c In AOI
If c.Value < 15 Then
If Result(0) >= 15 Then
StoreResult.Value = Application.WorksheetFunction.Average(Result())
Set StoreResult = StoreResult.Offset(1, 0)
End If
i = 0
Result(0) = 0
Else
ReDim Preserve Result(i)
Result(i) = c.Value
i = i + 1
End If
Next c

If Result(0) >= 15 Then
StoreResult.Value = Application.WorksheetFunction.Average(Result())
Set StoreResult = StoreResult.Offset(1, 0)
End If

End Sub
========================
--ron

 

[Ron Rosenfeld]

Hi all!

I'm not quite sure whether this is feasible or not, however I guess I
can give it a shot.

I have a worksheet with 3 different tabs: i) Jan-Jun03 ii) Jul-Dec03
iii) Jan-Apr04

In each of these tabs I need to average a range of criteria in column
"D". The entries in column D range from -5000 to 6000 or so and it
goes on for about 50000 rows in each tab. Here's the catch; I need
to average based on intervals. I need it to give me an average for
the range of data between entries that go below 15. So basically,
everytime the data goes below 15, I should get an average based on all
prior data greater than 15. When an entry goes below 15 is totally
random.

If all the average intervals could go in column "M", that would be
great!!!

If further information is required, do not hesitate to tell me!

Feel free to post or email me!!!

Thanks in advance,

Kris Taylor

Your description excludes values that are equal to 15 from being averaged. Is
that what you want? I thought not, so they are included in the VBA solution
I've posted below; but could easily be excluded:

=================================
Option Explicit
Sub AvgGT15()
Dim AOI As Range
Dim StoreResult As Range
Dim c As Range
Dim i As Long
Dim Result()

Set StoreResult = [M1]
Set AOI = [D1500]
[M1:M5000].ClearContents

i = 0
ReDim Preserve Result(i)

For Each c In AOI
If c.Value < 15 Then
If Result(0) >= 15 Then
StoreResult.Value = Application.WorksheetFunction.Average(Result())
Set StoreResult = StoreResult.Offset(1, 0)
End If
i = 0
Result(0) = 0
Else
ReDim Preserve Result(i)
Result(i) = c.Value
i = i + 1
End If
Next c

If Result(0) >= 15 Then
StoreResult.Value = Application.WorksheetFunction.Average(Result())
Set StoreResult = StoreResult.Offset(1, 0)
End If

End Sub
========================
--ron

I overlooked that you were working with multiple worksheets. Depending on your
preferences, you could put another loop in the macro to cover all the
worksheets; a button on each worksheet; etc.


--ron

 

[Ron Rosenfeld]

Noted some typos and a little cleanup:

=================================
Option Explicit
Sub AvgGT15()
Dim AOI As Range
Dim StoreResult As Range
Dim c As Range
Dim i As Long
Dim Result()

Set StoreResult = [M1]
Set AOI = [D15000]
[M1:M5000].ClearContents

i = 0
ReDim Preserve Result(i)

For Each c In AOI
If c.Value < 15 Then
If Result(0) >= 15 Then
StoreResult.Value = Application.WorksheetFunction.Average(Result())
Set StoreResult = StoreResult.Offset(1, 0)
End If
i = 0
Result(0) = 0
Else
ReDim Preserve Result(i)
Result(i) = c.Value
i = i + 1
End If
Next c

If Result(0) >= 15 Then StoreResult.Value = _
Application.WorksheetFunction.Average(Result())

End Sub
========================

--ron

 

[Kris Taylor]

Noted some typos and a little cleanup:

=================================
Option Explicit
Sub AvgGT15()
Dim AOI As Range
Dim StoreResult As Range
Dim c As Range
Dim i As Long
Dim Result()

Set StoreResult = [M1]
Set AOI = [D15000]
[M1:M5000].ClearContents

i = 0
ReDim Preserve Result(i)

For Each c In AOI
If c.Value < 15 Then
If Result(0) >= 15 Then
StoreResult.Value = Application.WorksheetFunction.Average(Result())
Set StoreResult = StoreResult.Offset(1, 0)
End If
i = 0
Result(0) = 0
Else
ReDim Preserve Result(i)
Result(i) = c.Value
i = i + 1
End If
Next c

If Result(0) >= 15 Then StoreResult.Value = _
Application.WorksheetFunction.Average(Result())

End Sub
========================

--ron

Ron,

Thanks for your help thus far. For some reason however, the macro
seems to be giving me a Type Mismatch error. On the first page, it
gives me 47 averages, then stops, the second page gives me around 10
and the third page gives me nothing.

I went through it and made sure there were no spaces, formatted so
that everything was a number and I still have the same problem...

Any suggestions? Is my spreadsheet too big?

Let me know!

Thanks again for all of your help up to now!

Kris Taylor

 

[Ron Rosenfeld]

Thanks for your help thus far. For some reason however, the macro
seems to be giving me a Type Mismatch error. On the first page, it
gives me 47 averages, then stops, the second page gives me around 10
and the third page gives me nothing.

I went through it and made sure there were no spaces, formatted so
that everything was a number and I still have the same problem...

Any suggestions? Is my spreadsheet too big?

Definitely need some troubleshooting.

I can't imagine that your SS is too big for this to work.

So let's see what we can find out.

Are you using the routine exactly as I posted it, or did you make some
modifications? If you did make any modifications, post back here the code you
are using.

On what line does it give the Type Mismatch error?

When that happens, float your cursor over the various variables and let me know
what's in them.

i =
Result(i) =
c.value =
Result(0) =

--ron

 

[Kris Taylor]

Definitely need some troubleshooting.

I can't imagine that your SS is too big for this to work.

So let's see what we can find out.

Are you using the routine exactly as I posted it, or did you make some
modifications? If you did make any modifications, post back here the code you
are using.

On what line does it give the Type Mismatch error?

When that happens, float your cursor over the various variables and let me know
what's in them.

i =
Result(i) =
c.value =
Result(0) =

--ron

Ron, for each spreadsheet, here is the requested data:

1)

i=6615
Result(i)=Subscript out of range
c.value=11.82
Result(0)=24.54

2)

i=6084
Result(i)=Subscript out of range
c.value=13.05
Result(0)=16.67

3)

i= 10379
Result(i)= Subscript Out Of Range
c.value= 0
Result(0)= 33.99

My code currently looks like:

Option Explicit
Sub AvgGT15()
Dim AOI As Range
Dim StoreResult As Range
Dim c As Range
Dim i As Long
Dim Result()

Set StoreResult = [M1]
Set AOI = [D155000]
[M1:M55000].ClearContents

i = 0
ReDim Preserve Result(i)

For Each c In AOI
If c.Value < 15 Then
If Result(0) >= 15 Then
StoreResult.Value = Application.WorksheetFunction.Average(Result())
Set StoreResult = StoreResult.Offset(1, 0)
End If
i = 0
Result(0) = 0
Else
ReDim Preserve Result(i)
Result(i) = c.Value
i = i + 1
End If
Next c

If Result(0) >= 15 Then StoreResult.Value = _
Application.WorksheetFunction.Average(Result())

End Sub

Thanks,

Kris Taylor