Ugly Formulas
A recent discussion prompted me to search for a particularly long formula that I had written. I stumbled on the following formula which computes the amount of a grant for a specific period. There are very specific rules to figure this out that I won’t go into here. Here’s the monstrosity.
=(mround((IF(OR(DAY(FED)=1,DAY(FED)=16),FED,IF(DAY(FED)>15,DATE(YEAR(FED),MONTH(FED),16), DATE(YEAR(FED),MONTH(FED),1)))-IF(OR(DAY(FBD)=1,DAY(FBD)=16),FBD,IF(DAY(FBD)>15,DATE( YEAR(FBD),MONTH(FBD)+1,1),DATE(YEAR(FBD),MONTH(FBD),16)))-1)/15,0.5)+(networkdays(IF(OR( DAY(FED)=1,DAY(FED)=16),FED,IF(DAY(FED)>15,DATE(YEAR(FED),MONTH(FED),16),DATE(YEAR(FED), MONTH(FED),1))),FED)/networkdays(IF(OR(DAY(FED)=1,DAY(FED)=16),FED,IF(DAY(FED)>15,DATE( YEAR(FED),MONTH(FED),16),DATE(YEAR(FED),MONTH(FED),1))),DATE(YEAR(FED),MONTH(FED)+IF( DAY(FED)>15,1,0),IF(DAY(FED)>15,0,15))))+(networkdays(FBD,IF(OR(DAY(FBD)=1,DAY(FBD)=16), FBD,IF(DAY(FBD)>15,DATE(YEAR(FBD),MONTH(FBD)+1,1),DATE(YEAR(FBD),MONTH(FBD),16)))-1)/ networkdays(DATE(YEAR(FBD),MONTH(FBD),IF(DAY(FBD)>15,15,1)),IF(OR(DAY(FBD)=1,DAY(FBD)=16), FBD,IF(DAY(FBD)>15,DATE(YEAR(FBD),MONTH(FBD)+1,1),DATE(YEAR(FBD),MONTH(FBD),16)))-1)))*(AR/24)
What I want now is the all-time worst formula. It has to be long, virtually uneditable, and used in real life. By the way, the above formula is 879 characters.
VKD:
I don’t have an uglier function, but I have a related question. Is there a way to keep the formula bar from expanding down to reveal the entire formula when a cell is selected? If I have to show a spread sheet that contains monster formulas like yours and I want to select a cell to talk about it, and don’t want the monster formula to obscure the top lines of the spread sheet, is there a way to do that without hiding the formula bar?
-Vincent
23 June 2004, 10:30 amJ-Walk:
Vincent, there are at least two options: (1) Turn off the formula bar (use View - Formula Bar), or (2) Unmaximize the workbook window. This lets you drag it around so it’s not obscured by the formula bar display.
23 June 2004, 10:40 amMichael:
I don’t have a single formula that long or horrible, but I do have spreadsheets. With interlocked equations all over the place…the worst is probably an actuarial model for calculating a cost index for health insurance plans, based on premium, cost-sharing, and coverage.
Was developed in iterations by two statisticians, two researcher scientists, a computer person and an assistant: it’s horribly inefficient, but it gets the job done.
23 June 2004, 10:51 amJonathan Rynd:
There are a few things you could do to prettify that formula. There is an addin available that lets you save parts of the calculation into temporary variables and use them later. In particular I note that the IF(OR( DAY(FED)=1,DAY(FED)=16),FED,IF(DAY(FED)>15,DATE(YEAR(FED),MONTH(FED),16),DATE(YEAR(FED), MONTH(FED),1))) is repeated.
Let me see if I can find it…
23 June 2004, 12:01 pmrzf:
This is long and ugly (979 characters) and used in real life. However, I think it’s reasonably editable and understandable. Do I get extra credit for leaving that job and donating this file to someone else (not to mention the rest of the 5MB workbook it comes with)? That should reduce editability quite a bit.
=IF(ISERROR(VLOOKUP(”S31 001.00006.00″,Sub,2,0)),0,VLOOKUP(”S31 001.00006.00″,Sub,2,0))+IF(ISERROR(VLOOKUP(”S31 006.00006.00″,Sub,2,0)),0,VLOOKUP(”S31 006.00006.00″,Sub,2,0))+IF(ISERROR(VLOOKUP(”S31 007.00006.00″,Sub,2,0)),0,VLOOKUP(”S31 007.00006.00″,Sub,2,0))+IF(ISERROR(VLOOKUP(”S31 008.00006.00″,Sub,2,0)),0,VLOOKUP(”S31 008.00006.00″,Sub,2,0))+IF(ISERROR(VLOOKUP(”S31 009.00006.00″,Sub,2,0)),0,VLOOKUP(”S31 009.00006.00″,Sub,2,0))+IF(ISERROR(VLOOKUP(”S31 010.00006.00″,Sub,2,0)),0,VLOOKUP(”S31 010.00006.00″,Sub,2,0))+IF(ISERROR(VLOOKUP(”S31 011.00006.00″,Sub,2,0)),0,VLOOKUP(”S31 011.00006.00″,Sub,2,0))+IF(ISERROR(VLOOKUP(”S31 006.01006.00″,Sub,2,0)),0,VLOOKUP(”S31 006.01006.00″,Sub,2,0))+IF(ISERROR(VLOOKUP(”S31 006.02006.00″,Sub,2,0)),0,VLOOKUP(”S31 006.02006.00″,Sub,2,0))+IF(ISERROR(VLOOKUP(”S31 007.01006.00″,Sub,2,0)),0,VLOOKUP(”S31 007.01006.00″,Sub,2,0))+IF(ISERROR(VLOOKUP(”S31 008.01006.00″,Sub,2,0)),0,VLOOKUP(”S31 008.01006.00″,Sub,2,0))
23 June 2004, 12:16 pmJ-Walk:
This formula isn’t very long, and it’s really not all that ugly. But it’s one of my favorite formulas:
=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))
<>MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)-
(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+
{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),”",DATE(YEAR(NOW()),
MONTH(NOW()),1)-(WEEKDAY(DATE(YEAR(NOW()),
MONTH(NOW()),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)
To use it:
1. Copy the formula text to the clipboard
2. Activate a sheet and select a 7-col by 6-row range
3. Press F2
4. Press Ctrl+V to paste the formula into the active cell
5. Press Ctrl+Shift+Enter (to make it a multicell array formula)
6. Format the cells using the “d” number format.
Voila! You have a calendar for the current month.
23 June 2004, 12:45 pmwaterrat:
Here’s a formula we use in a spreadsheet for quoting costs to our customers, which has to take into account certain hidden variables. I don’t think it’s as long as some here, but it’s ugly anyway.
=IF($F$10=”Y”,(IF($C$9=0,”$0.00″,IF($C$9<113,'Revenues & Costs'!$B$3,IF($C$9<281,('Revenues & Costs'!$C$3),IF($C$9<451,($C$9*'Revenues & Costs'!$D$3),IF($C$9<851,($C$9*'Revenues & Costs'!$E$3),IF($C$9<1201,($C$9*'Revenues & Costs'!$F$3),IF($C$9>1201,($C$9*’Revenues & Costs’!$G$3)))))))))*2,IF($C$9=0,”$0.00″,IF($C$9<113,'Revenues & Costs'!$B$3,IF($C$9<281,('Revenues & Costs'!$C$3),IF($C$9<451,($C$9*'Revenues & Costs'!$D$3),IF($C$9<851,($C$9*'Revenues & Costs'!$E$3),IF($C$9<1201,($C$9*'Revenues & Costs'!$F$3),IF($C$9>1201,($C$9*’Revenues & Costs’!$G$3)))))))))+IF($C$11=1,0,($C$11*10)*$C$12*’Revenues & Costs’!$B$5)+($F$7*’Revenues & Costs’!$B$6)
23 June 2004, 1:39 pmSteve:
All of my super-long formulas are single-cell array formulas, usually used to do weighted averages on multiple criteria. In fact, I try to make all of my Excel models use long and uninteligible formulas, because I’m one of the few around here that really understands Excel, and making everyone’s models run with my formulas is just my way of ensuring long-term job security.
23 June 2004, 2:20 pmSteve:
All of my super-long formulas are single-cell array formulas, usually used to do weighted averages on multiple criteria. In fact, I try to make all of my Excel models use long and uninteligible formulas, because I’m one of the few around here that really understands Excel, and making everyone’s models run with my formulas is just my way of ensuring long-term job security.
23 June 2004, 2:20 pmbiff:
Here are a couple I came up with. While not very long, they certainly qualify as ugly.
This one is used to calculate a football team’s winning percentage based on their won-loss record that’s saved in this format: W-L
=IF(ISERROR(–LEFT(B19,FIND(”-”,B19)-2)/–SUM(LEFT(B19,FIND(”-”,B19)-2),MID(B19,FIND(”-”,B19)+2,255))),”",–LEFT(B19,FIND(”-”,B19)-2)/–SUM(LEFT(B19,FIND(”-”,B19)-2),MID(B19,FIND(”-”,B19)+2,255))
And this one is used in a lottery program. I use the output of this formula in a list box. It counts the number of times a particular number combination was drawn.
=R1&S1&T1&” “&SUMPRODUCT((C$5:INDIRECT(”C”&”$”&COUNT(C$5:C$369)+4)=R1)*(D$5:INDIRECT(”D”&”$”&COUNT(D$5:D$369)+4)=S1)*(E$5:INDIRECT(”E”&”$”&COUNT(E$5:E$369)+4)=T1))
23 June 2004, 9:02 pmAndrew:
I don’t have any really ugly formulas unless you count this one. (Not so complex but does strain the eyeballs a bit)
=IF(F6=1,IF(VLOOKUP($D$6,$B$9:$E$51,4)=”All”,SUMIF(Payments!$N$6:Payments!N279,$I$9,Payments!$Q$6:Payments!Q279),SUMIF(Payments!$K$6:Payments!K279,$I$9,Payments!$Q$6:Payments!Q279)),IF(VLOOKUP(Data!$D$6,Data!$B$9:$E$51,4)=”All”,SUMIF(Payments!$O$6:Payments!Q279,Data!$I$9,Payments!$Q$6:Payments!Q279),SUMIF(Payments!$L$6:Payments!Q279,Data!$I$9,Payments!$Q$6:Payments!Q279)))
(I can’t believe J-Walk’s calendar array formula, it’s amazing!)
23 June 2004, 11:19 pmJamie Collins:
I use this (or a variation on the theme) a lot:
=IF(ROW(A1)=1,IF(ISERR(FIND(” “,$A$1)),$A$1,MID($A$1,1,FIND(” “,$A$1,1)-1)),IF(ROW(A1)
24 June 2004, 6:42 amTim:
Jwalks formula is excellent, thought I would record a macro while I did it to use later but when I ran it I got a run time error 1004 unable to get the formula array property of the range class and the formula highlighted. Could this be done with a macro? Thanks
24 June 2004, 8:37 amSelection.FormulaArray = _
“=MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))” & Chr(10) & “<>MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)-” & Chr(10) & “(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+” & Chr(10) & “{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1) “”"” DATE(YEAR(NOW()),” & Chr(10) & “MONTH(NOW()),1)-(WEEKDAY(DATE(YEAR(NOW()),” & Chr(10) & “MONTH(NOW()),1))-1) {”
Juan Pablo:
Tim,
First, you don’t need the Chr$(10) to enter the formula, and unfortunately, even if you remove it, you won’t be able to use this in a macro, because the length of the formula is greater than 255 characters. For some strange reason, MS has forgotten to increase this limit in VBA.
24 June 2004, 9:41 amTim:
Juan, thanks for the infomation
24 June 2004, 10:01 amrps:
Although not a cure for ugliness, here’s a way to make it bearable at times.
Use Alt+Enter (line break) & spaces to break formulas into a more legible format (this works for me in XL2000)
For example:
=IF(A1=1,”a”,IF(A1=2,”b”,IF(A1=3,”c”,”d”)))
becomes:
=IF(A1=1,”a”,~
__IF(A1=2,”b”,~
___IF(A1=3,”c”,”d”)))
(Replace ~ with Alt+Enter, and _ with spaces in Excel)
The formula still works, plus you can drag/fill and the formatting still remains. (Beware, if you select the formula in the formula bar and paste it, each line will paste into a new cell.)
rps
25 June 2004, 2:09 pmKen Plowman:
Hi this is a formula I wrote to work out how many trips it would take to service three ports at so many tonnes per trip and with each port having a different storage capacity and annual usage.
Actually worked well on a vessel evaluation project. 843 characters.
‘=IF(C21=0,IF(B21=0,0,B21/IF(B16*IF(B21=0,0,IF(C21=0,B21/B21,B21/SUM(B21:D21)))>B20-(IF(B21=0,0,B20/(B21/B18))-IF(B21=0,0,IF(C21=0,B20/(B21/B18)-B17,IF(B20/(B21/B18)C20-(IF(C21=0,0,C20/(C21/B18))-IF(C21=0,0,IF(B21=0,(C20/(C21/B18)-B17),IF(C20/(C21/B18)
26 June 2004, 3:00 pmNick Burns:
This is for Jamie who asked:
what does this formula do and who wrote it?
=MOD(DAY(B1)+HLOOKUP(MONTH(B1),{1,2,3,4,5,6,7,8,9,10,11,12;6,2,2,5,0,3,5,1,4,6,2,4},2,FALSE)+INT((YEAR(B1)-1900)/12)+MOD((YEAR(B1)-1900),12)+INT(MOD((YEAR(B1)-1900),12)/4)+IF(AND(OR(MONTH(B1)=1,MONTH(B1)=2),MOD(YEAR(B1),4)=0),-1,0),7)+1
I don’t know who wrote it, but it looks like it just shifts the number of the WEEKDAY() function so that the Monday is the 1st weekday and Sunday is the 7th.
23 July 2004, 11:44 amThis could have been easily done with this formula:
=CHOOSE(WEEKDAY(B1),7,1,2,3,4,5,6)
or
even easier:
=WEEKDAY(B1,2)
John Mansfield:
This formula calculates the amount of time dedicated to a certain shift. For example, the range names with a time entry for shift #1 are:
Range name “ST” = Shift Start Time - 6am
Range name “ET” = Shift End Time - 2pm
Range name “Tm1″ = Operation Start Time - 7am
Range name “Tm2″ = Operation End Time - 3pm
The formula should return 7 hours for shift #1 i.e. 7am to 2pm.
The same formula is applied to shifts 2, 3, etc. to get their times.
=((Tm1>Tm2)*(ST< =Tm1)*(ST>=Tm2)*(ET>=Tm1)*(ET-Tm1) + (Tm1>Tm2)*(ST< =Tm1)*(ST>=Tm2)*(ET< =Tm2)*(1-Tm1+ET) + (Tm1>Tm2) * (ST< =Tm1)*(ST>=Tm2)*(ET>Tm2)*(ETTm2)*(ST< =Tm2) * (ET>=Tm2)*(ET< =Tm1)*(Tm2-ST) + (Tm1>Tm2)*(ST< =Tm2) * (ET>ST)*(ET< =Tm2)*(ET-ST) + (Tm1>Tm2)*(ST< =Tm2)*(ETTm2)*(ST<=Tm2)*(ET>=Tm1)*(Tm2-ST+ET-Tm1) + (Tm1>Tm2)*(ST>=Tm1)*(ET>ST)*(ET-ST) + (Tm1>Tm2)*(ST>=Tm1) * (ET< =ST)*(1-ST+ET) + (Tm1>Tm2)*(ST>=Tm1)*(ET>=Tm2)*(ET< =Tm1)*(1-ST+Tm2) + (Tm1>Tm2)*(ST>=Tm1) * (ET>Tm1)*(ET=Tm1)*(ST< =Tm2)*((ET<=Tm1)+(ET>=Tm2))*(Tm2-ST) + (Tm1=Tm1)*(ST< =Tm2)*(ET=Tm1)*(ST<=Tm2)*(ET<=Tm2)*(ET-ST) + (Tm1=Tm2) * (ET>=Tm1)*(ET< =Tm2)*(ET-Tm1) + (Tm1=Tm2)*(ET>=Tm2)*(ET=Tm2))*(Tm2-Tm1) + (Tm1=Tm1)*(ET<=Tm2)*(ET-Tm1)) * 24
This is a “draft” but the plan is to eventually use it to calculate hospital operating room efficiency. If anyone knows of an easier way to do this I would appreciate the input. This is about as ugly as it gets.
1 August 2004, 2:12 amRob van Gelder:
John Mansfield:
I have the formula you need.
It’ll handle shifts over midnight.
It’ll handle shifts which last for many days.
I’ve got it on my website (Hours Affected by Dates) http://www.vangelder.co.nz/excel
A3: 1-Jan-2004 09:15:00
B3: 1-Jan-2004 11:30:00
C3: =TIME(HOUR(A3),MINUTE(A3),SECOND(A3))
D3: =TIME(HOUR(B3),MINUTE(B3),SECOND(B3))
E1: 00:00
E2: =E1+(1/24)
E3:
=IF(OR(AND($C3< =$D3,$D3<=E$1),AND($C3>=E$2,OR($C3< =$D3,$D3<=E$1))),0,IF(AND($D3<=E$2,$D3>=E$1,OR($C3< =E$1,$C3>=E$2)),$D3-E$1,IF(OR(AND($C3< =E$1,$D3>=E$2),AND($C3>=$D3,OR($C3< =E$1,$D3>=E$2))),E$2-E$1,IF(AND($C3< =$D3,$C3>=E$1,$D3< =E$2),$D3-$C3,IF(AND($C3>=E$1,$C3< =E$2,OR($D3<=E$1,$D3>=E$2)),E$2-$C3,($D3-E$1)+(E$2-$C3))))))+INT($B3-$A3)*(E$2-E$1)
(No spaces in the formula)
1 August 2004, 2:38 am1. A3 will be less than or equal to B3
2. E1 will be less than E2
3. If E2 (or F2, G2, etc…) is 00:00 (or greater than midnight) then it should be 00:00 + 1 or else assumption 2 won’t work.
AOL:
Let you know that I’m from Germany.
My ideas must not be the greatest,but
Sometimes ugly formulas look like long rat tails,
the better way to do some jobs is to use VBA, i think.
I want to create a WEB-Site that will give everyone the possibilty to program in VBA.
I know that this is the difficulty way to eliminate thus long formulas, but i aim that this is the best way.
Achim
13 September 2004, 4:27 pmGBradley:
My formula is a lot like rzf’s one above. Fortunately it is only 569 characters. Once it gets to 600 - I’ll probably go nuts.
=SUM(IF(ISERROR(VLOOKUP(1,’2007′!$B$3:$C$14,2,FALSE)=TRUE),0,VLOOKUP(1,’2007′!$B$3:$C$14,2,FALSE)),IF(ISERROR(VLOOKUP(1,’2007′!$D$3:$E$14,2,FALSE)=TRUE),0,VLOOKUP(1,’2007′!$D$3:$E$14,2,FALSE)),IF(ISERROR(VLOOKUP(1,’2007′!$F$3:$G$14,2,FALSE)=TRUE),0,VLOOKUP(1,’2007′!$F$3:$G$14,2,FALSE)),IF(ISERROR(VLOOKUP(1,’2007′!$H$3:$I$14,2,FALSE)=TRUE),0,VLOOKUP(1,’2007′!$H$3:$I$14,2,FALSE)),IF(ISERROR(VLOOKUP(1,’2007′!$J$3:$K$14,2,FALSE)=TRUE),0,VLOOKUP(1,’2007′!$J$3:$K$14,2,FALSE)),IF(ISERROR(VLOOKUP(1,’2007′!$L$3:$M$14,2,FALSE)=TRUE),0,VLOOKUP(1,’2007′!$L$3:$M$14,2,FALSE)) )
17 September 2004, 9:32 amJamie Collins:
“This is for Jamie who asked”
And this is for Nick Burns who replied.
It is based on Lewis Carroll’s algorithm for mentally (i.e. in your head, without using a spreadsheet) calculating the weekday for any given date. It looks complicated written this way but in practice (after a bit of practice) it takes just a second or two.
Jamie.
–
24 November 2004, 7:15 amCraig George:
J. Walk’s calendar formula has a minor typo, a missing “-” at the end of the first line. It took me a whole lunch hour to figure this out. Here’s a correct (I hope) version:
=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))-
MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)-
(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+
{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),”",DATE(YEAR(NOW()),
MONTH(NOW()),1)-(WEEKDAY(DATE(YEAR(NOW()),
MONTH(NOW()),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)
Use it with the “d” Custom Date Format.
2 December 2004, 12:21 pmFrank brutsaert:
Craig, I changed my Windows regional settings (set thousands and decimal separator to , and . in order replace the ; with the , list separator), but Excel says there is an error in the formula.
3 December 2004, 8:30 amjkpieterse:
This is one I used a couple of years ago:
=SUM(IF((TRANSPOSE(BfLengte)*(TRANSPOSE(BfRolcode)=VfRolcode)*(VfRolcode<>VfRolcode_1)-VfUsedTape-VfLengte/VfLambda)< $D$2,0,(TRANSPOSE(BfLengte)*(TRANSPOSE(BfRolcode)=VfRolcode)*(VfRolcode<>VfRolcode_1)-VfUsedTape-VfLengte/VfLambda)* ( TRANSPOSE((BfBreedte-BfRandbr_L-BfRandbr_R)*BfAvgBW)*(TRANSPOSE(BfRolcode)=VfRolcode)*(VfRolcode<>VfRolcode_1)) ))/1000000
It is involved in a yield computation for a film manufacturer.
If I hadn’t used defined names so much in the above, it would easily surpass the largest formula in this thread :-).
3 December 2004, 9:38 amThe Contrarian:
Why do people have this need for complicated formulas? In contrast to some of the stuff posted in the newsgroups, the formulas in this discussion are tame. Some of those newsgroup monstrosities make me shudder. Imagine what they do to those who ask for help! [I remember one where the poster repeated the same sub-formula three or four times. With just one intermediate cell…]
It sure can’t be a lack of real estate. After all, a single worksheet has 65536×256 cells!
Granted there are some formulas that *may* need to be array formulas and they *might* get a tad long. But other than that, why this burning need to stick all of Einstein’s work into a single cell?
Wouldn’t it make a lot more sense to lay out the work in intermediate steps? It makes it that much easier to audit, understand, and maintain one’s work.
And, honestly, if an XL formula must be overly complicated, it might be time to make it an UDF.
As one can imagine after the above, I cannot contribute a godforsakenly complicated formula. I don’t see the need for them. I don’t write them. {grin}
“Simplicity is the ultimate sophistication.”
4 December 2004, 8:33 pm— Leonardo da Vinci
Charles Williams:
I have to agree with Contrarian: although it is fun to practice the primitive form of cryptography that results from writing megaformulae, most of the time its counterproductive: megaformulae are usually incomprehensible, unmaintainable, untestable and error prone.
Sometimes they are slightly faster to execute, sometimes slower.
So I usually try to restrain myself when I feel the urge to write one!
5 December 2004, 2:37 pmMic Coper:
I would like to know if there is a formular for a sum if and the Criteria is a date”…. example… I have a file that I have a bunch of receipts and want to sum total for any particular month?
sumif (A:A,(if A:A=convert date to month = month looking for), B:B)
Is this possible? If so, what’s the proper setup
I have…
=SUMIF(Receipts!A2:A9999,”Montgh(A:A)”,Receipts!B2:B9999)
A B
3/15/05 $15.00
3/22/05 $ 1.00
4/01/02 $36.00
End results…. March/05 $16.00
How do I accomplish this?
Thanks!
Mic Cooper
23 April 2005, 5:02 pmAshley:
I want to know how to make my excel say:
28 April 2005, 4:50 pmThursday, 4/28/05. It wont put the day in with the date. Is this possible?
Rob van Gelder:
Ashley:
28 April 2005, 8:56 pmCustom Number Format
dddd, m/d/yy
Ken:
I have a need to write extremely long formulas. I currently get around the Excel character limit by creating a seperate page just for formulas (which I call “Batch Formulas” (from my old DOS days)
and breaking up long formulas into multiple cells. I then referance these multiple cells in a single cell on the spreadsheet page.
Does anyone have a better solution?
The following example causes an “X” to be placed in a specific cell on the “Timesheet” page if an “S” is placed in any of a series of other cells on that page.
EXAMPLE:
Cell B46:
=IF(OR(Timesheet!A23=”S”,Timesheet!C23=”S”,Timesheet!E23=”S”,Timesheet!G23=”
S”,Timesheet!I23=”S”,Timesheet!K23=”S”,Timesheet!M23=”S”,Timesheet!O23=”S”,Ti
mesheet!Q23=”S”,Timesheet!S23=”S”,Timesheet!U23=”S”,Timesheet!W23=”S”,Timesh
eet!Y23=”S”,Timesheet!AA23=”S”,Timesheet!AC23=”S”,Timesheet!AE23=”S”,Timesh
eet!AG23=”S”,Timesheet!AI23=”S”,Timesheet!AK23=”S”,Timesheet!AM23=”S”,Times
heet!AO23=”S”,Timesheet!AQ23=”S”,Timesheet!AS23=”S”,Timesheet!AU23=”S”,Time
sheet!AW23=”S”,Timesheet!AY23=”S”,Timesheet!BA23=”S”,Timesheet!BC23=”S”,Ti
mesheet!BE23=”S”,Timesheet!BG23=”S”),”X”,” “)
Cell C46:
=IF(OR(Timesheet!BI23=”S”,Timesheet!BK23=”S”,Timesheet!BM23=”S”,Timesheet!B
O23=”S”),”X”,” “)
Cell B47:
=IF(OR(Timesheet!A24=”S”,Timesheet!C24=”S”,Timesheet!E24=”S”,Timesheet!G24=”
S”,Timesheet!I24=”S”,Timesheet!K24=”S”,Timesheet!M24=”S”,Timesheet!O24=”S”,Ti
mesheet!Q24=”S”,Timesheet!S24=”S”,Timesheet!U24=”S”,Timesheet!W24=”S”,Timesh
eet!Y24=”S”,Timesheet!AA24=”S”,Timesheet!AC24=”S”,Timesheet!AE24=”S”,Timesh
eet!AG24=”S”,Timesheet!AI24=”S”,Timesheet!AK24=”S”,Timesheet!AM24=”S”,Times
heet!AO24=”S”,Timesheet!AQ24=”S”,Timesheet!AS24=”S”,Timesheet!AU24=”S”,Time
sheet!AW24=”S”,Timesheet!AY24=”S”,Timesheet!BA24=”S”,Timesheet!BC24=”S”,Ti
mesheet!BE24=”S”,Timesheet!BG24=”S”),”X”,” “)
Cell C47:
=IF(OR(Timesheet!BI24=”S”,Timesheet!BK24=”S”,Timesheet!BM24=”S”,Timesheet!B
O24=”S”),”X”,” “)
EXAMPLE:
=IF(OR(’Batch Formulas’!B46=”X”,’Batch Formulas’!C46=”X”,’Batch
Formulas’!B47=”X”,’Batch Formulas’!C47=”X”),”X”,” “)
Thanks in advance for any ideas you may have.
Ken
5 June 2005, 1:35 pmJosh:
Here is a formula that I use to calculate my time worked each day as a third shift employee. Not long but was tedious to figure out. It still doesn’t do exactly what I need but it works.
=IF(OR(I24=”1A”,I24=”1D”,I24=”1E”,I24=”1F”),J24 + IF((ROUNDDOWN(((E24*24)+(F24*24))-24,1)+H24)
15 July 2005, 5:19 amJosh:
repost of equation, it didn’t all show up.
=IF(OR(I24=”1A”,I24=”1D”,I24=”1E”,I24=”1F”),
15 July 2005, 5:20 amJ24 + IF((ROUNDDOWN(((E24*24)+(F24*24))-24,1)
+H24)
Sige:
Hello all,
Been really impressed by Jwalks array-formula!!!
But did “may” missed out the 1st this year? ;o)
=IF(MONTH(DATE(YEAR(A10),MONTH(A10),1))-
24 September 2005, 1:32 pmMONTH(DATE(YEAR(A10),MONTH(A10),1)-
(WEEKDAY(DATE(YEAR(A10),MONTH(A10),1))-2)+
{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),”",DATE(YEAR(A10),
MONTH(A10),1)-(WEEKDAY(DATE(YEAR(A10),
MONTH(A10),1))-2)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)
kurt:
can anyone figure out a formula for me;
i want to find the total number of bills i paid for a certain month
example:
I paid a bill on 10-Oct-06, 13-Oct-06, 17-Oct-06 & 27-Oct-06. There has to be some formula out there in which i can somehow enter >= 01-Oct-06 &
23 November 2006, 12:46 pmVin Underwood:
I just stumbled onto this discussion thread and I feel compelled to contribute an example of a complex formula. It certainly qualifies as “long, virtually uneditable, and used in real life”. In addition it has a subtle bug, due entirely to it’s unnecessary complexity.
Here’s the formula:
=IF(D8Assumptions!$C$39),(Assumptions!$C$39*Assumptions!$D$39)+((’volumes including CFS’!D8-Assumptions!$C$39)*Assumptions!$D$40),IF(AND(’volumes including CFS’!D8Assumptions!$C$40),(Assumptions!$C$39*Assumptions!$D$39)+((Assumptions!$C$40-Assumptions!$C$39)*Assumptions!$D$40)+((’volumes including CFS’!D8-Assumptions!$C$40)*Assumptions!$D$41),(Assumptions!$C$39*Assumptions!$D$39)+((Assumptions!$C$40-Assumptions!$C$39)*Assumptions!$D$40)+((Assumptions!$C$41-Assumptions!$C$40)*Assumptions!$D$41)+((’volumes including CFS’!D8-Assumptions!$C$41)*Assumptions!$D$42))))
It’s “only” 741 characters.
The formula implements a very standard “tiered pricing” formula, where the marginal price at each tier is lower as the total volume increases.
I found the bug by trying to simplify the formula. Here’s my first marginal improvement:
=IF(D8Tier1Threshold),(Tier1Threshold*Tier1Price)+((D8-Tier1Threshold)*Tier2Price),IF(AND(D8Tier2Threshold),(Tier1Threshold*Tier1Price)+((Tier2Threshold-Tier1Threshold)*Tier2Price)+((D8-Tier2Threshold)*Tier3Price),(Tier1Threshold*Tier1Price)+((Tier2Threshold-Tier1Threshold)*Tier2Price)+((Tier3Threshold-Tier2Threshold)*Tier3Price)+((D8-Tier3Threshold)*Tier4Price))))
This helps a little bit I guess.
To make a significant improvement I substituted a VLOOKUP as follows:
=VLOOKUP(D8,Residency_Pricing_Table,4,TRUE)+(D8-VLOOKUP(D8,Residency_Pricing_Table,2,TRUE))*VLOOKUP(D8,Residency_Pricing_Table,3,TRUE)
It must be paired with the following table, with a range name “Residency_Pricing_Table”:
Account Tier Lower
Range Boundary Rate Cum Cost
0 0 0.0300 0
4,500,000 4,500,000 0.0250 135,000.00
6,000,000 6,000,000 0.0200 172,500.00
9,000,000 9,000,000 0.0150 232,500.00
I think this is a vast improvement. However, I went one step further. The final version of the formula looks like this:
=TieredPrice(D8,Residency_Pricing_Table)
The User Function is:
Function TieredPrice(number, rngInput As Range)
Application.Volatile
‘
‘ A Tiered Pricing Table always has four columns:
‘ 1: the vlookup column, tier lower boundary
‘ 2: = col 1, data as the tier lower boundary
‘ 3: the tier marginal price
‘ 4: the cumulative cost up to the lower boundary of the tier
‘
‘ The table can have any number of rows (tiers)
‘
CumCost = Application.WorksheetFunction.VLookup(number, rngInput, 4, True)
NumInTier = number - Application.WorksheetFunction.VLookup(number, rngInput, 2, True)
CostInTier = NumInTier * Application.WorksheetFunction.VLookup(number, rngInput, 3, True)
TieredPrice = CumCost + CostInTier
End Function
The erroneous spreadsheet had tiered pricing formulas for many different components. The beauty of this function is that it works for any tiered formula, regardless of the number of tiers. Just pass the named range for the formula table to the function.
Soapbox comment: I believe there is never justification for a long, complex formula. Every effort should be made to simplify - often with User Functions. (I suppose the “final” version of a spreadsheet that must be optimized for speed would be a valid exception. But what spreadsheet is ever final?)
Regards,
21 January 2007, 12:13 pmVin Underwood