Look up alternate columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
Let A2:D4 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.
 
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
 
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(A2:D4,,FALSE)) and
=AVERAGE(ArrayAlternates(A2:D4,FALSE,FALSE))

Alan Beban
 
Back
Top