CODEWITHSUNDEEP
excel
brane
brane

Reputation: 685

Excel - Apply IF on all elements of the column before doing an Average

     A
1    100.00%
2    0.00%
3    20.00%

I want to calculate the average of 3 elements, but before that, I want to apply the formula to every cell. If the value is more than 0 than use 100 in the calculation. IF(Cellvalue>0,100,CellValue)

Result off the average of my column should be 66.7%

Upvotes: 0

Views: 27

Answers (3)

Chronocidal
Chronocidal

Reputation: 8081

=SUMPRODUCT(AVERAGE(--(A1:A3>0)))

SUMPRODUCT forces this to evaluate as an Array Formula. This means it is the same as =AVERAGE(--(A1>0), --(A2>0), --(A3>0))

The -- will convert from TRUE/FALSE to 1/0

(A1:A3>0) or (A1>0) just checks "Is this value greater than zero", so --(A1>0) is just a short way to write IF(A1>0, 1, 0)

Upvotes: 1

user4039065
user4039065

Reputation:

Try this array formula (finished with ctrl+shift+enter, not just enter).

=AVERAGE(--(A1:A3>0))

Upvotes: 1

Solar Mike
Solar Mike

Reputation: 8415

Use countif() to count items greater than 0, then divide by the number of items (using count), then calculate %.

=COUNTIF(A1:A3,">0")/COUNT(A1:A3)*100

assuming your data is in cells A1 to A3.

Upvotes: 1

Related Questions