Results 1 to 6 of 6
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Average range macro (excel 2000)

    All populated cells in column E contain the formula =ISBLANK(#REF!) resulting in FALSE, so apparently you have removed too much from the workbook.

  2. #2
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Average range macro (excel 2000)

    Hi Lounge,
    I bring forth a mind boggling question which soley belongs to the worksheet I have attached.

    What I have attached has me a in a perdicament.

    I have 5 columns a:E
    But the focus is on Column E the True / Falses.
    What I am trying to figure out is getting an average of the temperatures in column C.
    The True and False in Column E define a range,
    False followed by false are seperate ranges getting an average of the Temp in Col C for that row. 1 temp (27.8) =and average of 27.8
    but if the false in followed by a True on the next row it means that an average from column C needs to be taken from the False to the last true row

    for example
    E2 = False C2 = 27.78, the avarage shown in column F = 27.78
    E3 = False C3 = 12.78
    E4 = True C4 = 14
    the average of this range printed in F3 = 13.39

  3. #3
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Average range macro (excel 2000)

    sorry i was making the file smaller and removed to much

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Average range macro (excel 2000)

    Enter the following formula in G2:
    <code>
    =IF(F2,"",AVERAGE(OFFSET(C2,0,0,IF(F3,MATCH(FALSE, F4:F$468,0)+1,1),1)))
    </code>
    and fill down to G468 (for example by double clicking the fill handle of cell G2).
    To make the formula work for the last item, enter FALSE in F469 (below the last populated cell in column F)

  5. #5
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Average range macro (excel 2000)

    Thanks, never new it was possible in functions

    Thanks again Hans

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Average range macro (excel 2000)

    > never new

    You're never too old to learn <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

Posting Permissions

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