Percentage formula issues !

**amitzala** · 07-29-2013, 08:11 AM

Hi Guys,

I've posted up the sheet with what I am querying.

The sheet is working out GP%'s for a range of products sold at a bar.

Column C is Sales Price inc VAT
Column D is Sales Price ex VAT
Column E is Cost Price ex VAT
Column F works out the Gross Profit in £'s
Column G works out the GP as a %.

I now want to look at how discounting affects the GP%.

So in columns H to K I want the data to reflect what the GP% would be if the SALES PRICE inc VAT is dropped by the respective 10,15,20 and 25 %.

I've been trying to work this out but i'm going round in circles !!

HELP !!!

Thanks

QUERY SHEET.xlsx

**XOR LX** · 07-29-2013, 08:42 AM

Hi,

I assume that your formulae in column G use the entries in column C (Sales Price inc VAT) to calculate the GP %, so simply use these formulae again for your new calculations but replace all instances of references to this column appropriately.

For example, in cell H2 replace the reference to C2 with 0.9*C2, in cell I2 replace the reference to C2 with 0.85*C2, etc., etc.

Regards

**amitzala** · 07-29-2013, 09:28 AM

Lovely ! Thanks !!!

**mike65535** · 07-29-2013, 09:30 AM

I assume that your formulae in column G use the entries in column C

Not directly, no. I think that's part of the OP's problem in finding the solution. Anyway, it's almost always easier to create helper columns/rows for stuff like this - at least until you debug it.

See attached. I worked out the intermediate steps then combined them into one formula