Error in "IF" formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

reloanpro - 24 Nov 2005 21:07 GMT

Please help me to see the error in the following formula:

"=IF(G6>=0.9<=1,E6*G6,IF(G6>=0.75<0.9,(E6*G6)*1.05,IF(G6>=0.6<0.75,(E6*G6)*1.1,IF(G6>=0.45<0.6,(E6*G6)*1.15,IF(G6>=0.3<0.45,(E6*G6)*1.2,IF(G6>=0.15<0.3,(E6*G6)*1.25,IF(G6>=0.1<0.15,(E6*G6)*1.3,0)))))))"

The worksheet is returning FALSE to the very first argument, though there is
a value of 97% in G6. The value of 97% is coming from an INDIRECT formula
fetching data from another worksheet into G6.

Any help is appreciated.

Reply to this Message

duane - 24 Nov 2005 21:41 GMT

you cannot put in expression such as IF(G6>=0.75<0.9,.....

best to set up a table and do a vlookup (assume table in z1:aa8)
"G6 value" "multiplier"
0 0
0.1 1.3
0.15 1.25
0.3 1.2
0.45 1.15
0.6 1.1
0.75 1.05
0.9 1.0

your formula would be = vlookup(g6,z1:aa8,2,true)

> Please help me to see the error in the following formula:
>
[quoted text clipped - 5 lines]
>
> Any help is appreciated.

Reply to this Message

reloanpro - 24 Nov 2005 22:00 GMT

Thanks duane

> you cannot put in expression such as IF(G6>=0.75<0.9,.....
>
[quoted text clipped - 20 lines]
> >
> > Any help is appreciated.

Reply to this Message

Bob Phillips - 24 Nov 2005 21:41 GMT

=IF(AND(G6>=0.9,G6<=1),(E6*G6)*1,IF(AND(G6>=0.75,G6<0.9),(E6*G6)*1.05,IF(AND
(G6>=0.6,G6<0.75),(E6*G6)*1.1,IF(AND(G6>=0.45,G6<0.6),(E6*G6)*1.15,IF(AND(G6

>=0.3,G6<0.45),(E6*G6)*1.2,IF(AND(G6>=0.15,G6<0.3),(E6*G6)*1.25,IF(AND(G6>=0

.1,G6<0.15),(E6*G6)*1.3,0)))))))

or more simply

=(E6*G6)*(IF(AND(G6>=0.9,G6<=1),1,IF(AND(G6>=0.75,G6<0.9),1.05,IF(AND(G6>=0.
6,G6<0.75),1.1,IF(AND(G6>=0.45,G6<0.6),1.15,IF(AND(G6>=0.3,G6<0.45),1.2,IF(A
ND(G6>=0.15,G6<0.3),1.25,IF(AND(G6>=0.1,G6<0.15),1.3,0))))))))

or even simpler

=(E6*G6)*(IF(G6>1,0,IF(G6>=0.9,1,IF(G6>=0.75,1.05,IF(G6>=0.6,1.1,IF(G6>=0.45
,1.15,IF(G6>=0.3,1.2,IF(G6>=0.15,1.25,IF(G6>=0.1,1.3,0)))))))))

or yet simpler

=(E6*G6)*(VLOOKUP(G6,{0,0;0.1,1.3;0.15,1.25;0.3,1.2;0.45,1.15;0.6,1.1;0.75,1
.05;0.9,1},2))

Signature

HTH

RP
(remove nothere from the email address if mailing direct)

> Please help me to see the error in the following formula:

"=IF(G6>=0.9<=1,E6*G6,IF(G6>=0.75<0.9,(E6*G6)*1.05,IF(G6>=0.6<0.75,(E6*G6)*1
.1,IF(G6>=0.45<0.6,(E6*G6)*1.15,IF(G6>=0.3<0.45,(E6*G6)*1.2,IF(G6>=0.15<0.3,
(E6*G6)*1.25,IF(G6>=0.1<0.15,(E6*G6)*1.3,0)))))))"

> The worksheet is returning FALSE to the very first argument, though there is
> a value of 97% in G6. The value of 97% is coming from an INDIRECT formula
> fetching data from another worksheet into G6.
>
> Any help is appreciated.

Reply to this Message

reloanpro - 24 Nov 2005 21:59 GMT

Thanks Bob! The first formula worked like a charm!

> =IF(AND(G6>=0.9,G6<=1),(E6*G6)*1,IF(AND(G6>=0.75,G6<0.9),(E6*G6)*1.05,IF(AND
> (G6>=0.6,G6<0.75),(E6*G6)*1.1,IF(AND(G6>=0.45,G6<0.6),(E6*G6)*1.15,IF(AND(G6
[quoted text clipped - 29 lines]
> >
> > Any help is appreciated.

Reply to this Message