The Magic Café
Username:
Password:
[ Lost Password ]
  [ Forgot Username ]
The Magic Cafe Forum Index » » Puzzle me this... » » Find the Excel Formula (0 Likes) Printer Friendly Version

Craig Matsuoka
View Profile
Loyal user
Kailua, Hawaii
271 Posts

Profile of Craig Matsuoka
Here's a puzzle for those of you who use Microsoft Excel.

Scenario:

Cell A1 contains any number from 1 to 9.
Cell A2 contains any number from 1 to 9.
Cell A3 contains any number from 1 to 9.
Cell A4 contains any number from 1 to 9.

The challenge:

What formula would you put in cell A5 to concatenate the values in range A1:A4?

For example,
If:
Cell A1 = "1"
Cell A2 = "2"
Cell A3 = "3"
Cell A4 = "4"
Then cell A5 will display "1234".

Wait! Not so fast, Excel gurus! There's a catch.

YOU ARE NOT ALLOWED TO USE ANY CONCATENATION FUNCTIONS OR THE AMPERSAND OPERATOR (&).

Hint: This problem is solvable using only the following operators:

+ - * ( )

I'll post the answer next week.
bvbernard
View Profile
New user
North Vancouver, BC, Canada
93 Posts

Profile of bvbernard
Am I missing something or does this go back to when I was learning numbers at age 6?

A5=1000*A1+100*A2+10*A3+A4

Seems very straightforward unless I am missing something.

Bruce
Craig Matsuoka
View Profile
Loyal user
Kailua, Hawaii
271 Posts

Profile of Craig Matsuoka
That's the straightforward way.

Can you think of any others?
Jonathan Townsend
View Profile
Eternal Order
Ossining, NY
26973 Posts

Profile of Jonathan Townsend
Any restrictions on letters used, or number of additions?

more to the point, given our base 10 arithmetic, what specific aesthetic would you like given that the 'straight forward' approach was not desirable?

Could save the file to text, then pipe the text file through an ASCII filter and put the result into the cell. that convoluted enough?
...to all the coins I've dropped here
Craig Matsuoka
View Profile
Loyal user
Kailua, Hawaii
271 Posts

Profile of Craig Matsuoka
Jonathan,

You're headed in the right direction.

It's a different "aesthetic" meant to help obfuscate the "straight forward". Specifically, I'm looking for an Excel implementation of an old mathematically based trick.

A pretty useless implementation, admittedly. But so was the original trick.
Craig Matsuoka
View Profile
Loyal user
Kailua, Hawaii
271 Posts

Profile of Craig Matsuoka
There was an old calculator trick where you locate an imaginary object hidden in an imaginary house. You reveal the house number(A1), floor number(A2), room number(A3), drawer number(A4).

A5=(((10*A1)+A2)*10+A3)*10+A4

Or the Goldbergian formula as it occurs in the trick:

A5=(((A1*2+5)*5+10+A2)*10+A3)*10+35+A4-1111-2424

The constants can vary, of course.
H_Ho
View Profile
New user
76 Posts

Profile of H_Ho
A5=(((10*A1)+A2)*10+A3)*10+A4 is essentially what was posted in the second reply.

out of curiosity, what was this calculator trick?
Craig Matsuoka
View Profile
Loyal user
Kailua, Hawaii
271 Posts

Profile of Craig Matsuoka
You ask the audience to picture an imaginary path to an imaginary hiding place anywhere in the city.

To randomize the process, have spectator "A" think of a house number (1 thru 9). You hand him a calculator and instruct him to input the house number, multiply by 2, then add 5. He then multiplies the result by 5 and adds 10.

The calculator is then passed to spectator "B" who adds the floor number (1 thru 9) then multiplies the result by 10.

Calculator goes to spectator "C". He adds the room number (1 thru 9), and multiplies the result by 10. He next adds 35 and then passes the calculator to spectator "D".

Spectator "D" adds the drawer number (1 thru 9), and then subtracts 1111. He hands the calculator back to you.

You subtract 2424. The number displayed on the screen shows (from left to right), the house, floor, room, and drawer numbers.

This is possibly one of the dullest, most transparent tricks ever devised for a calculator.

Anyone know any good mathematical tricks using a spreadsheet? Key word here is "good".
The Magic Cafe Forum Index » » Puzzle me this... » » Find the Excel Formula (0 Likes)
[ Top of Page ]
All content & postings Copyright © 2001-2019 Steve Brooks. All Rights Reserved.
This page was created in 0.14 seconds requiring 5 database queries.
The views and comments expressed on The Magic Café
are not necessarily those of The Magic Café, Steve Brooks, or Steve Brooks Magic.
> Privacy Statement <

ROTFL Billions and billions served! ROTFL