

Subject:
Excel formula
Category: Computers > Software Asked by: livo11ga 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.  
 


Subject:
Re: Excel formula
Answered By: pafalafaga on 15 Mar 2005 05:00 PST 
livo11ga, 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. pafalafaga 

Subject:
Re: Excel formula
From: xarqiga 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: xarqiga 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: englandbubbaga 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: livo11ga on 14 Mar 2005 04:16 PST 
Hi xarqiga & pafalafaga! Thanks very much for your responses, it is a great help. I have tried the formula on a mockup as did pafalafaga 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 shiftclick 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, livo11ga 
Subject:
Re: Excel formula
From: pafalafaga on 14 Mar 2005 06:54 PST 
livo11ga, 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: xarqiga on 14 Mar 2005 16:05 PST 
Yaaargh  oops  I made a typo! Don't select the whole ROW for the filldown 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: livo11ga 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, livo11ga 
Subject:
Re: Excel formula
From: pafalafaga on 15 Mar 2005 05:01 PST 
A big "THANKS" to xarqiga, not only for kicking off the process, but for sticking with it to the end. A real professional job. paf 
If you feel that you have found inappropriate content, please let us know by emailing us at answerssupport@google.com with the question ID listed above. Thank you. 
Search Google Answers for 
Google Home  Answers FAQ  Terms of Service  Privacy Policy 