I have used the following statement, but it is returning an incorrect result for some cells. In an cell containing an age of 10 years, 11months..the formula is returning "6m-2y" when it should say older than 2. It also does this for the age 25 years 6 months and 18 years 10 months. Please help. =IF(AND(E203>="0years,0months",E203<="0years,3months"),"0-3",IF(AND(E203>"0years,3months",E203<="0years,6months"),"3-6",IF(AND(E203>"0years,6months",E203<="2years,0months"),"6m - 2y",IF(AND(E203>"2years,0months",E203<="99years,0months"),"Older than 2",""))))

0 |

2/25/2010 8:39:01 PM

It looks like it's returning the correct result to me. The string "10 years, 11months" satisfies the AND(E203>"0years,6months",E203<="2years,0months") condition, because the first digit 1 is greater than 0 and is less than 2. Perhaps you've forgotten that you are comparing text strings, not comparing numbers? -- David Biddulph "ck" <ck@discussions.microsoft.com> wrote in message news:E4760E0F-8923-41C2-ADAB-043C8ACB0FA0@microsoft.com... > I have used the following statement, but it is returning an incorrect > result > for some cells. In an cell containing an age of 10 years, 11months..the > formula is returning "6m-2y" when it should say older than 2. It also > does > this for the age 25 years 6 months and 18 years 10 months. Please help. > > =IF(AND(E203>="0years,0months",E203<="0years,3months"),"0-3",IF(AND(E203>"0years,3months",E203<="0years,6months"),"3-6",IF(AND(E203>"0years,6months",E203<="2years,0months"),"6m > - 2y",IF(AND(E203>"2years,0months",E203<="99years,0months"),"Older than > 2",""))))

0 |

2/25/2010 9:02:44 PM

ck wrote: > I have used the following statement, but it is returning an incorrect result > for some cells. In an cell containing an age of 10 years, 11months..the > formula is returning "6m-2y" when it should say older than 2. It also does > this for the age 25 years 6 months and 18 years 10 months. Please help. > > =IF(AND(E203>="0years,0months",E203<="0years,3months"),"0-3",IF(AND(E203>"0years,3months",E203<="0years,6months"),"3-6",IF(AND(E203>"0years,6months",E203<="2years,0months"),"6m > - 2y",IF(AND(E203>"2years,0months",E203<="99years,0months"),"Older than > 2","")))) What is in E203?

0 |

2/25/2010 9:05:31 PM

ck, I played with your formula and found that if you use 00years,00months -- create the accepability of a double digit number -- which you will have since the months in a year may be a single or a double digit number (0, 1, 2, . . . 9, 10, 11). Just change the formula to read: =IF(AND(E203>="00years,00months",E203<="00years,03months"),"0-3",IF(AND(E203>"00years,03months",E203<="00years,06months"),"3-6",IF(AND(E203>"00years,06months",E203<="02years,00months"),"6m > - 2y",IF(AND(E203>"02years,00months",E203<="99years,00months"),"Older than > 2","")))) "ck" wrote: > I have used the following statement, but it is returning an incorrect result > for some cells. In an cell containing an age of 10 years, 11months..the > formula is returning "6m-2y" when it should say older than 2. It also does > this for the age 25 years 6 months and 18 years 10 months. Please help. > > =IF(AND(E203>="0years,0months",E203<="0years,3months"),"0-3",IF(AND(E203>"0years,3months",E203<="0years,6months"),"3-6",IF(AND(E203>"0years,6months",E203<="2years,0months"),"6m > - 2y",IF(AND(E203>"2years,0months",E203<="99years,0months"),"Older than > 2",""))))

0 |

2/25/2010 9:50:01 PM

e203 contains the age calculated in years and months by using the following formula: =DATEDIF(D203,B203,"y")&"years,"&DATEDIF(D203,B203,"ym")&"months" "Glenn" wrote: > ck wrote: > > I have used the following statement, but it is returning an incorrect result > > for some cells. In an cell containing an age of 10 years, 11months..the > > formula is returning "6m-2y" when it should say older than 2. It also does > > this for the age 25 years 6 months and 18 years 10 months. Please help. > > > > =IF(AND(E203>="0years,0months",E203<="0years,3months"),"0-3",IF(AND(E203>"0years,3months",E203<="0years,6months"),"3-6",IF(AND(E203>"0years,6months",E203<="2years,0months"),"6m > > - 2y",IF(AND(E203>"2years,0months",E203<="99years,0months"),"Older than > > 2","")))) > > What is in E203? > . >

0 |

2/25/2010 10:26:05 PM

