Help required in merging two formulas into one

C

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.
 
B

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 M2:p2 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)
 
M

Max

Col said:
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)))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top