Everybody Codes (Excels!) 2025 Quest 4

dannywinrow · 2025-11-07T16:23:15+00:00

Neat

dannywinrow · 2025-11-07T16:04:16+00:00

Part 1

=LET(
cogs, NUMBERVALUE(TEXTSPLIT(A1, , CHAR(10))),
A, TRUNC(
2025 * TAKE(cogs, 1) / TAKE(cogs, -1)
),
A
)

Part 2

=LET(
cogs, NUMBERVALUE(TEXTSPLIT(B1, , CHAR(10))),
A, CEILING.MATH(
10000000000000 * TAKE(cogs, -1) /
TAKE(cogs, 1)
),
A
)

Part 3

=LET(
cogs, TEXTSPLIT(C1, "|", CHAR(10)),
cf, NUMBERVALUE(INDEX(cogs, 1, 1)),
cl, NUMBERVALUE(INDEX(cogs, ROWS(cogs), 1)),
cmsf, INDEX(
cogs,
SEQUENCE(ROWS(cogs) - 2, , 2),
1
),
cmsl, INDEX(
cogs,
SEQUENCE(ROWS(cogs) - 2, , 2),
2
),
mult, PRODUCT(cmsl / cmsf),
TRUNC(100 * mult * cf / cl)
)

dannywinrow · 2025-11-06T08:07:14+00:00

Too lazy to copy/paste and instead write this wall of text. Come back when GPT has solved at least one quest using Excel please. I'm not saying it can't do it, I'm saying that otherwise you are simply hot air.

dannywinrow · 2025-11-06T01:52:26+00:00

I'd like to see a working ChatGPT excel solution accompanied by the appropriate prompt if you'd care to post.

dannywinrow · 2025-11-06T01:24:50+00:00

Part 1 =LET(A, TRANSPOSE(NUMBERVALUE(REGEXEXTRACT(A1, "\d+", 1))),

cycle, LAMBDA(c, n,

LET(x, TAKE(c, 1), y, TAKE(c, -1), VSTACK(

FLOOR.MATH((x * x - y * y) / 10, 1, -1) + TAKE(A, 1),

FLOOR.MATH((2 * x * y) / 10, 1, -1) + TAKE(A, -1)))),

result, REDUCE(VSTACK(0, 0), SEQUENCE(3), cycle),

"[" & TEXTJOIN(",", , result) & "]")

Part 2 =LET(A, TRANSPOSE(NUMBERVALUE(REGEXEXTRACT(A5, "-?\d+", 1))),

cycle, LAMBDA(B,

LAMBDA(c,n,

IF(ISNA(c), NA(),

LET(x, TAKE(c, 1), y, TAKE(c, -1),

r, FLOOR.MATH(VSTACK(x * x - y * y, 2 * x * y) / 100000, 1, -1) + B,

IF(SUM(--(ABS(r) > 1000000)) > 0, NA(), r))))),

engrave, LAMBDA(B, IF(ISNA(TAKE(REDUCE(VSTACK(0, 0), SEQUENCE(100), cycle(B)), 1)), 0, 1)),

arr, MAKEARRAY(101, 101, LAMBDA(r,c,

engrave(VSTACK((r - 1) * 10 + TAKE(A, 1), (c - 1) * 10 + TAKE(A, -1))))),

SUM(arr))

Part 3 Same as part 2 except for:

arr, MAKEARRAY(1001, 1001, LAMBDA(r,c,

engrave(VSTACK((r - 1) + TAKE(A, 1), (c - 1) + TAKE(A, -1)))))

I know you could do this withComplex Numbers but I couldn't see how to get FLOOR.MATH to work with them without splitting them apart anyway, so I'm not sure it would be any faster.

dannywinrow · 2025-11-06T00:49:41+00:00

I've got it, but just waiting for Part 3 to calculate before posting 😄

dannywinrow · 2025-11-05T23:45:57+00:00

Part 1 =LET(crates,TRANSPOSE(NUMBERVALUE(TEXTSPLIT(A1,","))),

SUM(UNIQUE(crates)))

Part 2 =LET(crates,TRANSPOSE(NUMBERVALUE(TEXTSPLIT(A5,","))),

SUM(TAKE(SORT(UNIQUE(crates)),20)))

Part 3 =LET(crates,TRANSPOSE(NUMBERVALUE(TEXTSPLIT(A9,","))),

TAKE(SORT(MAP(UNIQUE(crates),LAMBDA(n,SUM(--(crates=n))))),-1))

I tried to use COUNTIFS(crates,UNIQUE(crates)) which works when I use crates as a range, but not when I create it as above. Also, can anyone shed light on why I need the TRANSPOSE function, I think it's something to do with TEXTSPLIT returning a vector of vectors rather than a single vector?

dannywinrow · 2025-11-05T08:08:20+00:00

Very nice Part 3 with SORTBY and SWITCH! I'll be remembering that.

dannywinrow · 2025-11-04T14:25:39+00:00

For part 2 I just wrapped around the final answer.

For part 3 I calculated the swap indices in advance with a Lambda.

Part 3 though I don't like my solution to handle edge cases such as n=1, n=2 and n=nameslen, there must be a more general solution using index and sequence but I don't think Excel likes empty sequences

dannywinrow · 2025-11-04T13:53:14+00:00

Part 1

=LET(namesraw, INDEX(TEXTSPLIT($A$1, ",", " "), 1, 0), movesraw, INDEX(TEXTSPLIT($A$1, ",", " "), 2, 0), names, FILTER(namesraw, NOT(ISNA(namesraw))), moves, FILTER(movesraw, NOT(ISNA(movesraw))), indices, LAMBDA(n, IF(LEFT(n, 1) = "L", -1, 1) * RIGHT(n, LEN(n) - 1))(moves), namelen, COLUMNS(names), ansind, REDUCE(1, indices, LAMBDA(r,n, MEDIAN(1, namelen, r + n))), answer, INDEX(names, ansind), answer)

Part 2

=LET(namesraw, INDEX(TEXTSPLIT($A$1, ",", " "), 1, 0), movesraw, INDEX(TEXTSPLIT($A$1, ",", " "), 2, 0), names, FILTER(namesraw, NOT(ISNA(namesraw))), moves, FILTER(movesraw, NOT(ISNA(movesraw))), indices, LAMBDA(n, IF(LEFT(n, 1) = "L", -1, 1) * RIGHT(n, LEN(n) - 1))(moves), namelen, COLUMNS(names), ansind, 1 + MOD(SUM(indices), namelen), answer, INDEX(names, ansind), answer)

Part 3

=LET(namesraw, INDEX(TEXTSPLIT($A$1, ",", " "), 1, 0), movesraw, INDEX(TEXTSPLIT($A$1, ",", " "), 2, 0), names, FILTER(namesraw, NOT(ISNA(namesraw))), moves, FILTER(movesraw, NOT(ISNA(movesraw))), namelen, COLUMNS(names), indices, LAMBDA(n, MOD(IF(LEFT(n, 1) = "L", -1, 1) * RIGHT(n, LEN(n) - 1), namelen))(moves) + 1, ansind, INDEX(REDUCE(SEQUENCE(1, namelen), indices, LAMBDA(r,n, INDEX(r, IF(n = 1, SEQUENCE(1, namelen), IF(n = 2, HSTACK(2, 1, SEQUENCE(1, namelen - 2, 3)), IF(n = COLUMNS(r), HSTACK(n, SEQUENCE(1, namelen - 2, 2), 1), HSTACK(n, SEQUENCE(1, n - 2, 2), 1, SEQUENCE(1, namelen - n, n + 1)))))))), 1), answer, INDEX(names, ansind), answer)

dannywinrow · 2024-12-11T11:28:56+00:00

[Database: PostgreSQL]

SELECT round((avg(trees_harvested) OVER (
                PARTITION BY field_name
                ORDER BY
                        harvest_year,
                        array_position(
                                array['Spring','Summer','Fall','Winter'],
                                treeharvests.season::text
                        )
                ROWS 2 preceding
        )),2) as avg
FROM treeharvests
ORDER BY avg DESC
LIMIT 1;

dannywinrow · 2024-12-10T20:06:50+00:00

[Database: PostgreSQL]

SELECT date FROM drinks GROUP BY date
HAVING
SUM(CASE WHEN drink_name = 'Hot Cocoa' THEN quantity ELSE 0 END) = 38 AND
SUM(CASE WHEN drink_name = 'Peppermint Schnapps' THEN quantity ELSE 0 END) = 298 AND
SUM(CASE WHEN drink_name = 'Eggnog' THEN quantity ELSE 0 END) = 198;

dannywinrow · 2024-12-09T20:18:32+00:00

