Excel calculate tax

varunwalla

New Member
Joined
Aug 13, 2020
Messages
32
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
I have two tax type
  1. Slab 1 &
  2. Slab 2
In sheet 1 Column G the tax value should be calculated based on the Column F and the value should be calculated from sheet 2 and please check the desired output of the tax value in column I

tax.xlsx
ABCDEFGHI
1NoNameQTYPriceTotal PriceTax TypeTax ValueFinal PriceDesired Output In Column G
21CTI52001000Slab 11000280
32CTI3195585Slab 1585205.3
43TCI3200600Slab 2600238
54CCC4100400Slab 2400192
65ICN3250750Slab 1750235
Sheet1
Cell Formulas
RangeFormula
H2:H6H2=(E2+G2)
I2:I3,I6I2=((E2*18)/100)+100
I4:I5I4=((E4*23)/100)+100
E2:E6E2=(C2*D2)



tax.xlsx
ABC
1IndexSlab 1Slab 2
2GST0.180.23
3DC10050
Sheet2
Cell Formulas
RangeFormula
B2B2=(18/100)
C2C2=(23/100)
 

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
684
Office Version
  1. 2010
Platform
  1. Windows
Thank you MARK858, still trying
this is what I got now, is true the slab1 and 2 is a condition I didn't considerate, so this is my new try, Please, correct me, a really appreciate your input. Thanks
VBA Code:
Sub varu_v2()
         Dim x As Long
         Dim y As Long
         Dim Cf As Integer
                  For x = 2 To 6
                           For y = 5 To 6
                                       If Cells(x, "f").Value = slab1 Then
                                                Cells(x, "J").Value = Cells(x, "E").Value * 0.18 + 100
                                                Else
                                                Cells(x, "J").Value = Cells(x, "E").Value * 0.23 + 50
                                       End If
                           Next y
                  Next x
End Sub
now this is the results (I am not saying nothing about , right or wrong, I am not sure).
1599145227462.png
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,064
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
I don't see the need for the y variable and
VBA Code:
.Value = slab1
should be
VBA Code:
.Value = "Slab 1"
So you end up with
VBA Code:
Sub varu_v2()
         Dim x As Long

                  For x = 2 To 6
                                       If Cells(x, "f").Value = "Slab 1" Then
                                                Cells(x, "L").Value = Cells(x, "E").Value * 0.18 + 100
                                                Else
                                                Cells(x, "L").Value = Cells(x, "E").Value * 0.23 + 50
                                       End If
                  Next x
End Sub

Results in Column L.
I think then you will get the correct results ;)
 

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
684
Office Version
  1. 2010
Platform
  1. Windows
MARK858 Thank you So much for your lesson.
Yes the line for y = 2 to 6 wasn't necessary. [first mistake]
The word slab1 didn't have " "[second mistake}
and
when I write Slab 1, I didn't separate the word from 1 [mistake 3]

Big lesson today from you Mr. Mark858
Thank you for your time.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,133
Messages
5,640,314
Members
417,136
Latest member
reeton3

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