# Allowing for Zeroes in Calculating a Variance to Forecast

#### AllyOops

##### New Member
Hi Everybody,

There are Three Scenarios where a variance is being reported as zero, Scenario A the forecast is zero or Scenario B the actuals are zero and Scenario C where both are zero. This works fine!

I am calculating a comment where 95-100% Accuracy is a Meets Forecast, >100% Exceeds Forecast and >0% but less than 95% is a Miss. I am trying to add a condition to allow for the variable reasons for a zero.

The following works to give me a distinction between no forecast and a missed forecast:
=IF(AND(Q16=0,P16>0),"Missed","")

but when I nest it into the full nested if statement the answer Missed flips to "Not Forecast"
=IF(Q16>1,"Exceed",IF(Q16>0.95,"Meet",IF(AND(Q17=0,P17>0),"Missed","Not Forecast")))

Is this an order of operations problem?

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
This is where P16 = Forecast and Q16 = Sales!

Replies
7
Views
374
Replies
0
Views
899
Replies
1
Views
1K
Replies
4
Views
806
Replies
1
Views
271

1,203,435
Messages
6,055,366
Members
444,781
Latest member
rishivar

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back