Nested IF function with rounding values

[Joe2577]

I am working on a annual salary increment file and I am having trouble with implementing an IF function at the beginning of the formula.

This overall formula should do three things:
1. Check if "Increase Recommended" is blank or 0. If true, then just return their original salary (without rounding it)
2. Prorate any value in "Increase Recommended" based on their start date and add it to their existing salary to form their new base salary
3. If receiving any increase, round their revised salary up to the closest hundred dollar

I believe I have the formula working correctly for the proration and rounding but I am struggling to for it to recognize if the "Increase Recommended" column is blank or 0. In this case it is rounding the value which is not correct.

Please feel free to improve my existing formula for proration and rounding as well, I greatly appreciate learning more efficient functions. Thanks!

[Melvosh]

You could just wrap your current formula in another IF statement:

Please Login or Register  to view this content.

Does that give you the results you're looking for?

[63falcondude]

Here's an alternative based on the small sample that you shared:

=IF(D2=0,C2,CEILING(C2+(1-(B2-I$2)/365)*D2,100))

[Joe2577]

Yup, that did. Much appreciated, I have been looking at it for far too long!

Thanks Melvosh!

[Joe2577]

I will look through this as well to test it out. I figured there was a quicker way to get to where I was going. Thank you!

[GeoffW283]

I hesitate to post this attempt as the formula is so much longer than the previously posted solutions, but I think it's the only one that deals correctly with an employs whose start date is either before or is after the fiscal year in question.

Please Login or Register  to view this content.

If you have no employee start dates after 31-Mar-19 in your data then this has no advantage over Melvosh's more concise solution.