(Follow on from previous post) Using IF statements

[paulkaye]

I have an alternating set of three columns, labelled (in row 5) Beg,
Mid and End. They represent the beginning, middle and end of each
month. Row 24 contains values for these periods. I would like to
calculate the average for all Beg columns, for all Mid columns and for
all End columns. In a previous post I got as far as putting following
formulae in three different cells:

=AVERAGE(IF('Cash flow'!5:5="Beg",'Cash flow'!24:24,""))
=AVERAGE(IF('Cash flow'!5:5="Mid",'Cash flow'!24:24,""))
=AVERAGE(IF('Cash flow'!5:5="End",'Cash flow'!24:24,""))

Strangely, the figure comes out as the same for all three, even when
there is only one value in the entire Cash flow'!24:24 row (it's in a
Beg column. Do these formulae really instruct Excel to average only
values in the Beg (or Mid or End) columns?

Many thanks,

Paul

 

[Bob Phillips]

When array entered it works fine for me.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[paulkaye]

Interesting - this time it worked. I think the problem was that,
although I was 'array entering' originally, after copying and pasting
the fomula into other cells, I was merely changing Beg to Mid/End etc.
Yes, I'm an idiot!

Thanks for your time,

Paul

 

[Bob Phillips]

Sometimes you just need the reassurance <g>

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)