|
Error in "IF" formula
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*G 6)*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. |
Error in "IF" formula
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) "reloanpro" wrote: 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*G 6)*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. |
Error in "IF" formula
=IF(AND(G6=0.9,G6<=1),(E6*G6)*1,IF(AND(G6=0.75,G 6<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(G61,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)) -- HTH RP (remove nothere from the email address if mailing direct) "reloanpro" wrote in message ... 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. |
Error in "IF" formula
Thanks Bob! The first formula worked like a charm!
"Bob Phillips" wrote: =IF(AND(G6=0.9,G6<=1),(E6*G6)*1,IF(AND(G6=0.75,G 6<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(G61,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)) -- HTH RP (remove nothere from the email address if mailing direct) "reloanpro" wrote in message ... 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. |
Error in "IF" formula
Thanks duane
"duane" wrote: 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) "reloanpro" wrote: 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*G 6)*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. |
| All times are GMT +1. The time now is 02:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com