Check box with complex IF/OR formula

[Stephen357]

Hi,

I am really stuck. Here's what I am trying to do.

I have a checkbox, when TRUE the following calculation needs to be done. A- a variable number entered by someone is multiplied by B- a variable number entered by someone. As the value of A goes up the multiplier values which are fixed D,E,F change. Example: A=50 X B=225, C=11,250. C X D=.0122 = 137.25. OR, A=125 X B=225, C=28,125. C X D=.0112 = 315.00

A X B= C
A>=24, A<=99 then C X D
A>=100, A<=250 then C X E
A>250 then C X F

My original statement with H2 as the Checkbox: =IF(H2=TRUE,(J6*0.0122+K6*0.0168)*(J7+K7),0) works fine but I can't seem to get multiple OR's to select variable D,E,F?

Can't figure it out. Help!!

 

[jtakw]

Hi,

This is based Only on your description, Not your sample formula, as the two don't match up, may be it will help you figure out your problem.

Book1
	A	B	C	D	E	F	G
1	22	1	2	3	4	5	A1 is less than 24
2	30	1	2	3	4	5	6
Sheet34

Cell Formulas
Range	Formula
G1	=IF(A1>=24,IF(A1<=99,C1*D1,IF(A1<=250,C1*E1,C1*F1)),"A1 is less than 24")

Change the "A1 is less than 24" to whatever you need when A1<24.

 

[Stephen357]

Wow, you have me close, what about a third argument? Here's what I came up with but can't get the third line to work. J6 is the first variable, J7 the second variable. M2, 3, 4 are the fixed multipliers.

=IF(H2=TRUE,IF(J6>=24,IF(J6<=99,J6*J7*M2,IF(J6<=250,J6*J7*M3)),IF(J6>250,J6*J7*M4)),"0")

 

[Stephen357]

Take a look at my post, I think I didn't reply to you correctly. I am trying to get the third argument M4 to work. You have me really, really, close to resolution.

 

[jtakw]

Wow, you have me close, what about a third argument? Here's what I came up with but can't get the third line to work. J6 is the first variable, J7 the second variable. M2, 3, 4 are the fixed multipliers.

=IF(H2=TRUE,IF(J6>=24,IF(J6<=99,J6*J7*M2,IF(J6<=250,J6*J7*M3)),IF(J6>250,J6*J7*M4)),"0")

Two ways:

Book1
	G
4	0
5	0
Sheet34

Cell Formulas
Range	Formula
G4	=IF(H2=TRUE,IF(J6>=24,IF(J6<=99,J6*J7*M2,IF(J6<=250,J6*J7*M3,J6*J7*M4)),"0"),"0")
G5	=IF(H2=TRUE,IF(J6>=24,J6*J7*IF(J6<=99,M2,IF(J6<=250,M3,M4)),"0"),"0")

You Don't need to Test for J6>250, since if it fails all previous tests (<=99, <=250), it Must be >250