Nested if formula

J

Jurek

Need help with building nested if formula.
Have following columns: date of hire, date of separation, date of salary
increase, old pay, new pay, amount of increase, and months of the year.
Trying to build statement that would display monthly salary for months worked
(show blanks outside this range. For example if employee worked from march to
august, display salary for the same months. Outside this range show zero.
Also if in may emplyee got increase in july show old salary for march thru
june and for july thru august a new wage. Is this doable. Please help.
Jurek
 
B

Bill Pfister

Jurek, this is easier to show in a workbook, but here's an explanation. Let
me know if you'd like the example.

C5: hire date
D5: separation date
E5: salary increase date
F5: old pay amount
G5: new pay amount
H5: amount of increase amount (not needed here)

J3-U3: date headers (1/1/06, 2/1/06, ... , 12/1/06)

J5-U5: put this formula in J5 and copy through U5:

=IF( AND( J$3>$C5, J$3<$D5 ), IF( J$3>=$E5, $G5, $F5 ), 0 )
 
J

Jurek

Bill:
Thanks. It works. I managed to solve the problem with a variation
=IF(HireDate=0,0,IF(SepDate<Jan,0,IF(Jan<HireDate,"",IF(IncDate=0,OldWage,IF(
Jan>IncDate,NewWage/12,OldWage/12))))).
But your formula is cleaner. I am dividing new and old salary by 12 to
display montlhy amounts.
The one caveat is that the separation date can not be an empy field. It has
to have some kind of data. Is there a way of displaying the results for null
data for separation. Because as is is now I have to "terminate" an employee
to get his/hers salary. If the formula could capture this data (salary) for
an active employee(no separation date) it would be great. Right now for
active employee I must project a termination date far into the future.
But thanks for your help and reply.
Jurek
 
D

Dave Peterson

The interface that you're using to read messages is showing the greater than and
less than symbols as gt and lt.

I use a plain text newsreader and Bill's message shows up very nice for me.
 
B

Bill Pfister

Sorry for the late reply; I've been traveling for the holidays.

The following formula will handle a lack of separation date. Let me know if
this doesn't cover what you need.

=IF( ISBLANK( $B5 ), 0, IF( ISBLANK( $C5 ), IF( I$3>$B5, $E$5, 0 ), IF( AND(
I$3>$B5, I$3<$C5 ), IF( I$3>=$D5, $F5, $E5 ), 0 ) ) )

Bill
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top