Refer to one row, take value from another

[paulkaye]

Hi,

I would like to display, in one cell, the average of all values in row
2 where "beg" appears in the same column of row 1.

For some reason I am incapable of getting the search terms for this
correct - I'm sure this must have been covered in a previous post!

Thanks in advance,

Paul

 

[Roger Govier]

Hi

Try the array entered formula
{=AVERAGE(IF(1:1="beg",2:2,""))}

To enter or amend array formulae, use Control + Shift + Enter (CSE) as
opposed to Enter
Do not type the curly braces { } yourself. If you use CSE, Excel will
insert them for you.

 

[paulkaye]

That's great - thank you! I was playing with LOOKUP functions and
getting nowhere!

A problem I didn't forsee was that there are zero values in row 2
which I need to ignore to get an accurate result. How can I do this?
An embedded IF statement?

 

[Don Guillett]

=AVERAGE(IF((1:1="beg")*(2:2<>0),2:2,""))

 

[paulkaye]

Unfortunately, that gave me the same result!

=AVERAGE(IF((1:1="beg")*(2:2<>0),2:2,""))

 

[Don Guillett]

I just retested. Details?

 

[Roger Govier]

Hi

Try
{=SUM((A1:G1="beg")*(A2:G2>0)*A2:G2)/SUM((A1:G1="beg")*(A2:G2>0))}

again array entered

 

[Roger Govier]

Hi Don

I have only just seen your response.
Yes it works fine.
It is better than my second response to the OP.

 

[paulkaye]

I don't really know what details to give! The final figure was lower
than expected and, when I checked, saw it was the average of all the
values including zeros.

Thank you both for your later responses but I have now realised that
there is a better way for me to deal with this problem. The zeros that
are being counted are caused by a reference to an empty cell. However,
there could be times that a zero is the 'real' figure required and by
eliminating all zeros I will prevent the spreadsheet from working
correctly. Instead of this, I will add an IF statement to the first
formula so that ISEMPTY cells will result in "", rather than zero.
This way, 'real' zeros will be taken into account and artifactual
zeros will not.

Thank you both again for your time,

Paul

 

[paulkaye]

ISBLANK, I mean!

I don't really know what details to give! The final figure was lower
than expected and, when I checked, saw it was the average of all the
values including zeros.

Thank you both for your later responses but I have now realised that
there is a better way for me to deal with this problem. The zeros that
are being counted are caused by a reference to an empty cell. However,
there could be times that a zero is the 'real' figure required and by
eliminating all zeros I will prevent the spreadsheet from working
correctly. Instead of this, I will add an IF statement to the first
formula so that ISEMPTY cells will result in "", rather than zero.
This way, 'real' zeros will be taken into account and artifactual
zeros will not.

Thank you both again for your time,

Paul

 

[paulkaye]

Hold on,

I thought all was well, but actually there's still a problem with the
original formula. Let me paste it 'as is' and give you the real
situation. I have a row with an alternating set of three columns,
labelled (in row 5) Beg, Mid and End. In row 24 I would like to have
the average for all Beg columns, for all Mid columns and for all End
columns. I therefore have the following formulae in three adjacent
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?

Thanks again,

Paul

 

[Don Guillett]

The formula below needs to be ARRAY entered using ctrl+shift+enter vs just
enter. Then it may be copied if desired. It will average ROW 24 for values
in ROW 5 having "Beg". IF?? you want columns modify to suit and make sure
that you do NOT include the entire column. Also, best for rows too. What do
you want?

=AVERAGE(IF('Cash flow'!5:5="Beg",'Cash flow'!24:24,""))
rows
=AVERAGE(IF('Cash flow'!a5:iv5="Beg",'Cash flow'!a24:iv24,""))
for columns
=AVERAGE(IF((b3:b33="beg")*(a3:a33<>0),a3:a33,""))

 

[paulkaye]

Hi Don,

I thought maybe this post had dropped off the radar so followed it up
in another post. 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!

Anyway, it's sorted now. Well, that bit is - I've just posted another
question now!

Thanks again for all your time,

Paul

 

[paulkaye]

Hi Don,

I thought maybe this post had dropped off the radar so followed it up
in another post. 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!

Anyway, it's sorted now. Well, that bit is - I've just posted another
question now!

Thanks again for all your time,

Paul

 

[paulkaye]

Hi Don,

I thought maybe this post had dropped off the radar so followed it up
in another post. 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!

Anyway, it's sorted now. Well, that bit is - I've just posted another
question now!

Thanks again for all your time,

Paul

 

[Don Guillett]

OK. I got this reply 3 times.