Percentage Formula

[pure vito]

Good Morning Experts,

Could i ask for some help please,

I have a range of cells from H2-H500 and L2-500,

I need a formula to tell me the Percentage of cells with a value, for example if i had 100 rows one mark on each row would = 1% no matter if their were more marks on a single row across the H-L range.

I really hope this makes sense i have attached a picture to help.

Thank You,

 

[etaf]

=SUM(N(MMULT(N(H2:L200=""),TRANSPOSE(COLUMN(H2:L200)^0))=COLUMNS(H2:L200)))
Array formula - but excel 365 will work that out
will give you the number of empty rows

then
ROWS(H2:H200) will give the rows total

=SUM(N(MMULT(N(H2:L200=""),TRANSPOSE(COLUMN(H2:L200)^0))=COLUMNS(H2:L200))) / ROWS(H2:H200)

Should give the %

Example using range A1 to E5 - just for simplicity

Book5
	A	B	C	D	E	F	G	H	I
1		12						Empty rows	1
2			2					Total Rows	5
3	1				1
4		A						Percent	20%
5
Sheet3

Cell Formulas
Range	Formula
I1	I1	=SUM(N(MMULT(N(A1:E5=""),TRANSPOSE(COLUMN(A1:E5)^0))=COLUMNS(A1:E5)))
I2	I2	=ROWS(A1:A5)
I4	I4	=SUM(N(MMULT(N(A1:E5=""),TRANSPOSE(COLUMN(A1:E5)^0))=COLUMNS(A1:E5)))/ROWS(A1:A5)

 

[pure vito]

That's fantastic thank you so much for taking the time to help out, works perfectly

 

[pure vito]

How might i reverse the percentage to show 0 from the start and work up to 100%

 

[etaf]

not sure i understand ?

 

[pure vito]

sorry for the misunderstanding, with the formula now inplace it shows 100% and then counts down to 0 with every value i add i need it to do the oppersite and count up from 0% to 100% and not 100% to 0%.

thank you for the additional help

 

[etaf]

OK, so still not sure i understand ,
As you add data , it will go from 0% all blank rows to 100% no blank rows
are we still counting blank rows ?

=ROWS(A1:A5)-SUM(N(MMULT(N(A1:E5=""),TRANSPOSE(COLUMN(A1:E5)^0))=COLUMNS(A1:E5)))

will tell you how many filled roes there are
so the % of filled rows
=(ROWS(A1:A5)-SUM(N(MMULT(N(A1:E5=""),TRANSPOSE(COLUMN(A1:E5)^0))=COLUMNS(A1:E5))))/ROWS(A1:A5)

In my example instead of showing 20% - 1 out of 5 rows all blank
it will show 80%
4 out of 5 rows full

if thats what you need then we could probably do that easier

Book5
	A	B	C	D	E	F	G	H	I
1		12						Empty rows	1
2			2					Total Rows	5
3	1				1
4		A							4
5
6								Percent	80%
7
Sheet3

Cell Formulas
Range	Formula
I1	I1	=SUM(N(MMULT(N(A1:E5=""),TRANSPOSE(COLUMN(A1:E5)^0))=COLUMNS(A1:E5)))
I2	I2	=ROWS(A1:A5)
I4	I4	=ROWS(A1:A5)-SUM(N(MMULT(N(A1:E5=""),TRANSPOSE(COLUMN(A1:E5)^0))=COLUMNS(A1:E5)))
I6	I6	=(ROWS(A1:A5)-SUM(N(MMULT(N(A1:E5=""),TRANSPOSE(COLUMN(A1:E5)^0))=COLUMNS(A1:E5))))/ROWS(A1:A5)

 

[pure vito]

Yes This is Exactly the result i needed thank you and sorry for the confusion, that's a great help and should keep me going for a little while

 

[etaf]

this maybe a better formula for counting cells that are NOT blank

=(ROWS(A1:A5) + ROWS(B1:B5) + ROWS(C1:C5)) - COUNTIF(A1:C5, "")

so
=((ROWS(H2:H200) + ROWS(I2:I200) + ROWS(J2:J200) + ROWS(K2:K200) + ROWS(L2:L200)) - COUNTIF(H2:L200, "")
gives the number of NONE blank rows

=ROWS(H2:H200) gives total number of rows

=((ROWS(H2:H200) + ROWS(I2:I200) + ROWS(J2:J200) + ROWS(K2:K200) + ROWS(L2:L200)) - COUNTIF(H2:L200, "") / ROWS(H2:H200)

maybe more efficient , although microsoft say ROWS() is volatile , I understand many sources say it isnt
Volatile is where every time you do something in the sheet , excel has to re-calculate

Book5
	A	B	C	D	E	F	G	H	I	J	K
1		12						Empty rows	2		3
2								Total Rows	5
3	1				1
4		A							3
5
6								Percent	60%		60%
Sheet3

Cell Formulas
Range	Formula
K1	K1	=(ROWS(A1:A5) + ROWS(B1:B5) + ROWS(C1:C5)) - COUNTIF(A1:C5, "")
I1	I1	=SUM(N(MMULT(N(A1:E5=""),TRANSPOSE(COLUMN(A1:E5)^0))=COLUMNS(A1:E5)))
I2	I2	=ROWS(A1:A5)
I4	I4	=ROWS(A1:A5)-SUM(N(MMULT(N(A1:E5=""),TRANSPOSE(COLUMN(A1:E5)^0))=COLUMNS(A1:E5)))
I6	I6	=(ROWS(A1:A5)-SUM(N(MMULT(N(A1:E5=""),TRANSPOSE(COLUMN(A1:E5)^0))=COLUMNS(A1:E5))))/ROWS(A1:A5)
K6	K6	=((ROWS(A1:A5) + ROWS(B1:B5) + ROWS(C1:C5)) - COUNTIF(A1:C5, ""))/ROWS(A1:A5)