+ Reply to Thread
Results 1 to 5 of 5

Calculate tax based on if there's an asterisk in another column

  1. #1
    Registered User
    Join Date
    10-01-2008
    Location
    Chicago Suburbia
    Posts
    5

    Question Calculate tax based on if there's an asterisk in another column

    Hello,

    I am attempting to put together a spreadsheet that acts as a quote for products and services we offer. The table has 5 columns (Item #, Item Description, Qty, Unit Price, Price (USD). Services are exempt from tax, but products aren't. For example, if we build a computer for them, it would be taxed. But if we are quoting for say a 10-hour block of on-site tech support, that total wouldn't be figured into the Tax section (there is a Sub Total, Tax, and Total section at the bottom I would like to auto sum). If the service is not taxed, I put an asterisk after the item description. If there is that asterisk, I'd like the tax calculation to not count the 7% tax under the Tax section.
    I have attached the table section with fake info and values for now. I'm not sure if this is possible through an expression, VBR or what.

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Calculate tax based on if there's an asterisk in another column

    Excel is funny with asterisks because they are used as wildcards for lookups. There is a workaround for this though; if you add a space after the asterisks, you could then use this formula in E19 to get the result you are after:

    Please Login or Register  to view this content.
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  3. #3
    Registered User
    Join Date
    10-01-2008
    Location
    Chicago Suburbia
    Posts
    5

    Re: Calculate tax based on if there's an asterisk in another column

    GENIUS!!! LOL! I never thought of adding a space!

    But now the issue is, it is adding the entire price of the non-taxable stuff to the taxes. So, instead of $797.99, it is showing $2,797.98. It needs to just not be in the calculation at all if it has that asterisk/space, like an "IF" statement or something.

  4. #4
    Registered User
    Join Date
    10-01-2008
    Location
    Chicago Suburbia
    Posts
    5

    Re: Calculate tax based on if there's an asterisk in another column

    I figured it out! I added a "*0" to the part about the asterisks. Woohoo! Thank you so much

    Please Login or Register  to view this content.

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Calculate tax based on if there's an asterisk in another column

    I created a column to indicate no tax item with a * taxable items didn't have a *.

    Sum for taxable items:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Sum for non taxable items:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

+ 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. Need to calculate average for a column based on moving start column
    By BonnD in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-03-2013, 08:35 AM
  2. Calculate standard deviation for column based on column heading
    By mattadler22 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2013, 04:25 PM
  3. [SOLVED] Formula to calculate sum of column range based on presence of value in another column
    By abreet in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-23-2012, 10:37 PM
  4. Replies: 5
    Last Post: 07-06-2012, 03:21 PM
  5. Replies: 0
    Last Post: 02-04-2005, 03:58 PM

Tags for this Thread

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