Excel 2007 : how to Calculate formula to exclude empty cells

[berk21]

I just want to know if cell P36 is averaging column P without zeros correctly.
right now there are 16 names. If the number of names alter, is the formula set up accurate and not include the cells without names.

[shg]

Yes, but it could be simplified to =SUM(P10:P34) / COUNTIF(P10:P34, "<>0")

However, by excluding zeros, you are excluding some legit values, like P10.

[berk21]

ok...then maybe I should have said, can that not average the cells without names. as names will be added and possible deleted time to time.

[solnajeff]

Hi

Why not just use the same formula as for each individual calculation as you already have the amounts totalled, you have already done this in cells J36 and K36.

Regards


Jeff

[JBeaucaire]

Currently, you are equating individual performance in the P column all through the rest of the sheet. So, Ryan in row 11, you are creating a failure percentage in P11 by dividing QC failures by total jobs QC'd. or O11/L11. Makes sense.

In the grand total column, you note 25 TOTAL failures in O36, and 94 QC'd jobs total in L36, so I would suggest that following the reasoning used in the individual rows above, the total QC Failure % is 27%. Your sumproduct formula currently in cell P36 is creating a higher percentage than that.

I don't know which is correct, the formula you're using or 25/94 = 27%. But 27% matches the logic you've used in the rest of the sheet, so that my 2 cents.

[berk21]

so I really over thought that one...basing it tech by tech really does no justice I guess.

Thanks again....that works

is there a site or book that could explain functions in excel to familiar myself with this. Please don't say Excel for dummies

[JBeaucaire]

I press F1 in Excel every day and type in questions to read up Excel's built-in tutorials on function. That also brings up MS own web support pages, as well.

That coupled with searching the big Excel web sites brings up answers 80% of the time, and great reading w/examples. The other 20% of the time asking a question ON those sites gets very rapid response.

ExcelForum.com
MrExcel.com
CodeCage.com
Cpearson.com
ozgrid...

Lots of resources.

[berk21]

Sweet...

I really don't think I have anything else to ask and thank all especially JB for helping me on all the issues I had.

Until we meet again!