Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

AverageIF for non-contiguous rows of numbers greater than 0

1

I am trying to calculate the average of a set of numbers (greater than zero) that are in different cells in a column.

               A          B

              12      81.42025

              13       82.95802

              14       67.05291

              15       52.51652

              16       53.39906

              17       66.46298

              18       84.16883

              19       76.45436

              20        82.01697

              21          60.9438

              22          54.75423

             23          56.82723

             24           76.10901

            25            88.3394

             26         87.48684

I need the average of B12,B13, B18, B20, B25, B26. I used averageif ((B12,B13,B18, B20,B25,B26),">0")

But it gave an error

Please help me

Thanks

Lakshmi

excel
MS Office Version: 10
posted by: lakshmikoushik (rep: 2) on Tue Jul 19, 2016 at 12:38 pm
Answer
Discuss

Discussion

Please don't forget to select the answer that worked best for you! Just click the Select Answer button at the bottom of the desired answer.
don (rep: 1989) Jul 21, '16 at 1:25 pm
Add to Discussion

Answers

1

Try this:

=SUM(B12,B13,B18,B20,B25,B26)/INDEX(FREQUENCY((B12,B13,B18,B20,B25,B26),0),2)
posted by: don (rep: 1989) on Tue Jul 19, 2016 at 3:25 pm
Discuss
0

I'm not sure but I think that you could use this

=averageif({B12,B13,B18,B20,B25,B26},">0")

( Just Replace this () with {} )

UPDATE:

THAT WILL NOT WORK

posted by: MRVMV (rep: 52) on Thu Jul 21, 2016 at 7:04 am
Discuss

Discussion

Unofrtunately, that won't work. In my opinion though, it should work and Excel should update its functions to work more intuitively like that.
don (rep: 1989) Jul 22, '16 at 3:49 pm
Oh, OK
MRVMV (rep: 52) Jul 22, '16 at 4:05 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login