#VALUE! with if great than or less than x then do this formula

[TeressaJP]

my formula below is returning #VALUE

What I need is: If C2 is "New" and G2 is less than 7 but greater than 3 then return .0.1; If G2 is less than 5 but greater than 3 then return .0125; If G2 is less than 3 but greater than 0 then return .015; if G2 equals zero then return zero.

If C2 is "Renewal" follows same as above but with different returns. What am I doing wrong?


'=IF(C2="New",IF(G2<7>3,0.01,IF(G2<5>3,0.0125,IF(G2<3>0,0.015,IF(G2=0,0.02,0))))),IF(C2="Renewal",IF(G2<4>3,0.01,IF(G2<3>0,0.015,IF(G2=0,0.02,0))),0)

 

[TeressaJP]

Thanks for your help.

 

[TeressaJP]

Some guessing, change the red bits to what you're wanting:

=IF(C2="New",INDEX({0.01;0.0125;0.015;0.2},MATCH(G2,{7;5;3;0},-1)),IF(C2="Renewal",INDEX({[COLOR=red]1;1.25;1.5;2[/COLOR]},MATCH(G2,{7;5;3;0},-1)),[COLOR=red]"Not 'New' Or 'Renewal'"[/COLOR]))

AWESOME! THANKS!!!

 

[Gerald Higgins]

OK. There are lots and lots of ways to do this.

This isn't necessarily the best one, but it works and should be easy to understand.

=IF(G2=0,0.2,IF(G2<3,0.015,IF(C2="New",IF(G2<5,0.0125,IF(G2<7,0.01,0)),IF(G2<4,0.01,0))))

Note, this assumes that there are only two possible values for C2. It assumes that C2 would never be blank.

 

[TeressaJP]

OK. There are lots and lots of ways to do this.

This isn't necessarily the best one, but it works and should be easy to understand.

=IF(G2=0,0.2,IF(G2<3,0.015,IF(C2="New",IF(G2<5,0.0125,IF(G2<7,0.01,0)),IF(G2<4,0.01,0))))

Note, this assumes that there are only two possible values for C2. It assumes that C2 would never be blank.

IT WORKED! YEAH! THANKS.

 

[TeressaJP]

Some guessing, change the red bits to what you're wanting:

=IF(C2="New",INDEX({0.01;0.0125;0.015;0.2},MATCH(G2,{7;5;3;0},-1)),IF(C2="Renewal",INDEX({[COLOR=red]1;1.25;1.5;2[/COLOR]},MATCH(G2,{7;5;3;0},-1)),[COLOR=red]"Not 'New' Or 'Renewal'"[/COLOR]))

This works but i have one remaining issue. There will be numbers greater than seven. How do I handle those? If greater than 7 the return should be .5

 

[p45cal]

This works but i have one remaining issue. There will be numbers greater than seven. How do I handle those? If greater than 7 the return should be .5

untested ('cos I just extended the formula here, not in Excel):

=IF(C2="New",INDEX({[COLOR=Blue]0.5;[/COLOR]0.01;0.0125;0.015;0.2},MATCH(G2,{[COLOR=Blue]200;[/COLOR]7;5;3;0},-1)),IF(C2="Renewal",INDEX({[COLOR=Blue]5;[/COLOR]1;1.25;1.5;2},MATCH(G2,{[COLOR=Blue]200;[/COLOR]7;5;3;0},-1)),"Not 'New' Or 'Renewal'"))

 

[Gerald Higgins]

If greater than 7 the return should be .5

Is this for NEW, or RENEW, or both ?

 

[TeressaJP]

Is this for NEW, or RENEW, or both ?

Both please.

 

[Gerald Higgins]

Here's one way.
I haven't tested it, but it probably works

=IF(G2>7,0.5,IF(G2=0,0.2,IF(G2<3,0.015,IF(C2="New",
IF(G2<5,0.0125,IF(G2<7,0.01,0)),IF(G2<4,0.01,0)))))

What do you want to do if G2=7 exactly ?
Maybe change >7 to >=7.
Or change <7 to <=7.

 

[TeressaJP]

untested ('cos i just extended the formula here, not in excel):

=if(c2="new",index({0.5;0.01;0.0125;0.015;0.2},match(g2,{200;7;5;3;0},-1)),if(c2="renewal",index({5;1;1.25;1.5;2},match(g2,{200;7;5;3;0},-1)),"not 'new' or 'renewal'"))

perfect! Thanks.