Excel: Array formula inside another formula

Question

B column copies values from column A and if it is not the minimum value in the given range, then adds 1, else it is left as it is with: =IF(NOT($A2=MIN($A$2:$A$7)),$A2+1,$A2)

C2 finds the cell with the minimum non-zero value from the range A2:A7 with =MIN(IF(A2:A7>0,A2:A7)) (with CTRLSHIFTENTER).

Is it possible to use this array formula as a sub-expression in another formula? Say I want to ADD 1 if the value in A2:A7 is NOT the minimum no-zero value?

So in the above example nothing will be added to A6. And D2:D7 values will be:

6    
5    
4    
3    
1    
1

robinCTS · Accepted Answer

Not sure if it's even possible to use that particular array formula as a sub-expression in another formula.

However, there's a work-around. You just need a non-array formula that works out the minimum non-zero value.

Such a formula can be created with the SMALL() function:

=SMALL($A$2:$A$7,COUNTIF($A$2:$A$7,0)+1)

The leads to the working solution

where the formula in  D2 is just the formula in B2 with the MIN() function replaced with the SMALL() function:

=IF(NOT($A2=SMALL($A$2:$A$7,COUNTIF($A$2:$A$7,0)+1)),$A2+1,$A2)

Note that this formula is not array entered!

I prefer writing these types of formulas refactored so they don't use a NOT() and the reference is outside the IF():

=$A2+IF($A2=SMALL($A$2:$A$7,COUNTIF($A$2:$A$7,0)+1),0,1)

My real favourite factorisation, though, is this:

=$A2+($A2<>SMALL($A$2:$A$7,COUNTIF($A$2:$A$7,0)+1))

Unfortunately, a lot of folks find that hacky and hard to read, so I tend not to use it.

Leonel B. · Answer

Currently there seems to exist a functionality for this, in case someone stumbles upon the same problem as I did.
Making use of the spill range functionality, you just need to append the '#' char to the address of the first cell of the spill range you want to use as part of some function, e.g. 'A1#'.

Rajesh S · Answer

Answer posted by @RobinCTS inspired me to do the useful corrections in both of your exciting formulas.

Your first formula should be written like shown below in Cell C2 to find minimum non-zero.

{=MIN(IF(A2:A7>0,A2:A7,A2+1))}

In Cell D2 write this array formula to add 1 if not minimum non-zero & fill it down.

{=IF(A2<>MIN(IF(A2:A7>0,A2:A7,A2+1)),A2+1,A2)}

N.B. Both above written formula are an array formula so finish both with Ctrl+Shift+Enter.

Excel: Array formula inside another formula

3 Answers

Add your own answers!

Ask a Question