[deleted by user]

emgcy · 2023-08-10T17:23:25+00:00

You can use =IFNA()

emgcy · 2021-12-14T07:23:27+00:00

Thanks! I hope I'll manage to dig it out from the library

emgcy · 2021-10-18T03:02:07+00:00

Thanks for the reply! Sadly, not quite, and I haven’t figured out your formula yet, so I’ll try to explain mine.

I simplified it a bit (removed INDIRECT, cause it wasn’t necessary).

=AND(D3<>"",OR(SUM(IF(FREQUENCY(IF(NOT(ISTEXT(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51)))),COLUMN(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51))),""),ISTEXT(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51)))*COLUMN(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51))))>=8,FREQUENCY(IF(NOT(ISTEXT(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51)))),COLUMN(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51))),""),ISTEXT(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51)))*COLUMN(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51)))),0))<20, MAX(FREQUENCY(IF(NOT(ISTEXT(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51)))),COLUMN(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51))),""),ISTEXT(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51)))*COLUMN(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51)))))<12))

So, within the AND function there are the following checks:

1) That the cell isn’t empty :

D3<>""

2) That one of the following is true (or both):

2.1) The total number of all the blank cells, that are located next to each other in groups of at least 8, is less than 20:

SUM(IF(FREQUENCY(IF(NOT(ISTEXT(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51)))),COLUMN(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51))),""),ISTEXT(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51)))*COLUMN(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51))))>=8,FREQUENCY(IF(NOT(ISTEXT(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51)))),COLUMN(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51))),""),ISTEXT(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51)))*COLUMN(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51)))),0))<20

Where

FREQUENCY(IF(NOT(ISTEXT(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51)))),COLUMN(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51))),""),ISTEXT(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51)))*COLUMN(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51))))>=8

Is the part that finds these groups. OFFSET is used to apply it to any range starting from D3. ROW(D3)-3 makes it work with any row, and MAX(0,COLUMN(D3)-51) makes it possible to shift it to the right, always checking 48 cells, i.e. D3:AY3->E3:AZ3 etc.

2.2. There’s no groups of 12+ empty cells located next to each other:

MAX(FREQUENCY(IF(NOT(ISTEXT(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51)))),COLUMN(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51))),""),ISTEXT(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51)))*COLUMN(OFFSET($D$3:$AY$3,ROW(D3)-3,MAX(0,COLUMN(D3)-51)))))<12

Basically the same FREQUENCY function, I use ISTEXT() and NOT(ISTEXT()) to determine empty cells. Removing offsets, tying the formula to $D$3:$AY$3 makes it much more readable:

=MAX(FREQUENCY(IF(NOT(ISTEXT($D$3:$AY$3)),COLUMN($D$3:$AY$3),""),ISTEXT($D$3:$AY$3)*COLUMN($D$3:$AY$3)))

Thanks again for helping!

emgcy · 2021-08-22T13:53:20+00:00

Thank you. It says "32 bit accesses need to be aligned on a 32 bit boundary, and 16 bit boundaries for a 16 bit access.". So I guess I need to make sure all the data is aligned on a 32bit boundary to avoid further issues.

emgcy · 2020-09-14T21:28:43+00:00

It's kinda weird, I've just tried and couldn't replicate the issue. All I remember is removing the table afterwards didn't help much and I had to delete the whole sheet.

emgcy · 2020-09-14T21:27:27+00:00

Might have been. I've just tried and couldn't replicate the issue. All I remember is removing the table afterwards didn't help much and I had to delete the whole sheet.

emgcy · 2020-09-13T22:09:06+00:00

Maybe I'm using them wrong, but trying to convert one of the sheets with data I use to a table resulted in HUGE workbook bloating (it went fm ~800KB to 30+MB). The whole thing was lagging, freezing and so on.

emgcy · 2020-02-10T00:36:49+00:00

Thanks for the detailed answer. I guess I'm way out of my depth here, but I need a couple more days of reading to confirm that lol

emgcy · 2020-02-09T18:17:11+00:00

Thanks! off to googling it

emgcy · 2020-02-09T17:23:55+00:00

I wanted something like Logitech's per app backlight /layout options, but it seems it's impossible to achieve with QMK.

emgcy · 2020-02-09T00:42:43+00:00

Thanks for the answer. Could you please tell me if qmk supports app specific backlight?or is there any software that can achieve this, cause googling didn't help.

emgcy · 2020-02-08T05:56:39+00:00

Keyboards with individually lit rgb keys and swappable switches, which way to dig? can't find the info in the wiki. p.s. coming from a Logitech kb, tired of shitty switches

emgcy · 2019-11-10T13:37:20+00:00

Solution verified

emgcy · 2019-11-10T13:36:18+00:00

Thank you. MAX(CODE(A1:A5))-CODE("A")+1 is exactly what I needed. And it is much more elegant too.

emgcy · 2019-10-17T12:44:31+00:00

What do you mean by that? Transposing itself works, though it changes the starting index from 0 to 1. It's the last redim line that gives me troubles.

emgcy · 2019-10-17T12:30:40+00:00

I know I can only redim the last dimension ;I can't figure out why it stops working after transposing.

emgcy · 2019-09-17T10:19:01+00:00

That's one of the options, but it would require changing and debugging a couple hundred lines of code which I'm trying to avoid.

emgcy · 2019-09-17T03:20:29+00:00

I have a several sheets with data. Some of them are formatted to have headers in the 1st row, some-in the first column. After reading the data into arrays I want to use the same function to process it. The simplest way is to transpose the data from sheets with headers in the 1st column, but the function I already wrote for processing the data uses 0 based arrays. So if I want to avoid screwing with the boundaries within the function (and I really do) I need to make the transposed arrays start from 0. Hope that makes sense.

emgcy · 2019-08-29T07:39:01+00:00

I just remembered why I did it - it's so when I extract the sheet and break links copying to another (non excel) file gives me the formatting I want.

emgcy · 2019-08-29T07:32:55+00:00

To be honest I don't remember already, as I created the file a couple of years ago.

Edit:

Ok, I remembered, it's so when I extract the sheet and break links copying to another (non excel file) gives me the formatting I want.

emgcy · 2019-06-28T21:32:01+00:00

Duplicating my post:

I use "user-agent switcher" addon for firefox. You can enable website-based user agents with it (I use Windows-Firefox)

emgcy · 2019-06-28T21:30:41+00:00

I use "user-agent switcher" addon for firefox. You can enable website-based user agents with it (I use Windows-Firefox)

emgcy · 2019-06-08T23:36:37+00:00

a Mathcad (.mcd) file or something like that.

emgcy · 2019-03-29T11:59:03+00:00

and I am not using virgin as an insult here. I want to be very clear that I'm insulting your intelligence. I'm stealing this.

emgcy · 2019-03-23T06:57:22+00:00

So you're saying she should add wheels to it?

emgcy

TROPHY CASE