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.

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

That's the straightforward way.

Can you think of any others?

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?

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.

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.

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?

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".