Formula to omit a value from a calculation

kasava

New Member
Joined
Aug 8, 2014
Messages
11
I am working on a worksheet where if a value in a range of cells, F6:F11 is less than 85% the value in that row isn't to be included in the calculation. There are several columns that are averaged or summed based on the value in F6:F11.

I would appreciate some guidance. I tried to do it with an If formula but, got a #VALUE error.

Thank you,
 
Last edited:

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If you are using Excel 2007 or higher:

=AVERAGEIF(F6:F11,">=0.85")

This will work in any version:
=SUMIF(F6:F11,">=0.85")
 

kasava

New Member
Joined
Aug 8, 2014
Messages
11
Thank you for the reply. The formula results in an error. Here is a little more information. G6:G11 needs to be summed f F6:F11 is greater than or equal to 85%. H6:H11 need to be averaged is F6:F11 is greater than or equal to 85%. If I can get those two formulas to work I can get the rest in place.

Thank again.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
What version of Excel are you running?

=AVERAGEIF(F6:F11,">=0.85",H6:H11)
=SUMIF(F6:F11,">=0.85",G6:G11)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,906
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top