Help with Google Sheets Formula

cryptomike

New Member
Joined
Jan 22, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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.

Sample Sheet: Excel Help Forum
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,072
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Link below to the OP's cross-post...

 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,045
Office Version
  1. 365
Platform
  1. Windows
Cryptomike,

I see that you are new here, so you may have missed the rule and may not be familiar with our policy on Cross-Posting. While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Note that most other forums have similar policies too, so you will probably want to check into that.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,439
Messages
5,642,121
Members
417,257
Latest member
Sarahbw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top