I need to create a formula that will count the number of my variables and average them for 30 days.
I have my sheet set up. Under "Dr. Bennett" I want C2 to equal the number of variables in 30 days and C3 to equal the average. And it would be 30 days from today. Thanks!!
where are your variables in that sheet ?count the number of my variables
Also mention your expected output.. thanks.
Regards,
DILIPandey
<click on below * if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), [email protected]
Not sure what variables you are looking for...
My expected output is a count of the inputs for the last 30 days and a average of those inputs.
Looking at the spreadsheet the inputs are C13:C?? currently C51 but will continue to add throughout the year.
Okay, by # of variables, do you mean total count or total unique count.
For last 30 days in your sheet (back to 3/20) there are 12 points or 5 unique values (2.8, 3, 3.2, 3.4 and 4). Which do you want?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
In that example I want 12. So I want 12 to be the answer in C2 and the average of those 12 variables in C3. And then the same idea for 60, 90, 120 days etc.
dilipandey - I am sorry that is not the results I am looking for.
C2 = number of variables within 30 day date range. (30 days from today.) In this case it is 12.
C3 = average of variables within 30 day date range. In this case it is 3.2
Okay... see the attached file:-
Review Counts and Averages.xls
Regards,
DILIPandey
<click on below * if this helps>
Okay,
=COUNTIF($A$13:$A$100, ">="& TODAY()-30)
and
=AVERAGE(INDEX($C$13:$C$100, MATCH(TODAY()-30, $A$13:$A$100)):INDEX($C$13:$C$100, MATCH(TODAY(), $A$13:$A$100)))
Gives 12 and 3.2
This is what I am looking for!
Is there a way to pull the COUNTIF from the column? My problems is that it is currently pulling from the "A" date column. But I also want it to pull 0 if there are no entries of that type. Example: C2 =12 but D2=0. I am trying to track whether or not I am receiving all work. Date range would still be the same.
Since you are using 2003, we'll switch to SUMPRODUCT
=SUMPRODUCT(--($A$13:$A$100 >= (TODAY()-30)), --(C$13:C100>0))
or for D
=SUMPRODUCT(--($A$13:$A$100 >= (TODAY()-30)), --(D$13:D100>0))
Ok let me give you a revised spreadsheet. Can you write something that accounts for DIV errors? Maybe if there is a 0 in either field to result in a blank.
Hmmm, with 2003, the only way I see to do that would be to duplicate your formula in an IF statement,
=IF(ISERROR(AVERAGE(INDEX(H$13:H$5000,MATCH(TODAY()-30,$A$13:$A$5000)):INDEX(H$13:H$5000,MATCH(TODAY(),$A$13:$A$5000)))),"",AVERAGE(INDEX(H$13:H$5000,MATCH(TODAY()-30,$A$13:$A$5000)):INDEX(H$13:H$5000,MATCH(TODAY(),$A$13:$A$5000))))
Simply
=IF(ISERROR(your formula), "", your formula)
Does that work for you?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks