Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help required in merging two formulas into one
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. -- Remove the 'old' to reply to me. |
#2
|
|||
|
|||
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) "Col" wrote in message ... 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. -- Remove the 'old' to reply to me. |
#3
|
|||
|
|||
"Col" wrote
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='Plann er 1st Qtr'!L2,I2:L2-'Planner 1st Qtr'!L2)),I2:L2-'Planner 1st Qtr'!L2,0))) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help, Urgent Excel Formulas are not calculating | Excel Discussion (Misc queries) | |||
Problem with named formula's | Excel Worksheet Functions | |||
How to make Excel run limited number of formulas on a given worksh | Excel Discussion (Misc queries) | |||
Way to make Excel only run certain formulas on a worksheet? | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |