If Statement with Named Range

[murphyjeff4]

Hello,

I am wanting to use a named range in a IF statement to significant shorten the formula. This is what I am currently using, but would need to expand the formula significantly to gather the information I need.

=IF(AND(F17>0,F17<J10),J10*0.1,IF(AND(F17>J10+1,F17<J11),J11*0.12,IF(AND(F17>J11+1,F17<J12),J12*0.14,0)))

If I inputted 500,000 into cell F17 I want it to import the value from the range in column j and multiple it by the correspond value in the column G (range). For example, if 530,000 is in F17 I want F18 to tell me it's 50,000 (500000*.1).The range/columns are below...

Column J Column G
500000 .1
600000 .11
700000 .12

 

[Johnny Thunder]

What you wrote is very confusing because it doesn't seem clear, you mention using Named Ranges but don't give any detail as to what the named ranges are or what the cell references are to those ranges. And your formula is missing some variables "F17J10" should have something in between each of those cell references.

 

[murphyjeff4]

Let me try this again. Thanks for asking for clarity.

Here is my formula. =IF(AND(F9>0,F9<J2),J2*L2,IF(AND(F9>J2+1,F9<J3),J3*L3,IF(AND(F9>J3+1,F9<J4),J4*L4,0)))

Essentially I want to create the above formula with the named ranges and the tables provided below. If I type in a value in F9 (530,000) then I want F10 to reference the "starting range" and "end range" to include the corresponding ending range number x the multiplier range number. Example below....


If F9 Value=$530,000
Then F10 Value= J3 (600,000)*L3 (.12)

So F10 = 72,000

Named Ranges

"Starting Range"= I:I
"Ending Range" =J:J
"Multiplier"= L:L



[TABLE="width: 500"]
<tbody>[TR]
[TD]F
[/TD]
[TD]I (Starting Range)
[/TD]
[TD]J (Ending Range)
[/TD]
[TD]L (Multiplier Range)
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0
[/TD]
[TD]500000
[/TD]
[TD].1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]500001
[/TD]
[TD]600000
[/TD]
[TD].12
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]600001
[/TD]
[TD]700000
[/TD]
[TD].14
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]700001
[/TD]
[TD]800000
[/TD]
[TD].16
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[murphyjeff4]

My formula keeps getting cut off...

Here you go.

=IF(AND(F9>0,F9<J2),J2*0.1,IF(AND(F9>J2+1,F9<J3),J3*0.12,IF(AND(F9>J3+1,F9<J4),J4*0.14,0)))

 

[murphyjeff4]

=if(and(f9>0,f9<j2),j2*0.1,
if(and(f9>j2+1,f9<j3),j3*0.12,
if(and(f9>j3+1,f9<j4),j4*0.14,0)))

 

[murphyjeff4]

I am trying this one last time...

=IF(AND(F9>0,F9<J2),J2*L2,
IF(AND(F9<J3),J3*L3,
IF(AND(F9<J4),J4*L4,0))

 

[dreid1011]

=IF(AND(F17>0,F17 < j10),j10*0.1,if(and(f17>J10+1,F17 < j11),j11*0.12,if(and(f17>J11+1,F17 < j12),j12*0.14,0)))

Mind the spaces added to each "<" if you copy this formula.

 

[murphyjeff4]

=if(and(f17>0,f17 <j10),j10*0.1,if(and(f17>j10+1,f17 <j11),j11*0.12,if(and(f17>j11+1,f17 <j12),j12*0.14,0)))