CODEWITHSUNDEEP
excel
osomanden
osomanden

Reputation: 611

excel calculate based on field value

I would like to calculate average percentage of the applications marked as in use (Yes)

Anyone?

    Average Percentage:     
    A       B           C
1   Name    Percentage  In Use
2   Hammer  65%         Yes
3   Fork    77%         Yes
4   Spoon   65%         No
5   Cars    33%         No
6   Wheel   87%         Yes

Upvotes: 0

Views: 42

Answers (3)

Jim
Jim

Reputation: 1005

For a one-time solution:

  • Mark all the values from A1 to C6.
  • Go to Filter and Sort and select Sort
  • In C1 there will now be a dropdown with 3 checkboxes: All, Yes, No, Empty. Check the Yes box
  • Now select all values in coumn B.
  • Then in the little tool square thing. (Don't know what its called) select Totals and Avarage.

The average percentage of all rows marked Yes wil now be calculated.

A Macro solution:

Sub avarage()
    Range("A2:C6").AutoFilter
    ActiveSheet.Range("$A$2:$C$6").AutoFilter Field:=3, Criteria1:="Yes"
    Range("B7").FormulaR1C1 = "=MIDDEL(R[-5]C:R[-1]C)"
    Range("B7").Font.Bold = True
End Sub

Upvotes: 0

Kyle
Kyle

Reputation: 2545

I would just use the built-in AVERAGEIF(). 

=AVERAGEIF($C$2:$C$6,"Yes",$B$2:$B$6)

Upvotes: 2

R3uK
R3uK

Reputation: 14537

With classical and useful functions :

=SUMIF($C$2:$C$6;"=Yes";$B$2:$B$6)/COUNTIF($C$2:$C$6;"=Yes")

Or with SUMPRODUCT (awesome function) :

=SUMPRODUCT(($C$2:$C$6="Yes")*($B$2:$B$6))/SUMPRODUCT(($C$2:$C$6="Yes")*1)

Upvotes: 0

Related Questions