What is wrong with this IF formula, it worked fine for a while?

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

Guest

Here is the formula that worked fine for a while now it wont work.

=IF(G6<101,"6"",IF(AND(G6>101.1,G6<151),"7"",IF(AND(G6>151.1,G6<201),"8"",IF(AND(G6>200.1,G6<275.1),"9"",IF(AND(G6>275.2,G6<350.1),"10"",IF(AND(G6>350.2,G6<600.1),"12"",)))))

It keeps going back to the "7"" here-------------^ and highlighting it.
It worked fine for a while then I added something and now I cannot figure
our what is wrong please help thanks.
 
You just have an extra quotation mark after each number, try this:

=IF(G6<101,"6",IF(AND(G6>101.1,G6<151),"7",IF(AND(G6>151.1,G6<201),"8",IF(AND(G6>200.1,G6<275.1),"9",IF(AND(G6>275.2,G6<350.1),"10",IF(AND(G6>350.2,G6<600.1),"12",))))))
 
Do the extra quotation marks denote inches?

If this is the case then I think you are confusing the machine b
having all the quotation marks. It is reading some of the functions a
text. Could you replace the quotation marks with "in" so it would read

"6 in"

instead of

"6""
 
You can also add an extra quotation after the number to close out that
quote and it will return a value like 6".

The formula would look like

=IF(G6<101,"6""",>... (Note the 3 quotation marks after 6.
 
I noticed a few gaps in your formula, for instance
G6<151),IF(G6>151.1
what happens if G6 is 151 or 151.1?? or G6 is over 601?
.....shouldn't it be
G6<=151),IF(G6>=151.1
In any case, here's something a bit shorter that will do the same thing:
=LOOKUP(G6,{0,101.1,151.1,201.1,275.2,350.2,600.1},{6,7,8,9,10,12,0})&CHAR(34)
wasn't sure what happens when G6 is over 601 so I put a "0", change it to
suit your needs.

HTH
JG
 
Back
Top