Formula to count value of cells and find the average percentage

[khanaran]

Greetings

I am trying to solve a formula to find a count of three values and therafter find the aveage percentage

When the range is consecutive , no problem formula works with a slight error

When I am trying to count the value in alternate cells experience a problem

Thanx in anticipation for th help

Rajan

[AliGW]

No idea from your workbook what will determine which three cells the average should be taken from - tell us everything that is in your head, because we are NOT clairvoyant.

[bebo021999]

H10:

=sumproduct((m8>0)+(m10>0)+(m12>0))/3*100

[khanaran]

hi

I do understand that I have not really made my self clear, I redid the sheet with an exoplanation

Trust that this may be correct for the request for help

Regards

rajan

[JohnTopley]

Your comments in the file do not match values: L10 =5 , L12=8

Count is number > 0 ? and hence in your example 2 (out of 3) are > 0 so average is 66% ?

[khanaran]

Hi Sir

yes it only countsor should I have raher have said that the contents and not value so if L10 contain an entry and L12 contained an entry then the formula may work

Rajan

[JohnTopley]

Try

=SUMPRODUCT(--(L8:L12>0)*(MOD(ROW($8:$12),2)=0))/3

[kvsrinivasamurthy]

I am not clear. This may be.
In G6 then copy down

=SUMPRODUCT((COUNTIF($D6:$F6,$L$6:$L$14)=1)*($M$6:$M$14>0))/3*100

[khanaran]

Hi Sir

many thanx for the solution working brilliantly

much appreciated

With gratitude

rajan

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.