Multiple of IF statements

[Bradser]

hi, I am looking to do a multiple if statements to calculate a value based on shares vesting at different % in different years. For example, in year 1, 15% of your allotted shares vest, a further 20% in years 2-4 and the last 25% in the final year. I keep getting a message saying that if I am not trying to type a formula, then I must not put +/- at the start of things.

I need to do two formulas (columns h & I). Column H gives the shares that vest only when full years have been completed. COlumn I calculates by day.

If anyone could help me please as I have got close but cannot finish the formula to take in the five years and am going crazy

Bradser

[etaf]

column H

=+IF(E4< 365,0,IF(AND(E4< 730,E4 > 365),G4*15%+IF(AND(E4 < 1095,E4 > 730),G4*35%,IF(AND(E4 < 1460,E4 > 1095),G4*55%,IF(AND(E4 < 1825,E4 > 1460),G4*75%,IF(E4 > 1825,G4))))))

Why the +
do you just have a range
< 365 = 0
< 730 = 15%
< 1095 = 35%
etc

you dont need the AND
the IF works left to right
so the LESS than 365 applies and so the next IF must be > 365 ????? , so you dont need the AND
BUT what happens IF = 365 as that has not be applied

so you could simplify to

=IF(E4 < 365,0,IF(E4 < 730,G4*15%,IF(E4 < 1095,G4*35%,IF(E4 < 1460,G4*55%,IF(E4 < 1825),G4*75%,IF(E4 > 1825,G4))))))

BUT you need to sort the value of days it applies
does 365 =0 , if so then

=IF(E4 <= 365,0,IF(E4 < 730,G4*15%,IF(E4 < 1095,G4*35%,IF(E4 < 1460,G4*55%,IF(E4 < 1825),G4*75%,IF(E4 > 1825,G4))))))

does 730 = 15%
is then 730 also needs a < or =

=IF(E4 <= 365,0,IF(E4 <= 730,G4*15%,IF(E4 <= 1095,G4*35%,IF(E4 <= 1460,G4*55%,IF(E4 <= 1825,G4*75%,G4)))))

you dont need the
IF(E4 > 1825,
as IF all the other tests do not apply - then it must be > 1825
or a negative number

[Bradser]

Thanks so much Etaf, this solved my problem for both columns and you explained it in a super easy way.

I am trying to see the reputation button on the left hand side but cannot see, it let me know where it is cause I want to give kudos

[etaf]

you are welcome

* Add Reputation
Bottom left of each post

[bebo021999]

IF solution should be neater with:

=G4* IF(E4 <= 365,0,IF(E4 <= 730,15%,IF(E4 <= 1095,35%,IF(E4 <= 1460,55%,IF(E4 <= 1825,75%,1)))))

And shorter with LOOKUP solution.