Excel calculation question

Niv

I want to work out a percentage in a range but if the return for one of the rows is zero i want to exclude it from the percentage calculation. Data set below to show what i have at the moment. What I want is the last entry to be disregarded because the return is zero. I know I could manually change the formula to exclude it but this will be used on a document that others will be using and they will not 'remember' or more concerning 'know' how to do it.
I am thinking that maybe I could use an if statement in the max available column so if weighted score is 0 then max score is zero. Would that maybe be the easiest way?
Any tips appreciated. 

Weighted Score	Overall Maximum Available
35	48
48	48
48	48
48	48
48	48
48	48
48	48
48	48
48	48
48	48
48	48
48	48
48	48
48	48
48	48
48	48
48	48
48	48
48	48
0	48
899	960
Overall percentage	94%

WaywardDriver

If values in columns A and B then enter the formula =SUM(A:A)/SUMIF(A:A,">0",B:B) in cell C1 say.
Edited to correct error in formula.

Niv

Thats great thank you so much 
WaywardDriver said:

If values in columns A and B then enter the formula =SUM(A:A)/SUM(A:A,">0",B:B) in cell C1 say.

Heedtheadvice

Niv, do take care with such summing methods where a whole column is used. It is very easy to inadvertantly have a value in a column that ought not be part of the calculation such as above or even well below the data set. Even more important if the sheet is being used by inexperienced persons!

Not familiar with that particular way of summing with specific criteria. The more common/understood way is to use the sumif function, designed to sum only values that meet formula defined criterium see https://support.office.com/en-us/article/sumif-function-169b8c99-c05c-4483-a712-1697a653039b

 or on that same page under See Also sumifs for multiple criteria.

Niv

Thanks for the heads up, it will be a hidden tab with the answer displayed elsewhere, believe me that is enough to stop them but I will also password protect the sheet.

WaywardDriver

Sorry - formula should be =SUM(A:A)/SUMIF(A:A,">0",B:B)
Yes agree should be careful when summing whole column although Excel will ignore non-numeric values.

Heedtheadvice

Glad you corrected the formula o wayward one. I thought I had missed something!!

Niv, if that is your answer to the security required then instead of having a sheet 'tab' hidden...just have it on another workbook that your other users do not have access to or is hidden to them. You can even move the sheet you have developed to the separate workbook....or alternatively have the user input sheets on their own?

There are advantages to that approach in that, for example, you can update/change your 'percentage' workbook ('offline') without affecting the user sheet(s) and just replace you working version with the new modified one!

Niv

OMG, I cant let users input sheets on their own they would seriously fail. I get queries asking me where their name is on the list only for me to repeatedly have to tell them 'there is a filter on the list, just change it on the drop-down'... I do go spare with them sometimes I will admit.

Heedtheadvice

Tee hee! Been there too. Good luck!