I changed the formula and I am still getting errors...Do I need to change anything to create the acceptability of a double digit number? "nita" wrote: > ck, I played with your formula and found that if you use 00years,00months -- > create the accepability of a double digit number -- which you will have since > the months in a year may be a single or a double digit number (0, 1, 2, . . . > 9, 10, 11). Just change the formula to read: > > =IF(AND(E203>="00years,00months",E203<="00years,03months"),"0-3",IF(AND(E203>"00years,03months",E203<="00years,06months"),"3-6",IF(AND(E203>"00years,06months",E203<="02years,00months"),"6m > > - 2y",IF(AND(E203>"02years,00months",E203<="99years,00months"),"Older than > > 2","")))) > > "ck" wrote: > > > I have used the following statement, but it is returning an incorrect result > > for some cells. In an cell containing an age of 10 years, 11months..the > > formula is returning "6m-2y" when it should say older than 2. It also does > > this for the age 25 years 6 months and 18 years 10 months. Please help. > > > > =IF(AND(E203>="0years,0months",E203<="0years,3months"),"0-3",IF(AND(E203>"0years,3months",E203<="0years,6months"),"3-6",IF(AND(E203>"0years,6months",E203<="2years,0months"),"6m > > - 2y",IF(AND(E203>"2years,0months",E203<="99years,0months"),"Older than > > 2",""))))

0 |

2/25/2010 10:28:01 PM

OK, so E203 contains TEXT. Try this instead: =IF(DATEDIF(D203,B203,"M")<3,"0-3",IF(DATEDIF(D203,B203,"M")<6,"3-6", IF(DATEDIF(D203,B203,"M")<24,"6m - 2Y","Older than 2"))) ck wrote: > e203 contains the age calculated in years and months by using the following > formula: =DATEDIF(D203,B203,"y")&"years,"&DATEDIF(D203,B203,"ym")&"months" > > "Glenn" wrote: > >> ck wrote: >>> I have used the following statement, but it is returning an incorrect result >>> for some cells. In an cell containing an age of 10 years, 11months..the >>> formula is returning "6m-2y" when it should say older than 2. It also does >>> this for the age 25 years 6 months and 18 years 10 months. Please help. >>> >>> =IF(AND(E203>="0years,0months",E203<="0years,3months"),"0-3",IF(AND(E203>"0years,3months",E203<="0years,6months"),"3-6",IF(AND(E203>"0years,6months",E203<="2years,0months"),"6m >>> - 2y",IF(AND(E203>"2years,0months",E203<="99years,0months"),"Older than >>> 2","")))) >> What is in E203? >> . >>

0 |

2/26/2010 2:22:45 PM

Did you remember to change your E203 formula to give 2 digits for years and months? Does your formula look like: =TEXT(DATEDIF(D203,B203,"y"),"00")&"years,"&TEXT(DATEDIF(D203,B203,"ym"),"00")&"months" ? -- David Biddulph "ck" <ck@discussions.microsoft.com> wrote in message news:3026BBDF-0714-4116-967A-FBD5822FA5B4@microsoft.com... > I changed the formula and I am still getting errors...Do I need to change > anything to create the acceptability of a double digit number? > > "nita" wrote: > >> ck, I played with your formula and found that if you use >> 00years,00months -- >> create the accepability of a double digit number -- which you will have >> since >> the months in a year may be a single or a double digit number (0, 1, 2, . >> . . >> 9, 10, 11). Just change the formula to read: >> >> =IF(AND(E203>="00years,00months",E203<="00years,03months"),"0-3",IF(AND(E203>"00years,03months",E203<="00years,06months"),"3-6",IF(AND(E203>"00years,06months",E203<="02years,00months"),"6m >> > - 2y",IF(AND(E203>"02years,00months",E203<="99years,00months"),"Older >> > than >> > 2","")))) >> >> "ck" wrote: >> >> > I have used the following statement, but it is returning an incorrect >> > result >> > for some cells. In an cell containing an age of 10 years, >> > 11months..the >> > formula is returning "6m-2y" when it should say older than 2. It also >> > does >> > this for the age 25 years 6 months and 18 years 10 months. Please >> > help. >> > >> > =IF(AND(E203>="0years,0months",E203<="0years,3months"),"0-3",IF(AND(E203>"0years,3months",E203<="0years,6months"),"3-6",IF(AND(E203>"0years,6months",E203<="2years,0months"),"6m >> > - 2y",IF(AND(E203>"2years,0months",E203<="99years,0months"),"Older than >> > 2",""))))

0 |

2/26/2010 3:43:01 PM

