excel average if-function advanced

Question

I am trying to get the average value(s) of some specific entries. I have two columns: A-which is an index column (it goes e.g. from 1 to 1000) and B which is the values column.

I know there is an AVERAGE function and there is an AVERAGE IF function, which will probably help me but I can't seem to get it working the way I need to. What I need to do is to get the average value of the entries in column B that match this description for the index in column A: 3 + (3*n) in which n >= 0. In this case I need the average of the values in column B, whose entries in A are 3, 6, 9, 12, 15...

Is it possible to do this with excel or do you think it would be better to write a program to get those values?

Thanks for your tips!!

-Jordi

@simoco: Well said. What incentive is there for us to answer this (by the way, simple) qustion? — Bathsheba
– Bathsheba, Commented Mar 3, 2014 at 14:54

barry houdini · Accepted Answer · 2014-03-03 15:41:55Z

2

You can use an "array formula" with AVERAGE function, e.g.

=AVERAGE(IF(MOD(A2:A100,3)=0,IF(A2:A100>0,B2:B100)))

confirmed with CTRL+SHIFT+ENTER

To modify according to your comments in simoco's answer you can use this version

=AVERAGE(IF(MOD(A2:A100-11,3)=0,IF(A2:A100-11>=0,B2:B100)))

That will average for 11, 14, 17, 20 etc.

answered Mar 3, 2014 at 15:41

barry houdini

46.9k8 gold badges67 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dmitry Pavliv Over a year ago

actually I don't add condition >0, because OP said it goes e.g. from 1 to 1000. But in genereal case you're right

barry houdini Over a year ago

Yes, you are correct - I didn't see 1 to 1000 so that may not be required - would possibly be needed if there are zeros in col A or even if there are blanks (because those would be treated as zeroes)

Dmitry Pavliv · Accepted Answer · 2014-03-03 15:41:38Z

2

You can use SUMPRODUCT for this:

=SUMPRODUCT((MOD(A1:A1000,3)=0)*B1:B1000)/MAX(1,SUMPRODUCT(1*(MOD(A1:A1000,3)=0)))

Explanation:

MOD(A1,3) gives you 0 only if value in A1 is in form 3*n
MOD(A1:A1000,3)=0 gives you array of true/false values {FALSE,FALSE,TRUE,FALSE,..}
since False is casts to 0 and TRUE casts to 1 when multipliybg by any value, (MOD(A1:A1000,3)=0)*B1:B1000 returns you array of values in column B where corresponding value in column A is in form 3*n (otherwise zero 0): {0,0,12,0,..}
SUMPRODUCT((MOD(A1:A1000,3)=0)*B1:B1000) gives you a sum of thouse values in column B
SUMPRODUCT(1*(MOD(A1:A1000,3)=0)) gives you number of values in form 3*n in column A
and the last thing: MAX(1,SUMPRODUCT(1*(MOD(A1:A1000,3)=0))) prevent you from #DIV/0! error in case when there is no values in column A in form 3*n

UPD: in general case, say for rule 11+3*n you could use MOD(A1:A1000-11,3)=0

edited Mar 3, 2014 at 15:41

answered Mar 3, 2014 at 15:03

Dmitry Pavliv

35.9k13 gold badges85 silver badges85 bronze badges

5 Comments

Bathsheba Over a year ago

+1, notwithstanding a potential division by zero. Now, OP, upvote and accept if this is good.

Bathsheba Over a year ago

I prefer to use IFERROR() rather than arbitrary circumventions of things like divisions by zero.

Dmitry Pavliv Over a year ago

Yep, it's a good point, since OP can add some error message =IFERROR(..,"there is no values 3*n in col A")

user2013394 Over a year ago

thanks a lot simoco, I am trying to understand the formel. I understand the first and second part very well. this is almost what I was looking for but I think I made a mistake at my description. Your formel would work when searching for entries in A that are multiples of 3 (or of any other number replacing it)-It seemed like that's what I was looking for, because of the example I wrote on my post. But I actually need a more specific formel. for example (3*n) + 11. I think I might just know how to use your formel for this though. so thank you

Dmitry Pavliv Over a year ago

@user2013394, in that case use MOD(A1:A1000-11,3)=0

Collectives™ on Stack Overflow

excel average if-function advanced

2 Answers 2

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related