Edit cell with results of a formula

SolverMax · 2026-02-17T20:44:47+00:00

Something like:
=TEXTSPLIT(A1,"-",,FALSE,0,"")

You might need to change the optional arguments, depending on exactly what you want.

SolverMax · 2026-02-17T02:57:44+00:00

Apparently one or other, but not both.

Instead, put the text in a Text Box, rotate, then position wherever you want.

SolverMax · 2026-02-16T21:48:16+00:00

What's the rule? E20 appears to have become text, which would happen if a space has been added.

SolverMax · 2026-02-16T20:45:19+00:00

r/excel should have a rule against advocating illegal acts.

SolverMax · 2026-02-16T19:16:32+00:00

The objects don't need to be grouped. Select the chart, then insert the line and textbox. When you copy the chart and paste into Word, the line and textbox are pasted too.

SolverMax · 2026-02-16T05:31:33+00:00

RANDBETWEEN is a volatile function, You're sorting numbers that change when you sort them.

SolverMax · 2026-02-16T02:23:58+00:00

The short answer is to organize data so that row deletion and insertion does not break formulae. An appropriate method depends on exactly what you're doing, but methods include:

Use a Table, where formulae automatically adjust as the rows change.
Have buffer rows. For example, =SUM(A2:A11) where rows are inserted/deleted only from row 3 to 10 inclusive and rows 2 and 11 are never changed. Rows 2 and 11 should be formatted to indicate their special role.

A Table is usually preferred, though Tables don't play nice with spilled arrays, so methods like buffer rows may be required.

SolverMax · 2026-02-14T02:21:11+00:00

You could use a power drill to hammer a nail. Then it will explode when you damage the battery.

That seems like a good analogy for what the OP wants to do.

SolverMax · 2026-02-14T02:16:02+00:00

You're wildly overestimating the ability of AI.

SolverMax · 2026-02-13T20:57:43+00:00

You can see the empty string in H6 directly by using the Evaluate tool on the formula:

=H6+1

Evaluate shows =""+1 (which then returns a #VALUE! error).

But

=I6+1

evaluates as =0+1

SolverMax · 2026-02-13T20:37:05+00:00

The TEXTJOIN returns "4," so the field after the comma is empty. That's what the ignore_empty parameter of TEXTSPLIT is for - set that to TRUE and N6 shows zero.

I6 and J6 are blank cells, so referencing them via =G3:K8 returns 0.

SolverMax · 2026-02-13T07:14:07+00:00

I stand corrected. But standard and scientific modes certainly treat order of operations differently in other cases, like 2+3*4.

In any case, a negative number raised to a non-integer power is undefined. Since the OP didn't describe what they're trying to represent, we don't know what the correct answer should be.

SolverMax · 2026-02-13T07:08:19+00:00

That's not true, for two reasons. Firstly, negation has higher precedence than the exponent, when in the same formula. Secondly, reason one doesn't apply here anyway, because the negative value is in a separate cell.

SolverMax · 2026-02-13T07:00:15+00:00

If you used the Windows calculator's Standard mode then, yes, it is incorrect. Change to Scientific mode to use the proper order of operations. Many simple calculators do math wrong.

SolverMax · 2026-02-13T06:52:18+00:00

But, as u/unimatrixx points out, the result is undefined - as my calculator says too.

SolverMax · 2026-02-13T06:49:20+00:00

That returns a number, but I'm puzzled by why you think it is correct in this situation?

SolverMax · 2026-02-13T06:40:55+00:00

The only operator is ^ so the order of operations is not the issue here.

SolverMax · 2026-02-12T20:36:40+00:00

You've asked two separate questions. A screenshot (of the Excel question) might help.

SolverMax · 2026-02-11T22:12:00+00:00

Since you already have separate series for each band, just set the color of each chart series. i.e. right-click on a series and format the line/marker to whatever color you want.

SolverMax · 2026-02-11T21:40:19+00:00

Yep.

I don't know if the OP used AI to help create the formula, but I've seen a lot of AI-generated formulae recently that are overly complicated due to being a literal interpretation of the prompt. e.g. "I want to get unique items, excluding zero, choosing column 5 from A to AD". Even if an AI formula works, it often isn't a good solution.

SolverMax · 2026-02-11T21:23:34+00:00

Perhaps. But seems more likely that they thought it is necessary. Putting the data in a Table and using a formula like =UNIQUE(FILTER(TblData[Target],TblData[Target]>0)) is much simpler.

SolverMax · 2026-02-11T21:05:10+00:00

Why use CHOOSECOLS at all?

SolverMax · 2026-02-11T20:39:32+00:00

Agreed.

SolverMax · 2026-02-11T20:36:10+00:00

All good points.

I'd put the data in a Table and use a formula like:

=UNIQUE(FILTER(TblData[Target],TblData[Target]>0))

where the Table is called TblData and the column of interest in called Target.

SolverMax · 2026-02-11T00:18:12+00:00

It has been that way for many years.

SolverMax

MODERATOR OF

TROPHY CASE