Google Answers Logo
View Question
Q: Microsoft Excel formula / code ( Answered 5 out of 5 stars,   5 Comments )
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.

Request for Question Clarification by pafalafa-ga on 16 Jun 2005 18:11 PDT
I think this formula is what you're looking for:


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?


Clarification of Question by ashtonhome-ga on 16 Jun 2005 19:35 PDT
Thank you so much.. that works!
Subject: Re: Microsoft Excel formula / code
Answered By: pafalafa-ga on 16 Jun 2005 19:46 PDT
Rated:5 out of 5 stars

Glad to hear it!

I think with the correct formula:


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.


ashtonhome-ga rated this answer:5 out of 5 stars

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

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

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:

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

I hope this helps.

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
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
- (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
- (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


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 with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

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