 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2007Multiple nested if statements
Hi, I have a list of 10 possible categories of debt, i.e: A 0 - 100 B 101 - 200 C 201 - 300 D 301 - 400 E 401 - 500 etc. I also have a list of debts, by invoice, with varying amounts, i.e: Invoice 1 $159 Invoice 2 $345 Invoice 3 $677 Against the invoice I want to add the category it falls into, i.e A,B,C etc. Because there are 10 categories there are too many to use =if(.... how can I do this? Thank you Amanda With an invoice in C5 try: =CHAR(64+FLOOR(C5,100)/100)  SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk > Hi, > [quoted text clipped - 21 lines] > > Amanda No make that: =CHAR(65+FLOOR(C5,100)/100) SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk > With an invoice in C5 try: > =CHAR(64+FLOOR(C5,100)/100) [quoted text clipped - 24 lines] >> >> Amanda Hi, Beyond $600 the bandings widen, so Category E = 500 - 600 Category F = 600 - 1,000 Category G = 1,000 - 15,000 and so on. Can it cope with this scenario? Thanks very much Amanda > With an invoice in C5 try: > =CHAR(64+FLOOR(C5,100)/100) [quoted text clipped - 24 lines] > > > > Amanda Amanda, In that case try: =LOOKUP(C5,{100,200,300,400,500,600,1000,1500,2000,2500},{"A","B","C","D","E","F","G","H","I","J"}) you could also put the 100 - 2500 and A - J in cells and reference them like: =LOOKUP(C5,I1:I10,J1:J10) notice that the letters do not need quotes around them when in cells. The advantage of the second method is that it is easier to chnge the ranges SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk > Hi, > [quoted text clipped - 41 lines] >> > >> > Amanda
|
Reply to this Message |
 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.