#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]

Sorry, I'm using Excel 2010.

 

[mikerickson]

The synatx for G2 is less than 7 but greater than 3 is AND(3 < G2, G2 <7)

 

[Scott Huish]

This format is not used by Excel:

G2<7>3

The equivalent is AND(G2>3,G2<7)

But a LOOKUP formula is simpler:

=IF(C2="NEW",LOOKUP(G2,{0,3,5,7},{0,0.15,0.125,0.1}),0)

 

[TeressaJP]

"But a LOOKUP formula is simpler: =IF(C2="NEW",LOOKUP(G2,{0,3,5,7},{0,0.15,0.125,0.1}),0)" When I use this formula, my return is zero if G2 is 2 or 4 which is why I need the less than/greater than included. Right?

"This format is not used by Excel: G2<7>3
The equivalent is AND(G2>3,G2<7)"

Changing the format of the greater than less than to match what is supported, see below, I still return the #VALUE!.


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

 

[Scott Huish]

Your return on my formula would only return 0 if C2 didn't equal NEW. It would however return 0 on 2 because it is not greater than or equal to 3. 4 would work however.

 

[Gerald Higgins]

Deleted, sorry, I misread the formula

 

[Gerald Higgins]

Let's start again.

Are these the desired results ?

C2.......G2....Result
New.....8......0
New.......6......0.01
New.......4......0.0125
New.......3......?????? please advise
New.......2......0.015
New.......0......0.02
Renewal..5......0
Renewal..3.5...0.01
Renewal..3......?????? please advise
Renewal..2......0.015
Renewal..0......0.02

 

[TeressaJP]

Let's start again.

These are the desired results:


C2.......G2....Result
New.....8......0
New.......6......0.01
New.......4......0.0125
New.......3.......0.0125
New.......2......0.015
New.......0......0.02
Renewal..5......0
Renewal..3.5...0.01
Renewal..3......0.01
Renewal..2......0.015
Renewal..0......0.02


(This format is very helpful)

 

[p45cal]

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]))