Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array function
How can I include an array function within an IF function?
Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array function
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 On Apr 23, 2:14*pm, nc wrote: How can I include an array function within an IF function? Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array function
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)),MA TCH(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. "Pete_UK" wrote: 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 On Apr 23, 2:14 pm, nc wrote: How can I include an array function within an IF function? Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array function
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 On Apr 23, 2:46*pm, nc wrote: 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","3*E","1O","2O","3O"},0)),M ATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1),Sheet 1*!$B$12:$B$16,0)-1,0))) I have tried your solution but does not seem to work. "Pete_UK" wrote: 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 On Apr 23, 2:14 pm, nc wrote: How can I include an array function within an IF function? Thanks.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array function
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. "Pete_UK" wrote: 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 On Apr 23, 2:46 pm, nc wrote: 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","3Â*E","1O","2O","3O"},0)), MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1),Shee t1Â*!$B$12:$B$16,0)-1,0))) I have tried your solution but does not seem to work. "Pete_UK" wrote: 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 On Apr 23, 2:14 pm, nc wrote: How can I include an array function within an IF function? Thanks.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OR function in array-entered IF function | Excel Worksheet Functions | |||
array function | Excel Worksheet Functions | |||
AND in an array function? | Excel Worksheet Functions | |||
#DIV/0! in the array function | Excel Worksheet Functions | |||
array function | Excel Discussion (Misc queries) |