Roundup an entire column
 

I have increased the prices on my price list by a %, now I need to roundup
the prices to the next dollar. I have three columns that are linked so that
when I increase the prices in one the prices in the other automatically
increase also. Can I round up the columns individually?

Roundup an entire column
 

You could do it with a macro. I'm not sure if you need to work with more
than one of the columns, but this code will allow you to pick one at a time
to work with.

May want to try it out on a copy of the real file, just in case. To get the
code into the workbook, press [Alt]+[F11] to open the Visual Basic Editor.
From its menu, choose Insert | Module. The copy the code below and paste it
into the module, make any changes needed, such as what row your first price
is on or what the default column should be, then close the VB Editor. To do
it, go to the sheet with your pricing, choose Tools | Macro | Macros and
click the macro named MakeNewPrices and then click the [Run] button.

Sub MakeNewPrices()
Const firstPriceRow = 2 ' change as needed

Dim anyEntry As Variant
Dim whatColumn As String
Dim percentIncrease As Single
Dim lastRow As Long
Dim LC As Long ' loop counter

'prices in column chosen will be altered
'default is column "A", you can change that if desired:
whatColumn = InputBox("Which column has prices to be changed?", _
"Price Column", "A")
If whatColumn = "" Then
Exit Sub ' [Cancel]d
End If

'if no value entered, no increase applied
anyEntry = InputBox("What percent increase to apply (" _
& "Enter 5 for 5%)?", _
"Increase", 0)
If anyEntry = "" Then
Exit Sub ' [Cancel]d
End If
percentIncrease = anyEntry
If percentIncrease = 0 Then
Exit Sub ' zero, negative is allowed for cost reduction
End If
lastRow = Range(whatColumn & Rows.Count).End(xlUp).Row
For LC = firstPriceRow To lastRow
Range(whatColumn & LC) = _
Int(Range(whatColumn & LC) * _
(1 + percentIncrease / 100)) + 1
Next

End Sub


"Boss Mary" wrote:

I have increased the prices on my price list by a %, now I need to roundup
the prices to the next dollar. I have three columns that are linked so that
when I increase the prices in one the prices in the other automatically
increase also. Can I round up the columns individually?

Roundup an entire column
 

Hi,

For more information, please refer to ROUNDUP funtion in the Microsoft Excel
2003 online help.

Challa Prabhu

"Boss Mary" wrote:

I have increased the prices on my price list by a %, now I need to roundup
the prices to the next dollar. I have three columns that are linked so that
when I increase the prices in one the prices in the other automatically
increase also. Can I round up the columns individually?

Roundup an entire column
 

See her other discussion of this issue also ...
http://www.microsoft.com/office/comm...a-2d5ae35192e7

Apparently ROUNDUP() didn't fill her needs, but I'm not quite sure why not.

"challa prabhu" wrote:

Hi,

For more information, please refer to ROUNDUP funtion in the Microsoft Excel
2003 online help.

Challa Prabhu

"Boss Mary" wrote:

I have increased the prices on my price list by a %, now I need to roundup
the prices to the next dollar. I have three columns that are linked so that
when I increase the prices in one the prices in the other automatically
increase also. Can I round up the columns individually?

Roundup an entire column
 

Here is what I did in real world (with calculator):
cost x 47% = ___ + cost = _____ x 4 = LP
What I need to accomplish in excel is one column holds the costs and can be
hidden and locked another column calculates the LP which I need to round up
to the next dollar (102.01 = 103.00). When I have a cost increase I would
like to put that percentage in one cell and have all the costs increase by
that percentange and then the list price automatically adjust accordingly.
Any Help?

"Boss Mary" wrote:

I have increased the prices on my price list by a %, now I need to roundup
the prices to the next dollar. I have three columns that are linked so that
when I increase the prices in one the prices in the other automatically
increase also. Can I round up the columns individually?

Roundup an entire column
 

If your cost is in A1, use =ROUNDUP(A1*(1+47%)*4,0) for your LP.

When you want to increase the cost prices by a given percentage (after
you've saved the file in its previous version), if your increase is 5%, put
1.05 into a cell, copy it, select column A, edit/ paste special/ multiply.

If your 47% was the last cost increase and your LP is merely the rounded up
value of 4 times the cost price, then your LP formula should merely be
=ROUNDUP(A1*4,0)
and you can apply that after using the paste special multiply technique to
multiply column A by 1.47.

But remember to save the earlier version, in case you do something wrong.
--
David Biddulph

"AprilTN" wrote in message
...
Here is what I did in real world (with calculator):
cost x 47% = ___ + cost = _____ x 4 = LP
What I need to accomplish in excel is one column holds the costs and can
be
hidden and locked another column calculates the LP which I need to round
up
to the next dollar (102.01 = 103.00). When I have a cost increase I would
like to put that percentage in one cell and have all the costs increase by
that percentange and then the list price automatically adjust accordingly.
Any Help?

"Boss Mary" wrote:

I have increased the prices on my price list by a %, now I need to
roundup
the prices to the next dollar. I have three columns that are linked so
that
when I increase the prices in one the prices in the other automatically
increase also. Can I round up the columns individually?

Roundup an entire column
 

THANK YOU! I had some of it figured out but your way was simple and exact.

"David Biddulph" wrote:

If your cost is in A1, use =ROUNDUP(A1*(1+47%)*4,0) for your LP.

When you want to increase the cost prices by a given percentage (after
you've saved the file in its previous version), if your increase is 5%, put
1.05 into a cell, copy it, select column A, edit/ paste special/ multiply.

If your 47% was the last cost increase and your LP is merely the rounded up
value of 4 times the cost price, then your LP formula should merely be
=ROUNDUP(A1*4,0)
and you can apply that after using the paste special multiply technique to
multiply column A by 1.47.

But remember to save the earlier version, in case you do something wrong.
--
David Biddulph

"AprilTN" wrote in message
...
Here is what I did in real world (with calculator):
cost x 47% = ___ + cost = _____ x 4 = LP
What I need to accomplish in excel is one column holds the costs and can
be
hidden and locked another column calculates the LP which I need to round
up
to the next dollar (102.01 = 103.00). When I have a cost increase I would
like to put that percentage in one cell and have all the costs increase by
that percentange and then the list price automatically adjust accordingly.
Any Help?

"Boss Mary" wrote:

I have increased the prices on my price list by a %, now I need to
roundup
the prices to the next dollar. I have three columns that are linked so
that
when I increase the prices in one the prices in the other automatically
increase also. Can I round up the columns individually?