Variance Formula - Two Conditions

[mattyblueice]

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

 

[AhoyNC]

Try:

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

 

[mfexcel]

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

 

[mattyblueice]

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)

 

[AhoyNC]

Excel Workbook
	A	B	C	D
3
4		5
5
6		30	5
7
Sheet

 

[mattyblueice]

Ugh thanks Ahoy. You're awesome!!

 

[AhoyNC]

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