EOSB CALCULATION

[Rasheed]

Dears,
I have a problem in calculating the EOSB using IF funct.
anybody can please help me with a formula

The rules are as follows:

<= 5 years gets half salary
> 5 years gets full salary

Thanks in advance

 

[Khalid NGO]

Hello Rahseed and Welcome the forum Chandoo.org

I suppose you have mentioned years in number in A2
and salary in B2

Try this: =IF(A2<=5,B2*50%,B2)

this is just an example, not sure about you data layout, specific answers can be provided if you can share a sample of your file with dummy data.

Regards,

 

[Rasheed]

Dear Mr. Khalid Thank you for your reply
5000 Monthly salary, start date 11 sep 1994, end date 30 Apr 2015. The first 5 years gets half of his salary for each year of the five, and one full salary for each year thereafter.

Hope this explains...

 

[Khalid NGO]

Hi Rasheed,
Thanks for the file.
Can you please include the expected result in the file? (if you have)

 

[Rasheed]

Dear Please see the attached

 

[Khalid NGO]

ok i have got the part for first 5 years:
=SUMPRODUCT((C6-(DATE(YEAR(B6)+5,MONTH(B6),DAY(B6))))/365)*(E6*50%)

can you check this, so we can move fwd?

 

[Rasheed]

Sorry its giving wrong answer , coz for 5 years the EOSB should be 50% of the last salary

Example for 10 years 8 months =(5*10000)*0.5 , + above 5.8*10000
the result should be like this =(5*10000)*0.5+ (5.8*10000) = ??

In this formula which you provide giving wrong answer

 

[prasaddn]

Hi,

Use this formula:
=IF(D13>5,((5*(E13/2))+(D13-5)*E13),(D13*E13))

Attached is the file.

Regards,
Prasad DN:

 

[Rasheed]

Thank you very much prasad , its working great ... thanks again

Solved ..

 

[Khalid NGO]

Sorry its giving wrong answer , coz for 5 years the EOSB should be 50% of the last salary

Example for 10 years 8 months =(5*10000)*0.5 , + above 5.8*10000
the result should be like this =(5*10000)*0.5+ (5.8*10000) = ??

In this formula which you provide giving wrong answer

Sorry Rasheed, i overlooked.
Thanks Prasad for backup.