Puzzles no. 519–523

Puzzles

Author: ExcelBI

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

Puzzle #519

Today we need to find very complex number. No not complex in mathematical sense, but have very interesting properties. We have to find numbers which itself, its square and cube, has identical sum of digits. It is time consuming, but satisfying calculation. Let’s check if it is hard to solve. Note that in some cases we are dealing with very large numbers, to which I used gmp package that have big integer bigz class.

Be also sure to change options to avoid scientific notation of numbers. Calculation crashed when “e” appears in number. To do it type in console: options(scipen = 999).

Loading libraries and data

library(tidyverse)
library(readxl)
library(gmp)

path = "Excel/519 Sum of Digits of Number, Square and Cube are Same.xlsx"
test = read_excel(path, range = "A1:A26")

Transformation

digit_sum <- function(x) {
  sum(as.integer(unlist(strsplit(as.character(x), ""))))
}

x <- 9
results <- tibble(x = numeric(), n = numeric(), s = numeric(), c = numeric())

while (nrow(results) < 25) {
  n <- digit_sum(x)
  s <- digit_sum(as.bigz(x)^2)
  c <- digit_sum(as.bigz(x)^3)
  
  if (n == s && n == c) {
    results <- results %>% add_row(x = x, n = n, s = s, c = c)
  }
  
  x <- x + 1
}

Validation

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

Puzzle #520

This time we have transformation that is pretty easy to make in Excel, but in R it is little bit more challening. There is nothing complicated to talk about so lets start our manipulation. We need to assign coordinate for each point of data place it in proper cell. Tricky but achievable.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/520 Alignment of Data.xlsx"
input = read_excel(path, range = "A1:I4")
test  = read_excel(path, range = "A8:E17", col_names = FALSE) %>% janitor::clean_names()

Transformation

result = input %>%
  pivot_longer(-c(1), names_to = "value_no", values_to = "value") %>%
  mutate(v_no = as.numeric(str_extract(value_no, "\\d+")),
         mod = (v_no - 1)  %/% 4) %>%
  select(-v_no) %>%
  nest_by(Group, mod) %>%
  filter(!all(is.na(data$value))) %>%
  mutate(data = list(list(t(data)) %>% as.data.frame())) %>%
  unnest(data) %>%
  ungroup() %>%
  select(-mod) %>%
  mutate(X4 = ifelse(row_number() == 9, NA, X4),
         X3 = ifelse(row_number() == 9, NA, X3))

colnames(result) = colnames(test)

Validation

identical(result, test)
# [1] TRUE

Puzzle #521

Current task sounds easy, but is not as easy as it sound. Let me tell that looks like 1/10, but is like 2/10. So still not hard. We need to find all dates that are between year 2000 (mistake in task) up to 3000, which consists of unique digits. That tells us that… distinct count of characters in such case would be 8. Let’s do it.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/521 Unique Digits in Dates.xlsx"
test = read_excel(path, sheet = 1)

Transformation

dates = seq(as.Date("1999-01-01"), as.Date("2999-12-31"), by = "days")
dates2 <- tibble(Dates = dates) %>%
  filter(str_remove_all(Dates, "-") %>%
           str_split("") %>%
           map_lgl(~ length(unique(.x)) == 8)) %>%
  mutate(Dates = as.character(Dates)) %>%
  select(Dates)

Validation

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

Puzzle #522

Only odd numbers… Yes, we need to find out if given number can be expressed as sum of consecutive odd numbers. So we need to make some looping to find different sequences. So far, for this small numbers loops are not really slow. Check it out.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/522 Express as Sum of Consecutive Odd Numbers.xlsx"
input = read_excel(path, range = "A1:A8")
test  = read_excel(path, range = "B1:B8")

Transformation

find_sum_consecutive <- function(n) {
  odd_numbers <- seq(1, n - 1, by = 2)
  for (start in seq_along(odd_numbers)) {
    for (length in 2:(length(odd_numbers) - start + 1)) {
      end <- start + length - 1
      if (end > length(odd_numbers)) {
        break
      }
      current_sum <- sum(odd_numbers[start:end])
      if (current_sum == n) {
        return(paste(odd_numbers[start:end], collapse = ", "))
      }
      if (current_sum > n) {
        break
      }
    }
  }
  return("NP")
}

result = input %>%
  mutate(`Answer Expected` = map_chr(Number, find_sum_consecutive)) %>%
  select(-Number)

Validation

identical(result, test)
# [1] TRUE

Puzzle #523

Today we have to build stairs of letters that looks like in example. As you know, everytime I need to make something graphical in Excel challenges I use matrices. And today it is not exeptcion. Let’s find out how to do it.

Loading libraries and data

library(tidyverse)
library(readxl)
library(janitor)

path = "Excel/523 Alphabets Staircase.xlsx"
given_number = read_excel(path, range = "B2", col_names = FALSE) %>% pull
test = read_excel(path, skip = 3, col_names = FALSE)

Transformation

M = matrix(nrow = given_number, ncol = given_number * 2 + 1)

for (i in 1:given_number) {
  start_col <- 2 * (i - 1) + 1
  M[i, seq(start_col, start_col + 2)] = LETTERS[i]
}
M = as_tibble(M) %>% remove_empty(c("rows", "cols"))
colnames(M) = colnames(test)

Validation

identical(M, 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.