average formula
I have a project that collects data from multiple worksheets. On each
sheet one cell keeps a count of times a person is in the office. What I want to do is have one formula that will average each of these counts only if the count is greater than zero. I can get the average of all of them together but the total comes out inaccurate because of the number of sheets that haven't had information put into them yet. I've hit a pretty big road block and my deadline is looming...Any advice? |
average formula
=SUMIF(range,"0")/COUNTIF(range,"0")
Since I do not know if you are using 3d references, or absolute references, I am making the assumption that you can sum your range. And technically, you don't need the sumif, you could just go: =SUM(range)/COUNTIF(range,"0") -- John C " wrote: I have a project that collects data from multiple worksheets. On each sheet one cell keeps a count of times a person is in the office. What I want to do is have one formula that will average each of these counts only if the count is greater than zero. I can get the average of all of them together but the total comes out inaccurate because of the number of sheets that haven't had information put into them yet. I've hit a pretty big road block and my deadline is looming...Any advice? |
average formula
wrote...
I have a project that collects data from multiple worksheets. *On each sheet one cell keeps a count of times a person is in the office. *What I want to do is have one formula that will average each of these counts only if the count is greater than zero. *I can get the average of all of them together but the total comes out inaccurate because of the number of sheets that haven't had information put into them yet. I've hit a pretty big road block and my deadline is looming...Any advice? Are these cells always at the same address in each of these worksheets, e.g., always in cell C5? If so, and if you've tried using something like =AVERAGE('first worksheet:last worksheet'!C5) but it's giving the wrong result, does that mean you have zeros in the C5 cells when the person in question hasn't been in the office at all? If so, have you considered clearing the zeros so your formula would ignore the then blank cells? If you can't clear the zeros, then consider using another cell in each worksheet, say, CC5 containing the formula =IF(COUNTIF(C5,"0"),C5) which would evaluate to the value in C5 if it's a positive number and to the boolean value FALSE otherwise. Excel's AVERAGE function ignores FALSE values, so the formula =AVERAGE('first worksheet:last worksheet'!CC5) would then return the correct average. If you can't modify the source worksheets at all, then if all the values from the other worksheets would be zero or positive numeric values, try the following formula. =SUM('first worksheet:last worksheet'!CC5) /INDEX(FREQUENCY('first worksheet:last worksheet'!CC5,{0}),2) If there could be anything in the C5 cells in the other workbooks and you only want to average the positive numbers, you're going to have to pull each of those worksheets' C5 values into your summary worksheet INDIVIDUALLY, then average those values. For example, in your summary worksheet, B7: =IF(COUNTIF('first worksheet'!C5,"0"),'first worksheet'!C5) B8: =IF(COUNTIF('second worksheet'!C5,"0"),'second worksheet'!C5) B9: =IF(COUNTIF('third worksheet'!C5,"0"),'third worksheet'!C5) : B##: =IF(COUNTIF('last worksheet'!C5,"0"),'last worksheet'!C5) Then use =AVERAGE(B7:B##) to average only the positive numbers in these C5 cells. Finally, you could enter the worksheet names in a list in your summary worksheet, name that list wslst, and use a formula like =SUMIF(INDIRECT("'"&wslst&"'!C5"),"0")/COUNTIF(INDIRECT("'"&wslst&"'! C5"),"0") |
All times are GMT +1. The time now is 10:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com