software

How can I get around the 7 nesting limitations. This is my formula
=IF(AND($A$10=quot;quanta utility services,
LLC.quot;,$G$27lt;499),0.33,IF(AND($A$10=quot;quanta utility services,
LLC.quot;,$G$27gt;=499,$G$27lt;=999),0.75,IF(AND($A$10=quot;qu anta utility services,
LLC.quot;,$G$27gt;=1000,$G$27lt;=1999),0.99,IF(AND($A$10=quot; quanta utility services,
LLC.quot;,$G$27gt;=2000,$G$27lt;=4999),9.99,IF(AND($A$10=quot; quanta utility services,
LLC.quot;,$G$27gt;=5000),19.99)))))
This works but I would like to have multiple values for A10. I tried to copy
it and change the value of a10 each time but I can only add 2 more.
Example:
=IF(AND($A$10=quot;quanta utility services,
LLC.quot;,$G$27lt;499),0.33,IF(AND($A$10=quot;quanta utility services,
LLC.quot;,$G$27gt;=499,$G$27lt;=999),0.75,IF(AND($A$10=quot;qu anta utility services,
LLC.quot;,$G$27gt;=1000,$G$27lt;=1999),0.99,IF(AND($A$10=quot; quanta utility services,
LLC.quot;,$G$27gt;=2000,$G$27lt;=4999),9.99,IF(AND($A$10=quot; quanta utility services,
LLC.quot;,$G$27gt;=5000),19.99)))))=IF(AND($A$10=quot;quanta utility services,
LLC.quot;,$G$27lt;499),0.33,IF(AND($A$10=quot;Pumpco.quot;,$G$27 gt;=499,$G$27lt;=999),0.75,IF(AND($A$10=quot;Pumpco.quot;,$G$2 7gt;=1000,$G$27lt;=1999),0.99,IF(AND($A$10=quot;Pumpco.quot;,$ G$27gt;=2000,$G$27lt;=4999),9.99,IF(AND($A$10=quot;Pumpco. quot;,$G$27gt;=5000),19.99)))))
This returns a value of false.
Any help would be appreciated
Thanks

Look in HELP for the VLOOKUP function.
Additional help can be found he

www.contextures.com/xlFunctions02.html

--
Kind regards,

Niek Otten

quot;Mike1quot; gt; wrote in message
...
gt; How can I get around the 7 nesting limitations. This is my formula
gt; =IF(AND($A$10=quot;quanta utility services,
gt; LLC.quot;,$G$27lt;499),0.33,IF(AND($A$10=quot;quanta utility services,
gt; LLC.quot;,$G$27gt;=499,$G$27lt;=999),0.75,IF(AND($A$10=quot;qu anta utility services,
gt; LLC.quot;,$G$27gt;=1000,$G$27lt;=1999),0.99,IF(AND($A$10=quot; quanta utility services,
gt; LLC.quot;,$G$27gt;=2000,$G$27lt;=4999),9.99,IF(AND($A$10=quot; quanta utility services,
gt; LLC.quot;,$G$27gt;=5000),19.99)))))
gt; This works but I would like to have multiple values for A10. I tried to
gt; copy
gt; it and change the value of a10 each time but I can only add 2 more.
gt; Example:
gt; =IF(AND($A$10=quot;quanta utility services,
gt; LLC.quot;,$G$27lt;499),0.33,IF(AND($A$10=quot;quanta utility services,
gt; LLC.quot;,$G$27gt;=499,$G$27lt;=999),0.75,IF(AND($A$10=quot;qu anta utility services,
gt; LLC.quot;,$G$27gt;=1000,$G$27lt;=1999),0.99,IF(AND($A$10=quot; quanta utility services,
gt; LLC.quot;,$G$27gt;=2000,$G$27lt;=4999),9.99,IF(AND($A$10=quot; quanta utility services,
gt; LLC.quot;,$G$27gt;=5000),19.99)))))=IF(AND($A$10=quot;quanta utility services,
gt; LLC.quot;,$G$27lt;499),0.33,IF(AND($A$10=quot;Pumpco.quot;,$G$27 gt;=499,$G$27lt;=999),0.75,IF(AND($A$10=quot;Pumpco.quot;,$G$2 7gt;=1000,$G$27lt;=1999),0.99,IF(AND($A$10=quot;Pumpco.quot;,$ G$27gt;=2000,$G$27lt;=4999),9.99,IF(AND($A$10=quot;Pumpco. quot;,$G$27gt;=5000),19.99)))))
gt; This returns a value of false.
gt; Any help would be appreciated
gt; Thanks
Mike,

Try this

=IF(ISNUMBER(MATCH($A$10,{quot;Pumpcoquot;,quot;quanta utility services,
LLC.quot;},0)),VLOOKUP($G$27,{0,0.33;500,0.75;1000,0.9 ;2000,9.99;5000,19.99},2),
quot;quot;)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Mike1quot; gt; wrote in message
...
gt; How can I get around the 7 nesting limitations. This is my formula
gt; =IF(AND($A$10=quot;quanta utility services,
gt; LLC.quot;,$G$27lt;499),0.33,IF(AND($A$10=quot;quanta utility services,
gt; LLC.quot;,$G$27gt;=499,$G$27lt;=999),0.75,IF(AND($A$10=quot;qu anta utility services,
gt; LLC.quot;,$G$27gt;=1000,$G$27lt;=1999),0.99,IF(AND($A$10=quot; quanta utility services,
gt; LLC.quot;,$G$27gt;=2000,$G$27lt;=4999),9.99,IF(AND($A$10=quot; quanta utility services,
gt; LLC.quot;,$G$27gt;=5000),19.99)))))
gt; This works but I would like to have multiple values for A10. I tried to
copy
gt; it and change the value of a10 each time but I can only add 2 more.
gt; Example:
gt; =IF(AND($A$10=quot;quanta utility services,
gt; LLC.quot;,$G$27lt;499),0.33,IF(AND($A$10=quot;quanta utility services,
gt; LLC.quot;,$G$27gt;=499,$G$27lt;=999),0.75,IF(AND($A$10=quot;qu anta utility services,
gt; LLC.quot;,$G$27gt;=1000,$G$27lt;=1999),0.99,IF(AND($A$10=quot; quanta utility services,
gt; LLC.quot;,$G$27gt;=2000,$G$27lt;=4999),9.99,IF(AND($A$10=quot; quanta utility services,
gt; LLC.quot;,$G$27gt;=5000),19.99)))))=IF(AND($A$10=quot;quanta utility services,
gt;
LLC.quot;,$G$27lt;499),0.33,IF(AND($A$10=quot;Pumpco.quot;,$G$27 gt;=499,$G$27lt;=999),0.75,IF(
AND($A$10=quot;Pumpco.quot;,$G$27gt;=1000,$G$27lt;=1999),0.99, IF(AND($A$10=quot;Pumpco.quot;,$G$
27gt;=2000,$G$27lt;=4999),9.99,IF(AND($A$10=quot;Pumpco.quot;, $G$27gt;=5000),19.99)))))
gt; This returns a value of false.
gt; Any help would be appreciated
gt; Thanks