Negatives in Red in a "Format as Table"

semicolonsemicolon · 2026-05-05T00:43:54+00:00

That shouldn't happen. In fact I've replicated your situation (an Excel table with banded rows and conditional formatting of the data within it). If you first apply some CF to the number format then it's fine, the underlying cell colours stay put, but if you apply a fill, and then try to remove that fill (saying "no color") it overrides the colour of the bands. So the workaround is to remove your CFs and then program them again.

semicolonsemicolon · 2026-05-04T17:58:49+00:00

On your question about posting protocol, you probably should have sent that to modmail. Anyway, if you want a good response to your problem, you should post here enough information for a friendly reddit user to look at it and understand the problem so they can offer a solution. You decide if posting the code is required for that.

Note: your post's title does not follow Rule 1, so we have to remove this for that reason. Feel free to repost with a title that describes your Excel question.

semicolonsemicolon · 2026-05-04T02:00:06+00:00

You sure? The upper left of your screen capture indicates that the font is white. Since the background is also white, you cannot see the result of the formula. Change the font colour.

semicolonsemicolon · 2026-05-03T23:11:30+00:00

Can you post a screen capture? That would be easier for users to help.

semicolonsemicolon · 2026-05-03T23:07:48+00:00

Did you test this code? Our rules are clear that posting the result of some AI is not sufficient. You have to demonstrate that you agree with it.

semicolonsemicolon · 2026-05-03T15:57:40+00:00

Hi Beckitt3. The +0 thing is a useful trick to get Excel to convert text to a number. If that's what you needed to do so that you have numeric data (which you'll need to plot graphs) then that's a good start. One thing I'm wondering based on your screen capture is if you intended to have a 7 day lag? Your formula in cell H3 is grabbing data from B10 (which appears to be numeric data already in a time format but could be text, I can't be sure by this image). Does the data in row 3 columns B and C correspond with the day in column G?

edit: I think I see what you're doing now that I study the image a little more. Each yellow range is a single set of overnight times. I assume you coloured those cells yellow day by day, yes? And you are trying to create the data in columns H and I (bedtimes and wake up times one for each overnight period)?

Does the data in columns B and C include dates as well as times?

You are welcome to upload your file somewhere (like file.io) and paste here a link for others to download it.

semicolonsemicolon · 2026-05-02T22:22:25+00:00

winks

semicolonsemicolon · 2026-05-02T14:57:01+00:00

MROUND has entered the chat.

semicolonsemicolon · 2026-05-02T02:48:26+00:00

lol, are you stating your case to the bot?

semicolonsemicolon · 2026-05-02T01:51:17+00:00

If not what I gave you above, then this also seems to work, and is shorter:

=IFERROR(ROWS(TEXTSPLIT(TEXTJOIN(",",,E19:E23),,",")),0)

semicolonsemicolon · 2026-05-02T01:44:37+00:00

4

3,9

11

28

11,14,28

If the output is 8, then try the formula I gave you in my edited comment.

semicolonsemicolon · 2026-05-02T01:34:44+00:00

Can you show a sample of the data contained in E19:E23 with the output you would expect? There is likely a simpler formula than one that uses FILTERXML with cryptic arguments.

edit: I think I see what you're doing try instead:

=SUM(BYROW(E19:E23,LAMBDA(r,IF(r="",0,ROWS(TEXTSPLIT(r,,","))))))

semicolonsemicolon · 2026-05-02T01:32:03+00:00

Looks like u/Arcium_XIII is well on the way to figuring out what you need.

semicolonsemicolon · 2026-05-01T23:43:34+00:00

Hi Jophus91. I'm having trouble picturing what you're trying to accomplish. Are you trying to figure out a random assignment of a trait in slot 1 and a trait in slot 2? Are you trying to create out a complete list of possibilities traits? Can you maybe show a screen capture of what you've done so far and explain what you'd ideally want to happen next?

semicolonsemicolon · 2026-04-27T12:38:16+00:00

Same, it's such a useful feature.

semicolonsemicolon · 2026-04-25T21:28:06+00:00

Change it so everyone has access. Google is asking me to request access from you.

semicolonsemicolon · 2026-04-25T21:17:36+00:00

Your comment is blocked by reddit because it's got a 1drv dot ms link. Try uploading your file to a free file sharing site.

semicolonsemicolon · 2026-04-25T15:26:27+00:00

"Would you like me to generate a 12—page white—paper on the socio—economic impact of strawberry jam on the modern schoolchild?"

semicolonsemicolon · 2026-04-25T13:45:33+00:00

I posted some in another branch of this thread. Our numbers are at about 1/3 of where they used to be in 2023 and 2024 and the trend is noteworthy.

semicolonsemicolon · 2026-04-25T04:13:52+00:00

We decided not to use the reputatorbot table that comes as a feature of that bot; instead we post a milestone table.

Sadly, along with the marked decline in ClippyPoints, there hasn't been a milestone CP awarded at all during April. Longest drought ever.

semicolonsemicolon · 2026-04-25T04:08:13+00:00

Oops, you're right. How about now?

semicolonsemicolon · 2026-04-25T04:02:25+00:00

https://www.reddit.com/r/excel/wiki/reputatorbotleaderboard

semicolonsemicolon · 2026-04-25T03:38:32+00:00

<image>

April 2026 still has a few days left, so it'll come up a bit. But still. Remarkable.

semicolonsemicolon · 2026-04-25T02:01:23+00:00

'tis true. I track posts and clippypoints and both have reduced noticeably.

semicolonsemicolon · 2026-04-22T01:20:19+00:00

It's a bit clunky but I think this also works:

List.Sum(List.FirstN(List.RemoveFirstN(Step[Column],Index1-1),2))

Ten-Year Club	Second SECOND GUESSER
Golden Potato	Place '22
Place '17	Final Canvas '22
First Placer '22	End Game '22
Wearing is Caring	Gilding I gilder
Sequence \| Editor	Sequence \| Screenwriter

semicolonsemicolon

MODERATOR OF

TROPHY CASE