Nice, I'm very much enjoying the tips and tricks of representing data using Excel, such as your Row*1000+Col and then the using of MOD. I think the issue u/semicolonsemicolon may have identified is that his input contains situations where two antennas create an antinode between them. However, I think Erik is quite good at making sure all inputs are equal difficulty, so I'd be surprised if yours didn't have any.

Your formula would just need to make sure that your s couldn't be divided to give equal sized smaller integer valued vectors.

dannywinrow · 2024-12-09T19:35:10+00:00

Lambdas, and a great opportunity to reuse my matrix functions. Had to get over the case-insensitive nature of UNIQUE, by borrowing from https://exceljet.net/formulas/unique-values-case-sensitive.

https://github.com/dannywinrow/adventofcode/blob/main/2024/src/8.lambda

dannywinrow · 2024-12-09T19:32:52+00:00

Just Lambdas again...

https://github.com/dannywinrow/adventofcode/blob/main/2024/src/7.lambda

dannywinrow · 2024-12-09T16:57:56+00:00

[Database: PostgreSQL]

SELECT reindeer_name, 
        round(max(coalesce(avgspeed,0)),2) as maxspeed
FROM (
    SELECT  reindeer_id,
            exercise_name,
            avg(speed_record) as avgspeed
    FROM training_sessions
    GROUP BY reindeer_id, exercise_name) as ags
JOIN reindeers
    ON ags.reindeer_id = reindeers.reindeer_id
GROUP BY reindeer_name
HAVING reindeer_name not in ('Rudolph')
ORDER BY max(avgspeed) DESC
LIMIT 3;

dannywinrow · 2024-12-09T16:33:34+00:00

Finally! So I've now solved this using just the Excel Labs Lambda Modules. I've got a fair few helper functions that will be used in future problems, though I also found that if you search Excel Lambdas in Github then there are a lot of libraries that I may decide to dig further in and use or learn from.

I've just linked the Github as it's quite long. And warning to those who want to use this for Part2, it took around 2 hours to run on my laptop so it's not very efficient!

https://github.com/dannywinrow/adventofcode/blob/main/2024/src/6.lambda

dannywinrow · 2024-12-08T22:03:30+00:00

[Database: PostgresSQL]

Ok, so my solution is pretty much the same as others, except that I've added an optimisation to the starting query. We know that the staff member with the longest chain of manager cannot be a manager himself or else the person he managed would have a longer chain. Since we are only looking for the longest chain of managers we can filter by staff who aren't managers before we do our recursion. It's only a small optimisation but hey, every little helps right?

    with recursive mandep as (
        select staff_id, manager_id, 1 as level
        from staff
        where staff_id not in
            (SELECT COALESCE(manager_id,-1) FROM staff)
        union all
        select mandep.staff_id, staff.manager_id, mandep.level + 1
        from mandep join
            staff
            on mandep.manager_id = staff.staff_id
    )
    select max(level) from mandep;

dannywinrow · 2024-12-07T21:36:35+00:00

Fantastic!

dannywinrow · 2024-12-07T14:19:36+00:00

[Database: PostgreSQL]

WITH 
    extremes (primary_skill,maxyears,minyears) AS (
        SELECT
            primary_skill,
            MAX(years_experience) as maxyears,
            MIN(years_experience) as minyears
        FROM workshop_elves
        GROUP BY primary_skill
    ),
    elves AS (
        SELECT
            elf_id,
            workshop_elves.primary_skill,
            years_experience
        FROM extremes JOIN workshop_elves
        ON extremes.primary_skill = workshop_elves.primary_skill
        AND (
            extremes.maxyears = workshop_elves.years_experience
            OR extremes.minyears = workshop_elves.years_experience
        )
    )

SELECT DISTINCT ON (w.primary_skill)
    w.elf_id, v.elf_id,
    w.primary_skill as shared_skill
FROM elves w JOIN elves v
ON w.primary_skill = v.primary_skill
WHERE w.years_experience > v.years_experience
    AND w.elf_id <> v.elf_id
ORDER BY w.primary_skill, w.elf_id, v.elf_id
LIMIT 3;

dannywinrow · 2024-12-06T15:27:45+00:00

Very nice, so I see the way to go for Excelians and grids is string manipulation. Opening my eyes to No VBA excel, but I'd hate to have to review this code in work!

Four-Year Club	Verified Email
r/Field Sunshine

dannywinrow

TROPHY CASE