Puzzles no. 404–408

Puzzles

Author: ExcelBI

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

Puzzle #404

Can analyst make something that looks good? Of course… Can analyst draw with numbers? Once more yeah. But today, like some times in past already, we have another way. I usually name making charts and dashboards — drawing or painting with numbers. Not today. We just recreate one specific graphic filling fields of spreadsheet (or in our case, make this graphic in console). And as you see above it is… Star-Spangled Banner aka flag of the USA.

Load libraries and data

library(tidyverse)
library(readxl)

test = read_excel("Excel/404 Generate US ASCII Flag.xlsx", range = "A1:AL15",
                  col_names = FALSE, .name_repair = "unique") %>% as.matrix() 
# remove attribute "names" from matrix
attr(test, "dimnames") = NULL

result = matrix(NA, nrow = 15, ncol = 38)r

Transformation

# border of flag
result[1,] = "-" 
result[15,] = "-"
result[2:14,1] = "|"
result[2:14,38] = "|"

# stripe section
for (i in 2:14){
  for (j in 2:37){
    if (i %% 2 == 0){
      result[i,j] = 0
    } else {
      result[i,j] = "1"
    }
  }
}

# star section
for (i in 2:10){
  for (j in 2:12){
    if (i %% 2 == 0){
      if (j %% 2 == 0){
        result[i,j] = "*"
      } else {
        result[i,j] = NA
      }
    } else {
      if (j %% 2 == 0){
        result[i,j] = NA
      } else {
        result[i,j] = "*"
      }
    }
  }
}

Validation

identical(result, test)
# [1] TRUE

Puzzle #405

Did you know sandwich numbers? That is that unique kind of numbers that as both neighbours has prime numbers, so they are like between two slices of toast bread. And our task is to find first 100 of sandwich numbers together with their “breads” aka neighbouring primes.

Load libraries and data

library(tidyverse)
library(readxl)

test = read_excel("Excel/405  Sandwich Numbers.xlsx", range = "A1:C101") %>% janitor::clean_names()

Transformation

is_prime <- function(x) {
  if (x <= 1) return (FALSE)
  if (x == 2 || x == 3) return (TRUE)
  if (x %% 2 == 0) return (FALSE)
  for (i in 3:sqrt(x)) {
    if (x %% i == 0) return (FALSE)
  }
  TRUE
} # of course I could use primes package, but I decided otherwise :D

is_sandwich <- function(x) {
  is_prime(x-1) && is_prime(x+1)
}

find_first_n_sandwich_numbers <- function(no) {
  keep(1:10000, is_sandwich) %>%
    unlist() %>%
    head(no) 
}

a = find_first_n_sandwich_numbers(100) 

check = tibble(sandwich_number = a) %>%
  mutate(before_number = sandwich_number - 1,
         after_number = sandwich_number + 1) %>%
  select(2,1,3)

Validation

all.equal(test, check)
# [1] TRUE

Puzzle #406

I suppose that in every educational system at least once Pythagorean Theorem is mentioned. In this puzzle given area and length of hypotenuse we have to find length of other two sides of right angled triangle. Of course there probably is some formula to do it at once, but I wanted to show you step by step way to do it. We are gonna use library numbers to use very useful function divisors. Otherwise we would have to check every combination of numbers to find numbers behind area of triangle.

Load libraries and data

library(tidyverse)
library(readxl)
library(numbers)

input = read_excel("Excel/406 Right Angled Triangle Sides.xlsx", range = "A2:B10") %>%
  janitor::clean_names()
test  = read_excel("Excel/406 Ri

Transformation

process_triangle = function(area, hypotenuse) {
  ab = 2 * area
  ab_divisors = divisors(ab)
  grid = expand_grid(a = ab_divisors, b = ab_divisors) %>%
    mutate(r = a * b, 
           hyp = hypotenuse,
           hyp_sq = hyp**2,
           sides_sq = a**2+b**2,
           check = hyp_sq == sides_sq,
           base_shorter = a < b) %>%
    filter(check, base_shorter) %>%
    select(base = a, perpendicular = b)
  return(grid)
}

result = input %>%
  mutate(res = map2(area, hypotenuse, process_triangle)) %>%
  unnest(res) %>%
  select(3:4)

Validation

identical(result, test)
# [1] TRUE

Puzzle #407

I like cyphering puzzles and I am really happy that we have one again. Today we merge 2 types of cyphers: Ceasar and Mirror, so we have reverse and shift coded text to succeed. Let’s check how it went.

Load libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/407 Mirror Cipher.xlsx", range = "A1:B10") %>%
  janitor::clean_names()
test  = read_excel("Excel/407 Mirror Cipher.xlsx", range = "C1:C10") %>%
  janitor::clean_names()

Transformation

code = function(text, shift) {
  if (shift == 0) {
    keycode = letters
  }
  else {
    keycode = c(letters[(26-shift+1):26],letters[1:(26-shift)])
  }
  keytable = tibble(letters = letters, code = keycode)
  
  chars = str_split(text, "")[[1]] %>% 
    rev()
  tab = tibble(text = chars) %>%
    left_join(keytable, by = c("text" = "code")) %>%
    mutate(letters = if_else(is.na(letters), " ", letters)) %>%
    select(letters) %>%
    pull() %>%
    str_c(collapse = "")
  return(tab)
}

result = input %>%
  mutate(answer_expected = map2_chr(plain_text, shift, code))

Validation

identical(result$answer_expected, test$answer_expected)
# [1] TRUE

Puzzle #408

Time: physics, math, eternity… but does time have any geometry? Stephen Hawking probably would say something about it, but we have much easier issue. We only need to check geometry of clock face. There are two or three hands on it. As long as we present time as cycles, we use circle presenting this cycle and positions of hands on the face of round, circular face of clock are enabling us to read time measurements. So lets check what angle hands presents at specific times of a day.

Load libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/408 Angle Between Hour and Minute Hands.xlsx", range = "A1:A10") 
test  = read_excel("Excel/408 Angle Between Hour and Minute Hands.xlsx", range = "B1:B10")

Transformation

angle_per_min_hh = 360/(60*12)
angle_per_min_mh = 360/60

result = input %>%
  mutate(time = as.character(Time),
         Time = str_extract(time, "\\s\\d{2}:\\d{2}")) %>%
  separate(Time, into = c("hour","mins"), sep = ":") %>%
  mutate(hour = as.numeric(hour),
         mins = as.numeric(mins), 
         hour12 = hour %% 12, 
         period_hh = hour12*60 + mins,
         period_mh = mins,
         angle_hh = period_hh * angle_per_min_hh,
         angle_mh = period_mh * angle_per_min_mh,
         angle_hh_to_mh = if_else(angle_hh > angle_mh, 
                                  360 - (angle_hh - angle_mh), 
                                  angle_mh - angle_hh)) %>%
  select(answer_expected = angle_hh_to_mh)

# there is probably single formula for this, 
# but I wanted to show you this step by step.

Validation

identical(result$answer_expected, test$`Answer Expected`)
# [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.

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.