Results 1 to 11 of 11

20060113, 20:26 #1
 Join Date
 Mar 2004
 Location
 Mechanicsville, Virginia, USA
 Posts
 130
 Thanks
 0
 Thanked 0 Times in 0 Posts
Need some help with a formula (Excel 2003)
Do I ever need some help!!!! <img src=/S/blank.gif border=0 alt=blank width=15 height=15> I have this spreadsheet (attached), and am trying to create a formula that will average productivity scores for a quarter. I am not having a problem with the monthly score (below):
=IF(ISERROR(IF(($A33)>End_Date,"",SUMIF(Month,$A33 ,Express_BookedLoans)+SUMIF(Month,$A33,SBALOC_Book edLoans))/SUMIF(Month,$A33,Express_ActualFTE)),"",IF(($A33)> End_Date,"",SUMIF(Month,$A33,Express_BookedLoans)+ SUMIF(Month,$A33,SBALOC_BookedLoans))/SUMIF(Month,$A33,Express_ActualFTE))
but need that same type of formula for each quarter. The data (dates, etc.) are setup to change based upon a start and end date at the top of the worksheet. Changing those dates will move the positions for the QTD cells. This formula is set up in the date fields:
=IF(ISERROR(MONTH(A33)),EOMONTH(A32,1),IF(OR(MONTH (A33)=3,MONTH(A33)=6,MONTH(A33)=9,MONTH(A33)=12)," Q"&INT((MONTH(A33)1)/3)+1&" Avg",EOMONTH(A33,1)))
Hoping that someone could take a look at it, and point me in the right direction, if it is even possible.
Thanks!

20060113, 20:43 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Need some help with a formula (Excel 2003)
What do you want to do with incomplete quarters (at the beginning of the date range)?

20060113, 20:45 #3
 Join Date
 Mar 2004
 Location
 Mechanicsville, Virginia, USA
 Posts
 130
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Need some help with a formula (Excel 2003)
Thanks so much for taking a look at this Hans. As for the incomplete qtrs, I would ideally like for the formula to calculate the 2 months that you do not see,if that is possible.

20060113, 20:54 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Need some help with a formula (Excel 2003)
But the data only start in December 2004. How can formulas calculate values for months before that?

20060113, 21:04 #5
 Join Date
 Mar 2004
 Location
 Mechanicsville, Virginia, USA
 Posts
 130
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Need some help with a formula (Excel 2003)
Sounds strange doesn't it. Initially, someone would go and change the sourcedata for the graphs each month (32 graphs), and that is what I am trying to prevent (all about saving myself some time). Those graphs reflect 12 months worth of information to view, including QTD information. Is something like that not possible?

20060113, 21:15 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Need some help with a formula (Excel 2003)
Again: but what should the quarterly average for Q4 2004 be?
And why do you want the quarterly averages mixed with the monthly averages? If you're goin to make charts, wouldn't separate tables for monthly and quarterly dates be better?

20060113, 23:22 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Need some help with a formula (Excel 2003)
> for Oct, Nov, and Dec 2004
But there are no data for Oct and Nov... <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

20060113, 23:41 #8
 Join Date
 Mar 2004
 Location
 Mechanicsville, Virginia, USA
 Posts
 130
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Need some help with a formula (Excel 2003)
Sorry Hans! Ran out for a bite to eat.
In answer to your questions, and forgive me for not providing you with the information that you needed in the first post. The QTD Average should be the average of the sum of Express_BookedLoans & SBALOC_BookedLoans/sum of Express_ActualFTE for Oct, Nov, and Dec 2004. This is the referred method for viewing (see attached graph). If it can't be done, I certainly understand, and will look for another way to save myself from all of those manual updates each month.
Thanks again!
Wow!!! I just became a 2 Star Lounger

20060113, 23:42 #9
 Join Date
 Mar 2004
 Location
 Mechanicsville, Virginia, USA
 Posts
 130
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Need some help with a formula (Excel 2003)
I really am making this difficult for you, aren't I??? <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15> I certainly do not mean to.
I know that going into 2006, this graph will be displaying data for 2005 as well, just because we like to keep 12 months of data showing at all times. If the date range were from Feb 2005 through Feb 2006, the very 1st Qtr avg should include Jan, Feb, and Mar of 2005.
Maybe I should go back to the drawing board. Question...will graphs only work with continuous ranges of data? Would it be possible to set up the data in month order and then have all of my QTR averages at the bottom, but yet set up the graph so that they appear as M,M,M,Q Avg, M,M,M, etc...
Wow, I just became a 2 Star Lounger!

20060113, 23:59 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Need some help with a formula (Excel 2003)
I think the attached version will do what you want.
When you unzip the workbook and open it, you'll see #NAME in most of the cells. Don't worry, recalculate the worksheet (or enter something in a cell) and that will correct itself. It is caused by the use a function (EOMONTH) from the Analysis ToolPak, this doesn't behave nicely when switching between languages.
PS Congratulations on becoming a 2StarLounger!

20060114, 00:20 #11
 Join Date
 Mar 2004
 Location
 Mechanicsville, Virginia, USA
 Posts
 130
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Need some help with a formula (Excel 2003)
Thanks Hans!
Thanks for not giving up on me and my crazy requests. <img src=/S/anigrin.gif border=0 alt=anigrin width=19 height=19>
Funny, I thought that 2 Star lounger was really something...until I saw that I only have 100 posts total. Doesn't compare to 41k.
Have a great weekend!!