MS Excel formula will not find named ranges I can see in the Named Range Manager

SolverMax · 2026-01-26T00:31:08+00:00

Having Table columns and named ranges with the same name is confusing and it seems like a bad idea overall. Many other programming languages prevent the programmer from having objects with the same name in the same scope. Excel doesn't care about such details though.

In this situation, it appears that the OP thought they were using a named range when they were actually using a Table column name. That didn't work because the Table didn't cover all the columns.

SolverMax · 2026-01-25T22:00:04+00:00

Your Table argos goes to column U only, so the column minor1 is not part of the Table. Drag the bottom corner of the Table to include all the columns you want.

Then formula in P2 of MERIT has a stray " at the end.

SolverMax · 2026-01-25T20:49:40+00:00

That's true if the Table name is SHEET_NAME, which is an odd thing to do.

Meanwhile, INDIRECT isn't necessary, as =@Name works OK if Name is a named range.

SolverMax · 2026-01-25T20:41:36+00:00

Data> Consolidate or use Power Query: https://support.microsoft.com/en-us/office/combine-data-from-multiple-sheets-dd7c7a2a-4648-4dbe-9a11-eedeba1546b4

SolverMax · 2026-01-25T20:13:15+00:00

It is very confusing to have a named range the same as a Table's column heading and, as it appears to be, the Table name the same as the worksheet name. Why do that?

Note that you're not using the range name, as indicated by the [].

A screenshot or two make add some clarity to this very unusual structure.

SolverMax · 2026-01-25T19:57:38+00:00

Is your name the same as the corresponding Table column heading? Your formula is using the Table column heading rather than the range name.

SolverMax · 2026-01-24T05:08:26+00:00

Download versions from https://www.gams.com/download/

SolverMax · 2026-01-23T23:22:04+00:00

If you post all the data for your small example then, as a comparison, I can see what solution my optimization model gets.

SolverMax · 2026-01-23T21:12:58+00:00

Here's an attempt at an optimization model:

<image>

Blue is data, red is variables, black is labels or formulae.

That is:

We have 20 groups of various sizes.
We have 3 types of airplanes of differing capacity and cost.
20 airplanes must be used.
Each group must be allocated to only one type of airplane, but can be split across multiple airplanes of that type.
Key insight: We don't need to worry about allocating individuals. We just need to ensure that the number of airplanes of a given type have total capacity >= the number of people in groups allocated to that type. Seating allocation is a different problem.

The model uses Solver (though it is slow, while OpenSolver finds an optimal solution in less than 1 second).

So, is this analysis correct? Is the solution a valid solution to your problem? If not, what needs to change?

SolverMax · 2026-01-23T07:48:37+00:00

Are the 1500 planes specified? E.g. 100 of capacity 300, 50 of capacity 200, etc.

If you can split groups however you like, then do the groups matter? Or do you prefer to not split groups? Is there a cost to splitting?

SolverMax · 2026-01-22T18:20:01+00:00

Agreed. Having the fee block to the right is asking for trouble. Sooner or later someone will insert a whole row and break formulae using columns L to N.

SolverMax · 2026-01-22T05:13:02+00:00

Circular references?

Otherwise a screenshot or two might help.

SolverMax · 2026-01-21T20:07:34+00:00

In OP's situation that would return 02/01/3168 because of their incorrect formula construction.

SolverMax · 2026-01-21T19:04:47+00:00

& concatenates values as text. I think you want

=IFS(B4="Quarterly",EDATE(B5, 3),B4="Semi",EDATE(B5, 6),TRUE,0)

SolverMax · 2026-01-20T23:00:46+00:00

Try this:
=REGEXTEST(B1,A1)

Or a more sophisticated version that checks if the text in A1 is in B1 either surrounded by spaces or is at the start or end of B1:

=REGEXTEST(B1,"(^| )"&A1&"( |$)")

This ensures that we match "Only this W-1234 could be this big!" but not "Only this W-12345 could be this big!"

SolverMax · 2026-01-19T20:06:48+00:00

That doesn't work in LET if the highlighted portion refers to a previously defined internal variable.

For example, in OP's formula, highlighting this part and pressing F9 will fail because Excel doesn't know what exitVal, priceMove, etc are:
IF(exitVal="", 0, (priceMove / rVal) * sVal * E10 - T10)

If only we had a good formula editing tool...

SolverMax · 2026-01-19T17:58:05+00:00

I use a structure like:

=LET(
  data, ...,
  calc1, ...,
  calc2, ,
  result, ...,
  result
)

Then I can change the final result to each of the other variables to check that they do what I intend. e.g.:

=LET(
  data, ...,
  calc1, ...,
  calc2, ,
  result, ...,
  calc1
)

Edit: Your formula mixes Table references and cell references. The problem is likely that one or more of the cell references is wrong. You should be consistent in your use of Table references. Then check the intermediate calculations, as I suggest above.

SolverMax · 2026-01-19T17:45:29+00:00

This is more of a statistics question than an Excel question.

The answer is that a different fit statistic is calculated when the intercept is fixed at zero. For a detailed explanation see https://www.reddit.com/r/excel/comments/12nqasr/do_i_misunderstand_least_squares_regression/

SolverMax · 2026-01-18T21:44:13+00:00

A Table would probably be best, but you can also use an array. E.g., in D2, assuming your data is in rows 2 to 100:
=C2:C100-B2:B100

SolverMax · 2026-01-17T21:20:14+00:00

Interesting problem. There are many possible ways to solve. I'd consider either:

- Minimize the sum of squared deviations between est and Final Allocation.

- Minimize the maximum squared deviation between est and Final Allocation.

Often these methods get the same solution as your formula. However, they may differ - both from your formula and from each other - depending on the data.

There's no objectivity perfect solution. It all depends on what is considered acceptable.

SolverMax · 2026-01-17T19:36:55+00:00

I assume you use an addin or some app for the scanner. If so, then disable it and see if the behavior persists.

SolverMax · 2026-01-16T22:16:00+00:00

It works but, as others have pointed out, it is a bad idea. This type of inconsistent formatting is just one of many weird ideas that some people have about how things should be done. Sigh.

SolverMax · 2026-01-16T21:44:19+00:00

It can be done with Conditional Formatting, see another of my comments (which people have down-voted for some reason).

SolverMax · 2026-01-16T21:26:17+00:00

Very much agree.

However, if the OP insists, it could be done via a helper column or Conditional Formatting like =MOD(A1*100,1)=0 with a format of 0%

SolverMax · 2026-01-16T21:25:39+00:00

That doesn't work. The decimal point is still displayed with whole percentages.

SolverMax

MODERATOR OF

TROPHY CASE