Google-sheets – Google Sheets: Displaying month with leading zero in array formula

Your problem comes not from using named ranges, but that the multiplication =INDIRECT(X1)*C1 only uses the first row. =INDIRECT("B1:B")*C1 gives the same result.

I'm not sure what you're trying to do, but if you want a list of values, you could wrap the expression in ArrayFormula: =ARRAYFORMULA(INDIRECT(X1)*C1). This will fill column D with values from column B, multiplied by C1.

If you instead want the sum of all values from September, multiplied by C1, you would do something like: =SUM(INDIRECT(X1)*C1).

I've cooked up an example spreadsheet here.

You can achieve the effect of CLAMP(value, min, max) with

=IFERROR(max-SQRT(max-IFERROR(SQRT(value-min)^2+min,min))^2,max)

which is compatible with ARRAYFORMULA and uses value only once. Explanation:

1. SQRT(value-min)^2+min returns value if value>=min, and gives an error otherwise.
2. IFERROR returns the first argument, unless an error occurred in it, which case it returns the second
3. Thus, IFERROR(SQRT(value-min)^2+min,min) clamps the value from below by min.
4. The other steps do a similar thing with max.

The above formula can be simplified, reducing the references to max and min from three to two of each:

=max-IFERROR(SQRT(max-min-IFERROR(SQRT(value-min)^2,0))^2,0))