I need to edit a lot of pricebooks by converting them from cost to different price levels (MRSP, NET, etc.) and each cell has a different dollar value.
Is there a type of macro in Numbers that allows me to do this by copy and paste? Please, advise, it will save me days of work...
Laura
iPad
Here is a simple example I hope you can apply to you situation.
It relies on a column of input prices (column A) and a single multiplier value to scale every price (cell D1).
In column B there is a formula which takes the price in column A and multiplies that value by the multiplier value in cell D1
select cell B2 and type (or copy and paste from here) the formula:
=$D$1×A2
short hand for this is:
B2=$D$1×A2
in the formula you can see that the value in A2 is multiplied by the value in cell D1. the reference to D1 is written as $D$1 because this is an absolute reference.
select cell B2, copy
select cells B2 thru the end of column B, paste
when you paste the formula you will notice that the reference to A2 will change as you move down the column while the reference to D1 (using $D$1) will remain unchanged.
you have a couple way to use the data in column B
1) instead of using the data in column A (the original, unmodified data) you could refer to the data in column B
2) you can copy/paste values into column A from their original location, set the multiplier, then copy from column B to the original location using the menu item "Edit > Paste Formula Results"
If you have a specific question or can provide more details we can provide a more specific answer
also possible an Apple script could do the job.
Can I add a formula to a group of cells with different values and get the relative results in every cell?
