Google sheet formulae for calculate gross salary

user_11987760 0
Viewed 60

If basic<10000 calculate gross=DA+basic, if basic>=10000 and <20000 , calculate gross=DA+basic+50% of HRA, if basic >=20000, calculate gross = basic+DA+HRA

Can anyone suggest google sheet formulae?

if(basic < 10000 )
{
   gross = DA + basic
}else if(basic >= 10000 && basic < 20000)
{
    gross = DA + basic + (0.5 * HRA)
}else
{
    gross = DA + basic + HRA
}
google-sheets-formula google-sheets
2 Answers

Just plug it to an IF formula.

=if(basic < 10000, DA + basic, if(and(basic >= 10000, basic < 20000), DA + basic + (0.5 * HRA), DA + basic + HRA))
user_161186860

You can also use the IFS function. If you have all the data in a table, you can just copy what I did in the following spreadsheet.

The full function would be:

=IFS(B2:B<10000, C2:C+B2:B, AND(B2:B>=10000, B2:B<20000), C2:C+B2:B+(0.5*D2:D), B2:B>=20000, C2:C+B2:B+D2:D)

Just take in consideration that Basic would be in column B, DA would be in column C, HRA would be in column D, and the result in column E.

You can just copy the spreadsheet to your own, and introduce the values you need.

user_179265910
Related