If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple IF statment help
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 |
#2
|
|||
|
|||
Multiple IF statment help
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" wrote: 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 |
#3
|
|||
|
|||
Multiple IF statment help
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($E42I$3,"",IF($D42I$3,$F42/12,"")) "Mike H" wrote: 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" wrote: 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 |
#4
|
|||
|
|||
Multiple IF statment help
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"",E42I3),"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" wrote: 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($E42I$3,"",IF($D42I$3,$F42/12,"")) "Mike H" wrote: 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" wrote: 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 |
#5
|
|||
|
|||
Multiple IF statment help
Hi Mike If E42 (end date) is prior (less than) I3 I want to put the cell blank Thanks again for your replies. "Mike H" wrote: 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"",E42I3),"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" wrote: 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($E42I$3,"",IF($D42I$3,$F42/12,"")) "Mike H" wrote: 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" wrote: 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 |
#6
|
|||
|
|||
Multiple IF statment help
Hi Mark
then I think what you want is =IF($D42="","",IF(AND($E42"",$E42I$3),"",$F42/12) -- Regards Roger Govier Mark D wrote: Hi Mike If E42 (end date) is prior (less than) I3 I want to put the cell blank Thanks again for your replies. "Mike H" wrote: 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"",E42I3),"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" wrote: 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($E42I$3,"",IF($D42I$3,$F42/12,"")) "Mike H" wrote: 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" wrote: 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 |
#7
|
|||
|
|||
Multiple IF statment help
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($E42I$3,"",IF($D42I$3,$F42/12,"")) Sorry I am sure I'm getting this wrong somewhere But "Roger Govier" wrote: Hi Mark then I think what you want is =IF($D42="","",IF(AND($E42"",$E42I$3),"",$F42/12) -- Regards Roger Govier Mark D wrote: Hi Mike If E42 (end date) is prior (less than) I3 I want to put the cell blank Thanks again for your replies. "Mike H" wrote: 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"",E42I3),"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" wrote: 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($E42I$3,"",IF($D42I$3,$F42/12,"")) "Mike H" wrote: 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" wrote: 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 . |
#8
|
|||
|
|||
Multiple IF statment help
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"",$E42I$3),"",IF($D4 2I$3,$F42/12,""))) -- Regards Roger Govier Mark D wrote: 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($E42I$3,"",IF($D42I$3,$F42/12,"")) Sorry I am sure I'm getting this wrong somewhere But "Roger Govier" wrote: Hi Mark then I think what you want is =IF($D42="","",IF(AND($E42"",$E42I$3),"",$F42/12) -- Regards Roger Govier Mark D wrote: Hi Mike If E42 (end date) is prior (less than) I3 I want to put the cell blank Thanks again for your replies. "Mike H" wrote: 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"",E42I3),"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" wrote: 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($E42I$3,"",IF($D42I$3,$F42/12,"")) "Mike H" wrote: 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" wrote: 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 . |
Thread Tools | |
Display Modes | |
|
|