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.
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.
Last edited by berk21; 01-27-2009 at 10:52 PM. Reason: Solved
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.
Entia non sunt multiplicanda sine necessitate
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.
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
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.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
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
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.
Last edited by JBeaucaire; 01-27-2009 at 11:02 PM.
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!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks