Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    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!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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)?

  3. #3
    2 Star Lounger
    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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?

  5. #5
    2 Star Lounger
    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?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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>

  8. #8
    2 Star Lounger
    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

  9. #9
    2 Star Lounger
    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!

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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!

  11. #11
    2 Star Lounger
    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!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •