R Solution for Excel Puzzles

Puzzles no. 494–498

Puzzles

Author: ExcelBI

All files (xlsx with puzzle and R with solution) for each and every puzzle are available on my Github. Enjoy.

Puzzle #494

Sometimes we are exploring new types of numbers, that although they are integers, they have some interesting properties. Today is the time for Tech Numbers. They always have even number of digits to enable cutting into halves. Then we are summing those halves, square the sum and we are getting… original numbers. So we need to find first 10 Tech numbers. I choose way by filtering down according to given properties. Here is how.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/494 Tech Numbers.xlsx"
test = read_excel(path)

Transformation

is_perfect_square = function(x) {
  sqrt_x = sqrt(x)
  return(sqrt_x == floor(sqrt_x))
}

has_even_number_of_digits = function(x) {
  return(nchar(x) %% 2 == 0)
}

table = tibble(x = 1:1e8) %>%
  mutate(x = as.numeric(x)) %>%
  filter(is_perfect_square(x)) %>%
  filter(has_even_number_of_digits(x)) %>%
  mutate(first_half = substr(x, 1, nchar(x) / 2),
         second_half = substr(x, nchar(x) / 2 + 1, nchar(x))) %>%
  filter((as.numeric(second_half) + as.numeric(first_half))**2 == x) %>%
  head(10) %>%
  select(x)

Validation

identical(table$x, test$`Answer Expected`)
# [1] TRUE

Puzzle #495

We already played with palindromes, even more than once. But today we are asked to count and sum all palindromes of given length. We can filter palindromes from all numbers, but last time I realize that it is faster to generate them from halves. And I used this approach here. Maybe code is little bit longer, but much faster in cases of 7 and 8 digit palindromes. Check it out.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/495 Sum of N Digit Palindrome Numbers.xlsx"
input = read_xlsx(path, range = "A2:A9")
test  = read_xlsx(path, range = "B2:C9")

Transformation

  if (num_digits < 1) {
    stop("Number of digits must be at least 1")
  }
  if (num_digits == 1) {
    return(0:9)
  }
  half_digits <- ceiling(num_digits / 2)
  start_num <- 10^(half_digits - 1)
  end_num <- 10^half_digits - 1
  palindromes <- vector("integer", length = 0)
  
  for (i in start_num:end_num) {
    num_str <- as.character(i)
    rev_str <- paste0(rev(strsplit(num_str, "")[[1]]), collapse = "")
    if (num_digits %% 2 == 0) {
      palindrome_str <- paste0(num_str, rev_str)
    } else {
      palindrome_str <- paste0(num_str, substring(rev_str, 2))
    }
    palindromes <- c(palindromes, as.integer(palindrome_str))
  }
  return(palindromes)
}

result = input %>%
  mutate(palindromes = map(N, generate_all_palindromes)) %>%
  mutate(Count = map_dbl(palindromes, length),
         Sum = map_dbl(palindromes, sum)) %>%
  select(Count, Sum)

Validation

identical(result, test)
# [1] TRUE

Puzzle #496

Some cleaning today… We have more than one unit of data in one cell of spreadsheet. But it is not a thing that we can not work out. Actually it is pretty easy if you would think about it.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/496 Sum Marks.xlsx"
input = read_excel(path, range = "A2:B7")
test  = read_excel(path, range = "D2:E6")

Transformation

result = input %>%
  separate_rows(Subjects, sep = ", ") %>%
  separate(Subjects, into = c("Subjects", "Marks"), sep = "(?<=\\D)(?=\\d)") %>%
  mutate(Subjects = str_remove_all(Subjects, "[^[:alpha:]]")) %>%
  summarise(Total = sum(as.numeric(Marks)), .by = Subjects) %>%
  arrange(Subjects)

Validation

identical(result, test)
#> [1] TRUE

Puzzle #497

Today we received list of 100 numbers, and we are asked to sum them up in certain way. First cell will get sum of first number, second of next two, third of next 3 and so on. An idea came to my mind, so we are on position 1, 3, 6 after those thre steps only. Wait, we are using 1,2,3 etc numbers in sequence. And I realized that we had this concept already at our challenges. Puzzle number 415 was about cyclopic triangular numbers. Today we don’t need cyclops, but triangular number is exactly what we need. Numbers that can form triangle like on billard table. And then puzzle become easy.

Loading libraries

library(tidyverse)
library(readxl)

path = "Excel/497 Sum for Increasing Range.xlsx"
input = read_excel(path, range = "A1:A100")
test  = read_excel(path, range = "C1:D15")

Transformation

is_triangular = function(n) {
  n = 8 * n + 1
  return(floor(sqrt(n)) == sqrt(n))
}

result <- input %>%
  mutate(row = row_number(),
         triangular = is_triangular(row),
         cumsum = cumsum(triangular),
         Cells = ifelse(!triangular, cumsum + 1, cumsum)) %>%
  summarise(Sum = sum(Numbers), .by = Cells) %>%
  mutate(Cells = ifelse(Cells == max(Cells), "Remaining", Cells))

Validation

identical(result, test)
#> [1] TRUE

Puzzle #498

Although we don’t have FIFA World Cup right now, but rather finals of continental federations cups like UEFA Euro 2024 or Copa America, our todays challenge has very close topic. We have summary of in which year teams get the championship, but we need to transform it to list of year and winner in order of time. Check out my solution.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/498 Soccer Champions Alignment.xlsx"
input = read_xlsx(path, range = "A2:B10")
test = read_xlsx(path, range = "D2:E24")

Transformation

result = input %>%
  separate_rows(Years, sep = ",") %>%
  mutate(Years = as.numeric(Years)) %>%
  arrange(desc(Years)) %>%
  select(Year = Years, Winner = Winners)

Validation

identical(result, test)
# [1] TRUE

Feel free to comment, share and contact me with advices, questions and your ideas how to improve anything. Contact me on Linkedin if you wish as well.
On my Github repo there are also solutions for the same puzzles in Python. Check it out!

R Solution for Excel Puzzles was originally published in Numbers around us on Medium, where people are continuing the conversation by highlighting and responding to this story.