# Thread: Average range macro (excel 2000)

1. ## 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. ## 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. ## Re: Average range macro (excel 2000)

sorry i was making the file smaller and removed to much

4. ## 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. ## Re: Average range macro (excel 2000)

Thanks, never new it was possible in functions

Thanks again Hans

6. ## 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
•