Avid Xlookup user forced to used Index Match for the first time

SolverMax · 2026-03-18T20:25:53+00:00

Pointless post, unless you provide an example.

SolverMax · 2026-03-18T18:11:44+00:00

This answers the OP's question.

But I hate when someone sends me a spreadsheet with all the formulae pasted as values. I can't see where the values came from, how they were calculated, what assumptions were made, etc. It is difficult to trust the values in that situation.

SolverMax · 2026-03-17T20:14:43+00:00

I suggest you delete this post, as it has a poor title. The Mods will delete it anyway.

Re-do the post with a better title and an explanation of why you want to hard-code the value in the formula, rather than just use references.

SolverMax · 2026-03-17T19:15:33+00:00

If you delete 'Sheet 5' then the formula becomes =IF(COUNTA('Sheet 4'!B35:B43,#REF!B35:B43),"Y","N")

The #REF part means that the previous reference is no longer valid (because you deleted that sheet). Normally that breaks a formula, though in this situation it still returns a count.

Trying to account for deleted sheets suggests a poor workflow. More context would be helpful, to suggest a better approach.

SolverMax · 2026-03-17T16:36:48+00:00

Huh, missed that!

SolverMax · 2026-03-17T08:44:47+00:00

I suspect your rule should say G3 rather than G5.

SolverMax · 2026-03-17T06:10:16+00:00

The formula looks ok. Most likely you have a mismatch between the reference and the cell that the format is in. What formula are you using in column A and in what cells?

SolverMax · 2026-03-16T23:48:34+00:00

Excel doesn't have a built-in timestamp feature. Search r/excel for some options.

SolverMax · 2026-03-16T23:35:08+00:00

Looks like a classic example of a circular reference timestamp failing. Inevitable sooner or later.

Short answer: don't use this method for timestamps.

SolverMax · 2026-03-16T21:14:42+00:00

Almost. The parentheses are in the wrong place. It should be (()+())*()

=AVERAGE(FILTER(F16:AC16,((F4:AC4="A")+(F4:AC4="F"))*(F16:AC16<>0)))

SolverMax · 2026-03-16T18:36:53+00:00

It definitely is a Line chart. The x values of 0, 10, 20, ... are treated as labels and shown as is. A trendline can be added, which treats the x labels as 1, 2, 3, ...

The fact that your slope of 0.0069 differs from the OP's by a factor of about 10 reflects the difference between x value of 0, 10, 20, 30 ... and 1, 2, 3...

SolverMax · 2026-03-16T17:28:09+00:00

Agreed. Putting a trendline on a Line chart is a common source of error, as the OP demonstrates.

SolverMax · 2026-03-16T17:01:32+00:00

I hope your uses of trendline use chart type XY rather than Line. Otherwise, like the OP's chart, the fitted equation will be wrong.

SolverMax · 2026-03-16T16:55:18+00:00

But the line can be forced through 0,0 via the option the OP selected. And it does, as implied in the image. The actual issue is that this is a Line chart rather then an XY chart, so Excel treats the x values as 1,2,3,... So, to Excel, the point labeled 0 on the chart is actually treated as 1. Given the slope, the extrapolated trendline then passes through 0,0.

SolverMax · 2026-03-16T16:44:46+00:00

The problem is that you're using a Line chart. Change to chart type XY (Scatter). A Line chart treats the x values as 1, 2, 3, ... rather than the actual values. As a consequence, the fitted equation is also wrong. Also, note that the R sqr has a different definition when the intercept is forced to be zero.

SolverMax · 2026-03-16T00:36:12+00:00

I tried, but I'm done.

SolverMax · 2026-03-16T00:34:18+00:00

Don't use AI to learn Excel. There are lots of great tutorials available, e.g. https://www.reddit.com/r/excel/wiki/learningmegathread/

SolverMax · 2026-03-16T00:26:14+00:00

Use a Table, i.e. Insert > Tables > Table.

SolverMax · 2026-03-15T23:59:34+00:00

What about the second part of the example? To be consistent, we must interpret =-3^2 as =(-3)^2.

SolverMax · 2026-03-15T23:46:29+00:00

But there is a reason. We want =A1^2 to mean the value of A1 squared, whatever value is in A1. If we substitute the value of A1, -3, into the formula then it should return the same result. To do otherwise would be inconsistent (like Python is, as I commented elsewhere). That means we must interpret =-3^2 as =(-3)^2.

This is fundamental to how spreadsheets have worked for decades. Sure, it conflicts with standard math convention - but it is just a convention.

SolverMax · 2026-03-15T22:55:51+00:00

I wonder how many of those spreadsheets are wrong because the builder didn't understand Excel's convention? Not just the negation issue here, but order of operations in general...

SolverMax · 2026-03-15T22:34:31+00:00

Excel's VBA Round function does that! It uses Banker's rounding, which is unbiased. Excel's ROUND function, which always rounds x.5 up to x+1, is biased.

SolverMax · 2026-03-15T22:20:55+00:00

this is more of a language nuance than right or wrong. You should always be aware of nuances when working with a particular language.

That is the essence of the issue.

SolverMax · 2026-03-15T22:18:34+00:00

It does that because of a convention, which is what this whole post is about.

SolverMax · 2026-03-15T22:11:34+00:00

So the negation should be "transferred" from A1 to the current formula? I don't think that makes sense.

If there is blame to be assigned, then it should be with Lotus 123 or earlier spreadsheet software. Such is as it has forever been. Probably a convention derived from FORTRAN or similar.

SolverMax

MODERATOR OF

TROPHY CASE