View Full Version : averaging 1 column depending on another
divingdingo
12-04-2007, 05:40 AM
i have two columns of data; a and b
i need to take the average of column "b" for all instances that column "a" is 1 then 2 then 3 then 4,5,6 etc....until 20
in order to do this i think i need to format column "a" to 1 significant figure.. (this but i can do!)
then i think i need to use a sumproduct function however i can't understand the syntax for sumfunction expressions.
(this is similar to one of my previous posts regarding averageing according to month. so i think i've already got the code i just need to alter it from looking up months to just numbers)
can anyone please guide(teach me!) the use of sumproduct, if that is indeed the expression i need.
many thanks
mark
mdmackillop
12-04-2007, 06:21 AM
=SUMPRODUCT(--(A1:A12=1),(B1:B12))/COUNTIF(A1:A12,1)
Bob Phillips
12-04-2007, 06:21 AM
=AVERAGE(IF(A2:A200=1,B2:B200))
=AVERAGE(IF(A2:A200=2,B2:B200))
etc.
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.
Bob Phillips
12-04-2007, 06:23 AM
=SUMPRODUCT(--(A1:A12=1),(B1:B12))/COUNTIF(A1:A12,1)
No need for SP here
=SUMIF(A1:A12,1,B1:B12)/COUNTIF(A1:A12,1)
divingdingo
12-04-2007, 07:22 AM
a few problems i am having with these expressions.
the first is that i thought i knew how to round down the numbers in column "a"
to one sig fig, using the "round" expression. however i can't get the syntax right in excel.
if column a isn't rounded down then i can't avarege column b's
the second problem would be how i would write the previously posted expressions in vba so that it worked from a macro?
thankyou
mark
divingdingo
12-04-2007, 07:30 AM
actually i think i've just cracked it using
activecell.formular1c1 = "=round(RC[-2],0)"
then autofilling the rest of the column.
brilliant
mdmackillop
12-04-2007, 08:14 AM
No need for SP here
I need the practice!!
Bob Phillips
12-04-2007, 08:22 AM
a few problems i am having with these expressions.
the first is that i thought i knew how to round down the numbers in column "a"
to one sig fig, using the "round" expression. however i can't get the syntax right in excel.
if column a isn't rounded down then i can't avarege column b's
the second problem would be how i would write the previously posted expressions in vba so that it worked from a macro?
thankyou
mark
Do it in one hit
Excel
=AVERAGE(IF(ROUNDDOWN(A2:A200,0)=2,B2:B200))
VBA
ActiveCell.FormulaArray = "=AVERAGE(IF(ROUNDDOWN(R2C1:R200C2,0)=2,R2C2:R200C2))"
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.