 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / February 2007Compact a Formula
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 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 > Hi all, > [quoted text clipped - 6 lines] > keep in mind the power of Antani > http://ilovemiliofede.altervista.org 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 > Try something like this: > [quoted text clipped - 17 lines] > > keep in mind the power of Antani > > http://ilovemiliofede.altervista.org =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)  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi all, > [quoted text clipped - 6 lines] > keep in mind the power of Antani > http://ilovemiliofede.altervista.org 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 > Hi all, > [quoted text clipped - 6 lines] > keep in mind the power of Antani > http://ilovemiliofede.altervista.org 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)))) SignatureRegards Roger Govier > Hi all, > [quoted text clipped - 6 lines] > keep in mind the power of Antani > http://ilovemiliofede.altervista.org 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) SignatureHTH :>) Dana DeLouis Windows XP & Office 2003 > Hi Silvio > [quoted text clipped - 12 lines] >> keep in mind the power of Antani >> http://ilovemiliofede.altervista.org 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) SignatureDana DeLouis Windows XP & Office 2003 > Another variation might be to factor out the common Sum formulas. > One "Mod" to eliminate the Ceiling function and narrow the values, and a [quoted text clipped - 18 lines] >>> keep in mind the power of Antani >>> http://ilovemiliofede.altervista.org Very nice solution, Dana! SignatureRegards Roger Govier > Maybe with just 1 Sum function... > [quoted text clipped - 22 lines] >>>> keep in mind the power of Antani >>>> http://ilovemiliofede.altervista.org "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) Ahhh... How'd you do that? I see a few techniques here that I really like. Very nice. Thanks!! SignatureDana DeLouis Windows XP & Office 2003 > "Dana DeLouis" <ddelo...@bellsouth.net> wrote... >>Maybe with just 1 Sum function... [quoted text clipped - 6 lines] > > =-MOD(SUMPRODUCT({3,1}*MID(A1&"0",{1;3;5;7;9;11}+{0,1},1)),-10) 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? SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk "Sandy Mann" <sandyma...@mailinator.com> wrote... ... > . . . 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. > text, so I padded A1 as text. As it stands, *10 might be better. Serendipity <g> SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk 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 > "Dana DeLouis" <ddelo...@bellsouth.net> wrote... > >Maybe with just 1 Sum function... [quoted text clipped - 6 lines] > > =-MOD(SUMPRODUCT({3,1}*MID(A1&"0",{1;3;5;7;9;11}+{0,1},1)),-10) 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. SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk > You're right..... > Working with your approach, how about this? [quoted text clipped - 17 lines] >> >> =-MOD(SUMPRODUCT({3,1}*MID(A1&"0",{1;3;5;7;9;11}+{0,1},1)),-10) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.