Help with complex IF function

han77729

New member
Joined
Dec 12, 2012
Messages
31
Reaction score
0
Points
0
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..

Please support,
 

Attachments

  • Formula Need Help.xlsx
    22.4 KB · Views: 24
Try:

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

copied across and down.
 
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.

thank you for your help!

Han

Try:

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

copied across and down.
 
Here is what I get....
 

Attachments

  • Copy of Formula Need Help.xlsx
    27.5 KB · Views: 22
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)



Here is what I get....
 
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?
 
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
 
Back
Top