Locgical test with nesting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone help me make it work? I'm not sure about the use of ( , " I'll look for the formula Santa to post. Thanks, Eugene

Logical test: =IFG4>0
Value to return if true: IFG4<.20 I need the value of C4-E4 times .017
IFG4<.30 I need the value of C4-E4 times .02 minus J
IfG4<.35 I need the value of C4-E4 times .025 minus J
IFG4<.40 I need the value of C4-E4 times .03 minus J
IFG4<.45 I need the value of C4-E4 times .035 minus J
IFG4<.50 I need the value of C4-E4 times .04 minus J
IFG4>=.50 I need the value of C4-E4 times .05 minus J
Value if false: 0
 
Eugene said:
Can anyone help me make it work? . . .
Logical test:
=IFG4>0,
Value to return if true:
IFG4<.20 I need the value of C4-E4 times .0175
IFG4<.30 I need the value of C4-E4 times .02 minus J4
IfG4<.35 I need the value of C4-E4 times .025 minus J4
IFG4<.40 I need the value of C4-E4 times .03 minus J4
IFG4<.45 I need the value of C4-E4 times .035 minus J4
IFG4<.50 I need the value of C4-E4 times .04 minus J4
IFG4>=.50 I need the value of C4-E4 times .05 minus J4
Value if false:
0
Click to expand...

=LOOKUP(G4,{-1E300;0;0.2;0.3;0.35;0.4;0.45;0.5},
{0;0.0175;0.02;0.025;0.03;0.035;0.04;0.05})*(C4-E4)
-IF(G4>=0.3,J4,0)
 
One of:

=IF(G4>0,LOOKUP(G4,$N$2:$O$8)*(C4-E4),0)

=IF(G4>0,VLOOKUP(G4,$N$2:$O$8,2,1)*(C4-E4),0)

where N2:O8 houses the following 2-column table:

{0,0.0175;
0.2,0.2;
0.3,0.025;
0.35,0.03;
0.4,0.035;
0.45,0.04;
0.5,0.05}

Eugene said:
Can anyone help me make it work? I'm not sure about the use of ( , "
Click to expand...
I'll look for the formula Santa to post. Thanks, Eugene.
 
Forgot to include the minus J4 bit...

=IF(G4>0,LOOKUP(G4,$N$2:$O$8)*(C4-E4)-(G4>=0.3)*J4,0)

=IF(G4>0,VLOOKUP(G4,$N$2:$O$8,2,1)*(C4-E4)-(G4>=0.3)*J4,0)
 
Back
Top