in a cell what is formula to make the numbers 1,2,3,4 = 8

B

Bernard Liengme

=1+2+3+4
but the answer is 10 not 8
Maybe we need the question reworded
Pease use the white space to ask it.
best wishes
 
R

Rick Rothstein \(MVP - VB\)

Odd question...
=SUM(-1+2+3+4) equals 8
Click to expand...

Why choose that instead of this?

=SUM(1+2*3+4^0)

<g>

I think the OP needs to re-ask his question and using more words and all
that white space that comes free with his messages.

By the way, I would note that the SUM function call is not necessary for
either of our formulas.

Rick
 
T

Toey

Sorry for confusion. I want to be able to put the number 1 or 2 or 3 or 4 in
a cell and when i add the colum up each of those numbers woul = 8
ie. 1
2
_
16
 
F

Fred Smith

I would create a helper column, using the formula

=if(a1<=4,8,a1)

Adjust a1 to suit your range. Then add up the numbers in the helper column.

Regards,
Fred.
 
G

Gord Dibben

Maybe this?

With 1234 in A1 enter formula in B1

=SUMPRODUCT(--MID($A$1,ROW(INDIRECT("1:" & LEN($A$1))),1))

Returns 10..........if you want 8 use this

=MIN(8,(SUMPRODUCT(--MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1))))


Gord Dibben MS Excel MVP

On Thu, 17 Jan 2008 10:40:02 -0800, Toey <[email protected]> wrote:
 
P

Pete_UK

Is this one of those logic puzzles, like use four 4s and any
arithmetic operation to create the numbers 1 to 10, eg:

44/44 = 1,
4/4 + 4/4 = 2,
(4+4+4)/4 = 3

and so on?

If so, then 4 + 3 + 2 - 1 = 8

Is this what you want?

Pete
 
D

David Biddulph

.... or =1-2+3^sqrt(4)
--
David Biddulph

Is this one of those logic puzzles, like use four 4s and any
arithmetic operation to create the numbers 1 to 10, eg:

44/44 = 1,
4/4 + 4/4 = 2,
(4+4+4)/4 = 3

and so on?

If so, then 4 + 3 + 2 - 1 = 8

Is this what you want?

Pete
 
G

Gord Dibben

Do you think it is possible we may never know OP's logic or real purpose?

That will drive me nuttier<g>


Gord
 
H

hjc

Toey,

If every cell in the column will contain a 1 or 2 or 3 or 4, then Bernard's
answer (in his second post) will give you what you want. If some cells might
contain other values that you do not wish to count, you can get the right
result as follows.

Assuming (as Bernard did) that the data you want to sum are in the range
A1:A10, enter the following formula in the cell where you want the sum to be:

= 8 * ( countif( a1:a10, "=1" ) + countif( a1:a10, "=2" ) + countif(
a1:a10, "=3" ) + countif( a1:a10, "=4" ) )

If all the cells will contain integers, you can simplify the formula to:

= 8 * ( countif( a1:a10, "<=4" ) - countif( a1:a10, "<1" ) )

I hope this helps,
Hugh John
 
Top