View Question
 Question
 Subject: Excel formula Category: Computers > Software Asked by: livo11-ga List Price: \$100.00 Posted: 12 Mar 2005 20:14 PST Expires: 11 Apr 2005 21:14 PDT Question ID: 493659
 ```I have a large excel file (nearly 25,000 pieces) of part number prices from my supplier in Austria listed by part numbers. I wish to convert these prices into my dealer and retail prices according to certain criteria as follows; Euro 0 to 10 x 3.2 Euro 10 to 75 x 2.7 Euro 75 to 150 x 2.5 Euro 150 to 200 x 2.3 Euro 200 to 250 x 2 Euro 250 to 350 x 1.8 Euro 300 to 400 x 1.6 Euro +400 x 1.5 Is there a formula that I can use to make this calculation without having to work through the whole file row by row? So far I have only been able to make an array for cells falling into the same price range and nested together as follows; {=A1:A24*3.2} The file must be kept in the same order for later upload to the company website that is used for my employees and dealers to make orders and that will display both the dealer and retail prices.``` Request for Question Clarification by pafalafa-ga on 13 Mar 2005 16:45 PST ```livo11-ga, I created an Excel mock-up and tried xarqi-ga's formula that was offered in the comments below, and which I'll repeat here: =IF(A1>=400,A1*1.5,IF(A1>=300,A1*1.6,IF(A1>=250,A1*1.8,IF(A1>=200,A1*2,IF(A1>=150,A1*2.3,IF(A1>=75,A1*2.5,IF(A1>=10,A1*2.7,A1*3.2))))))) It does exactly what you asked in your question..."recognizes" the size of the number, applies the appropriate multiplier, and puts the answer in the cell that you have selected. The only thing to note is that -- in your question -- the 'borderline' values occupy two places, and are therefore ambiguous. That is, for a number like "10", your rules have it multiplied by 3.2 (in the 0-to-10 category) as well as by 2.7, (in the 10-to-75 category) since the number is repeated in each category. The formula above treats each borderline value as belonging exclusively to the higher-value category -- 10 is multiplied by 2.7, not 3.2. 200 is multiplied by 2, but not 2.3, and so on. If that's an issue for you, let me know, and the formula can certainly be modified to meet your needs. I can also post my test file for you to view, if that is of interest. But the main question I have for you at this point is...Do you have everything you need...is your question answered? Or is there anything else a researcher here can do for you make for a complete answer to your question? Let us know. pafalafa-ga``` Request for Question Clarification by pafalafa-ga on 14 Mar 2005 06:53 PST ```livo11-ga, Glad to hear that the information we've provided thus far is getting you close to a solution. Let's see if we can get you 100% of the way there. You want to take the formula you have now and copy it down the length of a column so that it not only repeats (about 25,000 times) but also self-adjusts to perform the arithmetic on the appropriate column. In Excel, this is known as "relative copy". Try this: --go to the cell with the version of the formula that you want to copy to the cells below [e.g. If your formulas appear in column C, and you already have valid formulas in C1 through C10 -- and need to fill in C11-C25000 then click on cell C10] --A dark border should appear around the cell, with a "smart tag" in the lower right-hand corner. --Move your cursor over this smart tag -- the cursor will change from its normal appearance to a plus-sign (+) when it is properly positioned. --Click-and-hold on the smart tag. That is, click your mouse down, but don't release is. --Drag the cursor all the way down to the final position. [NOTE...it actually doesn't take all that long to drag, even for 25,000 cells. The further down the page you move your cursor, the faster the drag will go...when you start approaching the end of the spreadsheet, move the cursor higher up on the page to slow down the scrolling] --Release the mouse button. Your formula should then be pasted, with a relative copy, with all the proper adjustments made. This is a fairly straightforward process, but if you haven't done it before, it takes a bit of getting used to the right movements. I hope I've described them to you clearly here. Let me know if this works, and if you have everything you need, or if you need any additional information. Best of luck. pafalafa-ga```
 Subject: Re: Excel formula Answered By: pafalafa-ga on 15 Mar 2005 05:00 PST
 ```livo11-ga, That's great news...glad to hear that your little Excel problem worked out so well. Let us know if there's anything else we can do for you, now or in the future. pafalafa-ga```
 Subject: Re: Excel formula From: xarqi-ga on 13 Mar 2005 02:36 PST
 ```Assuming your prices are in column A: Paste the following formula into a cell in at the end of the row for the first part: =IF(A1>=400,A1*1.5,IF(A1>=300,A1*1.6,IF(A1>=250,A1*1.8,IF(A1>=200,A1*2,IF(A1>=150,A1*2.3,IF(A1>=75,A1*2.5,IF(A1>=10,A1*2.7,A1*3.2))))))) Then select this row, and all the 25000+ rows below it (select it, then shift click on the last cell), and do a "Fill Down" from the edit menu.```
 Subject: Re: Excel formula From: xarqi-ga on 13 Mar 2005 02:39 PST
 ```That also assumes your first part row is row one. Wherever the first part price happens to be, just use its cell address in place of "A1" in the given formula before you do the copy down.```
 Subject: I think I'm missing something.... From: englandbubba-ga on 13 Mar 2005 10:29 PST
 ```At first read, this seems really easy. Sheet 1 contains the parts. Sheet two contains a named range looking something like this: 0 3.2 10 3.2 75 2.7 150 2.5 200 2.3 250 2 350 1.8 400 1.5 The adjusted price column is then a vlookup of your named range: =vlookup(source cell, named range, 2, true)* source cell Say your range is named multiplier and you're checking the euro price in cell d2. The formula would look like =vlookup(d2,multiplier,2,true)*d2. But then, I could be missing something.....```
 Subject: Re: Excel formula From: livo11-ga on 14 Mar 2005 04:16 PST
 ```Hi xarqi-ga & pafalafa-ga! Thanks very much for your responses, it is a great help. I have tried the formula on a mock-up as did pafalafa-ga and had to do some syntax adjustments as the original file requires ',' as the decimal for the numbers and prices. Additionally, the supplier price is in column 'B'. However it has worked and now looks like this; =IF(B2>=400;B2*1,5;IF(B2>=300;B2*1,6;IF(B2>=250;B2*1,8;IF(B2>=200;B2*2;IF(B2>=150;B2*2,3;IF(B2>=75;B2*2,5;IF(B2>=10;B2*2,7;B2*3,2))))))) I am however having a problem with filling down the formula to the remaining rows correctly. Following the instructions of 'select row 1 then shift-click on the last row followed by edit menu and fill down' has only substituted the information in row 1 to all of the remaining rows loosing the original data contained therein. Am I doing something wrong here? Or, is there not a method to make the formula an array such as this; {=B1:B2500;IF(>=400;*1,5;IF(>=300;...)))} and so on? I am nearly there my friends. Thanks again, livo11-ga```
 Subject: Re: Excel formula From: pafalafa-ga on 14 Mar 2005 06:54 PST
 ```livo11-ga, I added a note in the Clarifications section (above)...have a look and let me know if everything works out OK. paf```
 Subject: Re: Excel formula From: xarqi-ga on 14 Mar 2005 16:05 PST
 ```Yaaargh - oops - I made a typo! Don't select the whole ROW for the fill-down operation, just select the CELL with the new formula in it, and all the empty cells below it, then do the fill down. My profound apologies.```
 Subject: Re: Excel formula From: livo11-ga on 15 Mar 2005 04:49 PST
 ```This has been a complete success! Thanks very much to everybody who has helped me on this, I was in danger of my hair going prematurly grey, but not any more thanks to you guy's! Keep up the good work, Ciao, livo11-ga```
 Subject: Re: Excel formula From: pafalafa-ga on 15 Mar 2005 05:01 PST
 ```A big "THANKS" to xarqi-ga, not only for kicking off the process, but for sticking with it to the end. A real professional job. paf```