How To Count The Number Of Items Less Than The Average

2017-11-05

Example

=COUNTIF(B3:B10,”<&AVERAGE(B3:B10))

Generic Formula

=COUNTIF(Numbers,”<&AVERAGE(Numbers))
Numbers – This is the range of numbers from which you want to count the number of items less than the average.

What It Does

This formula will take the average of a numerical range then return the count of the items in the range that are less than the average.

How It Works

AVERAGE(Numbers) will return the arithmetic mean or average of the Numbers. In our example this is (13+5+7+9+20+3+4+6)/8 = 8.4. “<"&8.4 then creates the text string “<8.4" which is used as a condition in our COUNTIF function.

COUNTIF(Numbers,”<8.4") will then count all the numbers in our list which are less than 8.4. In our example this results in 5 since there are 5 numbers in the list that are less than 8.4.

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Advertisement

Related Articles

Comments

0 Comments

Get The Latest News

Follow Us

Follow us on social media to stay up to date with the latest tips in Excel!

Pin It on Pinterest

Share This