# Fun and interesting challenge

#### daniels012

##### Well-known Member

Try to use as many formulas as you can to get the simple result of:

A1 =4
A2 =5
A3 =2
A4 =5
A5 =1
A6 =0
A7 =0
A8 =6
A9 =2
A10 =1
A11 = "=SUM(A1:A10)" 'which the result is 26

Let's have some fun with it!!

An example to get the idea started would be:
=ROUND(SUM(A1:A10),0)
this gives the exact same result using 2 formulas...

Use as many formulas as you can without changing the value of the result 26.

Hve Fun!

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### ExcelChampion

##### Well-known Member
=PRODUCT(QUOTIENT(SUMPRODUCT(--(ISNUMBER(A1:A10)),A1:A10),COLUMN(M1)),ROW(13:13))

Michael

#### Andrew Fergus

##### MrExcel MVP
I think I have to stop and do some work now because this is getting silly......
My contribution:
Code:
``=ROUND(SUM(MOD(COUNT(A1:A10), MAX(A1:A10)+A1),PRODUCT(COLUMN(A:A),SUMPRODUCT(--ISNUMBER(A1:A10),A1:A10,--ISERROR(((CODE(TEXT(A1:A10,"0")))/MIN(A1:A10)))),LOG(LARGE(A1:A10,A1),A1)-INT(AVEDEV(A1:A10))),FACT(A1),SQRT(A1)),0)``

19 unique functions! I might be able to slip the quotient and/or row functions in there too.....

If you evaluate this formula you will see it has been *highly* modified for the numbers given.

Andrew

{edited formula so it is no longer an array formula}

#### Lewiy

##### Well-known Member
Code:
``=AVERAGE(ROUND(SUM(MOD(CHOOSE(1,(COUNT(A1:A10))),MAX(A1:A10)+A1),PRODUCT((COLUMN(A:A)),SUMPRODUCT(--ISNUMBER(A1:A10),A1:A10,--ISERROR(((CODE(TEXT(A1:A10,"0")))/FLOOR(MIN(A1:A10),1)))),LOG(LARGE(A1:A10,A1),A1)-INT(AVEDEV(A1:A10))),FACT(A1),CEILING(SMALL(ABS(SQRT(A1)),1),1)),0))+(INT(RAND()*POWER(ODD(PI()*A1),0))+(INDIRECT("A"&ROW(A1))-A1))``

I think that's 31 different functions

#### daniels012

##### Well-known Member
Lewiy,
That looks awesome , now I need to go test it

I new this would be fun :wink:

Michael

{ edited: that indeed did the trick Lewiy }

1,191,684
Messages
5,987,993
Members
440,124
Latest member
dippy_egg

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back