Help required in merging two formulas into one

[Col]

Hi all,



First, thanks to those who helped me out with my concatenation problem in an
earlier post.



Now another problem if I may,



I have a series of dates in cells I2:L2 which move up in ascending order
through the year.



I've done an array formula as below;



{=IF(I2:L2>='Planner 1st Qtr'!O2,I2:L2,"No")}



Which looks to see if a cell in the range I2:L2 is greater than the cell O2
on the Planner 1st Qtr tab (date type) and is formula is copied across cells
M2 to P2.



I then use the following formula to display the nearest date to the cell O2
on the Planner tab as above (as of course all four cells may be greater);



=IF(M2<>"No",M2,IF(N2<>"No",N2,IF(O2<>"No",O2,P2)))



Is there any way I can merge both formulas into one? I've tried all
different ways but keep getting errors.



Thanks again for any help,



Regards,



Colin.

 

[Bob Phillips]

Col,

Put this array formula in M2, and copy across

=IF(I2>='Planner 1st QTr'!$O$2,I2,MIN(IF($I$2:$L$2>='Planner 1st
QTr'!$O$2,$I$2:$L$2)))

but make sure you do it as a single cell array formula, not a multi-cell,
that is don't select M22 and then input it, just select M2, input it,
array it, and copy across.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Max]

I have a series of dates in cells I2:L2 which move up in ascending order
through the year.
I've done an array formula as below;
{=IF(I2:L2>='Planner 1st Qtr'!O2,I2:L2,"No")}
Which looks to see if a cell in the range I2:L2 is greater than the cell O2
on the Planner 1st Qtr tab (date type) and is formula is copied across cells
M2 to P2.
I then use the following formula to display the nearest date to the cell O2
on the Planner tab as above (as of course all four cells may be greater);
=IF(M2<>"No",M2,IF(N2<>"No",N2,IF(O2<>"No",O2,P2)))
Is there any way I can merge both formulas into one? I've tried all
different ways but keep getting errors.

Assuming you want to return only the nearest date which is either equal to
or later than the ref date in 'Planner 1st Qtr'!L2

Try, array-entered (press CTRL+SHIFT+ENTER), in say M2:

=IF(AND(I2:L2<'Planner 1st
Qtr'!L2),"",INDEX(I2:L2,MATCH(MIN(IF(I2:L2>='Planner 1st
Qtr'!L2,I2:L2-'Planner 1st Qtr'!L2)),I2:L2-'Planner 1st Qtr'!L2,0)))