Look up alternate columns

[Alby]

Hi all

I have a spreadsheet thus

A B C D et
1 25 1 22 3
2 14 3 18
3 22 3 19

What I need to do is 2 things......lookup and sum alternate rows A & C and als
lookup and average B & D

How do I do this without having to click on every cell

Thanks
Alby

 

[Frank Kabel]

Hi Alby
what do you mena with 'lookup and sum'. Are you trying to sum values in
column C for all rows in which A has a specific value. Then you may use
=SUMPRODUCT(--(A1:A10=value),(C1:C100)

So it would be helpful if you give a little bit more information
Frank

 

[rmills]

Not sure exactly what you are asking it sounds like you want row 1 a+c & average of row 1 b&d if so you only need it once in an empty column and paste it down in the rest

cell e1 - = a1 + b1 and cell f1 =(b1+d1)/2 -- copy & paste it to the others beolw ?



----- Alby wrote: -----

Hi all,

I have a spreadsheet thus:

A B C D etc
1 25 1 22 3
2 14 3 18 2
3 22 3 19 1

What I need to do is 2 things......lookup and sum alternate rows A & C and also
lookup and average B & D.

How do I do this without having to click on every cell?

Thanks
Alby

 

[Aladin Akyurek]

Let A24 house the sample of numbers you provided...

To sum figures in A and C...

=SUMPRODUCT((MOD(COLUMN($A$2:$D$4)-CELL("Col",$A$2)+0,2)=0)*($A$2:$D$4))

The ranges to sum should not house any text values like a formula-blank
(i.e., "").

To average figures in B and D...

=AVERAGE(IF(MOD(COLUMN($A$2:$D$4)-CELL("Col",$A$2)+1,2)=0,($A$2:$D$4)))

which must be confirmed with control+shift+enter instead of just with enter.

 

[Alan Beban]

Nobody (including me) seems to be able to figure out what you're trying
to say. Perhaps you could state the results you expect for the
particular illustration you have provided.

Alan Beban

 

[Alby]

thanks Aladin

 

[Alan Beban]

And a different approach for those to whose workbooks the functions in
the freely downloadable file at http://home.pacbell.net/beban are available:

=SUM(ArrayAlternates(A24,,FALSE)) and
=AVERAGE(ArrayAlternates(A24,FALSE,FALSE))

Alan Beban