Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average If Macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average If Macro
=sumif(range,"<15",range)/countif(range,"<15")
-- Regards, Tom Ogilvy "Kris Taylor" wrote in message m... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average If Macro
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average If Macro
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 = [D1:D5000] [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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average If Macro
Ron Rosenfeld wrote in message . ..
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 = [D1:D5000] [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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average If Macro
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average If Macro
Ron Rosenfeld wrote in message . ..
On 2 Jun 2004 08:01:19 -0700, (Kris Taylor) wrote: 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 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 = [D1:D55000] [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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort, select and average macro help | Excel Discussion (Misc queries) | |||
Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
moving the formula "average" over one column in a macro | Excel Worksheet Functions | |||
Average Macro | Excel Discussion (Misc queries) | |||
Variable sized average macro/function | Excel Programming |