| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate accuracy of original estimate
Hi,
Does anyone have a formula for judging the accuracy of an initial estimated number against the final result? e.g. if the estimate was 4 and final result is 5 then the estimate was 75% accurate. e.g. if the estimate was 6 and final result is 2 then the estimate was 33% accurate. The third scenario I need to cater for is where the original estimate was 0 and then the final result was 1. If I use the formula '1-d6/d3' then I get "#DIV/0!" because you can't divide 1 by 0... The result I am expecting is -(minus)100%. Similarly, if the original estimate was 0 and the final result is 2 then the result should be -(minus)200% So, does anyone know how I can alter the formula to accommodate this scenario? (for your reference, the full formula that I have in the cell is 'IF(D3+D50,1-D6/D3,"")' I would be most grateful for help as it is driving me round the bend! Thanks Jas |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate accuracy of original estimate
=IF(D3=0,D6,IF(D3+D50,1-D6/D3,""))
Assumes D3 holds the estimate and D6 holds the actual result. -- Regards, Tom Ogilvy "Jas M" wrote in message ... Hi, Does anyone have a formula for judging the accuracy of an initial estimated number against the final result? e.g. if the estimate was 4 and final result is 5 then the estimate was 75% accurate. e.g. if the estimate was 6 and final result is 2 then the estimate was 33% accurate. The third scenario I need to cater for is where the original estimate was 0 and then the final result was 1. If I use the formula '1-d6/d3' then I get "#DIV/0!" because you can't divide 1 by 0... The result I am expecting is -(minus)100%. Similarly, if the original estimate was 0 and the final result is 2 then the result should be -(minus)200% So, does anyone know how I can alter the formula to accommodate this scenario? (for your reference, the full formula that I have in the cell is 'IF(D3+D50,1-D6/D3,"")' I would be most grateful for help as it is driving me round the bend! Thanks Jas |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate accuracy of original estimate
Thanks Tom, I did not mention that D6 is the difference,
D3 is the original and D5 is the final result. D6 formula is '=ABS(D3-D5'. Using your formula I get the answer '1' (or 100%). Are you able to change it? many thanks Jas -----Original Message----- =IF(D3=0,D6,IF(D3+D50,1-D6/D3,"")) Assumes D3 holds the estimate and D6 holds the actual result. -- Regards, Tom Ogilvy "Jas M" wrote in message ... Hi, Does anyone have a formula for judging the accuracy of an initial estimated number against the final result? e.g. if the estimate was 4 and final result is 5 then the estimate was 75% accurate. e.g. if the estimate was 6 and final result is 2 then the estimate was 33% accurate. The third scenario I need to cater for is where the original estimate was 0 and then the final result was 1. If I use the formula '1-d6/d3' then I get "#DIV/0!" because you can't divide 1 by 0... The result I am expecting is -(minus)100%. Similarly, if the original estimate was 0 and the final result is 2 then the result should be -(minus)200% So, does anyone know how I can alter the formula to accommodate this scenario? (for your reference, the full formula that I have in the cell is 'IF(D3+D50,1- D6/D3,"")' I would be most grateful for help as it is driving me round the bend! Thanks Jas . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate accuracy of original estimate
=IF(D3=0,-D5,IF(D3+D50,1-D6/D3,""))
produced all the results you have described (format the cell in the formula as Per Cent. -- Regards, Tom Ogilvy "Jas M" wrote in message ... Thanks Tom, I did not mention that D6 is the difference, D3 is the original and D5 is the final result. D6 formula is '=ABS(D3-D5'. Using your formula I get the answer '1' (or 100%). Are you able to change it? many thanks Jas -----Original Message----- =IF(D3=0,D6,IF(D3+D50,1-D6/D3,"")) Assumes D3 holds the estimate and D6 holds the actual result. -- Regards, Tom Ogilvy "Jas M" wrote in message ... Hi, Does anyone have a formula for judging the accuracy of an initial estimated number against the final result? e.g. if the estimate was 4 and final result is 5 then the estimate was 75% accurate. e.g. if the estimate was 6 and final result is 2 then the estimate was 33% accurate. The third scenario I need to cater for is where the original estimate was 0 and then the final result was 1. If I use the formula '1-d6/d3' then I get "#DIV/0!" because you can't divide 1 by 0... The result I am expecting is -(minus)100%. Similarly, if the original estimate was 0 and the final result is 2 then the result should be -(minus)200% So, does anyone know how I can alter the formula to accommodate this scenario? (for your reference, the full formula that I have in the cell is 'IF(D3+D50,1- D6/D3,"")' I would be most grateful for help as it is driving me round the bend! Thanks Jas . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate accuracy of original estimate
Many thanks, it does work now
Cheers! -----Original Message----- =IF(D3=0,-D5,IF(D3+D50,1-D6/D3,"")) produced all the results you have described (format the cell in the formula as Per Cent. -- Regards, Tom Ogilvy "Jas M" wrote in message ... Thanks Tom, I did not mention that D6 is the difference, D3 is the original and D5 is the final result. D6 formula is '=ABS(D3-D5'. Using your formula I get the answer '1' (or 100%). Are you able to change it? many thanks Jas -----Original Message----- =IF(D3=0,D6,IF(D3+D50,1-D6/D3,"")) Assumes D3 holds the estimate and D6 holds the actual result. -- Regards, Tom Ogilvy "Jas M" wrote in message ... Hi, Does anyone have a formula for judging the accuracy of an initial estimated number against the final result? e.g. if the estimate was 4 and final result is 5 then the estimate was 75% accurate. e.g. if the estimate was 6 and final result is 2 then the estimate was 33% accurate. The third scenario I need to cater for is where the original estimate was 0 and then the final result was 1. If I use the formula '1-d6/d3' then I get "#DIV/0!" because you can't divide 1 by 0... The result I am expecting is -(minus)100%. Similarly, if the original estimate was 0 and the final result is 2 then the result should be -(minus)200% So, does anyone know how I can alter the formula to accommodate this scenario? (for your reference, the full formula that I have in the cell is 'IF(D3+D50,1- D6/D3,"")' I would be most grateful for help as it is driving me round the bend! Thanks Jas . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Accuracy | Excel Discussion (Misc queries) | |||
| Formula to calculate differently based on original value | Excel Worksheet Functions | |||
| How to indicate that a value is an estimate? | Excel Discussion (Misc queries) | |||
| Auto save replaced my original file and now I need the original? | Excel Discussion (Misc queries) | |||
| Accuracy | Excel Programming | |||
Linear Mode
