Percent markup and discount in a range

[Legacy 206451]

<P style="MARGIN: 0in 0in 10pt" class=MsoNormal><FONT size=3 face=Calibri>Background: (1) I have a (Windows Vista) 2007 Excel worksheet with 14 columns and 2,354 rows, for a total of 32,956 cells. The file’s size is 304KB. Alone, the file serves as our parts and price list for our wholesale customers. We add pictures to the file to create a Microsoft Publisher catalog of 140 pages for our retail customers. (2) All prices are preceded with a dollar sign “$,” and are formatted as “numbers,” not “currency.” (3) Some prices contain only three decimal places such as: “$0.99,” but most contain five decimal places such as: “$155.20.” (4) Each year, our secretary automatically updated prices in our Peachtree accounting software, and manually updated prices in the retail worksheet before I updated the Publisher catalog. We no longer have a secretary position, and the task of updating the retail and wholesale worksheets has fallen into my hands.</FONT><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o><FONT size=3 face=Calibri> </FONT></o></P>
<P style="MARGIN: 0in 0in 10pt" class=MsoNormal><FONT size=3 face=Calibri>Problem: (1) There are 4,812 prices. Manually calculating each price twice - once for the year-over-year retail increase, and again, in a separate worksheet, the discounted wholesale prices - and updating them is tedious and lends itself to errors. (2) Specifically for this year, I need to add 5-percent to the prices in our 2011 retail worksheet. And from those updated 2012 retail prices, I need to create a wholesale price list that is 75-percent of the retail price list. (2) Both the retail and wholesale lists have about 40 categories, each of which has two sub-categories with prices in various columns, as are part numbers and descriptive text (in order to optimize the layout of the Publisher file). (3) I do not want to alter Original Equipment Manufacturers (OEM) part numbers, our part numbers, descriptive text, or empty cells, which is what I get when I use the multiply function in Excel.</FONT></P>
<P style="MARGIN: 0in 0in 10pt" class=MsoNormal><FONT size=3 face=Calibri>Solution: I believe I’m looking for an “If” macro that would: (1) find a number preceded by a dollar sign, (2) increase that value by 5-percent (3) round to the nearest cent ($.01) and (4) re-populate that cell with the new value. Or, if it would be easier, copy the entire range of cells in our 2011 worksheet while performing functions 1, 2 and 3 and dumping the results into a new worksheet. Subsequently, I also need a macro that would calculate 75-percent of the new 2012 retail values to get the wholesale prices. The markup value of 5-percent may vary from year to year.</FONT></P>
<P style="MARGIN: 0in 0in 10pt" class=MsoNormal><FONT size=3 face=Calibri>Thank you for your consideration. I’m interested in any relevant, on-line post or tutorial about this subject. I have yet to find a solution in Excel’s on-line support, Mr. Excel’s archive about percentages. I was referred to this site by John Walkenbach’s “Excel 2007 Power Programming with VBA.”</FONT></P>

 

[Firefly2012]

Hi James

Without seeing how your data is layed out it is difficult to offer up a solution - of you have the prices in a traditional tabular format ie every price in column D say with no blank rows between entries then you could use a simle formula such as:

=D2*1.05

in an adjacent column/cell to add 5% to the price and you could copy + pastespecial>values over the originals from these formula cells if all you wanted was to update the D column.

If the placement of your prices is more random, then further consideration would be required.

 

[Legacy 206451]

The prices are in random columns throughout the entire worksheet. There is no space in the cells around the prices to enter such a formula, and likewise, no space to put the answer to that formula.

I'm think I'm looking for an If-Then macro. For example, if any number in the worksheet is preceded by a dollar sign "$," then markup that number by 5-percent and re-populate the cell with the computed answer.