Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate with #div/0! error?
Hey there,
I'd like to get the average of a row filled with percentages. But because the percentages will only be filled in when the month has started I have some div/0! values. How can I calculate the average? And please try to keep it simple :D Hope I explained it clearly, if not let me know, see an example below. thx Nagje example (yes i know this isn't a row ;-) ): Jan 95.2% Feb 100.0% March 93.2% April 95.0% may 96.9% June 97.6% July #div/0! Aug #div/0! Sept #div/0! Okt #div/0! Nov #div/0! Dec #div/0! Average year = ??????? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate with #div/0! error?
Instead of something like:
=C1/D1 use =IF(D1="","",C1/D1) The #div/0 errors will vanish and the average will be over the available values. -- Gary''s Student - gsnu200757 "nagje" wrote: Hey there, I'd like to get the average of a row filled with percentages. But because the percentages will only be filled in when the month has started I have some div/0! values. How can I calculate the average? And please try to keep it simple :D Hope I explained it clearly, if not let me know, see an example below. thx Nagje example (yes i know this isn't a row ;-) ): Jan 95.2% Feb 100.0% March 93.2% April 95.0% may 96.9% June 97.6% July #div/0! Aug #div/0! Sept #div/0! Okt #div/0! Nov #div/0! Dec #div/0! Average year = ??????? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate with #div/0! error?
The customary practice is to test for 0.
Rather than: A1 = x/y (which would generate Div by zero errors) Restructure the formula to: A1= If(y=0, 0, x/y) I'm sure your formula is more complicated than x/y, but the principle remains the same. -- HTH, George "nagje" wrote in message ... Hey there, I'd like to get the average of a row filled with percentages. But because the percentages will only be filled in when the month has started I have some div/0! values. How can I calculate the average? And please try to keep it simple :D Hope I explained it clearly, if not let me know, see an example below. thx Nagje example (yes i know this isn't a row ;-) ): Jan 95.2% Feb 100.0% March 93.2% April 95.0% may 96.9% June 97.6% July #div/0! Aug #div/0! Sept #div/0! Okt #div/0! Nov #div/0! Dec #div/0! Average year = ??????? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate with #div/0! error?
The easiest way is to change the formula tat computes the average to
something like =IF(D100,D10/C1,""). Then your AVERAGE formula will work Alternatively, use the array formula =AVERAGE(IF(ISNUMBER(B1:B12),B1:B12,"")) which must be entered with SHIFT+CTRl+ENTER not just ENTER best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "nagje" wrote in message ... Hey there, I'd like to get the average of a row filled with percentages. But because the percentages will only be filled in when the month has started I have some div/0! values. How can I calculate the average? And please try to keep it simple :D Hope I explained it clearly, if not let me know, see an example below. thx Nagje example (yes i know this isn't a row ;-) ): Jan 95.2% Feb 100.0% March 93.2% April 95.0% may 96.9% June 97.6% July #div/0! Aug #div/0! Sept #div/0! Okt #div/0! Nov #div/0! Dec #div/0! Average year = ??????? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate with #div/0! error?
Thx for the quick reply, but this is not the solution to my problem I'm
afraid (or I simply don't get it ). I try to get the average of a row, in that row there can be several #div/0! errors. Not just one. I tried =IF(C1:D1="","",C1/D1) but that didn't work as well. "Gary''s Student" wrote: Instead of something like: =C1/D1 use =IF(D1="","",C1/D1) The #div/0 errors will vanish and the average will be over the available values. -- Gary''s Student - gsnu200757 "nagje" wrote: Hey there, I'd like to get the average of a row filled with percentages. But because the percentages will only be filled in when the month has started I have some div/0! values. How can I calculate the average? And please try to keep it simple :D Hope I explained it clearly, if not let me know, see an example below. thx Nagje example (yes i know this isn't a row ;-) ): Jan 95.2% Feb 100.0% March 93.2% April 95.0% may 96.9% June 97.6% July #div/0! Aug #div/0! Sept #div/0! Okt #div/0! Nov #div/0! Dec #div/0! Average year = ??????? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate with #div/0! error?
Thx...this helps a lot.
But, why is the answer from this calculation different then when I do a manual count of month results divided by the number of months where results have been entered? "Bernard Liengme" wrote: The easiest way is to change the formula tat computes the average to something like =IF(D100,D10/C1,""). Then your AVERAGE formula will work Alternatively, use the array formula =AVERAGE(IF(ISNUMBER(B1:B12),B1:B12,"")) which must be entered with SHIFT+CTRl+ENTER not just ENTER best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "nagje" wrote in message ... Hey there, I'd like to get the average of a row filled with percentages. But because the percentages will only be filled in when the month has started I have some div/0! values. How can I calculate the average? And please try to keep it simple :D Hope I explained it clearly, if not let me know, see an example below. thx Nagje example (yes i know this isn't a row ;-) ): Jan 95.2% Feb 100.0% March 93.2% April 95.0% may 96.9% June 97.6% July #div/0! Aug #div/0! Sept #div/0! Okt #div/0! Nov #div/0! Dec #div/0! Average year = ??????? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate with #div/0! error?
=IF(ISERROR(A1/B1),"",(A1/B1))
"nagje" wrote in message ... Hey there, I'd like to get the average of a row filled with percentages. But because the percentages will only be filled in when the month has started I have some div/0! values. How can I calculate the average? And please try to keep it simple :D Hope I explained it clearly, if not let me know, see an example below. thx Nagje example (yes i know this isn't a row ;-) ): Jan 95.2% Feb 100.0% March 93.2% April 95.0% may 96.9% June 97.6% July #div/0! Aug #div/0! Sept #div/0! Okt #div/0! Nov #div/0! Dec #div/0! Average year = ??????? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate with #div/0! error?
Most likely it is rounding. For example you may see 14.4% in a cell but the
actual stored values could be 14.35789% Show us the data from which the percentages are calculated. best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "nagje" wrote in message ... Thx...this helps a lot. But, why is the answer from this calculation different then when I do a manual count of month results divided by the number of months where results have been entered? "Bernard Liengme" wrote: The easiest way is to change the formula tat computes the average to something like =IF(D100,D10/C1,""). Then your AVERAGE formula will work Alternatively, use the array formula =AVERAGE(IF(ISNUMBER(B1:B12),B1:B12,"")) which must be entered with SHIFT+CTRl+ENTER not just ENTER best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "nagje" wrote in message ... Hey there, I'd like to get the average of a row filled with percentages. But because the percentages will only be filled in when the month has started I have some div/0! values. How can I calculate the average? And please try to keep it simple :D Hope I explained it clearly, if not let me know, see an example below. thx Nagje example (yes i know this isn't a row ;-) ): Jan 95.2% Feb 100.0% March 93.2% April 95.0% may 96.9% June 97.6% July #div/0! Aug #div/0! Sept #div/0! Okt #div/0! Nov #div/0! Dec #div/0! Average year = ??????? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate with #div/0! error?
The data is obtained from several other sheets (it are calculations based
upon lab experiments with good/not good results). Here is a row of data (all percentages), starting in january and going to december: 100.00 92.86 100.00 88.89 100.00 93.33 91.67 87.50 100.00 100.00 100.00 #div/0! Manual calculation over 11 months gives me: 95.84% The formula you gave me gives me 96.11%. A difference of 0.27%. Not that big but there are also rows where the difference is 1.00%. Hope this is what you asked for. Thanx again for helping. "Bernard Liengme" wrote: Most likely it is rounding. For example you may see 14.4% in a cell but the actual stored values could be 14.35789% Show us the data from which the percentages are calculated. best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "nagje" wrote in message ... Thx...this helps a lot. But, why is the answer from this calculation different then when I do a manual count of month results divided by the number of months where results have been entered? "Bernard Liengme" wrote: The easiest way is to change the formula tat computes the average to something like =IF(D100,D10/C1,""). Then your AVERAGE formula will work Alternatively, use the array formula =AVERAGE(IF(ISNUMBER(B1:B12),B1:B12,"")) which must be entered with SHIFT+CTRl+ENTER not just ENTER best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "nagje" wrote in message ... Hey there, I'd like to get the average of a row filled with percentages. But because the percentages will only be filled in when the month has started I have some div/0! values. How can I calculate the average? And please try to keep it simple :D Hope I explained it clearly, if not let me know, see an example below. thx Nagje example (yes i know this isn't a row ;-) ): Jan 95.2% Feb 100.0% March 93.2% April 95.0% may 96.9% June 97.6% July #div/0! Aug #div/0! Sept #div/0! Okt #div/0! Nov #div/0! Dec #div/0! Average year = ??????? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate with #div/0! error?
Um...not sure what you mean with this. Can you explain it a bit more?
"Gaurav" wrote: =IF(ISERROR(A1/B1),"",(A1/B1)) "nagje" wrote in message ... Hey there, I'd like to get the average of a row filled with percentages. But because the percentages will only be filled in when the month has started I have some div/0! values. How can I calculate the average? And please try to keep it simple :D Hope I explained it clearly, if not let me know, see an example below. thx Nagje example (yes i know this isn't a row ;-) ): Jan 95.2% Feb 100.0% March 93.2% April 95.0% may 96.9% June 97.6% July #div/0! Aug #div/0! Sept #div/0! Okt #div/0! Nov #div/0! Dec #div/0! Average year = ??????? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate with #div/0! error?
To exactly show you how the discrepancy occurs I would need not the
percentage values but the values used to compute the percentages. Take the second figure 92.86. You are seeing only two decimal places. The actual stored values could be 92.85999999999 or 92.863999999999999999999 or any value in between Select all the values and use the decimal increase tool to see what I am taking about Try this: suppose the non error values are in A1:A11. Write a formula =SUM(A1:A11) Do you get 'your' 95.84 or 'my' 96.11 ? If more help need please send me a copy of the file (remove confidential stuff) best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "nagje" wrote in message ... The data is obtained from several other sheets (it are calculations based upon lab experiments with good/not good results). Here is a row of data (all percentages), starting in january and going to december: 100.00 92.86 100.00 88.89 100.00 93.33 91.67 87.50 100.00 100.00 100.00 #div/0! Manual calculation over 11 months gives me: 95.84% The formula you gave me gives me 96.11%. A difference of 0.27%. Not that big but there are also rows where the difference is 1.00%. Hope this is what you asked for. Thanx again for helping. "Bernard Liengme" wrote: Most likely it is rounding. For example you may see 14.4% in a cell but the actual stored values could be 14.35789% Show us the data from which the percentages are calculated. best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "nagje" wrote in message ... Thx...this helps a lot. But, why is the answer from this calculation different then when I do a manual count of month results divided by the number of months where results have been entered? "Bernard Liengme" wrote: The easiest way is to change the formula tat computes the average to something like =IF(D100,D10/C1,""). Then your AVERAGE formula will work Alternatively, use the array formula =AVERAGE(IF(ISNUMBER(B1:B12),B1:B12,"")) which must be entered with SHIFT+CTRl+ENTER not just ENTER best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "nagje" wrote in message ... Hey there, I'd like to get the average of a row filled with percentages. But because the percentages will only be filled in when the month has started I have some div/0! values. How can I calculate the average? And please try to keep it simple :D Hope I explained it clearly, if not let me know, see an example below. thx Nagje example (yes i know this isn't a row ;-) ): Jan 95.2% Feb 100.0% March 93.2% April 95.0% may 96.9% June 97.6% July #div/0! Aug #div/0! Sept #div/0! Okt #div/0! Nov #div/0! Dec #div/0! Average year = ??????? |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate with #div/0! error?
On Nov 21, 3:07 pm, "Bernard Liengme"
wrote: "nagje" wrote: Manual calculation over 11 months gives me: 95.84%[.] The formula you gave me gives me 96.11%. To exactly show you how the discrepancy occurs I would need not the percentage values but the values used to compute the percentages. Take the second figure 92.86. You are seeing only two decimal places. The actual stored values could be 92.85999999999 or 92.863999999999999999999 or any value in between More to the point, what appears to be 92.86 could be as big as 92.8649...9 (enough 9s to fill 15 significant digits). But when I append 49...9 to all of the OPs 11 numbers, the largest average is still only 95.845909 -- not even close to 96.11. On the other hand, I cannot duplicate the OP's claim that your formula results in 96.11. So I think the problem is not simply rounded displayed values v. actual value, but the fact that the OP's numbers no longer match what was posted earlier -- or the OP has a typo in the range that specified for AVERAGE(), or the OP has misinterpreted what you suggested that the OP do. I suggest that the OP post the formulas that the OP is now using after applying your suggestion. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate with #div/0! error?
*is suddenly very ashamed* I found the problem....a typo in the formula that
you gave me made me take the average of more values. Fixing it gave me the correct values. Thx for all the help. If we'd be in a pub now I'd buy you a beer :-) "Bernard Liengme" wrote: To exactly show you how the discrepancy occurs I would need not the percentage values but the values used to compute the percentages. Take the second figure 92.86. You are seeing only two decimal places. The actual stored values could be 92.85999999999 or 92.863999999999999999999 or any value in between Select all the values and use the decimal increase tool to see what I am taking about Try this: suppose the non error values are in A1:A11. Write a formula =SUM(A1:A11) Do you get 'your' 95.84 or 'my' 96.11 ? If more help need please send me a copy of the file (remove confidential stuff) best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "nagje" wrote in message ... The data is obtained from several other sheets (it are calculations based upon lab experiments with good/not good results). Here is a row of data (all percentages), starting in january and going to december: 100.00 92.86 100.00 88.89 100.00 93.33 91.67 87.50 100.00 100.00 100.00 #div/0! Manual calculation over 11 months gives me: 95.84% The formula you gave me gives me 96.11%. A difference of 0.27%. Not that big but there are also rows where the difference is 1.00%. Hope this is what you asked for. Thanx again for helping. "Bernard Liengme" wrote: Most likely it is rounding. For example you may see 14.4% in a cell but the actual stored values could be 14.35789% Show us the data from which the percentages are calculated. best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "nagje" wrote in message ... Thx...this helps a lot. But, why is the answer from this calculation different then when I do a manual count of month results divided by the number of months where results have been entered? "Bernard Liengme" wrote: The easiest way is to change the formula tat computes the average to something like =IF(D100,D10/C1,""). Then your AVERAGE formula will work Alternatively, use the array formula =AVERAGE(IF(ISNUMBER(B1:B12),B1:B12,"")) which must be entered with SHIFT+CTRl+ENTER not just ENTER best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "nagje" wrote in message ... Hey there, I'd like to get the average of a row filled with percentages. But because the percentages will only be filled in when the month has started I have some div/0! values. How can I calculate the average? And please try to keep it simple :D Hope I explained it clearly, if not let me know, see an example below. thx Nagje example (yes i know this isn't a row ;-) ): Jan 95.2% Feb 100.0% March 93.2% April 95.0% may 96.9% June 97.6% July #div/0! Aug #div/0! Sept #div/0! Okt #div/0! Nov #div/0! Dec #div/0! Average year = ??????? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting and error while calculate business hours between 2 dates with holidays. | Excel Worksheet Functions | |||
Calculate and display standard error | Charts and Charting in Excel | |||
Calculate and display individual error bars for individual points | Charts and Charting in Excel | |||
Calculate and display individual error bars for individual points | Charts and Charting in Excel | |||
calculate average in percentage coloumn with #DIV/! (ignore error | Excel Discussion (Misc queries) |