Need two IF statements turned into a macro one macro

December 14, 2012 at 05:02:39
Specs: Windows 7
I need two if statement that im using in two different cells that are tied to gether to be turn into a macro because i keep getting circular reference errors and one day it will work fine then the next it will mess up again. Please take a look and telll me what you think and i will explain them.

D27==IF(D14=0,D27*0,IF(D14=1,(((C10-E10)/2)*200)+(1.54*200),IF(D14=2,(((C10-E10)/2)*200)+(1.54*200),IF(D14=3,(((C10-E10)/2)*200)+(1.54*200),IF(D14=4,(((C10-E10)/2)*200)+(1.54*200),IF(D14=5,(((C10-E10)/2)*200)+( 1.54*200),IF(D14=6,(((C10-E10)/2)*200)+(1.54*200),IF(D14=7,(((C10-E10)/2)*200)+(1.54*200),IF(D14=8,(((C10-E10)/2)*200)+(1.54*200),IF(D14=9,(((C10-E10)/2)*200)+(1.4*200)))))))))))

H27==IF(J10=0,H27*0,IF(D14=1,(((C10-J10)/2)*200)+(3.255*200),IF(D14=2,(((C10-J10)/2)*200)+(3.145*200),IF(D14=3,(((C10-J10)/2)*200)+(3.325*200),IF(D14=4,(((C10-J10)/2)*200)+(3.277*200),IF(D14=5,(((C10-J10)/2)*200)+(3.29*200),IF(D14=6,(((C10-J10)/2)*200)+(3.17*200),IF(D14=7,(((C10-J10)/2)*200)+(3.465*200),IF(D14=8,(((C10-J10)/2)*200)+(3.49*200),IF(D14=9,(((C10-J10)/2)*200)+(2.84*200)))))))))))


So its a little long but its really just continous. So what i need this is do is for "D27" if D14 id blank or 0 the D27"itself is zero. This part is most important because if there nothing in D14 there should be no value in D27 but this is happening with this equation, sometimes, it will work sometimes it wont.....Then the eq. gets continous saying if D14 is 1 do this, if D14 is 2 do this and so up to D14 = 9.

Then for H27 it is similar it uses C10 from the D27 eq. and needs to rely on J10 to say if it should calculate. Basically if J10 is blank of zero H27 or itself should be zero. so until J10 gets a value it does nothing.

Your help will be greatly apprieciated

let me know if you have any questions


See More: Need two IF statements turned into a macro one macro

Report •


#1
December 14, 2012 at 08:14:13
Check out the Lookup function and its cousins.

Report •

#2
December 14, 2012 at 11:05:12
What are you doing?
From your first formula, all you are doing is repeating yourself 9 times:

IF(D14=0,D27*0,
IF(D14=1,(((C10-E10)/2)*200)+(1.54*200),
IF(D14=2,(((C10-E10)/2)*200)+(1.54*200),
IF(D14=3,(((C10-E10)/2)*200)+(1.54*200),
IF(D14=4,(((C10-E10)/2)*200)+(1.54*200),
IF(D14=5,(((C10-E10)/2)*200)+(1.54*200),
IF(D14=6,(((C10-E10)/2)*200)+(1.54*200),
IF(D14=7,(((C10-E10)/2)*200)+(1.54*200),
IF(D14=8,(((C10-E10)/2)*200)+(1.54*200),
IF(D14=9,(((C10-E10)/2)*200)+(1.4*200)))))))))))

You could reduce the above to something like:

IF(D14=0,D27*0,(((C10-E10)/2)*200)+(1.54*200))

Your second formula could use a =VLOOKUP() and make it much simpler:

You would need to put your multipliers on Sheet 2, column B so it looks like:

    A     B
1)  1   3.255
2)  2   3.145
3)  3   3.325
4)  4   3.277
5)  5   3.29
6)  6   3.17
7)  7   3.465
8)  8   3.49
9)  9   2.84

then a formula something like:

IF(J10=0,H27*0,(((C10-J10)/2)*200)+(VLOOKUP(D14,Sheet2!A1:B9,2,0)*200))

Would make life a bit easier.

All of this is untested.


MIKE

http://www.skeptic.com/


Report •

#3
December 14, 2012 at 11:17:08
I agree with mike with this addition

the reason you are getting a circular reference is that in D27 you have the statement "if (D14=0,D27*0

Simplify this to just if(D14=0,0

Same for H27.


Report •

Related Solutions

#4
December 14, 2012 at 11:56:10
re: IF(D14=0,D27*0,(((C10-E10)/2)*200)+(1.54*200))

Not only could D27*0 be replaced with 0, but unless I'm missing something, it seems like it can be reduced even further:

=IF(D14=0,0,(((C10-E10)/2+1.54)*200

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
December 14, 2012 at 12:39:20
Hey thanks guys I think your right about this its just to much clutter i will hook these suggestions up and get back you shortly

Report •


Ask Question