N
nc
How can I include an array function within an IF function?
Thanks.
Thanks.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
P
Pete_UK
Depends what you want to do, but here's one approach:
=IF(X1="",0,array_formula)
commit with CTRL-SHIFT-ENTER.
However, if you want an alternative non-array solution to your earlier
post, check out my answer.
Hope this helps.
Pete
=IF(X1="",0,array_formula)
commit with CTRL-SHIFT-ENTER.
However, if you want an alternative non-array solution to your earlier
post, check out my answer.
Hope this helps.
Pete
N
nc
Peter
I am trying to use this array formula
=SUM(N(OFFSET(INDEX(Sheet1!$C$12:$H$16,0,MATCH(MIN($B2,3)&$C2,{"1E","2E","3E","1O","2O","3O"},0)),MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1),Sheet1!$B$12:$B$16,0)-1,0)))
I have tried your solution but does not seem to work.
I am trying to use this array formula
=SUM(N(OFFSET(INDEX(Sheet1!$C$12:$H$16,0,MATCH(MIN($B2,3)&$C2,{"1E","2E","3E","1O","2O","3O"},0)),MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1),Sheet1!$B$12:$B$16,0)-1,0)))
I have tried your solution but does not seem to work.
P
Pete_UK
My column headings were the opposite way round - E1, E2, E3, O1, O2,
O3, where E3 and O3 meant greater than 2 years.
I'm not sure what IF you want to incorporate within this array formula
from Bob.
Pete
O3, where E3 and O3 meant greater than 2 years.
I'm not sure what IF you want to incorporate within this array formula
from Bob.
Pete
N
nc
I would like to use Bob's array formula as below,
If(x1="PGR",SUM(N(OFFSET(INDEX(Sheet1!$C$12:$H$16,0,MATCH(MIN($B2,3)&$C2,{"1E","2E","3ÂE","1O","2O","3O"},0)),MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1),Sheet1Â!$B$12:$B$16,0)-1,0))),"")
The first condition returning the array formula.
If(x1="PGR",SUM(N(OFFSET(INDEX(Sheet1!$C$12:$H$16,0,MATCH(MIN($B2,3)&$C2,{"1E","2E","3ÂE","1O","2O","3O"},0)),MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1),Sheet1Â!$B$12:$B$16,0)-1,0))),"")
The first condition returning the array formula.