+ Reply to Thread
Results 1 to 4 of 4

Percentage formula issues !

  1. #1
    Registered User
    Join Date
    07-17-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2011
    Posts
    15

    Percentage formula issues !

    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

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,744

    Re: Percentage formula issues !

    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
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    07-17-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2011
    Posts
    15

    Re: Percentage formula issues !

    Lovely ! Thanks !!!

  4. #4
    Registered User
    Join Date
    07-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Percentage formula issues !

    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
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. vlookup formula based on formula issues
    By katejags in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-06-2012, 05:14 AM
  2. Vba formula issues
    By kasperblue in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-29-2012, 04:15 PM
  3. IF ANY Formula issues
    By Kezwick in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 09-19-2011, 10:02 AM
  4. Formula issues
    By ExcelNewby in forum Excel General
    Replies: 2
    Last Post: 02-17-2009, 08:38 AM
  5. IF Formula issues (want more than 7)
    By wandaww in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-07-2008, 05:09 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1