nesting issue in functions

[Mike1]

How can I get around the 7 nesting limitations. This is my formula
=IF(AND($A$10="quanta utility services,
LLC.",$G$27<499),0.33,IF(AND($A$10="quanta utility services,
LLC.",$G$27>=499,$G$27<=999),0.75,IF(AND($A$10="quanta utility services,
LLC.",$G$27>=1000,$G$27<=1999),0.99,IF(AND($A$10="quanta utility services,
LLC.",$G$27>=2000,$G$27<=4999),9.99,IF(AND($A$10="quanta utility services,
LLC.",$G$27>=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="quanta utility services,
LLC.",$G$27<499),0.33,IF(AND($A$10="quanta utility services,
LLC.",$G$27>=499,$G$27<=999),0.75,IF(AND($A$10="quanta utility services,
LLC.",$G$27>=1000,$G$27<=1999),0.99,IF(AND($A$10="quanta utility services,
LLC.",$G$27>=2000,$G$27<=4999),9.99,IF(AND($A$10="quanta utility services,
LLC.",$G$27>=5000),19.99)))))=IF(AND($A$10="quanta utility services,
LLC.",$G$27<499),0.33,IF(AND($A$10="Pumpco.",$G$27>=499,$G$27<=999),0.75,IF(AND($A$10="Pumpco.",$G$27>=1000,$G$27<=1999),0.99,IF(AND($A$10="Pumpco.",$G$27>=2000,$G$27<=4999),9.99,IF(AND($A$10="Pumpco.",$G$27>=5000),19.99)))))
This returns a value of false.
Any help would be appreciated
Thanks

[Niek Otten]

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

http://www.contextures.com/xlFunctions02.html

--
Kind regards,

Niek Otten

"Mike1" <[email protected]> wrote in message
news:[email protected]...
> How can I get around the 7 nesting limitations. This is my formula
> =IF(AND($A$10="quanta utility services,
> LLC.",$G$27<499),0.33,IF(AND($A$10="quanta utility services,
> LLC.",$G$27>=499,$G$27<=999),0.75,IF(AND($A$10="quanta utility services,
> LLC.",$G$27>=1000,$G$27<=1999),0.99,IF(AND($A$10="quanta utility services,
> LLC.",$G$27>=2000,$G$27<=4999),9.99,IF(AND($A$10="quanta utility services,
> LLC.",$G$27>=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="quanta utility services,
> LLC.",$G$27<499),0.33,IF(AND($A$10="quanta utility services,
> LLC.",$G$27>=499,$G$27<=999),0.75,IF(AND($A$10="quanta utility services,
> LLC.",$G$27>=1000,$G$27<=1999),0.99,IF(AND($A$10="quanta utility services,
> LLC.",$G$27>=2000,$G$27<=4999),9.99,IF(AND($A$10="quanta utility services,
> LLC.",$G$27>=5000),19.99)))))=IF(AND($A$10="quanta utility services,
> LLC.",$G$27<499),0.33,IF(AND($A$10="Pumpco.",$G$27>=499,$G$27<=999),0.75,IF(AND($A$10="Pumpco.",$G$27>=1000,$G$27<=1999),0.99,IF(AND($A$10="Pumpco.",$G$27>=2000,$G$27<=4999),9.99,IF(AND($A$10="Pumpco.",$G$27>=5000),19.99)))))
> This returns a value of false.
> Any help would be appreciated
> Thanks

[Bob Phillips]

Mike,

Try this

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Mike1" <[email protected]> wrote in message
news:[email protected]...
> How can I get around the 7 nesting limitations. This is my formula
> =IF(AND($A$10="quanta utility services,
> LLC.",$G$27<499),0.33,IF(AND($A$10="quanta utility services,
> LLC.",$G$27>=499,$G$27<=999),0.75,IF(AND($A$10="quanta utility services,
> LLC.",$G$27>=1000,$G$27<=1999),0.99,IF(AND($A$10="quanta utility services,
> LLC.",$G$27>=2000,$G$27<=4999),9.99,IF(AND($A$10="quanta utility services,
> LLC.",$G$27>=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="quanta utility services,
> LLC.",$G$27<499),0.33,IF(AND($A$10="quanta utility services,
> LLC.",$G$27>=499,$G$27<=999),0.75,IF(AND($A$10="quanta utility services,
> LLC.",$G$27>=1000,$G$27<=1999),0.99,IF(AND($A$10="quanta utility services,
> LLC.",$G$27>=2000,$G$27<=4999),9.99,IF(AND($A$10="quanta utility services,
> LLC.",$G$27>=5000),19.99)))))=IF(AND($A$10="quanta utility services,
>
LLC.",$G$27<499),0.33,IF(AND($A$10="Pumpco.",$G$27>=499,$G$27<=999),0.75,IF(
AND($A$10="Pumpco.",$G$27>=1000,$G$27<=1999),0.99,IF(AND($A$10="Pumpco.",$G$
27>=2000,$G$27<=4999),9.99,IF(AND($A$10="Pumpco.",$G$27>=5000),19.99)))))
> This returns a value of false.
> Any help would be appreciated
> Thanks