ARRAY CONSTANTS, not working

[Patrik De Paep]

buffer size			{150,200,300,400,500,650,700}
Buffer	419 ltr

Hello,

I entered my array constant in a single Excel Spreadsheet Cell, see above.

Then I "named" the cell (Excel/Formulas/Define Name) "Buffer"

I read on the net I should not just refer tot the Cell in the NAME definition field, but use the EVALUATE function.

Problems:

1. I'm using a DUTCH version of EXCEL 2016 and can't find a translation for EVALUATE

2. In the Cell below the ARRAY CONSTANT I calculate a buffer size and want to find a way to pick the buffer as close as possible to sizes given in the array constant Cell named Buffer

Can anyone make a suggestion?

Kind regards,

Patrik

[#2]

Hi Patrik

First of all... why wold you enter a list of values in an array in a cell (but as text)???

The simple solution would be entering the values in individual cells (e.g. B4:B10).

=LOOKUP(B1+AGGREGATE(15,6,ABS(B4:B10-B1),1)*{-1;1},B4:B10)

In B1 is the calculated buffer size.

[#3]

Hi Patrick,

the translation to Dutch of the Excel 4.0 Macro function EVALUATE is EVALUEREN. However, I would like to recommend to use a pure formula solution, like Detlef suggested.

Best,

Mourad

[#4]

Thanks Mourad,

In short the user can enter a list of buffer sizes. Currently this list is entered between brackets in one cell because I can never predict the number of different buffer sizes the user will enter. The moment that I use multiple cells: 1. in order to scoop with the unknown number of cells I will need to allow for an entire column ; 2. the layout of my spreadsheet is gone (but Ok is not that important)

So if there exists a formula to convert the text in between brackets entered in one cell into a matrixconstant this would rely help. In the English version of Excel you could name the cell and in the refers to section enter the EVALUATE formula. But this seems not to work in the Dutch version.

As an example I want to use the funtion =INDEX(NameOfNamedCell;3)

Or =INDEX($A$5;3)

Kind regards,

Patrik De Paep

[#5]

Hello Patrik

You could use an Excel table for the buffer sizes. It expands automatically when a new number is entered. If less numbers are required then the user has to resize the table size manually. In either case the structured reference for the column is the same.

The formula I suggested above would be:

=LOOKUP(B1+AGGREGATE(15,6,ABS(tbl_Buffer[Size]-B1),1)*{-1;1},tbl_Buffer[Size])