averaging 1 column depending on another

[divingdingo]

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]

=SUMPRODUCT(--(A1:A12=1),(B1:B12))/COUNTIF(A1:A12,1)

[Bob Phillips]

=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]

=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]

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]

actually i think i've just cracked it using

activecell.formular1c1 = "=round(RC[-2],0)"

then autofilling the rest of the column.

brilliant

[mdmackillop]

No need for SP here
I need the practice!!

[Bob Phillips]

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))"