Conditional Average Possible? - RESOLVED

mapakunk

New Member
Joined
May 7, 2002
Messages
40
I would like to average a range of numbers if the dates are between 4 weeks ago and today? any ideas.
The dates are in column d12:d100 and the values are in column p12:p100
any help would be greatly appreciated. Also if I can do this without VBA I would appreciate it beacuse I do not know VBA!

As far as I know there is no averageif function.

_________________
mapakunk
This message was edited by mapakunk on 2002-05-16 09:50
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
I would like to average a range of numbers if the dates are between 4 weeks ago and today? any ideas.
The dates are in column d12:d100 and the values are in column p12:p100

=AVERAGE(IF(D12:D100>TODAY()-28,P12:P100))


This is an array formula and must be entered into the cell by pressing Ctrl-Shift-Enter at the same time.
This message was edited by Steve Hartman on 2002-05-08 11:43
 
Upvote 0
On 2002-05-08 08:32, mapakunk wrote:
I would like to average a range of numbers if the dates are between 4 weeks ago and today? any ideas.
The dates are in column d12:d100 and the values are in column p12:p100
any help would be greatly appreciated. Also if I can do this without VBA I would appreciate it beacuse I do not know VBA!

As far as I know there is no averageif function.

=SUMPRODUCT((D12:D100>=A1-28)*(D12:D100<=A1),P12:P100)/MAX(1,SUMPRODUCT((D12:D100>=A1-28)*(D12:D100<=A1)))

where A1 holds the formula: =TODAY().

Aladin
 
Upvote 0
This seems to pick the right columns, however the values this would be are about 9000 and the average that is returned is 1384.80?? I copied your formula, pasted it, deleted the space, and rpessed Ctrl+shft+enter at the end to make it an array formula maybe there is something I should be telling you that I'm not??

Thanks for the SPEEDY reply!!!!
 
Upvote 0
Aladin,

That was the ticket! Thank you very much!
This is one to scribble down in the back of my excel2000 bible!!!!

:>
 
Upvote 0
On 2002-05-08 08:49, mapakunk wrote:
This seems to pick the right columns, however the values this would be are about 9000 and the average that is returned is 1384.80?? I copied your formula, pasted it, deleted the space, and rpessed Ctrl+shft+enter at the end to make it an array formula maybe there is something I should be telling you that I'm not??

Nope, something I should have told you. Thanks to the edit function, the formula you tried was not the right one. I realised after I posted it that the "less than" should have been a "greater than" and I edited the post. Unfortunately it was after you had seen the original.
 
Upvote 0
I think you could also go with

=SUMIF(D12:D100,">="&TODAY()-28,P12:P1002)/COUNTIF(D12:D100,">="&TODAY()-28)

if you notice any performance issues.

good luck

edited for the cool denominator trick
=SUMIF(D12:D100,">="&TODAY()-28,P12:P1002)/MAX(COUNTIF(D12:D100,">="&TODAY()-28),1)
This message was edited by IML on 2002-05-08 09:43
 
Upvote 0
On 2002-05-08 09:41, IML wrote:
I think you could also go with

=SUMIF(D12:D100,">="&TODAY()-28,P12:P1002)/COUNTIF(D12:D100,">="&TODAY()-28)

if you notice any performance issues.

good luck

edited for the cool denominator trick
=SUMIF(D12:D100,">="&TODAY()-28,P12:P1002)/MAX(COUNTIF(D12:D100,">="&TODAY()-28),1)
This message was edited by IML on 2002-05-08 09:43

Makapunk,

I'd suggest using the above SUMIF version. & it's not (just) a performance issue as Ian modestly puts. I just overlooked the fact that today is not a fixed date, so there is no need to take that as a second condition which necessitated SUMPRODUCT (you might still keep it on the back of Excel bible, though).

Regards,

Aladin
This message was edited by Aladin Akyurek on 2002-05-08 10:03
 
Upvote 0
I guess i could use the sumproduct formula, keeping today() in A1, and then change that date if I wanted to see an average for 4 weeks from any given date.

Thanks for the help, this BB is VERY Helpful.
especially because it is free. Maybe someday I'll be able to return the favor...

Tanks everybody,

mapakunk
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top