|
Averageif help
I have a fomula that is trying to average the totals of individual sections
of averages. I have one section where there is not an average to add to the formula. I would like to write a formula that would average all of the totals even if there is a null value so I don't have to rewrite the formula if when the section gains a value. Currently the null value is represented by #DIV/0!. Here is what I have so far; =AVERAGE(I10,I16,I22,I28,I34,I40,I46,I52,I58) This is what I have tried -- =AVERAGEIF(I10,I16,I22,I28,I34,I40,I46,I52,I58,"< #DIV/0!") Please help Drock-13 |
Averageif help
Try one of these array formulas** :
If you will *never* insert new rows above the range: =AVERAGE(IF((MOD(ROW(I10:I58),6)=4)*(ISNUMBER(I10: I58)),I10:I58)) If you might insert new rows above the range: =AVERAGE(IF((MOD(ROW(I10:I58)-ROW(I10),6)=0)*(ISNUMBER(I10:I58)),I10:I58)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff "smeldawg" wrote: I have a fomula that is trying to average the totals of individual sections of averages. I have one section where there is not an average to add to the formula. I would like to write a formula that would average all of the totals even if there is a null value so I don't have to rewrite the formula if when the section gains a value. Currently the null value is represented by #DIV/0!. Here is what I have so far; =AVERAGE(I10,I16,I22,I28,I34,I40,I46,I52,I58) This is what I have tried -- =AVERAGEIF(I10,I16,I22,I28,I34,I40,I46,I52,I58,"< #DIV/0!") Please help Drock-13 |
Averageif help
Hi Biff,
I see you've gone back to using your nick-name! Pete On Apr 15, 8:24*pm, Biff wrote: Try one of these array formulas** : If you will *never* insert new rows above the range: =AVERAGE(IF((MOD(ROW(I10:I58),6)=4)*(ISNUMBER(I10: I58)),I10:I58)) If you might insert new rows above the range: =AVERAGE(IF((MOD(ROW(I10:I58)-ROW(I10),6)=0)*(ISNUMBER(I10:I58)),I10:I58)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff "smeldawg" wrote: I have a fomula that is trying to average the totals of individual sections of averages. I have one section where there is not an average to add to the formula. I would like to write a formula that would average all of the totals even if there is a null value so I don't have to rewrite the formula if when the section gains a value. Currently the null value is represented by #DIV/0!. Here is what I have so far; =AVERAGE(I10,I16,I22,I28,I34,I40,I46,I52,I58) This is what I have tried -- =AVERAGEIF(I10,I16,I22,I28,I34,I40,I46,I52,I58,"< #DIV/0!") Please help Drock-13- Hide quoted text - - Show quoted text - |
Averageif help
For some reason I can't get access using OE. Keep getting "Unable to connect
to the server" errors so I'm currently accessing through the web interface. That's my sign-in for the web interface. I don't know how to change it! <g -- Biff "Pete_UK" wrote: Hi Biff, I see you've gone back to using your nick-name! Pete On Apr 15, 8:24 pm, Biff wrote: Try one of these array formulas** : If you will *never* insert new rows above the range: =AVERAGE(IF((MOD(ROW(I10:I58),6)=4)*(ISNUMBER(I10: I58)),I10:I58)) If you might insert new rows above the range: =AVERAGE(IF((MOD(ROW(I10:I58)-ROW(I10),6)=0)*(ISNUMBER(I10:I58)),I10:I58)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff "smeldawg" wrote: I have a fomula that is trying to average the totals of individual sections of averages. I have one section where there is not an average to add to the formula. I would like to write a formula that would average all of the totals even if there is a null value so I don't have to rewrite the formula if when the section gains a value. Currently the null value is represented by #DIV/0!. Here is what I have so far; =AVERAGE(I10,I16,I22,I28,I34,I40,I46,I52,I58) This is what I have tried -- =AVERAGEIF(I10,I16,I22,I28,I34,I40,I46,I52,I58,"< #DIV/0!") Please help Drock-13- Hide quoted text - - Show quoted text - |
Averageif help
The first one did the trick. Thank you for the help.
-- Drock-13 "Pete_UK" wrote: Hi Biff, I see you've gone back to using your nick-name! Pete On Apr 15, 8:24 pm, Biff wrote: Try one of these array formulas** : If you will *never* insert new rows above the range: =AVERAGE(IF((MOD(ROW(I10:I58),6)=4)*(ISNUMBER(I10: I58)),I10:I58)) If you might insert new rows above the range: =AVERAGE(IF((MOD(ROW(I10:I58)-ROW(I10),6)=0)*(ISNUMBER(I10:I58)),I10:I58)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff "smeldawg" wrote: I have a fomula that is trying to average the totals of individual sections of averages. I have one section where there is not an average to add to the formula. I would like to write a formula that would average all of the totals even if there is a null value so I don't have to rewrite the formula if when the section gains a value. Currently the null value is represented by #DIV/0!. Here is what I have so far; =AVERAGE(I10,I16,I22,I28,I34,I40,I46,I52,I58) This is what I have tried -- =AVERAGEIF(I10,I16,I22,I28,I34,I40,I46,I52,I58,"< #DIV/0!") Please help Drock-13- Hide quoted text - - Show quoted text - |
Averageif help
I think I found where to change the display name. Let's see if it shows up
differently this time. -- Biff Microsoft Excel MVP "Biff" wrote: For some reason I can't get access using OE. Keep getting "Unable to connect to the server" errors so I'm currently accessing through the web interface. That's my sign-in for the web interface. I don't know how to change it! <g -- Biff "Pete_UK" wrote: Hi Biff, I see you've gone back to using your nick-name! Pete On Apr 15, 8:24 pm, Biff wrote: Try one of these array formulas** : If you will *never* insert new rows above the range: =AVERAGE(IF((MOD(ROW(I10:I58),6)=4)*(ISNUMBER(I10: I58)),I10:I58)) If you might insert new rows above the range: =AVERAGE(IF((MOD(ROW(I10:I58)-ROW(I10),6)=0)*(ISNUMBER(I10:I58)),I10:I58)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff "smeldawg" wrote: I have a fomula that is trying to average the totals of individual sections of averages. I have one section where there is not an average to add to the formula. I would like to write a formula that would average all of the totals even if there is a null value so I don't have to rewrite the formula if when the section gains a value. Currently the null value is represented by #DIV/0!. Here is what I have so far; =AVERAGE(I10,I16,I22,I28,I34,I40,I46,I52,I58) This is what I have tried -- =AVERAGEIF(I10,I16,I22,I28,I34,I40,I46,I52,I58,"< #DIV/0!") Please help Drock-13- Hide quoted text - - Show quoted text - |
Averageif help
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "smeldawg" wrote: The first one did the trick. Thank you for the help. -- Drock-13 "Pete_UK" wrote: Hi Biff, I see you've gone back to using your nick-name! Pete On Apr 15, 8:24 pm, Biff wrote: Try one of these array formulas** : If you will *never* insert new rows above the range: =AVERAGE(IF((MOD(ROW(I10:I58),6)=4)*(ISNUMBER(I10: I58)),I10:I58)) If you might insert new rows above the range: =AVERAGE(IF((MOD(ROW(I10:I58)-ROW(I10),6)=0)*(ISNUMBER(I10:I58)),I10:I58)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff "smeldawg" wrote: I have a fomula that is trying to average the totals of individual sections of averages. I have one section where there is not an average to add to the formula. I would like to write a formula that would average all of the totals even if there is a null value so I don't have to rewrite the formula if when the section gains a value. Currently the null value is represented by #DIV/0!. Here is what I have so far; =AVERAGE(I10,I16,I22,I28,I34,I40,I46,I52,I58) This is what I have tried -- =AVERAGEIF(I10,I16,I22,I28,I34,I40,I46,I52,I58,"< #DIV/0!") Please help Drock-13- Hide quoted text - - Show quoted text - |
Averageif help
mark
-- Biff Microsoft Excel MVP "T. Valko" wrote in message ... You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "smeldawg" wrote: The first one did the trick. Thank you for the help. -- Drock-13 "Pete_UK" wrote: Hi Biff, I see you've gone back to using your nick-name! Pete On Apr 15, 8:24 pm, Biff wrote: Try one of these array formulas** : If you will *never* insert new rows above the range: =AVERAGE(IF((MOD(ROW(I10:I58),6)=4)*(ISNUMBER(I10: I58)),I10:I58)) If you might insert new rows above the range: =AVERAGE(IF((MOD(ROW(I10:I58)-ROW(I10),6)=0)*(ISNUMBER(I10:I58)),I10:I58)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff "smeldawg" wrote: I have a fomula that is trying to average the totals of individual sections of averages. I have one section where there is not an average to add to the formula. I would like to write a formula that would average all of the totals even if there is a null value so I don't have to rewrite the formula if when the section gains a value. Currently the null value is represented by #DIV/0!. Here is what I have so far; =AVERAGE(I10,I16,I22,I28,I34,I40,I46,I52,I58) This is what I have tried -- =AVERAGEIF(I10,I16,I22,I28,I34,I40,I46,I52,I58,"< #DIV/0!") Please help Drock-13- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 08:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com