 |
 | 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 / April 2007IF STATEMENT - HOW TO STRUCTURE THE LOGICAL OPERATORS
Hello, I have an inventory of over 6000 items. In order to do a systematic cycle count, I would like to breakdown the inventory in multiple groups based on the unit cost of the item, such as: Selection Criteria Unit Cost 1 = >$5,000 2 = >$3000 <$5000 3 = >$2000 <$3000 4 = >$1300 <$2000 5 = >$1000 <$1300 6 = >$800 <$1000 7 = >$600 <$800 8 = >$400 <$600 9 = >$200 <$400 10 = >$100 <$200 In order to be able to select the items based on any desired criteria (either thru pivot table or filter button), I was thinking of adding a column named "Selection Criteria" and having in that column a formula that would derive the correct narration such as "=>$5,000", "=>$3000 <$5000", so on and so forth. I would appreciate if someone can someone help in writing the expression. As a test, I wrote the expression as follows, which did not work. A2 has a value of 3557 and i was expecting to see a Y. =IF(A2>=3000<4000,"Y","N") If someone can suggets a better method, i would appreciate. Regards SJ > If someone can suggets a better method, i would appreciate I do not fully understand the problem you want to solve, but the following might help with some of the details. > As a test, I wrote the expression as follows, which did not work. A2 has a > value of 3557 and i was expecting to see a Y. > > =IF(A2>=3000<4000,"Y","N") I believe the expression you want above is: =if(and(A2>=3000, A2<4000), "Y", "N") > I have an inventory of over 6000 items. In order to do a > systematic cycle count, I would like to breakdown the [quoted text clipped - 12 lines] > 9 = >$200 <$400 > 10 = >$100 <$200 I wonder if the following solves your problem. In each row, put a formula similar to the following: =countif(A1:A6000, ">=100") - countif(A1:A6000, ">=200") That satisfies the conditions in row 10 above. It looks like you have too many arguments here for a nested if function, you might have to do a lookup function. a b 1 100 1 2 200 2 3 400 3 4 600 4 5 800 5 6 1000 6 7 1300 7 8 2000 8 9 3000 9 10 5000 10 11 12 3557 =IF(A12<100,"",VLOOKUP(A12,$A$1:$B$10,2)) > Hello, > [quoted text clipped - 22 lines] > Regards > SJ As you have 10 conditions, you will find it difficult to combine IF statements to cover them all. I would suggest you use a lookup table - enter the following, for example in cells X1 to Y10. Note that I have put an apostrophe in front of the = sign in column Y - this is to prevent Excel from trying to evaluate what it thinks is a formula (you could pre-format the cells as Text first) X Y 100 '= >$100 <$200 200 '= >$200 <$400 400 '= >$400 <$600 600 '= >$600 <$800 800 '= >$800 <$1000 1000 '= >$1000 <$1300 1300 '= >$1300 <$2000 2000 '= >$2000 <$3000 3000 '= >$3000 <$5000 5000 '= >$5,000 Then, assuming your unit cost data is in column G, this formula on row 2 will return the appropriate narration: =VLOOKUP(G2,X$1:Y$10,2) Copy this formula down your 6000 rows. Hope this helps. Pete > Hello, > [quoted text clipped - 22 lines] > Regards > SJ Pete, Thank you for your response. The solution did not work. Perhaps my explanation was not clear enough. Following is a sample of my inventory (many fields are deleted to show only the necessary data for the problem at hand) and, hopefully, a clearer explanation: 1) Every week I do a cycle count of the inventory. As the number of items are over 6000, I divided the entire inventory in 10 groups based on the unit cost, hence, the selection criteria. The selection criteria give a range of the unit cost. Every week I count the few groups depending on the number of item in a group. 2) Currently what I do is dump the download into a worksheet. Using Auto Filter I would select and the items based on a criteria and then copy them into another sheet and then with the help of the pivot table organize that data. If I intend to count four groups, then I would do the same excerise the 4 times. 3) The idea is to be able to easily select the items based on a specific criteria right from the main download and avoid all this auto filter selection and copying, etc. Through pivot table I should then be able to select a specific criteria which would list all the items in that range. The data in the column "selection criteria" below is how the result should be. PART DESCRIPTION QTY UOM UC @ A COST SELECTION CRITERIA 013-1636-010 BLACK BLADE ANTENNA 1 EA 6,308.34 6,308.34 = >$5,000 102A491 DC ELECTRIC MOTOR, 27V. 1 EA 3,322.35 3,322.35 = >$3000 <$5000 10800B2F11 OXYGEN MASK ASSEMBLY 26 EA 2,798.40 72,758.33 = >$2000 <$3000 20732 DP CURRENT TRANSFORMER 1 EA 1,860.13 1,860.13 = >$1300 <$2000 10706 GPS ANTENNA 7 EA 1,059.37 7,415.59 = >$1000 <$1300 013-1969-040 TUNABLE GASKET,12",NTN 3 EA 562.82 1,688.46 = >$400 <$600 Thanks SJ > As you have 10 conditions, you will find it difficult to combine IF > statements to cover them all. I would suggest you use a lookup table - [quoted text clipped - 60 lines] >> Regards >> SJ The table is a bit difficult to follow because of line wrap, but I think you have a column "COST" which seems to be the "QTY" column times the "UC @ A" column. Next to this you want the "SELECTION CRITERIA" column, made up of the narrative descriptions given in column Y of my suggested table. If you put the table where I suggested (X1 to Y10), then using the cut- down layout in this latest example, you would be wanting to search on the fifth column. The formula would then become: =VLOOKUP(E2,X$1:Y$10,2) entered into G2, and this should return = >$5,000 in that cell. When copied down it will return the other appropriate values in the cells in that column. If this does not work for you then please explain in a bit more detail what is happening. If you cannot put the table in X1:Y10, then put it somewhere else (eg BB1:BC10) and change the references in the formula, i.e.: =VLOOKUP(E2,BB$1:BC$10,2) Please come back if this does not work for you. Pete > Pete, > [quoted text clipped - 106 lines] > > - Show quoted text - Pete, It worked beautifully. That is EXACTLY what i was looking for. Amazing! Please do me a favor and explain to me the connection betwen the formula & the table. I understand the vlookup formula. It is also clear that the column Y contains text. SO what is the role of column X? If the unit cost is $350.99, how does it returns the text "=>$200<$400"? How is it measuring? Regards SJ > The table is a bit difficult to follow because of line wrap, but I > think you have a column "COST" which seems to be the "QTY" column [quoted text clipped - 152 lines] >> >> - Show quoted text - Glad it worked for you - thanks for feeding back. The VLOOKUP formula I gave you does not have a 4th parameter, which means that the table it looks through has to be in sequence and it operates an "approximate" match rather than an exact match. In looking down the first column of the lookup table, it will match on the largest value which is less or equal to the lookup value. So, if you are looking for 350.99, it will match with the 200 in the second row of the table, as the next value (400) is too high. The final parameter of the VLOOKUP formula, 2, determines which column of the table the value is returned from, so the formula will then return the value from the second column of the table on the same row as the matching value, i.e. "= >$200 <$400". Similarly, if you are looking for 749.99, this will match with the 4th row of the table (600) and return "= >$600 <$800". Hope this explanation helps you to see how it works. Pete > Pete, > [quoted text clipped - 171 lines] > > - Show quoted text - Thanks for the explanation. Regards SJ > Glad it worked for you - thanks for feeding back. > [quoted text clipped - 217 lines] >> >> - Show quoted text - |
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.