# Neste IF statements with 3 variables

1. ## Neste IF statements with 3 variables

I am using Excel 2010 and trying to write and a nested IF statement that does 4 things.
1. Find text 2014 in column B, for budget year 2014.
2. If it finds 2014 in column B, then find two digit month text in column C.
3. If 1 & 2 are true then place column D in that month of 2014.
4. If column C has more than 1 month code in 2014 than split \$ amount of column D by total number of month codes in column C.

Here is my current formula that needs some work.
=IF(OR(MID(\$B4,1,4)="2014",MID(\$B4,6,4)="2014",),IF(OR(MID(\$C4,1,1)=TEXT(F\$2,"#"),MID(\$C4,3,1)=TEXT(F\$2,"#"),MID(\$C4,5,1)=TEXT(F\$2,"#"),),IF(LEN(\$C4)<3,\$D4,IF(LEN(\$C4)<=4,\$D4/2,IF(LEN(\$C4)>4,\$D4/3,0)))))

Ken  Register To Reply

2. ## Re: Neste IF statements with 3 variables

Hi and welcome to the forum looking at your formula, what is this part supposed to be doing?
IF(OR(MID(\$C13,1,1)=TEXT(F\$2,"#"),MID(\$C13,3,1)=TEXT(F\$2,"#"),MID(\$C13,5,1)=TEXT(F\$2,"#")),
F2=01 and C13 = 10,11,12

I would have thought that if you were looking for 01 in 10,11,12, you would not find a match, yet in F13, you have a value?  Register To Reply

3. ## Re: Neste IF statements with 3 variables

Here is another modification I tried that works a little better for mont 01 through 09. Not sure why I am getting FALSE in some cells.

=IF(OR(MID(\$B10,1,4)="2014",MID(\$B10,6,4)="2014",),IF(OR(MID(\$C10,2,1)=TEXT(I\$2,"#"),MID(\$C10,5,1)=TEXT(I\$2,"#"),MID(\$C10,8,1)=TEXT(I\$2,"#"),),IF(LEN(\$C10)<3,\$D10,IF(LEN(\$C10)<=5,\$D10/2,IF(LEN(\$C10)>5,\$D10/3,0)))))  Register To Reply

4. ## Re: Neste IF statements with 3 variables

I would have thought that if you were looking for 01 in 10,11,12, you would not find a match, yet in F13, you have a value?
before I go any further, can you comment on that please?  Register To Reply

5. ## Re: Neste IF statements with 3 variables

Here is a third iteration that fixes the FALSE but has problems when month cover 2 years (2013,2014 or 2014,2015). See row 30 for example, I only want data in January 2014 but also get December from 2013. I understand why it doesn't work but not sure how to fix it.

=IF(OR(MID(\$B30,1,4)="2014",MID(\$B30,6,4)="2014",),IF(OR(MID(\$C30,1,2)=F\$2,MID(\$C30,4,2)=F\$2,MID(\$C30,7,2)=F\$2,),IF(LEN(\$C30)<3,\$D30,IF(LEN(\$C30)<=5,\$D30/2,IF(LEN(\$C30)>5,\$D30/3,0))),0),0)  Register To Reply

6. ## Re: Neste IF statements with 3 variables

The Text function was truncating the F column to a single digit, now I am looking for the full text string "01","02","03" etc.  Register To Reply

7. ## Re: Neste IF statements with 3 variables

OK give this a try....

=IF(ISNUMBER(SEARCH(2014,\$B13,1)),IF(ISNUMBER(SEARCH(F\$2,\$C13,1)),IF(LEN(\$C13)<3,\$D13,IF(LEN(\$C13)<=4,\$D13/2,IF(LEN(\$C13)>4,\$D13/3,0))),""),"")  Register To Reply

8. ## Re: Neste IF statements with 3 variables

There is still the issue of row 30 where December is actually 2013 data and shouldn't appear. But the Month(s) column does not distinguish what year the month lies. This might require a little manual editing but will still save lots of time. The worksheet is actuall much bigger and linked to another worksheet that is still in flux. Once they approve the final update I will only need some minor edits for the 2014 cash flow budget. This is probabaly solved for now.
Thanks again,
Ken   Register To Reply

9. ## Re: Neste IF statements with 3 variables

Happy to help  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Tags for this Thread #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1