How to simplify "multiple if" formula

[markx]

Hello,

I have a basic formula in J6 =EDATE(I6;1)

I'm try to extend this formula from J6 to the right (every cell adds one
month to the previous), but once the EDATE([...]6;1) will get bigger than
the value in cell D4, I would like to have the formula giving me "" (empty
cell).

I started with
=IF(EDATE(I4;1)>$D$4;"";EDATE(I4;1))
but it's not good enough: the first cell that is bigger than $D$4 is indeed
blank, but all the following give me #VALUE error.

So I modified to
=IF(OR(EDATE(I4;1)>$D$4;ISERROR(EDATE(I4;1)));"";EDATE(I4;1))
but it's the same: first cell bigger than D4 value is blank (that's fine!),
but then I still can't get rid of #VALUE errors that follow -().

So I reformulated it to
=IF(ISERROR(EDATE(I4;1));"";IF(EDATE(I4;1)>$D$4;"";EDATE(I4;1)))
and it's functionning correctly (finally!): all the cells bigger than the
value in D4 are blank!

I'm asking however myself if I can simplify the last formula and make it
look "nicer"...
The other question I have is why the second formula (with OR argument) is
not functionning like the third...

Thank you in advance for any hints regarding this,
Regards,

Mark

 

[Dav]

if(edate(i4;1)<=$d$4,edate(i4;1);"")

or use the column function

if(edate($i4;column()-9)<=$d$4,edate($i4;(column()-9);"")

J is the 10th column hence column-9=1 and will increase as the formula
is copied to the right

Regards

Dav

 

[Bob Phillips]

Perhaps this

=IF(I4="","",IF(EDATE(I4,1)>$D$6,"",EDATE(I4,1)))

The problem is that OR gets evaluated even if the first condition fails, so
you get the #VALUE

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Ron Rosenfeld]

Hello,

I have a basic formula in J6 =EDATE(I6;1)

I'm try to extend this formula from J6 to the right (every cell adds one
month to the previous), but once the EDATE([...]6;1) will get bigger than
the value in cell D4, I would like to have the formula giving me "" (empty
cell).

I started with
=IF(EDATE(I4;1)>$D$4;"";EDATE(I4;1))
but it's not good enough: the first cell that is bigger than $D$4 is indeed
blank, but all the following give me #VALUE error.

So I modified to
=IF(OR(EDATE(I4;1)>$D$4;ISERROR(EDATE(I4;1)));"";EDATE(I4;1))
but it's the same: first cell bigger than D4 value is blank (that's fine!),
but then I still can't get rid of #VALUE errors that follow -().

So I reformulated it to
=IF(ISERROR(EDATE(I4;1));"";IF(EDATE(I4;1)>$D$4;"";EDATE(I4;1)))
and it's functionning correctly (finally!): all the cells bigger than the
value in D4 are blank!

I'm asking however myself if I can simplify the last formula and make it
look "nicer"...
The other question I have is why the second formula (with OR argument) is
not functionning like the third...

Thank you in advance for any hints regarding this,
Regards,

Mark

The "trick", if you will, is to reference everything back to the original date.
Since you've changed references from Row 4 to Row 6, I'm not certain exactly
what you want, but something like this, will work. Adjust to fit your cell
references:

D6: Maximum Date
I6: Base Date
J6: =IF(edate($I$6,COLUMNS($A:A))>$D$6,"",edate($I$6,COLUMNS($A:A)))

Copy/Drag across as far as you wish.

The COLUMNS($A:A) function acts as a counter and will increase by one with each
cell dragged across.

In addition, by referring back to Base Date and using a increment, you avoid
the problem of unwanted date changes if base date is, for example, 31 Jan 2006.




--ron

 

[Pete_UK]

I think the problem is that once you have set a cell to blank then
EDATE( ... ) will return an error if it acts upon that cell.

Try this (a variation of your second formula):

=IF(I4="";"";IF(EDATE(I4;1)>$D$4;"";EDATE(I4;1)))

and then copy this across.

Hope this helps.

Pete