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
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.84then 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.
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.
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.
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
