David Lock

I'm not very good at Excel as will be apparent

I have a column of net prices for a range of products but these have increased by 10% so I want new columns showing new price, VAT and gross price.

OK so I can get a column multiplying original prices by 1.1% which is new net price. But when I try and multiply this new column by 0.2% Excel won't give me new figures as it has become a "Circular Reference" i.e. trying to recalculate on a column that is already subject to a formula change.

So what do I do?

Cheers David

PS. I did try Excel HELP but retired hurt...

Cpt Jack Sparrow

iTrader: (14)

my take would be

in cell a1 3.50

in cell b1 formula =A1*1.1

in cell c1 formula =B1+(B1*0.2)

that gives you £3.50 increased to £3.85 grossed up to £4.62

is that what you are after?

Paul

David Lock

Cheers Paul,

But I do need a separate column to show VAT amount on its own, next to first column with increased base prices.

I'll have another crack tomorrow.

David

urban

iTrader: (1)

Well David

Either do Pauls suggestion and subtract b1 from c1, or in a cell of your choice do, (b1 *.2)

David Lock

Thanks chaps. Well I now have columns with old net price A, new net price B, VAT C and gross price D.

So I now want to delete my old first column A which shows the old net price, before prices went up.

But since I used this old column as basis for calculating new prices then if I delete this column then it vapes all the other prices in B, C, D and I end up with columns full of zeros.

I am trying...

David

john banks

You could just hide the unwanted column.

Or you could copy the results or save them in a csv and repaste them if you want to obliterate the original data.

You can sit down now Think of my predicament.

urban

iTrader: (1)

CTRL A - select all
Right click and choose paste special - pick values.
Then delete whatever you want

David Lock

What a helpful bunch you are

Off for a sit down

David

GeeDee

iTrader: (2)

Not sure of the relevance of a CSV.

I've sent an email with an example of how I think it should be set up.

GeeDee

iTrader: (2)

Now I think I know what you are trying to do!!

Copy the original calculations and then paste them as values. This converts the original formulae to their calculated numbers without the need with phaffing around with different file formats.