You are a genious! Thank you! Thank you! "Glenn" wrote: > OK, so E203 contains TEXT. Try this instead: > > =IF(DATEDIF(D203,B203,"M")<3,"0-3",IF(DATEDIF(D203,B203,"M")<6,"3-6", > IF(DATEDIF(D203,B203,"M")<24,"6m - 2Y","Older than 2"))) > > > > ck wrote: > > e203 contains the age calculated in years and months by using the following > > formula: =DATEDIF(D203,B203,"y")&"years,"&DATEDIF(D203,B203,"ym")&"months" > > > > "Glenn" wrote: > > > >> ck wrote: > >>> I have used the following statement, but it is returning an incorrect result > >>> for some cells. In an cell containing an age of 10 years, 11months..the > >>> formula is returning "6m-2y" when it should say older than 2. It also does > >>> this for the age 25 years 6 months and 18 years 10 months. Please help. > >>> > >>> =IF(AND(E203>="0years,0months",E203<="0years,3months"),"0-3",IF(AND(E203>"0years,3months",E203<="0years,6months"),"3-6",IF(AND(E203>"0years,6months",E203<="2years,0months"),"6m > >>> - 2y",IF(AND(E203>"2years,0months",E203<="99years,0months"),"Older than > >>> 2","")))) > >> What is in E203? > >> . > >> > . >

0 |

2/26/2010 5:51:02 PM

