+ Reply to Thread
Results 1 to 3 of 3

Formula to Show Percentage Amount

  1. #1
    Registered User
    Join Date
    04-02-2012
    Location
    Space
    MS-Off Ver
    Excel 2007
    Posts
    5

    Formula to Show Percentage Amount

    Hi All.

    I know how to calculate percentages. I know how to do a bit of formula stuff in Excel, but I have a simple spreadsheet which I could complicate (read: Change the entire look of the invoice) to get the result I'm looking for, but I'm hoping there's a simple formula to place in the one cell that will do it and I keep effing it up. :/

    Here's the simple setup:

    Column A has descriptions of items (manually typed in)
    Column B has Prices of items (manually typed in)

    B15 is SUM(b1:b14)

    Now, A14/B14 is reserved for a discount on all items (Not being selective here.. it's all or nothing).

    A14 = 10% Discount

    I want B14 to show the actual amount being deducted (I.E. A negative amount) so that B15 still works correctly, and if I add an item later (To say A9/B9) B14 will auto calculate the new amount off.

    Since I know how to work percentages, I've just been manually entering the discount, but I made a mistake where I forgot to recalculate the discount when I added an item. SO I've tried a few different things but it just never works out right for me. I'm missing something.

    Here's a simplistic look:

    A1 Widget B1 25
    A2 Wudget B2 20

    A14 10% Discount B14 (4.50)
    A15 Total B15 40.50 (SUM B1:B14)

    So, I need a formula for B14 that will SUM b1:b13, calculate the percentage off, and display the actual money being subtracted.

    Thanks in advance.

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Formula to Show Percentage Amount

    Surely, it is as simple as

    =-SUM(B1:B13)*A14

  3. #3
    Registered User
    Join Date
    04-02-2012
    Location
    Space
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Formula to Show Percentage Amount

    Well, A14 is a text field (says "Discount 10%"). But I got the gist.

    Crap! That WAS easy. I didn't know I could simply place a - after the =. This was where I was having issues. I was doing basically the same thing but with a lot more crap (Namely: =SUM(B1:B13) * 10% - SUM(B1:B13) * 10% - SUM(B1:B13) * 10%.

    I hated how long and iccky it was.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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