Multiple IF statment help

[Mark D]

Morning all

Having some trouble in getting a formula together. Am hoping someone can
help me

I have to put a formula (believe it to be an if) which has 3 possible outcomes

For example
D42 has a start date - E42 an end date (although not all the time), F42 a
salary figure and finally I3-AR3 has months starting from 31/01/10 - 31/12/12

I need the statment to have the following

If the start date in D42 is prior to the month (I3) then take the base
salary in F42 and divide by 12.

However

If E42's endate is prior to (I3) then don't take F42 and divide by 12 (and
leave the cell blank)

finally

IF D42 has nothing in it then leave the cell blank

Can anybody help me, driving me nuts

Thank you

 

[Mike H]

Mark

If the start date in D42 is prior to the month (I3) then take the base

This isn't clear; well to me it isn't, what do you have in I3, is it a
number representing a month? a date? something else?. When comparing to I3
are we ignoring the year?
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mark D]

Sorry Mike I was worried I didnt put enough information in

I3 - AR 3 has a date in it (so I3 has 31/01/10, J3 28/02/10 and so on).

So I always need to compare the start date of the employee in this case to
the date in I3, if its prior to I3 etc then take the salary divided by 12),
unless the end date of the employee exists in which case do the calculation
off of the end date.

I wrote the following since posting on here which I think is working.

=IF($E42<I$3,"",IF($D42<I$3,$F42/12,""))

 

[Mike H]

Mark,

I think this satisfies all the conditions but I still don't understand what
to do if E42 is < i3 but you should be able top substitute the text with the
formula you want.

=IF(D42="","",IF(AND(E42<>"",E42<I3),"I don't understand",F42/12))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mark D]

Hi Mike

If E42 (end date) is prior (less than) I3 I want to put the cell blank

Thanks again for your replies.

 

[Roger Govier]

Hi Mark

then I think what you want is
=IF($D42="","",IF(AND($E42<>"",$E42<I$3),"",$F42/12)

 

[Mark D]

Hi Roger

Maybe it's my fault in explaining things but I am still having problems.

I entered your formula and it is generating the salary / 12 even if the
start date (D42) is > I3 (31/01/10)

The formula I have created works but I have 1 issue with is. That is if D42
is blank - it's still generating the salary / 12

=IF($E42<I$3,"",IF($D42<I$3,$F42/12,""))

Sorry I am sure I'm getting this wrong somewhere



But

 

[Roger Govier]

Hi

Sorry, I missed a parenthesis off my last formula, and, I hadn't catered
for the situation where D42 was not blank, but was also later that the
value in row 3
Try
=IF($D42="","",IF(AND($E42<>"",$E42<I$3),"",IF($D42<I$3,$F42/12,"")))