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!

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

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

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.

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.

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

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