 |
 | 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 / August 2007Calculating using IF and AND to show numbers based on %
Hi, I have an issues which should be solved in Excel, but I cannot seem to find an answer, here's the problem. In cel H2 is a percentage of target realisation. In cel I2 is the formula which will give a numer of points based on the percentage in H2. E.G: if H2 has the percentage 100% then cel I2 should show the number "30" if H2 has the percentage 100,07% then cell I2 should show the number "30" if H2 has the percentage 102% then cell I2 should show the number "40" This is the table on which the point are given: 0-100% = 0 (points) 100 till 101 = 30 (points) 101 till 105 = 40 (points) 105 + = 50 (points) For some reason I cannot seem to get this to work... Can anyone please help me out here as I am drastically stuck... :( Thanks in advance Try this in I2: =IF(H2>=1.05,50,IF(H2>=1.01,40,IF(H2>=1,30,0))) Hope this helps. Pete On Aug 7, 2:10 pm, bloemk...@gmail.com wrote: > Hi, > [quoted text clipped - 23 lines] > > Thanks in advance Hi Pete, For some reason I still get an error message....strange. And of course the error messages in excel aren''t that helpfull. In H2 is the following formula: =(D2+E2+F2+G2)/C2 (This calculates the percentage of the 4 weeks in a month). Could you pleaaaaaaase, check the formula if there is an error of some kind, I tried looking at it, but my eyes started roling about. :) Thanks in advance Pete!!!! What error message do you get? If it is #DIV?0 then you need to check that the value of C2 is not zero. If you get #VALUE then one of the values in D2, E2, F2 or G2 could be text. There are no errors in the formula I gave you, but if you copied it directly from the NG you might have encountered a spurious line-wrap (this tends to give you hyphens in the middle of the formula). Make sure you type in the full- stops and commas as given in the formula - depending on where you are based, you might have to use semi-colons instead of the commas. Hope this helps. Pete > Hi Pete, > [quoted text clipped - 13 lines] > > Thanks in advance Pete!!!! What I forgot to mention is the following VERY IMPORTANT part; The values in between the 100 - 101 and the 101 - 105 also count. So the values between 101-105 should result in 40 points... The first part of the formula checks if H2 is greater than or equal to 105% - if so, then it returns 50. The second part checks to see whether it is larger or equal to 101% (and, because it will have got past the first IF, it will be less than 105% - thus H2 can be any number between 101% and 104.999999999 % inclusive to give 40 points. Is this what you meant? It is unclear in your first example whether 101% belongs to the 30-point range or to the 40-point range, or whether 105% should yield 40 points or 50 points. Please clarify Pete > What I forgot to mention is the following VERY IMPORTANT part; > > The values in between the 100 - 101 and the 101 - 105 also count. > So the values between 101-105 should result in 40 points... Do you have an email address? I can send it to you....? OK, send it to: pashurst <at> auditel.net Make the obvious changes. Pete > Do you have an email address? > I can send it to you....?
|
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.