Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AVERAGE(IF Formula (Excel 2000)

    Have checked through some previous posts looking for help with this function when dates are involved, but couldn't seem to get any of them to work for me. In the attached .xls, Cel J6,I would like to have an Avg of Jumbo_ND based upon the RDate and BOM. I did try:

    {=AVERAGE(IF((Tran_Date=<RDate)*(Tran_Date=>BOM),J umbo_ND)*FDIM))}

    This did not seem to work.

    Thanks for any assistance that you can provide.

    Marie

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AVERAGE(IF Formula (Excel 2000)

    Is this one OK:

    {=AVERAGE(IF((Tran_Date<=RDate)*(Tran_Date>=BOM),J umbo_ND)*FDIM)}
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AVERAGE(IF Formula (Excel 2000)

    Just the average (not including *FDIM) should come to 54, and I am getting 36? Do you think that this has something to do with the blank cells?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: AVERAGE(IF Formula (Excel 2000)

    Try:
    =AVERAGE(IF((Tran_Date<=RDate)*(Tran_Date>=BOM)*IS NUMBER(Jumbo_ND),Jumbo_ND)*FDIM)

    This will ignore blanks in Jumbo_ND and not treat them as zeroes.

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AVERAGE(IF Formula (Excel 2000)

    That was the ticket!

    Thanks again!!!

Posting Permissions

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