calculation of professional tax

Normally professional tax in not applicable all over the country but there are some state where the government take professional tax 

Karnataka,
Bihar, 
West Bengal,
AndhraPradesh,
Telangana, 
Maharashtra,
Tamil Nadu, 
Gujarat,
Assam, 
Kerala,
Meghalaya,
Odisha, 
Tripura, 
Madhya Pradesh
Sikkim.

 Professional tax caluclted on two basis  1. Professional Tax on Total Salary.

2. Professional Tax on Basis Salary.

So Here is the Exanple

Formula:                                                                                                                                 =IF((E7>=$A$2)*(E7<=$B$2),$C$2,IF((E7>=$A$3)*(E7<=$B$3),$C$3,IF((E7>=$A$4)*(E7<=$B$4),$C$4,IF((E7>=$A$5)*(E7<=$B$5),$C$5,IF((E7>=$A$6)*(E7<=$B$6),$C$6))))) 


($ it is the sign that fix the cell, select the cell and press F4 or Fn+F4)

2. Calculation of professional Tax according to the basis salary

Replace negative into positive

Replace all Negative value in positive. Here is the table

1. Select the whole negative row

2. Press Ctrl+F

3. Click on Replace All.

Click on Ok 

Now all the negative values are changed into Positive.

PMT formula

Basically PMT formula is use to calucalt the amount of loan.We will try to uderstand the formula throgh the Example. 

Suppose we have taken loan form the bank and we have to find out the EMI Amount. 

Syntex

=pmt(rate,nper,pv[fv],[type])

 rate  = rate is The percent which is fix by the bank on the loan

 nper = nper is the time which is in month like 5 years(60 months)

 pv    = pv is present value (we can say the loan which is taken by us)

The value will show in minus because we are paying the money.

Forcast formula

Forcast formula is used to predict anything in excel.
 For example- We sale somthing and we have the detail of sale for the month of jan, feb, March, April and now we want to know that what will be the sale in December month. So then we use forcast formula. You Can't totally Dependent  that result which is given by the formula but It will give you the approx value.
So here is an Example.


SYNTEX- (X,Known_y's, known x_'s)

X               =is month you want to know the sale like october
known_y's= known result (the Sale )
known_x's= known Result( month)

vlookup

VLOOKUP foumula, as the formula name show its use. This formula is use when we have data in bulk and we have to find one person detail.

SYNTEX: =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Lookup_value    = Select the cell which you find
Table_array        = Select the whole table
Col_index_num = Column number is the table have the numer of column
range_lookup     =  0 (zero is true value)

Here is an Example of vloolup

Here is the link

https://docs.google.com/spreadsheets/d/1GmZYiQ6-J_qKjteDQIJcaNu_vLOK4b23nWt_zitpc5w/edit#gid=0`

sumifs

Through the formula of sumifs we can find the sum with the condition.

For Example we have 5 Sales men and we want to find that how much product sale by each sales men. So we can easliy find.

Syntex__ =sumifs(sum range,criteria_range1, criteria1, creiteria_range2,cretria2)

Sum_range          =  Select the sum range

Creteria_range1  = is select the range First column 

Criteria 1             = Slect the name which you find

Cretria_range2    = Select the range Second column

Cretria2               =Select the product you want to find

When you will change the salesmen name (where you apply the formula) it will show you the total sale of that particular salesmen.

Transform Row in column

There are few steps to transform column in row or Rows in column 

1. Select and copy  the row you want to transform.

2. Then select the cell where you want to paste and right Click and paste special.

3.After then click on transform.

4. The column will chage in Row

Randbetween

              Randbetween Function


To Get a random value in a table we use this Formula(Randbetween).

Syntex: 
=Randbetween(Bottom,Top)

  Bottom= Here Put The Botton Value
  Top      = Here put the Top Value

Example- we want a random table between 500 & 1000. Here is bottom value is 500 and                         top value 1000.

excel introduction

Excel was Developed by Microsoft and first released on September 30, 1985, Excel is capable of creating and editing spreadsheets that are saved with a .xls or .xlsx file extension. General uses of Excel include cell-based calculation, pivot tables, and various graphing tools. For instance, with an Excel spreadsheet, you could create a monthly budget, track business expenses, or sort and organize large amounts of data.