Formula to calculate % only of filled in cells

%3CLINGO-SUB%20id%3D%22lingo-sub-1398864%22%20slang%3D%22en-US%22%3EFormula%20to%20calculate%20%25%20only%20of%20filled%20in%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1398864%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20struggling%20to%20put%20this%20into%20words%20(let%20alone%20write%20a%20formula%20for%20it)%20so%20hopefully%20you%20can%20understand%20what%20I%20mean.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20a%20teacher%20and%20I%20have%20an%20excel%20document%20where%20I%20record%20the%20marks%20each%20student%20receives%20in%20their%20tasks%20completed%20during%20class%2C%20for%20example%20in%20lesson%201%20we%20might%20do%203%20tasks%20and%20a%20student%20scores%203%2F4%2C%201%2F4%20and%205%2F6%3B%20in%20lesson%202%20they%20score%204%2F6%20and%205%2F5%3B%20and%20in%20lesson%203%20they%20score%201%2F2%2C%203%2F3%20and%203%2F3.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20then%20have%20a%20second%20sheet%20which%20calculates%20their%20score%20into%20an%20overall%20percentage%20(by%20dividing%20by%20the%20total%20of%20all%20questions%2C%20in%20the%20above%20example%2033).%20The%20problem%20is%2C%20if%20a%20student%20misses%20a%20lesson%20their%20score%20goes%20down%20significantly%20-%20I%20would%20like%20to%20calculate%20only%20the%20percentage%20of%20the%20scores%20that%20have%20been%20filled%20in.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHopefully%20someone%20can%20help%20me%20achieve%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EDarren.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1398864%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1399010%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20calculate%20%25%20only%20of%20filled%20in%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1399010%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F671156%22%20target%3D%22_blank%22%3E%40MrCampbellPE%3C%2FA%3E%26nbsp%3Byou%20can%20do%20something%20like%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1399684%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20calculate%20%25%20only%20of%20filled%20in%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1399684%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F671156%22%20target%3D%22_blank%22%3E%40MrCampbellPE%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Darren%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attach%20a%20slightly%20different%20solution%20which%20hopefully%20will%20give%20what%20you%20want.%3C%2FP%3E%3CP%3EI%20have%20kept%20it%20all%20on%20one%20sheet%20so%20that%20you%20could%20see%20the%20totals%20and%20calculations%20but%20there%20is%20no%20reason%20why%20you%20can't%20move%20the%20totals%20to%20a%20second%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehope%20this%20helps%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1399957%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20calculate%20%25%20only%20of%20filled%20in%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1399957%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F671156%22%20target%3D%22_blank%22%3E%40MrCampbellPE%3C%2FA%3E%26nbsp%3B%20There%20are%20many%20ways%20to%20do%20things%20so%20here%20is%20one%20way.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1411380%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20calculate%20%25%20only%20of%20filled%20in%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1411380%22%20slang%3D%22en-US%22%3E%3CP%3EExcellent%20guys%2C%20thank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1411831%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20calculate%20%25%20only%20of%20filled%20in%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1411831%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F671156%22%20target%3D%22_blank%22%3E%40MrCampbellPE%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Darren%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20please%20mark%20the%20solution%20as%20complete.%20Many%20thanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

 

I'm struggling to put this into words (let alone write a formula for it) so hopefully you can understand what I mean.

 

I am a teacher and I have an excel document where I record the marks each student receives in their tasks completed during class, for example in lesson 1 we might do 3 tasks and a student scores 3/4, 1/4 and 5/6; in lesson 2 they score 4/6 and 5/5; and in lesson 3 they score 1/2, 3/3 and 3/3.

 

I then have a second sheet which calculates their score into an overall percentage (by dividing by the total of all questions, in the above example 33). The problem is, if a student misses a lesson their score goes down significantly - I would like to calculate only the percentage of the scores that have been filled in.

 

Hopefully someone can help me achieve this.

 

Thanks,

Darren.

5 Replies

@MrCampbellPE you can do something like attached file.

@MrCampbellPE 

 

Hi Darren,

 

I attach a slightly different solution which hopefully will give what you want.

I have kept it all on one sheet so that you could see the totals and calculations but there is no reason why you can't move the totals to a second sheet.

 

hope this helps you!

 

Peter

 

@MrCampbellPE  There are many ways to do things so here is one way.

 

Excellent guys, thank you.

@MrCampbellPE 

 

Hi Darren,

 

Can you please mark the solution as complete. Many thanks!

 

Peter