Calculating Percentage when each value is a piece of a total

[jcalvacca]

Example


A1: 5

A2: 6

A3: 9

A4: 2


Each of these values are out of a possible 10, so I would like the cell used to show the result to be able to count the "entries" and then return the correct percentage for the value against the total amount of the entries.


So above should be able to count 40 ("10" for each entry) and then take the total value ("22") and return in the cell 22/40=.55


The value of each "entry" would be static (always 10)

Obviously the results of each box will be different but always <=10

The number of entries will be dynamic but will always be part of a range of cells

(20 cells across, but perhaps 7 cells will be blank out of 20 AND some txt that won't count like N/A)


Thanks.

 

[hiwiller]

So in A1:C3 you have {7,6,3,8,3,6,6,2,4}


1 2 3

A 7 8 6

B 6 3 2

C 3 6 4


you could in another cell use the following array formula (use CTRL+SHIFT+ENTER to enter either of the formulas below - one using sumproduct, other using sumif)

=SUMIF(A1:C3,">0")/(COUNTIF(A1:C3,">0")*10)

=SUMPRODUCT(A1:C3,IF(ISNUMBER(A1:C3),1,0))/(COUNTIF(A1:C3,">0")*10)

expand the range as needed.

 

[jcalvacca]

Close......I need it to count 0 as a "0 value"


so 5,6,0,4,A,B Returns .375

 

[hiwiller]

=SUMIF(A1:C3,">-1")/(COUNTIF(A1:C3,">-1")*10)

 

[jcalvacca]

Awsome.

Thanks.

"COUNTIF" is really strong, 1st time using it.

 

[hiwiller]

glad to help