An Excel formula that will...

py2006

(I hope this is the right place to ask this question?)

...calculate percentage completion.

For example, I am keeping a record of class exercises students have completed. There are about 25 in total. So under headings like Ex1, Ex2 etc I have the word 'Done' if they are completed and just left the others blank.

Now I know how to do a countblank or a countif but I need a formula that would display something along the lines of Student A has 85% of exercises completed based on reading all the 'Dones' against the total number of cells?

That make sense?

Edit: I could do a countif to count the 'dones' and a countblank to count to empty cells and calculate it that way?

advertsfox

Here you go - http://office.microsoft.com/en-ie/excel-help/count-nonblank-cells-HP003056101.aspx

gctest50

I think it will count cells that have a "space" in then as non-empty - copy the list somewhere through TRIM etc and then count them ?

Lu Tze

py2006 wrote: »

(I hope this is the right place to ask this question?)

...calculate percentage completion.

For example, I am keeping a record of class exercises students have completed. There are about 25 in total. So under headings like Ex1, Ex2 etc I have the word 'Done' if they are completed and just left the others blank.

Now I know how to do a countblank or a countif but I need a formula that would display something along the lines of Student A has 85% of exercises completed based on reading all the 'Dones' against the total number of cells?

That make sense?

Edit: I could do a countif to count the 'dones' and a countblank to count to empty cells and calculate it that way?

Look up the concatenate function for this part, like the following

=CONCATENATE(B1," has ",C1, "% of exercises completed")

Where B1 is referencing a cell with the students name, and C1 their percent complete giving you something like this as a result

John Stewart has 85% of exercises completed

dmc17

If there's 25 exercises in total, why not just use the countif to count how many the student has done and then divide by 25 and format as a percentage.

Neon_Lights

It may sound simple but equal the excerises completed to 0.04 if theres 25, or replace each value you have been using with 0.04, then SUM the rows up for each student. Then in the total column for each students format as percent.

If you wanted to get really fancy do a lookup, well its not that fancy, but you're best of keeping it simple.

py2006

Thanks guys, I am trying to get my head around it.

I seem to be confused with even basic maths now too :eek:

PaddyFagan

Given the blanks indicate not done the COUNTBLANK function might be easier - my example for 4 columns:

=1-COUNTBLANK(B2:E2)/4

Then format the column as a percentage - presto!

Paddy