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

[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.

 

[Guest]

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",))))))

 

[mphell0]

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""

 

[mphell0]

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.

 

[Guest]

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