CODEWITHSUNDEEP
excel
user2013394
user2013394

Reputation: 159

excel average if-function advanced

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

Upvotes: 0

Views: 1346

Answers (2)

barry houdini
barry houdini

Reputation: 46331

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.

Upvotes: 2

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35843

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

Upvotes: 2

Related Questions