I have a complex worksheet/graph I'm building that requires a lot of array building. I've run into either a bug or a side-effect that prevents an array function from being evaluated properly when used in the following order of functions:
IF ( condition, OFFSET( ref, ROW(A1:A4), col))
Normally, OFFSET( ref, ROW(A1:A4) will give you an array of values. But the version inside the IF function doesn't work as expected when evaluating with the F9 key or the Evaluate Formula tool. Interestingly it does when performed with CSE (Ctrl+Shift+Enter).
I'm attaching a link to a minimal working example to illustrate the difference in behavior. Is there some way around this? I've tried using CHOOSE as well with no luck.
Link: Bad Functions! ROW in OFFSET in IF
Note: Some may ask why OFFSET. For what I need, the OFFSET function is necessary. The reason is because OFFSET allows you to not be restricted by 1:1 mapping. For instance, you can transform a 3x3 matrix into a 9x1 column. In my case, I'm needing transformations that aren't 1:1. So under "WITH OFFSET", but before apply the IF logic, if I wanted {"Apple";"Apple";"Ball";"Ball";"Car";"Car";"Dog";"Dog"}, I couldn't do that without OFFSET. I think of OFFSET like a way to perform programmatic loops. Sure, stepping from 1 to 4 with OFFSET is overkill, but when going from 1 to 10 by 2's or stepping through a range by prime numbers, or some other more complex pattern, you need OFFSET to accomplish this.
Now that I've hopefully explained why OFFSET is required, I hope we don't need to discuss why to use OFFSET or not. I hope there's a way to do it with the ROW, OFFSET and IF functions as shown in the example file. I'd also like to point out that I'm developing this workbook on Excel 2016 but it needs to be compatible with Excel 2010 (other users will use my template). And VBA is not an option.
IF ( condition, OFFSET( ref, ROW(A1:A4), col))
Normally, OFFSET( ref, ROW(A1:A4) will give you an array of values. But the version inside the IF function doesn't work as expected when evaluating with the F9 key or the Evaluate Formula tool. Interestingly it does when performed with CSE (Ctrl+Shift+Enter).
I'm attaching a link to a minimal working example to illustrate the difference in behavior. Is there some way around this? I've tried using CHOOSE as well with no luck.
Link: Bad Functions! ROW in OFFSET in IF
Note: Some may ask why OFFSET. For what I need, the OFFSET function is necessary. The reason is because OFFSET allows you to not be restricted by 1:1 mapping. For instance, you can transform a 3x3 matrix into a 9x1 column. In my case, I'm needing transformations that aren't 1:1. So under "WITH OFFSET", but before apply the IF logic, if I wanted {"Apple";"Apple";"Ball";"Ball";"Car";"Car";"Dog";"Dog"}, I couldn't do that without OFFSET. I think of OFFSET like a way to perform programmatic loops. Sure, stepping from 1 to 4 with OFFSET is overkill, but when going from 1 to 10 by 2's or stepping through a range by prime numbers, or some other more complex pattern, you need OFFSET to accomplish this.
Now that I've hopefully explained why OFFSET is required, I hope we don't need to discuss why to use OFFSET or not. I hope there's a way to do it with the ROW, OFFSET and IF functions as shown in the example file. I'd also like to point out that I'm developing this workbook on Excel 2016 but it needs to be compatible with Excel 2010 (other users will use my template). And VBA is not an option.
