+ Reply to Thread
Results 1 to 8 of 8

Google sheet: how to use multiple parameters based on a certain value?

  1. #1
    Registered User
    Join Date
    01-18-2020
    Location
    Europe
    MS-Off Ver
    None
    Posts
    4

    Google sheet: how to use multiple parameters based on a certain value?

    I'm working on a tax sheet for personal use in Google Sheets. The hard thing is that the tax rate has been different for the last three years, making it hard for me to make the correct formula.

    I have two tabs in the sheet
    - Main sheet (showing taxes and tax rate
    - Tax calculation sheet

    The tax sheet has a calculation now based on a fixed percentage for taxes, but I want it to calculate based on the correct tax rate for the correct year, based on the date for the row.

    On the main sheet I have listed:
    A5 = Year 2017
    A6 = Year 2018
    A7 = Year 2019
    B5 = 2017 tax calculations
    B6 = 2018 tax calculations
    B7 = 2019 tax calculations
    C5 = 2017 Tax % rate
    C6 = 2018 Tax % rate
    C7 = 2019 Tax % rate

    In the Tax sheet I add one row for each trade which includes the profit made - shown in the V column, then profits multiplied with the tax rate which is taking place in the W column. The code used today is =$V9*24%. The date for the trade is taking place in the H column. What I want to do is something like this:

    If the date is >=1/1/2017",$H$2:$H$991,"<1/1/2018, then multiply the profits in V column with C5.
    If the date is >=1/1/2018",$H$2:$H$991,"<1/1/2019, then multiply the profits in V column with C6.
    If the date is >=1/1/2019",$H$2:$H$991,"<1/1/2020, then multiply the profits in V column with C7.

    All of these conditions need to be set in a single formula to be used in the W column.

  2. #2
    Forum Contributor
    Join Date
    11-10-2017
    Location
    INDIA
    MS-Off Ver
    365
    Posts
    184

    Re: Google sheet: how to use multiple parameters based on a certain value?

    Hi,

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    It not working share sample file (visual are more interactive than theory)

    *If solved click * and mark as SOLVED

  3. #3
    Registered User
    Join Date
    01-18-2020
    Location
    Europe
    MS-Off Ver
    None
    Posts
    4

    Re: Google sheet: how to use multiple parameters based on a certain value?

    It didn't work with your formula, but I have attached a sample where you can see how the spreadsheet looks like.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    11-10-2017
    Location
    INDIA
    MS-Off Ver
    365
    Posts
    184

    Re: Google sheet: how to use multiple parameters based on a certain value?

    Hi cryptomike,

    Check this out, actually the tax value was not in V, But have shared the updated file.
    Attached Files Attached Files
    *If you wish you click on *,a way to say ThankYou

  5. #5
    Registered User
    Join Date
    01-18-2020
    Location
    Europe
    MS-Off Ver
    None
    Posts
    4

    Re: Google sheet: how to use multiple parameters based on a certain value?

    Hi,

    It calculated the tax amount based on the V column, but maybe your solution works too. What did you change? Because the problem was not on the "Main" sheet, but the "Tax" sheet.

  6. #6
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Google sheet: how to use multiple parameters based on a certain value?

    Quote Originally Posted by cryptomike View Post
    . . .

    On the main sheet I have listed:
    ...
    C5 = 2017 Tax % rate
    C6 = 2018 Tax % rate
    C7 = 2019 Tax % rate

    In the Tax sheet I add one row for each trade which includes the profit made - shown in the V column, then profits multiplied with the tax rate which is taking place in the W column. . . .

    If the date is >=1/1/2017",$H$2:$H$991,"<1/1/2018, then multiply the profits in V column with C5.
    If the date is >=1/1/2018",$H$2:$H$991,"<1/1/2019, then multiply the profits in V column with C6.
    If the date is >=1/1/2019",$H$2:$H$991,"<1/1/2020, then multiply the profits in V column with C7.

    All of these conditions need to be set in a single formula to be used in the W column.
    Maybe

    W2: =V2*lookup(year(H2),{2017;2018;2019},main!$C$5:$C$7)

  7. #7
    Registered User
    Join Date
    01-18-2020
    Location
    Europe
    MS-Off Ver
    None
    Posts
    4

    Re: Google sheet: how to use multiple parameters based on a certain value?

    Quote Originally Posted by hrlngrv View Post
    Maybe

    W2: =V2*lookup(year(H2),{2017;2018;2019},main!$C$5:$C$7)
    This worked like a charm. Thank you!

  8. #8
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Re: Google sheet: how to use multiple parameters based on a certain value?

    Cross-posted here
    If my solution worked (or not) please let me know. If your question is answered then please remember to mark it solved

    Computers are like air conditioners. They work fine until you start opening windows. ~Author Unknown

+ 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. [SOLVED] Google Sheet: Retrieve data from another sheet, but based on variable of certain column
    By naveron in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 6
    Last Post: 08-17-2019, 01:42 PM
  2. Synchronize multiple users one particular excel work sheet in google sheet?
    By bala04msw in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-07-2018, 04:22 AM
  3. list data on summary sheet from multiple sheets, based on parameters
    By tillyman in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-11-2015, 10:06 AM
  4. Move data to another sheet based on certain parameters
    By paulim7 in forum Excel General
    Replies: 4
    Last Post: 10-29-2014, 04:21 PM
  5. Date calculation based on multiple parameters
    By koudesak in forum Outlook Formatting & Functions
    Replies: 2
    Last Post: 10-03-2011, 10:18 AM
  6. Query and sum based on multiple parameters
    By minkus in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-30-2007, 04:34 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