I'm setting up a spreadsheet using dropdown lists that a bank will use to come up with a risk rating on a loan. For instance: The first dropdown is a list of class codes: 1 = auto loan, 2 = home loan, etc... The second is a list of collateral codes: 1000 = savings/CD's, 2000 = business assets, 3000 = residence, etc... I'd like to narrow the second list based on the first one, since certain types of loans can only take certain types of collateral. I know how to do this in MS Access, but I'm not sure how in Excel. Each set of codes has its own sheet (i.e."LkupCl...

Hi I hope you can help me with this it is driving me nuts. I've created a database which I needed to give multiple people access to but didn't want them messing so I created an MDE file. I ensured that the MDB file was set to shared, not compacting and set to run users not owners permissions before I converted. However, if one of the users goes into the database, it does not create and LDB file and if anyone else tries to get it, the error message: Could not use <name>; file already in use. (Error 3045) appears. Some of the tables in the MDE files were linking to and MDB fi...

I'm not real familiar with outlook, we use outlook express in our office however new person wants to use "outlook" for email because he wants to transfer the contacts he had from his old company. We have office 2000 here and he can use outlook, that's fine. The problem is, I've imported his contacts and now when I open the Outlook program I get the following error: "A program is trying to access email addressess you have stored in outlook. Do you want to allow this? If this is unexpected, it may be a virus and you should choose no. Allow access for 1, 2, 5...

I'm not well enough versed in spreadsheet use to calculate some intermediate values I need. I would be greatful for anyone who can offer a formula or suggestion. I have 1 guidline column, numbered 1-30. Then 2 other columns, A & B are beside it. The values in A & B change as they go from 1-30, they change at different rates. I have some intermediary values at given points but need to calculate what the values in between would be, based on the fixed values at 1, 4, 8, 15 and 30. Below, I have shown the layout as best I can without including an attachment. Thanks. chris@cjalexander...

. Reply (E-mail) Forward (E-mail) Subject: Can anyone help with Outlook 2002 Problems From: "robin hickey" <robin@e-s-p.eu.com> Sent: 3/3/2004 9:38:36 AM Hi, I have Office XP pro, which I upgrated to because my other installation of 2000 still had the following problems... When I send an e-mail it is received by whoever it is sent to, and it appears in the outbox before it is sent, but then just disapeers without being able to be found anywhere?? Is this a virus? Also, when creating my own letterheads for two different e- mail accounts, but using one...

If I need to look something up related to Printer I don't want the help program to tell me how to "Find and install printer drivers for Windows Vista" followed by "Find printer drivers for Windows XP". I entered Printer into Outlook help and the first ten results were: Add or remove a printer Change your default printer Find and install printer drivers for Windows Vista Find a printer manufacturer's Web site Find printer drivers for Windows XP Get the version number for your Office program and information about your computer Change an ink or ton...

Ok, this is for more advanced Excel users. Here's the issue. Lets say I have two sheets in Excel, S1 and S2 respectively. Let's say in S1, if I type a 4-digit number IN COLUMN A, which is actually an ID number for a person, I want their name to automatically appear in COLUMN B from a list in S2. This is how S2's list is set up... A1 has a person's ID #, and B1 has that same persons name. A2 has a different person's ID #, and B2 has that person's name, etc... So when I type the ID # in S1 in column A, it will lookup in S2 and match that person's nam...

Ok hoping you guys can help me like you have in the past. I am trying to create a user form to display customer details in a particular way. The form is in 3 columns Col1 = vertical scrolling column to show all customers name (you highlight the name you want not double click) Col2 = Box showing address of highlighted selection Col3 = Box showing further details from another section of the database ie prices I saw this setup on a fancy Nokkia mobile phone and just saw it as a great layout, the part that I will struggle on is creating the coding for when I highlight. Table...

I am trying to use an if statement that checks for a value across multiple cells and I can't seem to get it to work. I am not sure if I am supposed to use the COUNT or not. As an example I have 5 cells c1 c2 c3 c4 c5 And the value in these cells is either going to be Yes or No Then I have cell d1 This cell will have a value that is a number I need to use the above data in 2 different ways - one and IF(AND and one in and IF(Or I came up with the following ideas but neither seem to work: if(and(c1:c5="Yes",d1<1),"Good",If....(checks so...

I am trying to set up a production flow excel sheet. I would part of a row of information to move to the next work sheet when one specific column in the row is filled in. To move client information from one department to the next when it is initialed out of the first department? Is this too much to ask? Seems like it should be simple enough but I have limited knowledge of Excel. Any help greatly appreciated. some expert may give a better solution. meanwhile name the range e.g. "alpha" go to sheet2 and highlight the same no. of cells in a row and in the first cell type =al...

When i'm on Outlook, how can i know which user account i use? but when i start Microsoft CRM, i know which user i am because i must log in! Could you tell me, what is the problem? I wish you understand what i said Thanks to reply ------=_NextPart_0001_42E90E06 Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi and thanks for posting your question. When you launch Outlook, it will use the credentials that you use to log on to the computer. This will be a domain account, not a local machine account. When Outlook is launched for the first time, it will create a profile fo...

I need to determine a value that is predicated upon the selections of about 6 or so drop down menus. It'd be General Text within drop-downs but final value is an Accountant price Basically if someone selects Option #1 out of Cells A-G's drop down menus, then the value of H1 = a cell on another page that fits the category of 1- 1-1-1-1-1 from the drop down menus. I don't know how to do this. I imagine it's possible but have no clue. Your help is greatly appreciated. From what you say, the meat of this problem lies in the layout/format of the data you have in this "...

I need some assistance writing a formula that will count how many dates (in a list of dates in m/d/y format) are in each month. So... Column A [1/1/08, 2/7/08, 10/19/08, ...] Column B [Months, Jan, Feb, Mar, ...] Column C [Counts, {count of dates in January}, {count of dates in February}, ...] Make sense? The obvious answer would be to use another column to extract the month values and then just perform a COUNTIF function. But I can't use an extra column. Any help is appricated. Thanks, Shelton Try this in C1: =3DSUMPRODUCT(--(MONTH(A$1:A$100)=3DROW(A1)),--((A$1:A$100)<>"...

Does anyone know of a free HTML Help 2 reader? Microsoft has completely redone their docs, so that for most help topics it's all or nothing: Install .Net and the entire Windows SDK -- which comes with Help 2 files and the necessary reader software -- or get no docs at all. That's about 1GB of stuff that I neither want nor. (Since most of it is just .Net docs.) I just want access to the latest versions of things like WMI help that used to come as independent downloads containing a CHM file. On Thu, 25 Mar 2010 15:25:00 GMT, mayayana wrote: > Does anyone kno...

I've got Outlook 2003 and got a POP3 mail account set up. However I want to do a fresh installation of Windows and will lose my account details. I do not remember my password and was wondering how can I retreive my password for the "Logon Information" and also "More Settings | Outgoing Server" passwords. Since the passwords are in ****** format. I assume the passwords are stored in Outlook files encrypted right? KevinGPO, you wrote on Thu, 15 Dec 2005 09:20:48 -0000: > I've got Outlook 2003 and got a POP3 mail account set up. However I want to > d...

My Outlook 2003 testing on POP 3 indicates everything is okay. But when ever I go to send or receive anything it always show the "Operation failed. Object could not be found." Need help because Microsoft sure as heck doesn't have any solution for this problem. Thank you You've provided no information that would permit anyone to answer. State your how you configured your information store. State whether Outlook ever worked for you and if so the steps you used to corrupt your Outlook profile. -- Russ Valentine [MVP-Outlook] "Bushie" <Bushie@discussions.mic...

I'm trying to use "IF" to do a calculation. What I need to do is run one of two calculations based on the contents of a cell. Example: If A1="Salary" Sum(B1*12). If A1="Hourly" Sum(B1*2080). A1 is always going to be either Salary or Hourly and B1 is the rate of Pay. This is to calculate an annual salary. Please help =IF(A1="Hourly",B1*12,B1*2080) Or, slightly shorter =B1*IF(IfA1="Hourly",12,2080) Note that you don't need SUM -- Kind regards, Niek Otten Microsoft MVP - Excel "robertm600635" <robertm600635@discus...

Hi. I'm trying to write simple code on a subforms On Current event to disable/enable a command button based on the value of a text box. The textbox is formatted to currency. Here's a sample of what I need but it's not working (I think because of the currency). if me.totalpurchase > 250 then cmd.PrintGC.enabled = true else cmd.PrintGC.enabled = false end if Hope someone can help. cathywoodford@personainternet.com wrote in news:1191601909.233057.279720@o80g2000hse.googlegroups.com: > Hi. I'm trying to write simple code on a subforms On Current > event to di...

I have a data table which im trying to produce a some charts from but cant seem to get it right. bit hard to explain without seeing the table so I have provided the dummy workbook im working on at www.darkcity.nildram.co.uk/test.xls in this example ive got the big data table, and the type of chart im trying to produce form it us just below. the problem is ive had to manually create another smaller table under the big one in order to get the chart to look anything like what its meant to. i really need to have 3 charts produced based on whats in the big table if possible, I need charts by we...

I would like to know how to plot multiple sets of data on a scatter graph for example i have 3 sets of data for three types of lighting systems, red, green and blue i want to plot the voltage againt the current, and i want all the data on one graph so i can compare them, how do i do it? Frances I suggest you try the following. Plot a scatter graph as normal with your first set of data. Then to add more data to the scatter graph... Right click on chart and select <Source Data> and select the <Series> tab. Select <Add> in the series dialog box and type in the relevan...

CRM 3.0 refuses to install on my SBS 2003 R2 premium server. I was encountering the known issues with CRM 3 on SBS R2 using the CDs that came in the Action Pack subscription so I downloaded the updated CRM CDs from http://www.microsoft.com/downloads/details.aspx?FamilyID=7d418781-69ad-422d-92fa-87fdb2538e2c&DisplayLang=en This copy gave me a different set of problems. The default setup mode pops up a couple errors. The first one is the lack of full text search so I installed that and ran the setup again. The next error was: "The edition 'Workgroup Edition' of the specified SQL...

I have a workbook with 2 worksheets. Information and Table in the worksheet Information, cell S24 if the number is greater than or equal to $100,000 but less than $149,999 then I'd like it to put in cell S25 the number $3000. Also if the number is between $150,000 to $199,999 i'd like it to put in $3500 in the S25 cell. Any help would be highly appreciated... Thanks alex -- alexm999 ------------------------------------------------------------------------ alexm999's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=4918 View this thread: http://www.excel...

Hi all Unable to add a new contact in CRM 4.0 as it errors out saying "Object reference not set to an instance of an object". Here is the event log information. Web Service Plug-in failed in OrganizationId: 01f0210d-6bf6-4c27-8919-00eccab73e2d; SdkMessageProcessingStepId: ab0fa3e5-3ceb-dc11-8840-0003ffbb159c; EntityName: contact; Stage: 50; MessageName: Create; AssemblyName: AlertCRM40Plugin.CRM40PostPlugIn, AlertCRM40Plugin, Version=1.0.0.0, Culture=en-US, PublicKeyToken=4406fa73b5445f20; ClassName: AlertCRM40Plugin.CRM40PostPlugIn; Exception: Unhandled Exception: System....

I'm struggling with the syntax of the open statement, the helpfil states it as -Open pathname For mode [Access access] [lock] As [#]filenumbe [Len=reclength]- I know this makes me look thick but I've tried various things that hav all failed, can someone give me an example of how this would look for file named "carrier" that I wanted to input to as the mode? Thank -- carl0s6 ----------------------------------------------------------------------- carl0s66's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1662 View this thread: http://www.excelf...

Hi, I am running a G5 Imac 1.8Ghz and a G4 Powerbook 1.5GHz and have been happily running office X on both machines for as long as I've owned them (1 and 2 years respectively) Before the Pbook, I also had an I book running Office X for 18 months with no troubles. I just installed Office 2004 2 weeks ago, and am consistently noticing, on both machines, serious system slowdown over the course of the day. To the point where, by mid-afternooon, my system needs to be restarted. The symptoms are identical on both macs (naturally the Pbook runs even slower than the G5 as these problems advan...