excel formula required

[aparna]

hello sir/madam,
i need an excel formula for calculating PF and ESIC
the conditions are as follows:
1)if basic>15001 then person should be out of PF
2)if ESIC>15001(CTC) then person should be out of ESIC.
I am posting sample file.formula needs to be enter in Basic column.
one more query is there
i am attaching one excel sheet in that excel the entry marked in maroon for that basic salary the other component is coming in negative marked in red .It should show only positive value or zero depending upon no of payable days.

 

[srinidhi]

Please use this formula in q26, if there is a negeative value, then it will become 0.
=IF(L26-M26-N26-O26-R26-S26<=0,0,L26-M26-N26-O26-R26-S26)

The formula for 1st & 2nd question is already in your excel, what do you want.

 

[NARAYANK991]

Hi Aparna ,

To add to what Srinidhi has posted , an alternative to her formula would be :

=MAX(0,L2-M2-N2-O2+P2-R2-S2)

I have another suggestion ; you have too many constants used freely all over your workbook ; for instance , the constant 15000 occurs in 3 places , along with constants such as 14999 and 15001 ; if at all this threshold figure is changed to say 20000 , you will have to change each and every occurrence manually.

If you can instead either set up a table where all such constants are placed and create named ranges , or even create named ranges having these values , then when the threshold changes , you will have to effect the change in only one place. When developing a workbook , always anticipate changes and plan for them. Also , anticipate that what you do today may , at a later stage , have to be used by someone else ; maintaining a well-developed workbook is easier.

Narayan