TransWikia.com

Excel: Array formula inside another formula

Super User Asked on February 13, 2021

enter image description here

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

3 Answers

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

Worksheet Screenshot

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.

Correct answer by robinCTS on February 13, 2021

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#'.

Answered by Leonel B. on February 13, 2021

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

enter image description here

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.

Answered by Rajesh S on February 13, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP