average with N/A
I have a spreadsheet with 9 worksheets (named by employee last name).
On the 9th worksheet I want to get the average of cells B4 from each of the other sheets. Some of these cells may include an N/A. Can you give me a formula that will overlook the N/A cells? Thank you! |
average with N/A
Hi,
=IFERROR(AVERAGE(Sheet2:Sheet3!A2),AVERAGE(Sheet2: Sheet3!A2)) I supposed that you have sheet 2 and 3 and the cell to average is A2 If this helped please click yes. Thank you "dernspiker" wrote: I have a spreadsheet with 9 worksheets (named by employee last name). On the 9th worksheet I want to get the average of cells B4 from each of the other sheets. Some of these cells may include an N/A. Can you give me a formula that will overlook the N/A cells? Thank you! |
average with N/A
This is my formula then:
=IFERROR(AVERAGE(Adams:Webb!B4),AVERAGE(Adams:Webb !B4)) But now it returns a #NAME? error "Eduardo" wrote: Hi, =IFERROR(AVERAGE(Sheet2:Sheet3!A2),AVERAGE(Sheet2: Sheet3!A2)) I supposed that you have sheet 2 and 3 and the cell to average is A2 If this helped please click yes. Thank you "dernspiker" wrote: I have a spreadsheet with 9 worksheets (named by employee last name). On the 9th worksheet I want to get the average of cells B4 from each of the other sheets. Some of these cells may include an N/A. Can you give me a formula that will overlook the N/A cells? Thank you! |
average with N/A
I would use 9 cells (on a separate (hidden???) worksheet:
='Smith'!B4 ='Jones'!b4 .... ='Zydecker'!b4 Then use a 10th cell to get the average. =average(if(isnumber(a1:a9),a1:a9)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) or this formula (normally entered): =sumif(a1:a9,"<1E37")/count(a1:a9) 1E37 is a very large number (1 followed by 37 zeros). Or maybe: =if(count(a1:a9)=0,"No numbers",sumif(a1:a9,"<1E37")/count(a1:a9)) dernspiker wrote: I have a spreadsheet with 9 worksheets (named by employee last name). On the 9th worksheet I want to get the average of cells B4 from each of the other sheets. Some of these cells may include an N/A. Can you give me a formula that will overlook the N/A cells? Thank you! -- Dave Peterson |
All times are GMT +1. The time now is 01:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com