PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Discussion > Compact a Formula

Reply
Page 1 of 2 1 2
Thread Tools Rate Thread

Compact a Formula

 
 
Silvio Dante
Guest
Posts: n/a
 
      19th Feb 2007
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
 
Reply With Quote
 
 
 
 
=?Utf-8?B?Um9uIENvZGVycmU=?=
Guest
Posts: n/a
 
      19th Feb 2007
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


"Silvio Dante" wrote:

> 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
>
>
>
 
Reply With Quote
 
Bob Phillips
Guest
Posts: n/a
 
      19th Feb 2007
=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)



"Silvio Dante" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> 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
>
>
 
Reply With Quote
 
Robert Morley
Guest
Posts: n/a
 
      19th Feb 2007
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

"Silvio Dante" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> 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
>
>
 
Reply With Quote
 
Roger Govier
Guest
Posts: n/a
 
      19th Feb 2007
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))))

--
Regards

Roger Govier


"Silvio Dante" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> 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
>
>
 
Reply With Quote
 
=?Utf-8?B?Um9uIENvZGVycmU=?=
Guest
Posts: n/a
 
      19th Feb 2007
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


"Ron Coderre" wrote:

> 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
>
>
> "Silvio Dante" wrote:
>
> > 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
> >
> >
> >
 
Reply With Quote
 
Dana DeLouis
Guest
Posts: n/a
 
      19th Feb 2007
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)

--
HTH :>)
Dana DeLouis
Windows XP & Office 2003


"Roger Govier" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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))))
>
> --
> Regards
>
> Roger Govier
>
>
> "Silvio Dante" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
>> 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
>>
>>
>
>
 
Reply With Quote
 
Dana DeLouis
Guest
Posts: n/a
 
      20th Feb 2007
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)

--
Dana DeLouis
Windows XP & Office 2003


"Dana DeLouis" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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)
>
> --
> HTH :>)
> Dana DeLouis
> Windows XP & Office 2003
>
>
> "Roger Govier" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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))))
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Silvio Dante" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
>>> 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
>>>
>>>
>>
>>
>
>
 
Reply With Quote
 
Roger Govier
Guest
Posts: n/a
 
      20th Feb 2007
Very nice solution, Dana!

--
Regards

Roger Govier


"Dana DeLouis" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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)
>
> --
> Dana DeLouis
> Windows XP & Office 2003
>
>
> "Dana DeLouis" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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)
>>
>> --
>> HTH :>)
>> Dana DeLouis
>> Windows XP & Office 2003
>>
>>
>> "Roger Govier" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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))))
>>>
>>> --
>>> Regards
>>>
>>> Roger Govier
>>>
>>>
>>> "Silvio Dante" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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
>>>>
>>>>
>>>
>>>
>>
>>
>
>
 
Reply With Quote
 
Harlan Grove
Guest
Posts: n/a
 
      20th Feb 2007
"Dana DeLouis" <ddelo...@bellsouth.net> wrote...
>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)
 
Reply With Quote
 
 
 
Reply
Page 1 of 2 1 2

« Excel 2003 Format Painter - Keyboard Shortcut doesn't work! | How do I make this load and save faster? »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Compact Plugs & Compact Injection Mariano Vicario Microsoft Dot NET Compact Framework 1 16th Mar 2010 09:48 PM
Re: odd jetcomp compact error on odd compact attempts Jeff Boyce Microsoft Access Database Table Design 0 20th Jan 2007 12:56 AM
Compact an Access Applicaton by checking Compact on Close tomlebold@msn.com Microsoft Access 2 15th Jan 2007 07:00 PM
Stopping Compact and Repair (and Compact on Close) - Records Disappear gee664@gmail.com Microsoft Access 7 25th Apr 2006 05:17 PM
This formula needs to be more compact. britgirl Microsoft Excel Worksheet Functions 2 30th Dec 2005 01:58 AM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 10:05 AM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top