

Subject:
Microsoft Excel formula / code
Category: Computers > Software Asked by: ashtonhomega List Price: $10.00 
Posted:
14 Jun 2005 09:14 PDT
Expires: 14 Jul 2005 09:14 PDT Question ID: 533214 
How do I create a code/formula for Excel to automatically change wholesale prices to the prices I want to sell? Example: wholesale price + 3.5 + 30% + 5% and then round up to $.95 I also need to know how to change the formula for future reference. Thanks. www.ashtonhomeinteriordecor.com  
 


Subject:
Re: Microsoft Excel formula / code
Answered By: pafalafaga on 16 Jun 2005 19:46 PDT Rated: 
ashtonhomega, Glad to hear it! I think with the correct formula: =((A1+3.5)*1.3)*1.05 and the earlier information you got on rounding, you should have everything you need. However, please don't rate this answer until your are fully satisfied. If there's anything else I can do for you, just post a Request for Clarification to let me know, and I'm at your service. Cheers, pafalafaga 
ashtonhomega rated this answer: 

Subject:
Re: Microsoft Excel formula / code
From: pace_ricciardelliga on 14 Jun 2005 12:57 PDT 
As far as the adding dollars and percentages to a wholesale price, Excel has builtin math functions (+,,*,/) that you can use to modify prices. In your example, if the original wholesale value were stored in A1, you could modify the price by putting the following in cell B1 (without the quotation marks): "=A1+3.5+(.3*A1)+(.05*A1)" This function would add 3.5 dollars, then add 30% of what was in A1, then add another 5% of what was in A1. For the rounding (which is the hard part), say you're storing the modified wholesale price in cell B1. Pick another cell (say, B2) and type "=ROUNDDOWN(A1,0)+.95" In essence, this takes whatever is in cell B1 and rounds it down to the nearest integer value (i.e., zero decimal places), and adds .95 to it. The only problem with this formula is that if B1 has a decimal value between .95 and .99, it will be rounded down to .95 by the formula. If you don't care about four cents, go ahead and use the above formula. However, if you would like to correct it, you can use an IF statement to do so. Putting the following formula in B3 will do that: "=IF((B1ROUNDDOWN(B1,0))<.95,ROUNDDOWN(B1,0)+.95,ROUNDUP(B1,0)+.95)" In this new formula, if the decimal value of B1 is less than .95, it will perform the same function as the one in B2. If it is greater than .95, it will round up and add the .95, as opposed to rounding down. I hope this helps. Pace 
Subject:
Re: Microsoft Excel formula / code
From: ashtonhomega on 14 Jun 2005 14:16 PDT 
Thank you Pace. That worked... but that only takes care of one cell at a time. How do you make the code adjust for each new cell in the column? I am trying to adjust the prices for hundreds of items at a time. 
Subject:
Re: Microsoft Excel formula / code
From: manukaga on 14 Jun 2005 18:24 PDT 
Your formula in B2 should refer to B1, not A1. Also, the formula itself should be in C1 rather than B2 so as not to interfere with the next row. There's no need to use an IF formula to take care of the rounding. Simply change the formula in C1 to "=ROUNDDOWN(B1+0.04,0)+.95". This will bump prices in the __.96 to __.99 range up to the next dollar before rounding down. To make the formula work for other cells, all you have to do is fill it down, or even copy and paste. Ways to accomplish this in Excel:  (autofill): select the cells with the formulas (B1 and C1). Move the cursor to the bottom right corner of the selection until it changes shape to a thin black cross. Click and drag down to the end of your list.  (manual fill down): select the cells with the formula and the cells beneath them to the end of the list. Then press CtrlD or use Edit>Fill>Down.  (copy and paste): copy the cells with the formula. Select all the cells beneath them to the end of the list and paste. 
Subject:
Re: Microsoft Excel formula / code
From: ashtonhomega on 16 Jun 2005 14:31 PDT 
The formula works, but is not what I am looking for. According to my calculations for example, if I have a product that is $100 dlls, my final price is 141.27 > 100+3.5+30%+5%=141.27 The final price of this formula "=A1+3.5+(.3*A1)+(.05*A1)" is $138.50. Can I have a formula that gives me 141.27 and not 138.50 Thanks Nicole 
Subject:
Re: Microsoft Excel formula / code
From: manukaga on 06 Jul 2005 00:00 PDT 
Hi Nicole, Sorry for the delay. Pretty simple issue here  we assumed the percentages were of the base price, but you're calculating them on the total price. So, change the formula to "=(A1+3.5)*1.3*1.05". Note that multiplying by 1.3 (respectively 1.05) is the same as adding 30% (respectively 5%). The difference is that in this formula we're applying it to everything that's gone before. Cheers, manukaga 
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 