Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / March 2008

Tip: Looking for answers? Try searching our database.

Need more than 7 formulas in one cell

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Askew in help - 28 Mar 2008 05:53 GMT
Here is the formula but it has more formulas than allowed in one cell. How do
I fix this error to keep the result in one field????

=IF(F20>=G21,10%,IF(F20>=G22,9%,IF(F20>=G23,8%,IF(F20>=G24,7%,IF(F20>=G25,6%,IF(F20>=G26,5%,IF(F20>=G27,4%,IF(F20>=G28,3%,IF(F20>=G29,"2%",IF(F20>=G30,"1%",IF(F20<=G30,"0%")))))))))))
T. Valko - 28 Mar 2008 07:08 GMT
If you "flip" your range values so that they're in ascending order rather
than descending order:

=IF(F20<G21,0,MATCH(F20,G21:G30)/100)

Format as Percentage

Signature

Biff
Microsoft Excel MVP

> Here is the formula but it has more formulas than allowed in one cell. How
> do
> I fix this error to keep the result in one field????
>
> =IF(F20>=G21,10%,IF(F20>=G22,9%,IF(F20>=G23,8%,IF(F20>=G24,7%,IF(F20>=G25,6%,IF(F20>=G26,5%,IF(F20>=G27,4%,IF(F20>=G28,3%,IF(F20>=G29,"2%",IF(F20>=G30,"1%",IF(F20<=G30,"0%")))))))))))
BOONER - 28 Mar 2008 07:50 GMT
If you place your percentages in column "H" next to their corresponding
values in "G", then you can simply use a VLOOKUP.  If you use "TRUE" for the
range_lookup, Excel will lookup the closest value that is greater than or
equal to the value that you are searching for.  Just make sure that  column G
is sorted from smallest to largest.  For example, you could use  
VLOOKUP(F20,$G:$H,2,TRUE) for the following data:

G      H

0      0%
5      1%
10    2%
20    3%
30    4%
40    5%
50    6%
60    7%
75    8%
100  9%

There is no limit here, so you can add as many parameters as you like up to
the row limit of your version of Excel.  You can create the reverse effect
(<=) by sorting from largest to smallest in column "G".

-BN

> Here is the formula but it has more formulas than allowed in one cell. How do
> I fix this error to keep the result in one field????
>
> =IF(F20>=G21,10%,IF(F20>=G22,9%,IF(F20>=G23,8%,IF(F20>=G24,7%,IF(F20>=G25,6%,IF(F20>=G26,5%,IF(F20>=G27,4%,IF(F20>=G28,3%,IF(F20>=G29,"2%",IF(F20>=G30,"1%",IF(F20<=G30,"0%")))))))))))
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.