Compact a Formula

[Silvio Dante]

Hi all,

is there a way to express this formula below in a more compact way?

=CEILING(3*(SUM(MID(A1,1,1),MID(A1,3,1),MID(A1,5,1),MID(A1,7,1),MID(A1,9,1),MID(A1,11,1)))+SUM(MID(A1,2,1),MID(A1,4,1),MID(A1,6,1),MID(A1,8,1),MID(A1,10,1)),10)-(3*(SUM(MID(A1,1,1),MID(A1,3,1),MID(A1,5,1),MID(A1,7,1),MID(A1,9,1),MID(A1,11,1)))+SUM(MID(A1,2,1),MID(A1,4,1),MID(A1,6,1),MID(A1,8,1),MID(A1,10,1)))



Thanks

keep in mind the power of Antani
http://ilovemiliofede.altervista.org

 

[Guest]

Try something like this:

=CEILING(3*SUM(--MID(A1,{1,3,5,7,9,11},1))+SUM(--MID(A1,{2,4,6,8,10},1)),10)-(3*SUM(--MID(A1,{1,3,5,7,9,11},1))+SUM(--MID(A1,{2,4,6,8,10},1)))

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Bob Phillips]

=CEILING(3*SUMPRODUCT(--(MID(A1,{1,3,5,7,9,11},1)))+SUMPRODUCT(--(MID(A1,{2,4,6,8,10},1))),10)
-(3*SUMPRODUCT(--(MID(A1,{1,3,5,7,9,11},1)))+SUMPRODUCT(--(MID(A1,{2,4,6,8,10},1))))

Better still, put

SUMPRODUCT(--(MID(A1,{1,3,5,7,9,11},1)))

in B1, and

SUMPRODUCT(--(MID(A1,{2,4,6,8,10},1)))

in C1 and use

=CEILING(3*B1+C1,10)-(3*B1+C1)


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Robert Morley]

Well, you can use a helper cell to do all the MID calculations and then
shorten the formula in the final cell. Let's say you're using A2 as the
helper cell and A3 as the final cell, you'd get:

A2 =
3*(SUM(MID(A1,1,1),MID(A1,3,1),MID(A1,5,1),MID(A1,7,1),MID(A1,9,1),MID(A1,11,1)))+SUM(MID(A1,2,1),MID(A1,4,1),MID(A1,6,1),MID(A1,8,1),MID(A1,10,1))

A3 = CEILING(A2,10)-A2

Unless there's some really fancy trick for adding all the MID()'s, though,
that's about the only thing I can see.



Rob

 

[Roger Govier]

Hi Silvio

Try
=CEILING(3*(SUM(--(MID(A1,{1,3,5,7,9,11},1))))+SUM(--(MID(A1,{2,4,6,8,10},1))),10)
-(3*(SUM(--(MID(A1,{1,3,5,7,9,11},1))))+SUM(--(MID(A1,{2,4,6,8,10},1))))

 

[Guest]

These are a bit shorter......

Regular formula:
=CEILING(SUM(INDEX(MID(A1,ROW($1:$11),1)*{3;1;3;1;3;1;3;1;3;1;3},0)),10)-SUM(INDEX(MID(A1,ROW($1:$11),1)*{3;1;3;1;3;1;3;1;3;1;3},0))

Array Formula (committed with Ctrl+Shift+Enter)
=CEILING(SUM(MID(A1,ROW($1:$11),1)*{3;1;3;1;3;1;3;1;3;1;3}),10)-SUM(MID(A1,ROW($1:$11),1)*{3;1;3;1;3;1;3;1;3;1;3})

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Dana DeLouis]

Another variation might be to factor out the common Sum formulas.
One "Mod" to eliminate the Ceiling function and narrow the values, and a
Second "Mod" to fine tune the conversion.

=MOD(20,MOD(3*SUM(--MID(A1,{1,3,5,7,9,11},1))+SUM(--MID(A1,{2,4,6,8,10},1)),10)+10)

 

[Dana DeLouis]

Maybe with just 1 Sum function...

=MOD(20,MOD(SUM(3*(MID(A1,{1,3,5,7,9,11},1)),(--MID(A1,{2,4,6,8,10},1))),10)+10)

 

[Roger Govier]

Very nice solution, Dana!

 

[Harlan Grove]

Maybe with just 1 Sum function...

=MOD(20,MOD(SUM(3*(MID(A1,{1,3,5,7,9,11},1)),
(--MID(A1,{2,4,6,8,10},1))),10)+10)

....

Too verbose. If you're trying for terse, make it TERSE.

=-MOD(SUMPRODUCT({3,1}*MID(A1&"0",{1;3;5;7;9;11}+{0,1},1)),-10)

 

[Dana DeLouis]

Ahhh... How'd you do that?
I see a few techniques here that I really like.
Very nice. Thanks!!

 

[Sandy Mann]

Harlan,

=-MOD(SUMPRODUCT({3,1}*MID(A1&"0",{1;3;5;7;9;11}+{0,1},1)),-10)

If I may be so bold as to ask, I assume - and I'm not trying to be a smart
alec here - that the A1&"0" is to make the number a 12 character text
number. I know that you then use a text function to slice it up but is
there any disavantage to using A1*10 and still using the text function? It
seems to work when I try it.

In other words, unlike older spreadsheets, text functions work on *real*
numbers in Excel but is it considered *bad* programming to use it thus?


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Harlan Grove]

. . . that the A1&"0" is to make the number a 12 character text
number. I know that you then use a text function to slice it up
but is there any disavantage to using A1*10 and still using the
text function? It seems to work when I try it.

....

You're right that the &"0" adds a 12th character that adds nothing to
the final sum. As for *10 instead, it'd shorten the formula by one
character. I used &"0" because the result would be manipulated as
text, so I padded A1 as text. As it stands, *10 might be better.

 

[Sandy Mann]

text, so I padded A1 as text. As it stands, *10 might be better.

Serendipity <g>

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

You're right.....
Working with your approach, how about this?

=10-MOD(SUM({3;1}*MID(A1&"0",{1,3,5,7,9,11}+{0;1},1)),10)


***********
Regards,
Ron

XL2002, WinXP

 

[Sandy Mann]

Well done! So if we combine the formula shortening options we get:

=-MOD(SUM({3;1}*MID(A1*10,{1,3,5,7,9,11}+{0;1},1)),-10)

55 key presses in place of the original 310. That is what I like about
these NG's.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk