Pro Rata Entitlement Formula

[CheekyNess]

Hi there,

Hoping someone might be able to help me with a Formula to calculate the funding availale for a Nurses Scholarship Fund Entitlement based on length of membership.

I have a cell showing length of membership in years (say A1) and another cellshowing length of membership in days (say A2).

I need a formula that will calculate the following:

< 6 months = $0
> 6 months + < 12 months = $800 ProRata
>12 months + <6 years = $800
>6 years + <10 years = $1000
>10 years = $1,200


The ProRata portion has me struggling - Thanks so much!

Ness

 

[Mazbuka]

Not sure about your intention with the pro-rata bit, if you mean that at 6 months they get half the 800, up to the full 800 by 12 months, then the below should work, just using the years column, you could tweak it for the days if needed.

=IF(A1>10,1200,IF(A1>6,1000,IF(A1>1,800,IF(A1>0.5,800*A1,0))))

 

[CheekyNess]

Thanks Mazbuka! I ended up with the following formula

C17 is A1/365 to give the membership length in days rather than years, and 2.19.... is 800/365 to give the daily pro-rata figure.

=IF(A1<0.5,0,IF(A1<1,(C17*2.19180821981),IF(A1<6,800,IF(A1<10,1000,IF(A1<100,1200))))