# Variance Formula - Two Conditions

#### mattyblueice

##### Board Regular
I am trying to put together a variance calcuation with two conditions - one to eliminate the #DIV/0! error and the second condition to eliminate large variances over 1,000% or under (1,000%).

My formula is:

IF(ISERROR(B6-C6)/C6,0,IF(OR(B6-C6)/C6>1000,(B6-C6)/C6<1000,"**",(B6-C6)/C6)

I can't get acceptance on the formula and not sure what is missing. Can anyone help please?

Thanks!

Matt

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Try:

Code:
``=IF(ISERROR((B6-C6)/C6),0,IF(OR((B6-C6)/C6>1000,(B6-C6)/C6<-1000),"**",(B6-C6)/C6))``

I guess you miss the % and the - for your condition stated below...

OR((B6-C6)/C6>1000%,(B6-C6)/C6<-1000%)

... the second condition to eliminate large variances over 1,000% or under (1,000%).

My formula is:

IF(ISERROR(B6-C6)/C6,0,IF(OR(B6-C6)/C6>1000,(B6-C6)/C6<1000,"**",(B6-C6)/C6)

I can't get acceptance on the formula and not sure what is missing. Can anyone help please?

Thanks!

Matt

Well I modified the formula and I now get a #VALUE error when I press enter. I can't see what's causing it. Any ideas?

=IF(ISERROR((B6-C6)/C6),0),IF(OR((B6-C6)/C6)>2000,(B6-C6)/C6)<-2000),"**",(B6-C6)/C6)

Excel Workbook
ABCD
3
45
5
6305
7
Sheet

Ugh thanks Ahoy. You're awesome!!

You're welcome. Glad you got it to work.

Replies
8
Views
611
Replies
5
Views
517
Replies
3
Views
201
Replies
8
Views
704
Replies
3
Views
502

1,207,096
Messages
6,076,555
Members
446,213
Latest member
bettigb

### 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