Shortest formula to create a special array

**kayard** · 02-03-2011, 05:42 PM

Hi,

I need the shortest possible array formula to create an output array B given an input array A so that each element of B is

0 if the corresponding element of A is < 0,
1 if the corresponding element of A is >0
equal to the corresponding element of A when that is between 0 and 1

example: if A is -2 ; 0,5 ; 3 then B would be 0; 0,5; 1

I have come up with this formula which is too long for my task (considering that the input array A is quite a long array formula itself..)

Please Login or Register  to view this content.

I would like to have some trick like the MAX/MIN combo which doesn't work with arrays unfortunately... something like this where I have to repeat arrayA (the long input formula) only once

=MAX(0;MIN(arrayA;1))

Any idea ?

Thanks for your support and sorry for my english

Paolo

Italy

**DonkeyOte** · 02-04-2011, 04:27 AM

I'm not sure why you think your IF array is too long - it is a good / efficient single cell approach:

Please Login or Register  to view this content.

Using IFs as above is a good idea as it reduces the no. of calculations being performed per cell - min 1 max 2

Regards MAX & MIN - you can't use these in Arrays in this context given they are applied to the Array as a whole rather than to each cell iteratively.

**martindwilson** · 02-04-2011, 05:51 AM

coundnt you use a named formula? not sure myself tho.

**kayard** · 02-07-2011, 12:01 PM

The problem with the IF solution is that ArrayA is a very long formula whose result is an array therefore I'm trying not to repeat that more than once. The max/min combo allowed that ...

So basically I'm looking for an alternative which minimise the occurrence of ArrayA ... not really looking for performances here.

thanks anyway

regards

Paolo

**DonkeyOte** · 02-07-2011, 12:04 PM

Perhaps then it would make sense to detail how you are arriving at ArrayA

**shg** · 02-07-2011, 12:22 PM

Or put arrayA in a column and go from there.

**daddylonglegs** · 02-07-2011, 01:01 PM

Try using TEXT function to reference array once only, i.e.

=TEXT(ArrayA,"[<0]""0"";[>1]1;general")+0

That's the syntax I use, might need to change depending on your regional settings