If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
#DIV/0!
Sorry,
I should have said if all cells in the range are blank, and therefore sum to zero, then you get the #DIV/0 error. If any of the cells contain a value, then COUNT will be at least 1 and the error won't occur. -- Regards Roger Govier "joeu2004" wrote in message ups.com... On Mar 8, 4:21 pm, "Roger Govier" wrote: =AVERAGE(ESP1!F24+ESP2!F24+LV!F24+LA!F24+RAT!F24+S F1!F24+ SF2!F24+SF3!F24+TA*OS!F24) then if they sum to 0, you will get the #DIV0 error message Are you sure? Works fine for me when all cells are on the same sheet. |
#12
|
|||
|
|||
#DIV/0!
On Mar 8, 11:50 pm, "Roger Govier"
wrote: "joeu2004" wrote: On Mar 8, 4:21 pm, "Roger Govier" wrote: =AVERAGE(ESP1!F24+ESP2!F24+LV!F24+LA!F24+RAT!F24+S F1!F24+ SF2!F24+SF3!F24+TA*OS!F24) then if they sum to 0, you will get the #DIV0 error message Are you sure? Works fine for me when all cells are on the same sheet. Sorry, I should have said if all cells in the range are blank, and therefore sum to zero, then you get the #DIV/0 error. Not when I try it. AVERAGE(A1+A2+A3) works just fine when all 3 cells are blank. I suspect you are making the same mistake that I made and thinking of AVERAGE(A1,A2,A3), which does indeed fail with #DIV/0! if all 3 cells are blank. |
#13
|
|||
|
|||
#DIV/0!
Ah, yes I can see that AVERAGE(A1+A2+A3) would work, as the + between
each item is coercing a zero value for the blank cells. I always use =AVERAGE(A1:A100) as I wouldn't want to type all the plus's and with some formulae would hit the number of characters limit. My way would be =IF(COUNT(A1:A100)0,AVERAGE(A1:A100),"") -- Regards Roger Govier "joeu2004" wrote in message oups.com... On Mar 8, 11:50 pm, "Roger Govier" wrote: "joeu2004" wrote: On Mar 8, 4:21 pm, "Roger Govier" wrote: =AVERAGE(ESP1!F24+ESP2!F24+LV!F24+LA!F24+RAT!F24+S F1!F24+ SF2!F24+SF3!F24+TA*OS!F24) then if they sum to 0, you will get the #DIV0 error message Are you sure? Works fine for me when all cells are on the same sheet. Sorry, I should have said if all cells in the range are blank, and therefore sum to zero, then you get the #DIV/0 error. Not when I try it. AVERAGE(A1+A2+A3) works just fine when all 3 cells are blank. I suspect you are making the same mistake that I made and thinking of AVERAGE(A1,A2,A3), which does indeed fail with #DIV/0! if all 3 cells are blank. |
#14
|
|||
|
|||
#DIV/0!
Wow what a response, thanks. Let me take the first reference in the formula
and explain, ESP1!F24 is a cell that also has a formula in its own cell that asks for an average of 4 other cells C24, D24, and E24 on the same worksheet, one of those cells has data inserted and the others dont but I still get the #DIV/0! and I wanted this formula to reflect the existing data from C even though the other cells are still without data, thats the reason for using the average formula. I had put the /9 because it wasnt giving me an average but a sum total even though it said average. -- Jim Salyer Area Supervisor Home 505-474-4863 Mobile 505-670-4138 Fax 505-474-4540 "joeu2004" wrote in message ups.com... Errata.... On Mar 8, 6:12 pm, "joeu2004" wrote: First of all, you probably want: AVERAGE(ESP1!F24,ESP2!F24,LV!F24,LA!F24,RAT!F24,SF 1!F24,SF2!F24,SF3! F24,TAO*S!F24) [....] AVERAGE() probably returned #DIV/0! because AVERAGE() thinks that all the cells are blank or contain non-numeric values (e.g. text). Oops, my bad! I was thinking of __my__ form of AVERAGE(). AVERAGE(A1+...+A9) works just fine if all cells in the range are blank (or zero). There is no problem with AVERAGE() if all the arguments sum to zero. If any cell is non-numeric, the expression will returns #VALUE!, and so does AVERAGE(). |
#15
|
|||
|
|||
#DIV/0!
Substitute your cell references and you will get an average of those that
contain data. Be sure to press Ctrl/Shift/Enter since this is an array formula. =AVERAGE(IF(J9:J200, L9:L20,"")) -- Best wishes, Jim "Jim S" wrote: Wow what a response, thanks. Let me take the first reference in the formula and explain, ESP1!F24 is a cell that also has a formula in its own cell that asks for an average of 4 other cells C24, D24, and E24 on the same worksheet, one of those cells has data inserted and the others dont but I still get the #DIV/0! and I wanted this formula to reflect the existing data from C even though the other cells are still without data, thats the reason for using the average formula. I had put the /9 because it wasnt giving me an average but a sum total even though it said average. -- Jim Salyer Area Supervisor Home 505-474-4863 Mobile 505-670-4138 Fax 505-474-4540 "joeu2004" wrote in message ups.com... Errata.... On Mar 8, 6:12 pm, "joeu2004" wrote: First of all, you probably want: AVERAGE(ESP1!F24,ESP2!F24,LV!F24,LA!F24,RAT!F24,SF 1!F24,SF2!F24,SF3! F24,TAOÂ*S!F24) [....] AVERAGE() probably returned #DIV/0! because AVERAGE() thinks that all the cells are blank or contain non-numeric values (e.g. text). Oops, my bad! I was thinking of __my__ form of AVERAGE(). AVERAGE(A1+...+A9) works just fine if all cells in the range are blank (or zero). There is no problem with AVERAGE() if all the arguments sum to zero. If any cell is non-numeric, the expression will returns #VALUE!, and so does AVERAGE(). |
|
Thread Tools | |
Display Modes | |
|
|