Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
eliminating the 0 in DIV/0
I am a very basic XL user, but I work with spread sheets using the simple AVG
formula. Is there any way to by pass the "0" in DIV/0? in other words, I am using 4 columns of numbers that were each averaged. There is another column with the average of the 4 separate columns, but I get the "DIV/0" unless all 4 of the other colums have a base larger than "0". In other words do I have to wait until all 4 coulms are completed before I can determine the on going average, or is there a formula or way to get the averages of the columns that do not have a zero base and not have to wait until all 4 colums have a base larger than zero? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
eliminating the 0 in DIV/0
what is the formula you are using right now?
teacher1 wrote: I am a very basic XL user, but I work with spread sheets using the simple AVG formula. Is there any way to by pass the "0" in DIV/0? in other words, I am using 4 columns of numbers that were each averaged. There is another column with the average of the 4 separate columns, but I get the "DIV/0" unless all 4 of the other colums have a base larger than "0". In other words do I have to wait until all 4 coulms are completed before I can determine the on going average, or is there a formula or way to get the averages of the columns that do not have a zero base and not have to wait until all 4 colums have a base larger than zero? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
eliminating the 0 in DIV/0
Maybe try something like
=IF(COUNT(A1:A10)=0,"",AVERAGE(A1:A10)) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "teacher1" wrote in message ... I am a very basic XL user, but I work with spread sheets using the simple AVG formula. Is there any way to by pass the "0" in DIV/0? in other words, I am using 4 columns of numbers that were each averaged. There is another column with the average of the 4 separate columns, but I get the "DIV/0" unless all 4 of the other colums have a base larger than "0". In other words do I have to wait until all 4 coulms are completed before I can determine the on going average, or is there a formula or way to get the averages of the columns that do not have a zero base and not have to wait until all 4 colums have a base larger than zero? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
eliminating the 0 in DIV/0
I think you're looking to average across 4 columns,
BUT, if some of the columns are empty, you want to average *whatever* columns you have values in. If I interpreted that correctly, try this *array* formula in say E1, with your data in A1 to D1: =AVERAGE(A1:INDEX(A1:D1,SMALL(COLUMN(A1:D1)*(A1:D1 <""),4))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. You *must also* use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "teacher1" wrote in message ... I am a very basic XL user, but I work with spread sheets using the simple AVG formula. Is there any way to by pass the "0" in DIV/0? in other words, I am using 4 columns of numbers that were each averaged. There is another column with the average of the 4 separate columns, but I get the "DIV/0" unless all 4 of the other colums have a base larger than "0". In other words do I have to wait until all 4 coulms are completed before I can determine the on going average, or is there a formula or way to get the averages of the columns that do not have a zero base and not have to wait until all 4 colums have a base larger than zero? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
eliminating the 0 in DIV/0
avg A B C D
1 91 ****** 91 91 91 91 2 89 89 89 89 89 3 78 78 78 78 78 4 65 65 65 65 65 5 77 77 77 77 77 6 68 68 68 68 68 7 86 86 86 86 86 8 89 89 89 89 89 9 95 95 95 95 95 10 #DIV/0! #DIV/0! 11 #DIV/0! #DIV/0! 12 #DIV/0! #DIV/0! AVG #DIV/0! (THIS IS A MONTHLY AVERAGE) ****** = SUM OF COLUMN A WHICH IS THE AVERAGE OF COLUMNS B TO D Can I get the current monthly average without having to post all of the months results, that is, the average without the months that have "DIV/0" which would be 82? Many thanks for your help!! "curlydave" wrote: what is the formula you are using right now? teacher1 wrote: I am a very basic XL user, but I work with spread sheets using the simple AVG formula. Is there any way to by pass the "0" in DIV/0? in other words, I am using 4 columns of numbers that were each averaged. There is another column with the average of the 4 separate columns, but I get the "DIV/0" unless all 4 of the other colums have a base larger than "0". In other words do I have to wait until all 4 coulms are completed before I can determine the on going average, or is there a formula or way to get the averages of the columns that do not have a zero base and not have to wait until all 4 colums have a base larger than zero? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
eliminating the 0 in DIV/0
Hi Ragdyer! Thank you for taking the time to provide me with an answer! I
put the formula you gave me in and it did indeed give me the average, but what I want to is get the average of a column that has a DIV/0 in the column. For example: I am trying to get the running average as each months figures are entered in another column. All of the below figures represent the total figures from the other columns, but the 5th cell down represents unknown results. 100 89 90 79 #DIV/0! #DIV/0! What I want to do is keep the formulas in the cells, and even though there is a DIV/0 in one of the cells it will give me average (here it would be 89.5 or the average of the first four numbers and not counting that cell with the DIV/0 or unknown resluts. And, later, when the data is entered with that months figures (assuming that figure is 65) it would give me the average of all (5) cells ( which would then be 85 as the average) Many Thanks again for your help!! "Ragdyer" wrote: I think you're looking to average across 4 columns, BUT, if some of the columns are empty, you want to average *whatever* columns you have values in. If I interpreted that correctly, try this *array* formula in say E1, with your data in A1 to D1: =AVERAGE(A1:INDEX(A1:D1,SMALL(COLUMN(A1:D1)*(A1:D1 <""),4))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. You *must also* use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "teacher1" wrote in message ... I am a very basic XL user, but I work with spread sheets using the simple AVG formula. Is there any way to by pass the "0" in DIV/0? in other words, I am using 4 columns of numbers that were each averaged. There is another column with the average of the 4 separate columns, but I get the "DIV/0" unless all 4 of the other colums have a base larger than "0". In other words do I have to wait until all 4 coulms are completed before I can determine the on going average, or is there a formula or way to get the averages of the columns that do not have a zero base and not have to wait until all 4 colums have a base larger than zero? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
eliminating the 0 in DIV/0
Try something like this:
=SUMIF(K2:K120,"<#DIV/0!")/COUNT(K2:K120) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "teacher1" wrote in message ... Hi Ragdyer! Thank you for taking the time to provide me with an answer! I put the formula you gave me in and it did indeed give me the average, but what I want to is get the average of a column that has a DIV/0 in the column. For example: I am trying to get the running average as each months figures are entered in another column. All of the below figures represent the total figures from the other columns, but the 5th cell down represents unknown results. 100 89 90 79 #DIV/0! #DIV/0! What I want to do is keep the formulas in the cells, and even though there is a DIV/0 in one of the cells it will give me average (here it would be 89.5 or the average of the first four numbers and not counting that cell with the DIV/0 or unknown resluts. And, later, when the data is entered with that months figures (assuming that figure is 65) it would give me the average of all (5) cells ( which would then be 85 as the average) Many Thanks again for your help!! "Ragdyer" wrote: I think you're looking to average across 4 columns, BUT, if some of the columns are empty, you want to average *whatever* columns you have values in. If I interpreted that correctly, try this *array* formula in say E1, with your data in A1 to D1: =AVERAGE(A1:INDEX(A1:D1,SMALL(COLUMN(A1:D1)*(A1:D1 <""),4))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. You *must also* use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "teacher1" wrote in message ... I am a very basic XL user, but I work with spread sheets using the simple AVG formula. Is there any way to by pass the "0" in DIV/0? in other words, I am using 4 columns of numbers that were each averaged. There is another column with the average of the 4 separate columns, but I get the "DIV/0" unless all 4 of the other colums have a base larger than "0". In other words do I have to wait until all 4 coulms are completed before I can determine the on going average, or is there a formula or way to get the averages of the columns that do not have a zero base and not have to wait until all 4 colums have a base larger than zero? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
eliminating the 0 in DIV/0
Ragdyer - Thanks again, but I am not quiet sure what you mean by "click to
show or h ide the original message or reply test" Is this a format, options or right click function. Sorry to ask for what appears to be simple answer, but I am slighly on the XL gray matter (dumb is the politically incorrect word) challenged. Thanks again! "teacher1" wrote: Hi Ragdyer! Thank you for taking the time to provide me with an answer! I put the formula you gave me in and it did indeed give me the average, but what I want to is get the average of a column that has a DIV/0 in the column. For example: I am trying to get the running average as each months figures are entered in another column. All of the below figures represent the total figures from the other columns, but the 5th cell down represents unknown results. 100 89 90 79 #DIV/0! #DIV/0! What I want to do is keep the formulas in the cells, and even though there is a DIV/0 in one of the cells it will give me average (here it would be 89.5 or the average of the first four numbers and not counting that cell with the DIV/0 or unknown resluts. And, later, when the data is entered with that months figures (assuming that figure is 65) it would give me the average of all (5) cells ( which would then be 85 as the average) Many Thanks again for your help!! "Ragdyer" wrote: I think you're looking to average across 4 columns, BUT, if some of the columns are empty, you want to average *whatever* columns you have values in. If I interpreted that correctly, try this *array* formula in say E1, with your data in A1 to D1: =AVERAGE(A1:INDEX(A1:D1,SMALL(COLUMN(A1:D1)*(A1:D1 <""),4))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. You *must also* use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "teacher1" wrote in message ... I am a very basic XL user, but I work with spread sheets using the simple AVG formula. Is there any way to by pass the "0" in DIV/0? in other words, I am using 4 columns of numbers that were each averaged. There is another column with the average of the 4 separate columns, but I get the "DIV/0" unless all 4 of the other colums have a base larger than "0". In other words do I have to wait until all 4 coulms are completed before I can determine the on going average, or is there a formula or way to get the averages of the columns that do not have a zero base and not have to wait until all 4 colums have a base larger than zero? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
eliminating the 0 in DIV/0
Ragdyer -disregard last message sorry but i am still laughing at my self
"teacher1" wrote: Hi Ragdyer! Thank you for taking the time to provide me with an answer! I put the formula you gave me in and it did indeed give me the average, but what I want to is get the average of a column that has a DIV/0 in the column. For example: I am trying to get the running average as each months figures are entered in another column. All of the below figures represent the total figures from the other columns, but the 5th cell down represents unknown results. 100 89 90 79 #DIV/0! #DIV/0! What I want to do is keep the formulas in the cells, and even though there is a DIV/0 in one of the cells it will give me average (here it would be 89.5 or the average of the first four numbers and not counting that cell with the DIV/0 or unknown resluts. And, later, when the data is entered with that months figures (assuming that figure is 65) it would give me the average of all (5) cells ( which would then be 85 as the average) Many Thanks again for your help!! "Ragdyer" wrote: I think you're looking to average across 4 columns, BUT, if some of the columns are empty, you want to average *whatever* columns you have values in. If I interpreted that correctly, try this *array* formula in say E1, with your data in A1 to D1: =AVERAGE(A1:INDEX(A1:D1,SMALL(COLUMN(A1:D1)*(A1:D1 <""),4))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. You *must also* use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "teacher1" wrote in message ... I am a very basic XL user, but I work with spread sheets using the simple AVG formula. Is there any way to by pass the "0" in DIV/0? in other words, I am using 4 columns of numbers that were each averaged. There is another column with the average of the 4 separate columns, but I get the "DIV/0" unless all 4 of the other colums have a base larger than "0". In other words do I have to wait until all 4 coulms are completed before I can determine the on going average, or is there a formula or way to get the averages of the columns that do not have a zero base and not have to wait until all 4 colums have a base larger than zero? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
eliminating the 0 in DIV/0
When you calm down and are able to stop laughing, can we know if it worked
for you ... or not? -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "teacher1" wrote in message ... Ragdyer -disregard last message sorry but i am still laughing at my self "teacher1" wrote: Hi Ragdyer! Thank you for taking the time to provide me with an answer! I put the formula you gave me in and it did indeed give me the average, but what I want to is get the average of a column that has a DIV/0 in the column. For example: I am trying to get the running average as each month's figures are entered in another column. All of the below figures represent the total figures from the other columns, but the 5th cell down represents unknown results. 100 89 90 79 #DIV/0! #DIV/0! What I want to do is keep the formulas in the cells, and even though there is a DIV/0 in one of the cells it will give me average (here it would be 89.5 or the average of the first four numbers and not counting that cell with the DIV/0 or unknown resluts. And, later, when the data is entered with that month's figures (assuming that figure is 65) it would give me the average of all (5) cells ( which would then be 85 as the average) Many Thanks again for your help!! "Ragdyer" wrote: I think you're looking to average across 4 columns, BUT, if some of the columns are empty, you want to average *whatever* columns you have values in. If I interpreted that correctly, try this *array* formula in say E1, with your data in A1 to D1: =AVERAGE(A1:INDEX(A1:D1,SMALL(COLUMN(A1:D1)*(A1:D1 <""),4))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. You *must also* use CSE when revising the formula. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "teacher1" wrote in message ... I am a very basic XL user, but I work with spread sheets using the simple AVG formula. Is there any way to by pass the "0" in DIV/0? in other words, I am using 4 columns of numbers that were each averaged. There is another column with the average of the 4 separate columns, but I get the "DIV/0" unless all 4 of the other colums have a base larger than "0". In other words do I have to wait until all 4 coulms are completed before I can determine the on going average, or is there a formula or way to get the averages of the columns that do not have a zero base and not have to wait until all 4 colums have a base larger than zero? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
eliminating the 0 in DIV/0
Unfortunately, it did not work. I only got a "VALUE" response. I think it
might be best if I start over with my question. Look at the following example of a worksheet: A C D E F G H I 1 YRY AVG GRADES GRD AVG 2 1-6WK 73.0 1-6WK 77 65 77 73 3 2-6WK 75.3 2-6WK 77 73 76 75.3 4 3-6WK 72.7 3-6WK 68 71 79 72.7 5 4-6WK 92.7 4-6WK 99 92 87 92.7 6 5-6WK DIV/0 5-6WK (BLANK CELLS) DIV/0 7 6-6WK DIV/0 6-6WK (BLANK CELLS) DIV/0 8 9Yrly AvG #DIV/0! 10 The average should be :78.4 through date in cell C 9 but will only get "DIV/0" until data is entered in at least cell E 6 and /or E 7 to generate the averages in I 6 and I 7 While this data will not be available until later in the year, I nonetheless, need to keep a current average of those that have been posted through the current date. Now, considering the above example, is there a formula that will allow me to obtain the running year to date averages in cell 9 without considering the cells in C 6 (DIV/0) and/or C7: that is, only show the average for those cells C2 -C5 and as soon as the data is entered in at least cells F6 and F7, then it will automatically show these results when they are entered? Again, I appreciate the time you and the group has taken to help me! "RagDyeR" wrote: When you calm down and are able to stop laughing, can we know if it worked for you ... or not? -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "teacher1" wrote in message ... Ragdyer -disregard last message sorry but i am still laughing at my self "teacher1" wrote: Hi Ragdyer! Thank you for taking the time to provide me with an answer! I put the formula you gave me in and it did indeed give me the average, but what I want to is get the average of a column that has a DIV/0 in the column. For example: I am trying to get the running average as each month's figures are entered in another column. All of the below figures represent the total figures from the other columns, but the 5th cell down represents unknown results. 100 89 90 79 #DIV/0! #DIV/0! What I want to do is keep the formulas in the cells, and even though there is a DIV/0 in one of the cells it will give me average (here it would be 89.5 or the average of the first four numbers and not counting that cell with the DIV/0 or unknown resluts. And, later, when the data is entered with that month's figures (assuming that figure is 65) it would give me the average of all (5) cells ( which would then be 85 as the average) Many Thanks again for your help!! "Ragdyer" wrote: I think you're looking to average across 4 columns, BUT, if some of the columns are empty, you want to average *whatever* columns you have values in. If I interpreted that correctly, try this *array* formula in say E1, with your data in A1 to D1: =AVERAGE(A1:INDEX(A1:D1,SMALL(COLUMN(A1:D1)*(A1:D1 <""),4))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. You *must also* use CSE when revising the formula. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "teacher1" wrote in message ... I am a very basic XL user, but I work with spread sheets using the simple AVG formula. Is there any way to by pass the "0" in DIV/0? in other words, I am using 4 columns of numbers that were each averaged. There is another column with the average of the 4 separate columns, but I get the "DIV/0" unless all 4 of the other colums have a base larger than "0". In other words do I have to wait until all 4 coulms are completed before I can determine the on going average, or is there a formula or way to get the averages of the columns that do not have a zero base and not have to wait until all 4 colums have a base larger than zero? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
eliminating the 0 in DIV/0
Sorry, the worksheet did not come out the way I posted it!
"teacher1" wrote: Unfortunately, it did not work. I only got a "VALUE" response. I think it might be best if I start over with my question. Look at the following example of a worksheet: A C D E F G H I 1 YRY AVG GRADES GRD AVG 2 1-6WK 73.0 1-6WK 77 65 77 73 3 2-6WK 75.3 2-6WK 77 73 76 75.3 4 3-6WK 72.7 3-6WK 68 71 79 72.7 5 4-6WK 92.7 4-6WK 99 92 87 92.7 6 5-6WK DIV/0 5-6WK (BLANK CELLS) DIV/0 7 6-6WK DIV/0 6-6WK (BLANK CELLS) DIV/0 8 9Yrly AvG #DIV/0! 10 The average should be :78.4 through date in cell C 9 but will only get "DIV/0" until data is entered in at least cell E 6 and /or E 7 to generate the averages in I 6 and I 7 While this data will not be available until later in the year, I nonetheless, need to keep a current average of those that have been posted through the current date. Now, considering the above example, is there a formula that will allow me to obtain the running year to date averages in cell 9 without considering the cells in C 6 (DIV/0) and/or C7: that is, only show the average for those cells C2 -C5 and as soon as the data is entered in at least cells F6 and F7, then it will automatically show these results when they are entered? Again, I appreciate the time you and the group has taken to help me! "RagDyeR" wrote: When you calm down and are able to stop laughing, can we know if it worked for you ... or not? -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "teacher1" wrote in message ... Ragdyer -disregard last message sorry but i am still laughing at my self "teacher1" wrote: Hi Ragdyer! Thank you for taking the time to provide me with an answer! I put the formula you gave me in and it did indeed give me the average, but what I want to is get the average of a column that has a DIV/0 in the column. For example: I am trying to get the running average as each month's figures are entered in another column. All of the below figures represent the total figures from the other columns, but the 5th cell down represents unknown results. 100 89 90 79 #DIV/0! #DIV/0! What I want to do is keep the formulas in the cells, and even though there is a DIV/0 in one of the cells it will give me average (here it would be 89.5 or the average of the first four numbers and not counting that cell with the DIV/0 or unknown resluts. And, later, when the data is entered with that month's figures (assuming that figure is 65) it would give me the average of all (5) cells ( which would then be 85 as the average) Many Thanks again for your help!! "Ragdyer" wrote: I think you're looking to average across 4 columns, BUT, if some of the columns are empty, you want to average *whatever* columns you have values in. If I interpreted that correctly, try this *array* formula in say E1, with your data in A1 to D1: =AVERAGE(A1:INDEX(A1:D1,SMALL(COLUMN(A1:D1)*(A1:D1 <""),4))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. You *must also* use CSE when revising the formula. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "teacher1" wrote in message ... I am a very basic XL user, but I work with spread sheets using the simple AVG formula. Is there any way to by pass the "0" in DIV/0? in other words, I am using 4 columns of numbers that were each averaged. There is another column with the average of the 4 separate columns, but I get the "DIV/0" unless all 4 of the other colums have a base larger than "0". In other words do I have to wait until all 4 coulms are completed before I can determine the on going average, or is there a formula or way to get the averages of the columns that do not have a zero base and not have to wait until all 4 colums have a base larger than zero? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with dividing and using ISERROR for Div/0! | Excel Worksheet Functions | |||
how to remove #DIV/0! | Excel Worksheet Functions | |||
How do you write an if statement that replaces #DIV/0! with 0 | Excel Discussion (Misc queries) | |||
How to replace div/0 with a zero | Excel Discussion (Misc queries) | |||
How to hide #DIV/0! in cells waiting for input? | Excel Discussion (Misc queries) |