|
DIV/0 error
Hi Experts
I want to produce figures for our sales team to show how accurate the forecasts are... I have 2 tables - SPLITS_1 which has the forecast in ACTUAL which has the sales in I am using the following formula =IF(SPLITS_1!D64/ACTUAL!D641,SPLITS_1!D64/ACTUAL!D64-1,1-SPLITS_1!D64/ACTUAL!D64) this works fine unless there is a zero in one of the tables. I need to be able to return that if the forecast said 50, and the sales were 0, then it was 100% wrong also visa versa, otherwise do the calculation... to give the % Many thanks for your help as always... |
DIV/0 error
Use another IF statement like so:
=IF(ACTUAL!D64=0,"100% wrong",IF(SPLITS_1!D64/ACTUAL!D641,SPLITS_1!D64/ACTUAL!D64-1,1-SPLITS_1!D64/ACTUAL!D64)) This should prevent the error message. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Nicky" wrote: Hi Experts I want to produce figures for our sales team to show how accurate the forecasts are... I have 2 tables - SPLITS_1 which has the forecast in ACTUAL which has the sales in I am using the following formula =IF(SPLITS_1!D64/ACTUAL!D641,SPLITS_1!D64/ACTUAL!D64-1,1-SPLITS_1!D64/ACTUAL!D64) this works fine unless there is a zero in one of the tables. I need to be able to return that if the forecast said 50, and the sales were 0, then it was 100% wrong also visa versa, otherwise do the calculation... to give the % Many thanks for your help as always... |
DIV/0 error
This partly worked but does not capture all senarios...
ie; F/Cast Actual Answer 0 110 0.00% this should be 100% wrong as there is no forecast 90 0 "100% wrong" correct 0 0 "100% wrong" this should be 0% as there is no forecast or sales Do I need more If statements... I'm getting a bit lost, or should I be tackling it a different way? Many thanks for the help.. "Luke M" wrote: Use another IF statement like so: =IF(ACTUAL!D64=0,"100% wrong",IF(SPLITS_1!D64/ACTUAL!D641,SPLITS_1!D64/ACTUAL!D64-1,1-SPLITS_1!D64/ACTUAL!D64)) This should prevent the error message. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Nicky" wrote: Hi Experts I want to produce figures for our sales team to show how accurate the forecasts are... I have 2 tables - SPLITS_1 which has the forecast in ACTUAL which has the sales in I am using the following formula =IF(SPLITS_1!D64/ACTUAL!D641,SPLITS_1!D64/ACTUAL!D64-1,1-SPLITS_1!D64/ACTUAL!D64) this works fine unless there is a zero in one of the tables. I need to be able to return that if the forecast said 50, and the sales were 0, then it was 100% wrong also visa versa, otherwise do the calculation... to give the % Many thanks for your help as always... |
DIV/0 error
HELP...anyone got any suggestions
"Nicky" wrote: This partly worked but does not capture all senarios... ie; F/Cast Actual Answer 0 110 0.00% this should be 100% wrong as there is no forecast 90 0 "100% wrong" correct 0 0 "100% wrong" this should be 0% as there is no forecast or sales Do I need more If statements... I'm getting a bit lost, or should I be tackling it a different way? Many thanks for the help.. "Luke M" wrote: Use another IF statement like so: =IF(ACTUAL!D64=0,"100% wrong",IF(SPLITS_1!D64/ACTUAL!D641,SPLITS_1!D64/ACTUAL!D64-1,1-SPLITS_1!D64/ACTUAL!D64)) This should prevent the error message. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Nicky" wrote: Hi Experts I want to produce figures for our sales team to show how accurate the forecasts are... I have 2 tables - SPLITS_1 which has the forecast in ACTUAL which has the sales in I am using the following formula =IF(SPLITS_1!D64/ACTUAL!D641,SPLITS_1!D64/ACTUAL!D64-1,1-SPLITS_1!D64/ACTUAL!D64) this works fine unless there is a zero in one of the tables. I need to be able to return that if the forecast said 50, and the sales were 0, then it was 100% wrong also visa versa, otherwise do the calculation... to give the % Many thanks for your help as always... |
DIV/0 error
Hi,
I cant read your question very well - it is all jumbled up. Please repost your question or else mail me your workbook (a sample of the problem) at -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Nicky" wrote in message ... HELP...anyone got any suggestions "Nicky" wrote: This partly worked but does not capture all senarios... ie; F/Cast Actual Answer 0 110 0.00% this should be 100% wrong as there is no forecast 90 0 "100% wrong" correct 0 0 "100% wrong" this should be 0% as there is no forecast or sales Do I need more If statements... I'm getting a bit lost, or should I be tackling it a different way? Many thanks for the help.. "Luke M" wrote: Use another IF statement like so: =IF(ACTUAL!D64=0,"100% wrong",IF(SPLITS_1!D64/ACTUAL!D641,SPLITS_1!D64/ACTUAL!D64-1,1-SPLITS_1!D64/ACTUAL!D64)) This should prevent the error message. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Nicky" wrote: Hi Experts I want to produce figures for our sales team to show how accurate the forecasts are... I have 2 tables - SPLITS_1 which has the forecast in ACTUAL which has the sales in I am using the following formula =IF(SPLITS_1!D64/ACTUAL!D641,SPLITS_1!D64/ACTUAL!D64-1,1-SPLITS_1!D64/ACTUAL!D64) this works fine unless there is a zero in one of the tables. I need to be able to return that if the forecast said 50, and the sales were 0, then it was 100% wrong also visa versa, otherwise do the calculation... to give the % Many thanks for your help as always... |
| All times are GMT +1. The time now is 03:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com