View Question
Q: Microsoft Excel formula / code ( Answered ,   5 Comments )
 Question
 Subject: Microsoft Excel formula / code Category: Computers > Software Asked by: ashtonhome-ga 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``` Request for Question Clarification by pafalafa-ga on 16 Jun 2005 18:11 PDT ```I think this formula is what you're looking for: =((A1+3.5)*1.3)*1.05 although it gives an answer of 141.2775, which is ever so slightly different than your expected answer of 141.27. Does this look like it does the trick? pafalafa-ga``` Clarification of Question by ashtonhome-ga on 16 Jun 2005 19:35 PDT `Thank you so much.. that works!`
 Answer
 Subject: Re: Microsoft Excel formula / code Answered By: pafalafa-ga on 16 Jun 2005 19:46 PDT Rated:
 ```ashtonhome-ga, 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, pafalafa-ga```
 ashtonhome-ga rated this answer:

 Comments
 Subject: Re: Microsoft Excel formula / code From: pace_ricciardelli-ga on 14 Jun 2005 12:57 PDT
 ```As far as the adding dollars and percentages to a wholesale price, Excel has built-in 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((B1-ROUNDDOWN(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: ashtonhome-ga 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: manuka-ga 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 Ctrl-D 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: ashtonhome-ga 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: manuka-ga 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, manuka-ga```
 Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service. If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for

 Google Home - Answers FAQ - Terms of Service - Privacy Policy