Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
dividing 0 by 0 in excel 2003
Hi, I have a spreadsheet where I am working out the correctness of an
expected result in % but have a problem with dividing 0 by 0. example from my spreadsheet: if E10 is the predicted value and E11 is the actual value achieved the % of correctness is shown in E12. E12 is currently holding the formulae below: =IF((E10E11),E11/E10,E10/E11) The problem arrises if both E10 and E11 are showing a zero value as the result is #DIV/0! I want it to show 0% which it does if only one of them is a zero. Most of the threads I have found on the website suggest checking for the zero in the divisor before dividing but I cant work out how to check for a zero in both E10- & E11 and then incorporate it into the formulae above. probably really simple but been at it so long cant see the wood for the trees. any help gratefully received thanks carrach |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
dividing 0 by 0 in excel 2003
Hi Carrach,
Try this: =IF(AND(E11=0,E20=0),0,if(E10E11),E11/E10,E10/E11)) -- Hope this helps "Carrach" wrote: Hi, I have a spreadsheet where I am working out the correctness of an expected result in % but have a problem with dividing 0 by 0. example from my spreadsheet: if E10 is the predicted value and E11 is the actual value achieved the % of correctness is shown in E12. E12 is currently holding the formulae below: =IF((E10E11),E11/E10,E10/E11) The problem arrises if both E10 and E11 are showing a zero value as the result is #DIV/0! I want it to show 0% which it does if only one of them is a zero. Most of the threads I have found on the website suggest checking for the zero in the divisor before dividing but I cant work out how to check for a zero in both E10- & E11 and then incorporate it into the formulae above. probably really simple but been at it so long cant see the wood for the trees. any help gratefully received thanks carrach |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
dividing 0 by 0 in excel 2003
Try
=IF(AND(E10=0,E11=0),0,IF((E10E11),E11/E10,E10/E11)) -- HTH Bob "Carrach" wrote in message ... Hi, I have a spreadsheet where I am working out the correctness of an expected result in % but have a problem with dividing 0 by 0. example from my spreadsheet: if E10 is the predicted value and E11 is the actual value achieved the % of correctness is shown in E12. E12 is currently holding the formulae below: =IF((E10E11),E11/E10,E10/E11) The problem arrises if both E10 and E11 are showing a zero value as the result is #DIV/0! I want it to show 0% which it does if only one of them is a zero. Most of the threads I have found on the website suggest checking for the zero in the divisor before dividing but I cant work out how to check for a zero in both E10- & E11 and then incorporate it into the formulae above. probably really simple but been at it so long cant see the wood for the trees. any help gratefully received thanks carrach |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
dividing 0 by 0 in excel 2003
Hi Carrach
Maybe something like this : =IF(AND(E10=0,E11=0),0,IF(E10E11,E11/E10,E10/E11)) HTH John "Carrach" wrote in message ... Hi, I have a spreadsheet where I am working out the correctness of an expected result in % but have a problem with dividing 0 by 0. example from my spreadsheet: if E10 is the predicted value and E11 is the actual value achieved the % of correctness is shown in E12. E12 is currently holding the formulae below: =IF((E10E11),E11/E10,E10/E11) The problem arrises if both E10 and E11 are showing a zero value as the result is #DIV/0! I want it to show 0% which it does if only one of them is a zero. Most of the threads I have found on the website suggest checking for the zero in the divisor before dividing but I cant work out how to check for a zero in both E10- & E11 and then incorporate it into the formulae above. probably really simple but been at it so long cant see the wood for the trees. any help gratefully received thanks carrach |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
dividing 0 by 0 in excel 2003
Hi. If 'x' is the name of two adjacent cells, maybe...
(Excel 2007) =IFERROR(MIN(x)/MAX(x),0) = = = = = = = HTH :) Dana DeLouis On 4/26/2010 11:21 AM, Carrach wrote: Hi, I have a spreadsheet where I am working out the correctness of an expected result in % but have a problem with dividing 0 by 0. example from my spreadsheet: if E10 is the predicted value and E11 is the actual value achieved the % of correctness is shown in E12. E12 is currently holding the formulae below: =IF((E10E11),E11/E10,E10/E11) The problem arrises if both E10 and E11 are showing a zero value as the result is #DIV/0! I want it to show 0% which it does if only one of them is a zero. Most of the threads I have found on the website suggest checking for the zero in the divisor before dividing but I cant work out how to check for a zero in both E10-& E11 and then incorporate it into the formulae above. probably really simple but been at it so long cant see the wood for the trees. any help gratefully received thanks carrach |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
dividing 0 by 0 in excel 2003
"Carrach" wrote:
The problem arrises if both E10 and E11 are showing a zero value as the result is #DIV/0! I want it to show 0% [....] Most of the threads I have found on the website suggest checking for the zero in the divisor before dividing but I cant work out how to check for a zero in both E10- & E11 You could write: =if(E10*E11=0, 0, min(E10,E11)/max(E10,E11)) But.... if E10 is the predicted value and E11 is the actual value achieved the % of correctness is shown in E12. So if E12 is 110%, how can anyone know if actual is 110% of predicted or predicted is 110% of actual? I think most people would prefer E12 to indicate just one or the other, and typically that actual is x% of predicted, for example 110% or 91% of predicted. So you can simply write: =if(E10=0, 1, E11/E10) formatted as Percentage. Note that I chose 1, not zero, when predicted is zero. This is an arbitrary choice; mathematically, there is no correct number. But if you predicted 0 and actual is any positive number, I doubt that people would express actual as 0% of predicted. I choose 100% of predicted arbitrarily. PS: All of this assumes that neither E10 nor E11 is negative. If either or both can be negative, your formula needs more work. ----- original message ----- "Carrach" wrote: Hi, I have a spreadsheet where I am working out the correctness of an expected result in % but have a problem with dividing 0 by 0. example from my spreadsheet: if E10 is the predicted value and E11 is the actual value achieved the % of correctness is shown in E12. E12 is currently holding the formulae below: =IF((E10E11),E11/E10,E10/E11) The problem arrises if both E10 and E11 are showing a zero value as the result is #DIV/0! I want it to show 0% which it does if only one of them is a zero. Most of the threads I have found on the website suggest checking for the zero in the divisor before dividing but I cant work out how to check for a zero in both E10- & E11 and then incorporate it into the formulae above. probably really simple but been at it so long cant see the wood for the trees. any help gratefully received thanks carrach |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
dividing 0 by 0 in excel 2003
thank you so much, that works, sorry for the delay in replying as I am new to
this and couldn't find my thread. -- all help gratefully received thanks carrach "JudithJubilee" wrote: Hi Carrach, Try this: =IF(AND(E11=0,E20=0),0,if(E10E11),E11/E10,E10/E11)) -- Hope this helps "Carrach" wrote: Hi, I have a spreadsheet where I am working out the correctness of an expected result in % but have a problem with dividing 0 by 0. example from my spreadsheet: if E10 is the predicted value and E11 is the actual value achieved the % of correctness is shown in E12. E12 is currently holding the formulae below: =IF((E10E11),E11/E10,E10/E11) The problem arrises if both E10 and E11 are showing a zero value as the result is #DIV/0! I want it to show 0% which it does if only one of them is a zero. Most of the threads I have found on the website suggest checking for the zero in the divisor before dividing but I cant work out how to check for a zero in both E10- & E11 and then incorporate it into the formulae above. probably really simple but been at it so long cant see the wood for the trees. any help gratefully received thanks carrach |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
dividing 0 by 0 in excel 2003
thank you bob, that worked
new to this so bear with me please thanks carrach "Bob Phillips" wrote: Try =IF(AND(E10=0,E11=0),0,IF((E10E11),E11/E10,E10/E11)) -- HTH Bob "Carrach" wrote in message ... Hi, I have a spreadsheet where I am working out the correctness of an expected result in % but have a problem with dividing 0 by 0. example from my spreadsheet: if E10 is the predicted value and E11 is the actual value achieved the % of correctness is shown in E12. E12 is currently holding the formulae below: =IF((E10E11),E11/E10,E10/E11) The problem arrises if both E10 and E11 are showing a zero value as the result is #DIV/0! I want it to show 0% which it does if only one of them is a zero. Most of the threads I have found on the website suggest checking for the zero in the divisor before dividing but I cant work out how to check for a zero in both E10- & E11 and then incorporate it into the formulae above. probably really simple but been at it so long cant see the wood for the trees. any help gratefully received thanks carrach . |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
dividing 0 by 0 in excel 2003
-thanks for the help but working in 2003 thanks carrach "Dana DeLouis" wrote: Hi. If 'x' is the name of two adjacent cells, maybe... (Excel 2007) =IFERROR(MIN(x)/MAX(x),0) = = = = = = = HTH :) Dana DeLouis On 4/26/2010 11:21 AM, Carrach wrote: Hi, I have a spreadsheet where I am working out the correctness of an expected result in % but have a problem with dividing 0 by 0. example from my spreadsheet: if E10 is the predicted value and E11 is the actual value achieved the % of correctness is shown in E12. E12 is currently holding the formulae below: =IF((E10E11),E11/E10,E10/E11) The problem arrises if both E10 and E11 are showing a zero value as the result is #DIV/0! I want it to show 0% which it does if only one of them is a zero. Most of the threads I have found on the website suggest checking for the zero in the divisor before dividing but I cant work out how to check for a zero in both E10-& E11 and then incorporate it into the formulae above. probably really simple but been at it so long cant see the wood for the trees. any help gratefully received thanks carrach . |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
dividing 0 by 0 in excel 2003
Everybody is so helpful
many thanks for this - it works -- all help gratefully received thanks carrach "John" wrote: Hi Carrach Maybe something like this : =IF(AND(E10=0,E11=0),0,IF(E10E11,E11/E10,E10/E11)) HTH John "Carrach" wrote in message ... Hi, I have a spreadsheet where I am working out the correctness of an expected result in % but have a problem with dividing 0 by 0. example from my spreadsheet: if E10 is the predicted value and E11 is the actual value achieved the % of correctness is shown in E12. E12 is currently holding the formulae below: =IF((E10E11),E11/E10,E10/E11) The problem arrises if both E10 and E11 are showing a zero value as the result is #DIV/0! I want it to show 0% which it does if only one of them is a zero. Most of the threads I have found on the website suggest checking for the zero in the divisor before dividing but I cant work out how to check for a zero in both E10- & E11 and then incorporate it into the formulae above. probably really simple but been at it so long cant see the wood for the trees. any help gratefully received thanks carrach . |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
dividing 0 by 0 in excel 2003
Hi John, thanks for the reply
I agree that it does not give the best results but this is exactly what they want to see in the hope that it will drive behaviour and eventually they will all be at over 90% correct. thanks again -- thanks carrach "Joe User" wrote: "Carrach" wrote: The problem arrises if both E10 and E11 are showing a zero value as the result is #DIV/0! I want it to show 0% [....] Most of the threads I have found on the website suggest checking for the zero in the divisor before dividing but I cant work out how to check for a zero in both E10- & E11 You could write: =if(E10*E11=0, 0, min(E10,E11)/max(E10,E11)) But.... if E10 is the predicted value and E11 is the actual value achieved the % of correctness is shown in E12. So if E12 is 110%, how can anyone know if actual is 110% of predicted or predicted is 110% of actual? I think most people would prefer E12 to indicate just one or the other, and typically that actual is x% of predicted, for example 110% or 91% of predicted. So you can simply write: =if(E10=0, 1, E11/E10) formatted as Percentage. Note that I chose 1, not zero, when predicted is zero. This is an arbitrary choice; mathematically, there is no correct number. But if you predicted 0 and actual is any positive number, I doubt that people would express actual as 0% of predicted. I choose 100% of predicted arbitrarily. PS: All of this assumes that neither E10 nor E11 is negative. If either or both can be negative, your formula needs more work. ----- original message ----- "Carrach" wrote: Hi, I have a spreadsheet where I am working out the correctness of an expected result in % but have a problem with dividing 0 by 0. example from my spreadsheet: if E10 is the predicted value and E11 is the actual value achieved the % of correctness is shown in E12. E12 is currently holding the formulae below: =IF((E10E11),E11/E10,E10/E11) The problem arrises if both E10 and E11 are showing a zero value as the result is #DIV/0! I want it to show 0% which it does if only one of them is a zero. Most of the threads I have found on the website suggest checking for the zero in the divisor before dividing but I cant work out how to check for a zero in both E10- & E11 and then incorporate it into the formulae above. probably really simple but been at it so long cant see the wood for the trees. any help gratefully received thanks carrach |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel datasheet needs dividing into more datasheets | Excel Discussion (Misc queries) | |||
Excel automatically dividing value by 100... | Excel Discussion (Misc queries) | |||
Dividing in excel easy questions | Excel Discussion (Misc queries) | |||
Excel is dividing my entries...why?? | Excel Discussion (Misc queries) | |||
What is the formula for dividing a number by 4 in excel | Excel Worksheet Functions |