Need some help with a formula (Excel 2003)

Topic

New Reply

Do I ever need some help!!!! 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_BookedLoans))/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!

Reply | Quote

Replies

What do you want to do with incomplete quarters (at the beginning of the date range)?

Reply | Quote

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.

Reply | Quote

But the data only start in December 2004. How can formulas calculate values for months before that?

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

> for Oct, Nov, and Dec 2004

But there are no data for Oct and Nov…

Reply | Quote

I really am making this difficult for you, aren’t I??? 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!

Reply | Quote

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!

Reply | Quote

Thanks Hans!

Thanks for not giving up on me and my crazy requests.

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!!

Reply | Quote