# Thread: Help with complex IF function

1. ## Help with complex IF function

Hello All,

Please assist if you can. This is what I'm trying to get..

What I'm trying to do is to get Column C~N to pick up a IF statement based off of data coming from Column A and B.

Let's take A2 and A3 for example..

If A3 is empty.. Then C2:N2 will output B2/12 all throughout Row 2 to Column N (Dec.)

Now..

If A3 has a date (this date will always be greater than the previous Aug-13 in this case, then it will start the calculation at J2 (Aug.) going through N2

I hope this explains what I'm trying to do..

I've tried to use IF and Lookup formula but it just doesn't work and I can't get the logic right..

2. Try:

=IFERROR(IF(ISNUMBER(\$A3),IF(C\$1>=EOMONTH(\$A3,-1)+1,\$B2/12,0),\$B2/12),0)

copied across and down.

3. Hello NBVC,

Thanks so much for your assistance. I found it working for the first row.. however; when I try to duplicate that same formula across and down.. some cells towards the bottom are not responding correctly. I've checked if its the absolute value that had something to do with it, but its not. Could you please take a look for me, or even better if you could send me the version copied down.

Han

Originally Posted by NBVC
Try:

=IFERROR(IF(ISNUMBER(\$A3),IF(C\$1>=EOMONTH(\$A3,-1)+1,\$B2/12,0),\$B2/12),0)

copied across and down.

4. Here is what I get....

5. Hi there,

I see what you did and that was my initial fix too, but then you realize in the following rows (ex: row 6 and 7, where it should start on May-2013.. but it starts at Jan)

Originally Posted by NBVC
Here is what I get....

6. What I understood from your initial statement:

...If A3 is empty.. Then C2:N2 will output B2/12 all throughout Row 2 to Column N (Dec.)...
is that if the second date cell in the row was empty we would just divide B2 by 12 all the way across. In rows 6:7, A7 is empty, so I just divided B6 by 12 all the way across.

Did I misunderstand?

7. Hi
Whilst looking at this, I noticed that some of the dollar figures are formatted wrongly because in the pipeline sheet the leading dollar sign has been typed into the cell, making it text rather than a number.
This will force an error instead of the 1/12 calculation.

HTH

Herc

#### Posting Permissions

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