 |
 | 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 / November 2006if then help please
I have three columns, B, C and D. In B I would have ranges such as 1-5 6-10 and so on. In D I would have yes or now, paid or not paid. I would kind of like it to be checked or not checked if possible. C must be something like "if B is 1,2,3,4 or 5, then xxx and if D is yes or checked it is a positive and if it is empty then it is a negative." I kind of have an idea how to do this, but nothing I try works. How do I accomplish this? TIA Steven "A democracy is nothing more than mob rule, where fifty-one percent of the people may take away the rights of the other forty-nine." - Thomas Jefferson Hi Steven Maybe =IF(AND(B1>0,B1<=5),999*IF(D1="",-1,1),"") Change the 999 to any number you want to use.  SignatureRegards Roger Govier >I have three columns, B, C and D. In B I would have ranges such as 1-5 >6-10 and so on. In D I would have yes or now, paid or not paid. I would [quoted text clipped - 11 lines] > percent of the people may take away the rights of the other > forty-nine." - Thomas Jefferson Thank you so far. I still have questions though. Will that work for several sets of ranges? I need it to be 1-5, 6-10, 11-15, on up to 60. Also, I think D would be simplest if it was either P or blank. I also need to set C to be whatever value corresponds to the specific range, like 1-5 makes C 20, while 6-10 makes C 40 and so on. I am sorry I did not think this through properly before making my original post. > Hi Steven > [quoted text clipped - 17 lines] >> percent of the people may take away the rights of the other >> forty-nine." - Thomas Jefferson Expand on this idea =LOOKUP(E1,{0,1,6,11,16;0,1,2,3,4}) SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com > Thank you so far. I still have questions though. Will that work for > several sets of ranges? I need it to be 1-5, 6-10, 11-15, on up to 60. [quoted text clipped - 25 lines] >>> percent of the people may take away the rights of the other >>> forty-nine." - Thomas Jefferson Sorry, but that makes no sense to me, I don't know enough. > Expand on this idea > =LOOKUP(E1,{0,1,6,11,16;0,1,2,3,4}) [quoted text clipped - 28 lines] >>>> percent of the people may take away the rights of the other >>>> forty-nine." - Thomas Jefferson Steven, If you fill H1 to H13 with 1,6,11,16 and so on to 61, and if you fill I1 to I13 with 20,40,60,80 and so on to 260, and enter in C1 formula: =LOOKUP(B1,$H$1:$I$13) and copy this formula down, then the result is 20 in C1 for values 1,2,3,4,5 in B1 =IF(D1="P";LOOKUP(B1,$H$1:$I$13);-LOOKUP(B1,$H$1:$I$13)) will make this value positive if D1 is P, or negative if it is not. HTH Erik > Sorry, but that makes no sense to me, I don't know enough. > [quoted text clipped - 30 lines] >>>>> percent of the people may take away the rights of the other >>>>> forty-nine." - Thomas Jefferson I really have not explained what I want clearly. Please forgive me. I will try to be more exact. I have three columns. The first (B) is the level a person is at. This can be 1-60, in sets of 5, so 1-5, 6-10 and so on. The second column (C) is the guild dues they owe, which is based on their character level range. Everyone who is level 1-5 owes the same guild dues, while everyone who is 6-10 owes a higher amount and so on. So what I need is to be able to put their level in (B) and have it bring up one of 12 different dues amounts, based on the level entered in (B). So if I enter a number between 1 and 5, column (C) would automatically show the one value out of the 12 that corresponds to that level. Column (D) needs to be either blank or have a P in it. If blank, then the figure in (C) would be red or negative and if there is a P, then it would be black/positive. This is for a game, where we all pay game money to the guild, to help it grow. I am in charge of keeping track of who pays and who does not, so I need something to make it easier and quicker. So something like [if b = 1-5 then 20c if 6-10 then 40c, if 11-15 then 80c on to 56-60 and if (D) is blank then (C) is negative and if (D) is P then (C) is positive/paid. > Steven, > [quoted text clipped - 43 lines] >>>>>> fifty-one percent of the people may take away the rights of the other >>>>>> forty-nine." - Thomas Jefferson Hi Steven It is still unclear (to me at least), whether the entry in column B will be either 1 or 2 or 3 etc. up to 60 or whether it will be "1-5" or "6-10" etc. up to "56-60". If the former, then on another part of the sheet (or even a different sheet) set up a table as follows 0 0.20 6 0.40 11 0.60 . . 60 2.40 Mark the range of this 2 column table, and in the Name box (the small square to the left of column A and above row 1) type Dues and press Enter Then using Erik's idea enter in C1 =IF(B1="","",IF(D1="P",VLOOKUP(B1,Dues,2),-VLOOKUP(B1,Dues,2))) If, on the other hand, you are using "1-5" etc. in B1, then create a table as follows (Note: all the entries of 1-5 etc will either have had to have the cells pre-formatted as Text, or you will need to precede the entries with a single quote '1-5 to force them to be text vales) 1-5 0.20 6-10 0.40 11-15 0.60 etc. Again name this table as Dues as outlined before. Now, use the formula in C1 =IF(B1="","",IF(D1="P",VLOOKUP(B1,Dues,2,0),-VLOOKUP(B1,Dues,2,0))) Your entries in column B will also have to be Text entries. Format>Cells>Text SignatureRegards Roger Govier >I really have not explained what I want clearly. Please forgive me. I >will try to be more exact. I have three columns. The first (B) is the [quoted text clipped - 66 lines] >>>>>>> fifty-one percent of the people may take away the rights of the >>>>>>> other forty-nine." - Thomas Jefferson the entry will be was going to be a single digit, 1 - 60 but I think in order to simplify it, it can be a single digit, A - L representing the 12 level groups. Thanks for asking that question, it clarified that for me. I don't think logically enough, that's for sure. > Hi Steven > [quoted text clipped - 103 lines] >>>>>>>> fifty-one percent of the people may take away the rights of the >>>>>>>> other forty-nine." - Thomas Jefferson Hi Steven Slight problem with terminology Single digit would imply 0 to 9, not 0 to 60 as once we get past 9 we would have 2 digits in the cell. Single digit A to L should be single character, A to L as these are alpha characters not digits. If you decide to go with the digit entry, then the first of my 2 solutions will work fine whether you type 6 or 23 or any other number into the cell. If you are going to go the text route, then either single characters A through L or "1 -5", "6 - 10" will work using the second method. just make the entries in the first column of the table Dues, as A, B, C etc. SignatureRegards Roger Govier > the entry will be was going to be a single digit, 1 - 60 but I think > in order to simplify it, it can be a single digit, A - L representing [quoted text clipped - 111 lines] >>>>>>>>> fifty-one percent of the people may take away the rights of >>>>>>>>> the other forty-nine." - Thomas Jefferson use the letters > Hi Steven > [quoted text clipped - 126 lines] >>>>>>>>>> fifty-one percent of the people may take away the rights of the >>>>>>>>>> other forty-nine." - Thomas Jefferson I know this is not a proper formula, but it will show you what I need each cell in column C to do. =IF(AND(B2=i3),h3)=IF(AND(B2=i4),h4)=IF(AND(B2=i5),h5)=IF(AND(B2=i6),h6) on out to h14 and i14. I don't know how to format the equation so it will do that. > Hi Steven > [quoted text clipped - 17 lines] >> percent of the people may take away the rights of the other >> forty-nine." - Thomas Jefferson Will please give an example of : - what is in cell B2 - what is in cell I3 and H3 - what is in cell I4 and H4 - what the result is in cell C2? Erik >I know this is not a proper formula, but it will show you what I need each >cell in column C to do. [quoted text clipped - 25 lines] >>> percent of the people may take away the rights of the other >>> forty-nine." - Thomas Jefferson there is an example, in the attachment to one of my posts a few lines up. > Will please give an example of : > - what is in cell B2 [quoted text clipped - 33 lines] >>>> percent of the people may take away the rights of the other >>>> forty-nine." - Thomas Jefferson Steven, I don't see an attachment and I doubt an attachment is allowed is the group. To write: "if B2 equals I3 then let C2 be equal to H3" the formula in cell C2 can be: =IF(B2=I3,H3,0) To write: "if B2 equals I3 then let C2 be equal to H3, else if B2 equals I4 let C2 be equal to H4", the formula in cell C2 can be: =IF(B2=I3,H3,IF(B2=I4,H4),0)) To write: "if B2 equals I3 then let C2 be equal to H3, else if B2 equals I4 let C2 be equal to H4, else if B2 equals I5 let C2 be equal to H5", the formula in cell C2 can be: =IF(B2=I3,H3,IF(B2=I4,H4),IF(B2=I5,H5),0))) and so on There used to be a limit to the amount of IFs you can put in one formula, so you should consider that the formula in cell C2 can be: =VLOOKUP(B2,I3:H14,2) but then first switch I3:I14 with H3:H14. Erik > there is an example, in the attachment to one of my posts a few lines up. > [quoted text clipped - 35 lines] >>>>> percent of the people may take away the rights of the other >>>>> forty-nine." - Thomas Jefferson Ok, everything is working and set up now. Thanks to everyone who helped, especially Roger Govier who gave me the solutions I needed. >I have three columns, B, C and D. In B I would have ranges such as 1-5 6-10 >and so on. In D I would have yes or now, paid or not paid. I would kind of [quoted text clipped - 10 lines] > percent of the people may take away the rights of the other forty-nine." - > Thomas Jefferson
|
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.