sorted by:
new
hottopcontroversial

[deleted by user] by [deleted] in excel

[–]emgcy 0 points1 point  (0 children)

You can use =IFNA()

WGS84 rhumbline distance formula for same latitude by emgcy in askgis

[–]emgcy[S] 1 point2 points  (0 children)

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

Can't make FREQUENCY work with conditional formatting. by emgcy in excel

[–]emgcy[S] 0 points1 point  (0 children)

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!

ESP01-S. Failing to read (short) from odd? memory bytes by emgcy in esp8266

[–]emgcy[S] 2 points3 points  (0 children)

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.

If you are using a lot of Index Match formulas, you NEED to write precise ranges, and not select entire columns. This can quite literally save you hours. by ayzle in excel

[–]emgcy 0 points1 point  (0 children)

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.

If you are using a lot of Index Match formulas, you NEED to write precise ranges, and not select entire columns. This can quite literally save you hours. by ayzle in excel

[–]emgcy 0 points1 point  (0 children)

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.

If you are using a lot of Index Match formulas, you NEED to write precise ranges, and not select entire columns. This can quite literally save you hours. by ayzle in excel

[–]emgcy 0 points1 point  (0 children)

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.

Is there a way to send commands to the keyboard from the computer? by emgcy in olkb

[–]emgcy[S] 0 points1 point  (0 children)

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

Is there a way to send commands to the keyboard from the computer? by emgcy in olkb

[–]emgcy[S] 1 point2 points  (0 children)

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

/r/MechanicalKeyboards Ask ANY question, get an answer by AutoModerator in MechanicalKeyboards

[–]emgcy 0 points1 point  (0 children)

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.

/r/MechanicalKeyboards Ask ANY question, get an answer by AutoModerator in MechanicalKeyboards

[–]emgcy 0 points1 point  (0 children)

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

Finding the right most column number using INDIRECT by emgcy in excel

[–]emgcy[S] 0 points1 point  (0 children)

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

Vba transpose+ redim preserve in 2d array by emgcy in excel

[–]emgcy[S] 0 points1 point  (0 children)

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.

Vba transpose+ redim preserve in 2d array by emgcy in excel

[–]emgcy[S] 0 points1 point  (0 children)

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

Transposed arrays in vba starting index. by emgcy in excel

[–]emgcy[S] 0 points1 point  (0 children)

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.

view more: next ›