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.
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
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.
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.
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…
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))
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.
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)
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.
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.
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))
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!)
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)
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
Selection.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) {“
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.
Juan, thanks for the infomation
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
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)
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.
This could have been easily done with this formula:
=CHOOSE(WEEKDAY(B1),7,1,2,3,4,5,6)
or
even easier:
=WEEKDAY(B1,2)
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.
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. 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.
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
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)) )
“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.
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.
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.
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 :-).
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.”
— Leonardo da Vinci
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!
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
I want to know how to make my excel say:
Thursday, 4/28/05. It wont put the day in with the date. Is this possible?
Ashley:
Custom Number Format
dddd, m/d/yy
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
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)
repost of equation, it didn’t all show up.
=IF(OR(I24=”1A”,I24=”1D”,I24=”1E”,I24=”1F”),
J24 + IF((ROUNDDOWN(((E24*24)+(F24*24))-24,1)
+H24)
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))-
MONTH(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)
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 &
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,
Vin Underwood
Only just happened on this site – how about this bad boy:
=IF(OR(Main!D77=Options!$F$14,Main!D77=Options!$F$15,Main!D77=Options!$F$16,Main!D77=Options!$F$17,Main!D77=Options!$F$18,Main!D77=Options!$F$19),0,IF(Main!D77?”,SUM(VALUE(LEFT(Main!D77,FIND(Options!$F$11,Main!D77)-1))*256,IF(Options!$D$7=”Y”,VALUE(MID(Main!D77,FIND(Options!$F$11,Main!D77,1)+1,FIND(Options!$F$11,(SUBSTITUTE(Main!D77,Options!$F$11,”X”,1)))-FIND(Options!$F$11,Main!D77,1)-1))*16,VALUE(RIGHT(Main!D77,LEN(Main!D77)-FIND(Options!$F$11,Main!D77)))*16),IF(Options!$D$7=”Y”,RIGHT(Main!D77,LEN(Main!D77)-FIND(Options!$F$11,(SUBSTITUTE(Main!D77,Options!$F$11,”X”,1)),1)),0)),0))
I wrote it as part of an angling match result calculator. The actual spreadsheet contains this formula in 50 rows x 50 columns.
I wrote this over at PHD a few weeks ago
=+TREND(OFFSET($J$6,+MATCH($C7,$J$7:$J$16,1),+MATCH($D7,OFFSET($K$6:$P$6,MATCH($C7,$J$7:$J$16,1)-1,0)),1,2),OFFSET($J$6,+MATCH($C7,$J$7:$J$16,1)-1,+MATCH($D7,OFFSET($K$6:$P$6,MATCH($C7,$J$7:$J$16,1)-1,0)),1,2),$D7)
Noting that it isn’t padded out with page names, yet !
It calculates a new Y value for a given X value between a set of X and Y Co-ordinates in an Array which can be in different locations based on 2 other variables (I think that’s what it did)
I wonder what formula will overload or break these comment boxes and yet still do something useful in Excel ?
I like this array formula for finding cells that sum to a target value.
It returns TRUE or FALSE next to each value to include in the sum.
=MOD(MOD(SMALL(ABS(ROUND((
(MOD(ROW(A:A)/2^0,2)>=1)*A2+
(MOD(ROW(A:A)/2^1,2)>=1)*A3+
(MOD(ROW(A:A)/2^2,2)>=1)*A4+
(MOD(ROW(A:A)/2^3,2)>=1)*A5+
(MOD(ROW(A:A)/2^4,2)>=1)*A6+
(MOD(ROW(A:A)/2^5,2)>=1)*A7+
(MOD(ROW(A:A)/2^6,2)>=1)*A8+
(MOD(ROW(A:A)/2^7,2)>=1)*A9+
(MOD(ROW(A:A)/2^8,2)>=1)*A10+
(MOD(ROW(A:A)/2^9,2)>=1)*A11+
(MOD(ROW(A:A)/2^10,2)>=1)*A12+
(MOD(ROW(A:A)/2^11,2)>=1)*A13+
(MOD(ROW(A:A)/2^12,2)>=1)*A14+
(MOD(ROW(A:A)/2^13,2)>=1)*A15+
(MOD(ROW(A:A)/2^14,2)>=1)*A16+
(MOD(ROW(A:A)/2^15,2)>=1)*A17
-A1)*10^8,-6))+ROW(A:A),B1),10^6)/2
^(ROW(A2:A17)-ROW(A2)),2)>=1
Put a target value in A1, values to sum in A2:A17, and B1=1.
Select B2:B17 and array-enter this formula with {Ctrl+Shift+Enter}.
It takes a few seconds to run, you can try B1=2,3,.. for other solutions.
Two decimal places accuracy is assumed.
I don’t think it’s possible to do in many less characters than this (568).
It could be extended to up to 20 values in Excel 2007+ (~690). lhm
@Lori
Can you explain in a little more detail what your formula is calculating? It looks like it is identifying the values in A2:A17 that, when added together, will total up to (could be less), but no more than, the target value… if that is the case, then what is the value in B1 for/doing?
Rick – yes, the formula finds the closest selection of values to the given target value. It returns TRUE next to each value to include in the sum. The result could be larger or smaller than the target.
For 16 values, there are 2^16=65536 combinations, equal to the number of rows in Excel 2003. Each combination is tested by calculating the difference (scaled up and rounded) and then adding on the position index. The results are passed to SMALL(.,B1), which returns the smallest combination of difference and position index. Changing B1 to 2 returns the next smallest combination etc.
In the Excel 2010 beta this more flexible (though even more incoherent!) version seems to work, which extends to up to 20 test values.
=MOD(MOD(SMALL(ABS(ROUND((MMULT(INT(MOD(ROW(A1:INDEX(A:A,2^ROWS(A2:A17)))
/2^TRANSPOSE((ROW(A2:A17)-ROW(A2))),2)),A2:A17)-A1)*10^9,-7))+
ROW(A1:INDEX(A:A,2^ROWS(A2:A17))),B1),10^7)/2^(ROW(A2:A17)-ROW(A2)),2)>=1
The MMULT function works for larger ranges now apparently, don’t know about Excel 2007?
Rick – yes, the formula finds the closest selection of values to the given target value. It returns TRUE next to each value to include in the sum. The result could be larger or smaller than the target.
For 16 values, there are 2^16=65536 combinations, equal to the number of rows in Excel 2003. Each combination is tested by calculating the difference (scaled up and rounded) and then adding on the position index. The results are passed to SMALL(.,B1), which returns the smallest combination of difference and position index. Changing B1 to 2 returns the next smallest combination etc.
In the Excel 2010 beta this more flexible (though even more incoherent!) version seems to work, which extends to up to 20 test values.
=MOD(MOD(SMALL(ABS(ROUND((MMULT(INT(MOD(ROW(A1:INDEX(A:A,2^ROWS(A2:A17)))
/2^TRANSPOSE((ROW(A2:A17)-ROW(A2))),2)),A2:A17)-A1)*10^9,-7))+
ROW(A1:INDEX(A:A,2^ROWS(A2:A17))),B1),10^7)/2^(ROW(A2:A17)-ROW(A2)),2)>=1
The MMULT function works for larger ranges now apparently, don’t know about Excel 2007?
Couldn’t help but throw my hat into the ring here…
=IF(OR($B2=”DS”,$B2=”Manual”)=TRUE,IF($B2=”DS”,IF($C2=”Price”,OFFSET(‘DS Data’!D$1,MATCH($D2,’DS Data’!$B$2:$B$1048576,0),0)/OFFSET(‘DS Data’!C$1,MATCH($D2,’DS Data’!$B$2:$B$1048576,0),0)-1,OFFSET(‘DS Data’!C$1,MATCH($D2,’DS Data’!$B$2:$B$1048576,0),0)/1200),IF($C2=”Price”,OFFSET(‘Manual Data’!$A$2,MATCH(E$1,’Manual Data’!$A$3:$A$1048576,0),MATCH($A2,’Manual Data’!$B$1:$XFD$1,0))/OFFSET(‘Manual Data’!$A$2,MATCH(E$1,’Manual Data’!$A$3:$A$1048576,0)-1,MATCH($A2,’Manual Data’!$B$1:$XFD$1,0))-1,OFFSET(‘Manual Data’!$A$2,MATCH(E$1,’Manual Data’!$A$3:$A$1048576,0)-1,MATCH($A2,’Manual Data’!$B$1:$XFD$1,0))/1200)),ERROR.TYPE(1))
Just written this, I think it’s hilarious – works though!
=IF(OR($G$18=””,$L$36=””),””,IF($G$18=” TOTAL COMPANY”,IF($C$17,IF($D$17,IF($E$17,SUM(SUMIFS(IF($M$67=1,Data!$CW:$CW,IF($M$67=2,Data!$CY:$CY,IF($M$67=3,Data!$DA:$DA,IF($M$67=4,Data!$DC:$DC,IF($M$67=5,Data!$DE:$DE,IF($M$67=6,Data!$DG:$DG,IF($M$67=7,Data!$DI:$DI,IF($M$67=8,Data!$DK:$DK,IF($M$67=9,Data!$DM:$DM,IF($M$67=10,Data!$DO:$DO,””)))))))))),Data!$H:$H,$L$36,Data!$I:$I,$C$18),SUMIFS(IF($M$67=1,Data!$CW:$CW,IF($M$67=2,Data!$CY:$CY,IF($M$67=3,Data!$DA:$DA,IF($M$67=4,Data!$DC:$DC,IF($M$67=5,Data!$DE:$DE,IF($M$67=6,Data!$DG:$DG,IF($M$67=7,Data!$DI:$DI,IF($M$67=8,Data!$DK:$DK,IF($M$67=9,Data!$DM:$DM,IF($M$67=10,Data!$DO:$DO,””)))))))))),Data!$H:$H,$L$36,Data!$I:$I,$D$18),SUMIFS(IF($M$67=1,Data!$CW:$CW,IF($M$67=2,Data!$CY:$CY,IF($M$67=3,Data!$DA:$DA,IF($M$67=4,Data!$DC:$DC,IF($M$67=5,Data!$DE:$DE,IF($M$67=6,Data!$DG:$DG,IF($M$67=7,Data!$DI:$DI,IF($M$67=8,Data!$DK:$DK,IF($M$67=9,Data!$DM:$DM,IF($M$67=10,Data!$DO:$DO,””)))))))))),Data!$H:$H,$L$36,Data!$I:$I,$E$18)),SUM(SUMIFS(IF($M$67=1,Data!$CW:$CW,IF($M$67=2,Data!$CY:$CY,IF($M$67=3,Data!$DA:$DA,IF($M$67=4,Data!$DC:$DC,IF($M$67=5,Data!$DE:$DE,IF($M$67=6,Data!$DG:$DG,IF($M$67=7,Data!$DI:$DI,IF($M$67=8,Data!$DK:$DK,IF($M$67=9,Data!$DM:$DM,IF($M$67=10,Data!$DO:$DO,””)))))))))),Data!$H:$H,$L$36,Data!$I:$I,$C$18),SUMIFS(IF($M$67=1,Data!$CW:$CW,IF($M$67=2,Data!$CY:$CY,IF($M$67=3,Data!$DA:$DA,IF($M$67=4,Data!$DC:$DC,IF($M$67=5,Data!$DE:$DE,IF($M$67=6,Data!$DG:$DG,IF($M$67=7,Data!$DI:$DI,IF($M$67=8,Data!$DK:$DK,IF($M$67=9,Data!$DM:$DM,IF($M$67=10,Data!$DO:$DO,””)))))))))),Data!$H:$H,$L$36,Data!$I:$I,$D$18))),IF($E$17,SUM(SUMIFS(IF($M$67=1,Data!$CW:$CW,IF($M$67=2,Data!$CY:$CY,IF($M$67=3,Data!$DA:$DA,IF($M$67=4,Data!$DC:$DC,IF($M$67=5,Data!$DE:$DE,IF($M$67=6,Data!$DG:$DG,IF($M$67=7,Data!$DI:$DI,IF($M$67=8,Data!$DK:$DK,IF($M$67=9,Data!$DM:$DM,IF($M$67=10,Data!$DO:$DO,””)))))))))),Data!$H:$H,$L$36,Data!$I:$I,$C$18),SUMIFS(IF($M$67=1,Data!$CW:$CW,IF($M$67=2,Data!$CY:$CY,IF($M$67=3,Data!$DA:$DA,IF($M$67=4,Data!$DC:$DC,IF($M$67=5,Data!$DE:$DE,IF($M$67=6,Data!$DG:$DG,IF($M$67=7,Data!$DI:$DI,IF($M$67=8,Data!$DK:$DK,IF($M$67=9,Data!$DM:$DM,IF($M$67=10,Data!$DO:$DO,””)))))))))),Data!$H:$H,$L$36,Data!$I:$I,$E$18)),SUMIFS(IF($M$67=1,Data!$CW:$CW,IF($M$67=2,Data!$CY:$CY,IF($M$67=3,Data!$DA:$DA,IF($M$67=4,Data!$DC:$DC,IF($M$67=5,Data!$DE:$DE,IF($M$67=6,Data!$DG:$DG,IF($M$67=7,Data!$DI:$DI,IF($M$67=8,Data!$DK:$DK,IF($M$67=9,Data!$DM:$DM,IF($M$67=10,Data!$DO:$DO,””)))))))))),Data!$H:$H,$L$36,Data!$I:$I,$C$18))),IF($D$17,IF($E$17,SUM(SUMIFS(IF($M$67=1,Data!$CW:$CW,IF($M$67=2,Data!$CY:$CY,IF($M$67=3,Data!$DA:$DA,IF($M$67=4,Data!$DC:$DC,IF($M$67=5,Data!$DE:$DE,IF($M$67=6,Data!$DG:$DG,IF($M$67=7,Data!$DI:$DI,IF($M$67=8,Data!$DK:$DK,IF($M$67=9,Data!$DM:$DM,IF($M$67=10,Data!$DO:$DO,””)))))))))),Data!$H:$H,$L$36,Data!$I:$I,$D$18),SUMIFS(IF($M$67=1,Data!$CW:$CW,IF($M$67=2,Data!$CY:$CY,IF($M$67=3,Data!$DA:$DA,IF($M$67=4,Data!$DC:$DC,IF($M$67=5,Data!$DE:$DE,IF($M$67=6,Data!$DG:$DG,IF($M$67=7,Data!$DI:$DI,IF($M$67=8,Data!$DK:$DK,IF($M$67=9,Data!$DM:$DM,IF($M$67=10,Data!$DO:$DO,””)))))))))),Data!$H:$H,$L$36,Data!$I:$I,$E$18)),SUMIFS(IF($M$67=1,Data!$CW:$CW,IF($M$67=2,Data!$CY:$CY,IF($M$67=3,Data!$DA:$DA,IF($M$67=4,Data!$DC:$DC,IF($M$67=5,Data!$DE:$DE,IF($M$67=6,Data!$DG:$DG,IF($M$67=7,Data!$DI:$DI,IF($M$67=8,Data!$DK:$DK,IF($M$67=9,Data!$DM:$DM,IF($M$67=10,Data!$DO:$DO,””)))))))))),Data!$H:$H,$L$36,Data!$I:$I,$D$18)),IF($E$17,SUMIFS(IF($M$67=1,Data!$CW:$CW,IF($M$67=2,Data!$CY:$CY,IF($M$67=3,Data!$DA:$DA,IF($M$67=4,Data!$DC:$DC,IF($M$67=5,Data!$DE:$DE,IF($M$67=6,Data!$DG:$DG,IF($M$67=7,Data!$DI:$DI,IF($M$67=8,Data!$DK:$DK,IF($M$67=9,Data!$DM:$DM,IF($M$67=10,Data!$DO:$DO,””)))))))))),Data!$H:$H,$L$36,Data!$I:$I,$E$18),””))),IF($C$17,IF($D$17,IF($E$17,SUM(SUMIFS(IF($M$67=1,Data!$CW:$CW,IF($M$67=2,Data!$CY:$CY,IF($M$67=3,Data!$DA:$DA,IF($M$67=4,Data!$DC:$DC,IF($M$67=5,Data!$DE:$DE,IF($M$67=6,Data!$DG:$DG,IF($M$67=7,Data!$DI:$DI,IF($M$67=8,Data!$DK:$DK,IF($M$67=9,Data!$DM:$DM,IF($M$67=10,Data!$DO:$DO,””)))))))))),Data!$H:$H,$L$36,Data!$I:$I,$C$18,Data!$F:$F,$G$18),SUMIFS(IF($M$67=1,Data!$CW:$CW,IF($M$67=2,Data!$CY:$CY,IF($M$67=3,Data!$DA:$DA,IF($M$67=4,Data!$DC:$DC,IF($M$67=5,Data!$DE:$DE,IF($M$67=6,Data!$DG:$DG,IF($M$67=7,Data!$DI:$DI,IF($M$67=8,Data!$DK:$DK,IF($M$67=9,Data!$DM:$DM,IF($M$67=10,Data!$DO:$DO,””)))))))))),Data!$H:$H,$L$36,Data!$I:$I,$D$18,Data!$F:$F,$G$18),SUMIFS(IF($M$67=1,Data!$CW:$CW,IF($M$67=2,Data!$CY:$CY,IF($M$67=3,Data!$DA:$DA,IF($M$67=4,Data!$DC:$DC,IF($M$67=5,Data!$DE:$DE,IF($M$67=6,Data!$DG:$DG,IF($M$67=7,Data!$DI:$DI,IF($M$67=8,Data!$DK:$DK,IF($M$67=9,Data!$DM:$DM,IF($M$67=10,Data!$DO:$DO,””)))))))))),Data!$H:$H,$L$36,Data!$I:$I,$E$18,Data!$F:$F,$G$18)),SUM(SUMIFS(IF($M$67=1,Data!$CW:$CW,IF($M$67=2,Data!$CY:$CY,IF($M$67=3,Data!$DA:$DA,IF($M$67=4,Data!$DC:$DC,IF($M$67=5,Data!$DE:$DE,IF($M$67=6,Data!$DG:$DG,IF($M$67=7,Data!$DI:$DI,IF($M$67=8,Data!$DK:$DK,IF($M$67=9,Data!$DM:$DM,IF($M$67=10,Data!$DO:$DO,””)))))))))),Data!$H:$H,$L$36,Data!$I:$I,$C$18,Data!$F:$F,$G$18),SUMIFS(IF($M$67=1,Data!$CW:$CW,IF($M$67=2,Data!$CY:$CY,IF($M$67=3,Data!$DA:$DA,IF($M$67=4,Data!$DC:$DC,IF($M$67=5,Data!$DE:$DE,IF($M$67=6,Data!$DG:$DG,IF($M$67=7,Data!$DI:$DI,IF($M$67=8,Data!$DK:$DK,IF($M$67=9,Data!$DM:$DM,IF($M$67=10,Data!$DO:$DO,””)))))))))),Data!$H:$H,$L$36,Data!$I:$I,$D$18,Data!$F:$F,$G$18))),IF($E$17,SUM(SUMIFS(IF($M$67=1,Data!$CW:$CW,IF($M$67=2,Data!$CY:$CY,IF($M$67=3,Data!$DA:$DA,IF($M$67=4,Data!$DC:$DC,IF($M$67=5,Data!$DE:$DE,IF($M$67=6,Data!$DG:$DG,IF($M$67=7,Data!$DI:$DI,IF($M$67=8,Data!$DK:$DK,IF($M$67=9,Data!$DM:$DM,IF($M$67=10,Data!$DO:$DO,””)))))))))),Data!$H:$H,$L$36,Data!$I:$I,$C$18,Data!$F:$F,$G$18),SUMIFS(IF($M$67=1,Data!$CW:$CW,IF($M$67=2,Data!$CY:$CY,IF($M$67=3,Data!$DA:$DA,IF($M$67=4,Data!$DC:$DC,IF($M$67=5,Data!$DE:$DE,IF($M$67=6,Data!$DG:$DG,IF($M$67=7,Data!$DI:$DI,IF($M$67=8,Data!$DK:$DK,IF($M$67=9,Data!$DM:$DM,IF($M$67=10,Data!$DO:$DO,””)))))))))),Data!$H:$H,$L$36,Data!$I:$I,$E$18,Data!$F:$F,$G$18)),SUMIFS(IF($M$67=1,Data!$CW:$CW,IF($M$67=2,Data!$CY:$CY,IF($M$67=3,Data!$DA:$DA,IF($M$67=4,Data!$DC:$DC,IF($M$67=5,Data!$DE:$DE,IF($M$67=6,Data!$DG:$DG,IF($M$67=7,Data!$DI:$DI,IF($M$67=8,Data!$DK:$DK,IF($M$67=9,Data!$DM:$DM,IF($M$67=10,Data!$DO:$DO,””)))))))))),Data!$H:$H,$L$36,Data!$I:$I,$C$18,Data!$F:$F,$G$18))),IF($D$17,IF($E$17,SUM(SUMIFS(IF($M$67=1,Data!$CW:$CW,IF($M$67=2,Data!$CY:$CY,IF($M$67=3,Data!$DA:$DA,IF($M$67=4,Data!$DC:$DC,IF($M$67=5,Data!$DE:$DE,IF($M$67=6,Data!$DG:$DG,IF($M$67=7,Data!$DI:$DI,IF($M$67=8,Data!$DK:$DK,IF($M$67=9,Data!$DM:$DM,IF($M$67=10,Data!$DO:$DO,””)))))))))),Data!$H:$H,$L$36,Data!$I:$I,$D$18,Data!$F:$F,$G$18),SUMIFS(IF($M$67=1,Data!$CW:$CW,IF($M$67=2,Data!$CY:$CY,IF($M$67=3,Data!$DA:$DA,IF($M$67=4,Data!$DC:$DC,IF($M$67=5,Data!$DE:$DE,IF($M$67=6,Data!$DG:$DG,IF($M$67=7,Data!$DI:$DI,IF($M$67=8,Data!$DK:$DK,IF($M$67=9,Data!$DM:$DM,IF($M$67=10,Data!$DO:$DO,””)))))))))),Data!$H:$H,$L$36,Data!$I:$I,$E$18,Data!$F:$F,$G$18)),SUMIFS(IF($M$67=1,Data!$CW:$CW,IF($M$67=2,Data!$CY:$CY,IF($M$67=3,Data!$DA:$DA,IF($M$67=4,Data!$DC:$DC,IF($M$67=5,Data!$DE:$DE,IF($M$67=6,Data!$DG:$DG,IF($M$67=7,Data!$DI:$DI,IF($M$67=8,Data!$DK:$DK,IF($M$67=9,Data!$DM:$DM,IF($M$67=10,Data!$DO:$DO,””)))))))))),Data!$H:$H,$L$36,Data!$I:$I,$D$18,Data!$F:$F,$G$18)),IF($E$17,SUMIFS(IF($M$67=1,Data!$CW:$CW,IF($M$67=2,Data!$CY:$CY,IF($M$67=3,Data!$DA:$DA,IF($M$67=4,Data!$DC:$DC,IF($M$67=5,Data!$DE:$DE,IF($M$67=6,Data!$DG:$DG,IF($M$67=7,Data!$DI:$DI,IF($M$67=8,Data!$DK:$DK,IF($M$67=9,Data!$DM:$DM,IF($M$67=10,Data!$DO:$DO,””)))))))))),Data!$H:$H,$L$36,Data!$I:$I,$E$18,Data!$F:$F,$G$18),””)))))
Totaling columns of those columns that have yes. Usgly but it works.
=IF(Y$10=”yes”,Y12,0)+IF(Z$10=”yes”,Z12,0)+IF(AA$10=”yes”,AA12,0)+IF(AB$10=”yes”,AB12,0)+IF(AC$10=”yes”,AC12,0)+IF(AD$10=”yes”,AD12,0)+IF(AE$10=”yes”,AE12,0)+IF(AF$10=”yes”,AF12,0)+IF(AG$10=”yes”,AG12,0)+IF(AH$10=”yes”,AH12,0)+IF(AI$10=”yes”,AI12,0)+IF(AJ$10=”yes”,AJ12,0)+IF(AK$10=”yes”,AK12,0)+IF(AL$10=”yes”,AL12,0)+IF(AM$10=”yes”,AM12,0)+IF(AN$10=”yes”,AN12,0)+IF(AO$10=”yes”,AO12,0)+IF(AP$10=”yes”,AP12,0)+IF(AQ$10=”yes”,AQ12,0)+IF(AR$10=”yes”,AR12,0)+IF(AS$10=”yes”,AS12,0)+IF(AT$10=”yes”,AT12,0)+IF(AU$10=”yes”,AU12,0)+IF(AV$10=”yes”,AV12,0)+IF(AW$10=”yes”,AW12,0)+IF(AX$10=”yes”,AX12,0)+IF(AY$10=”yes”,AY12,0)+IF(AZ$10=”yes”,AZ12,0)+IF(BA$10=”yes”,BA12,0)+IF(BB$10=”yes”,BB12,0)+IF(BC$10=”yes”,BC12,0)+IF(BD$10=”yes”,BD12,0)+IF(BE$10=”yes”,BE12,0)+IF(BF$10=”yes”,BF12,0)+IF(BG$10=”yes”,BG12,0)+IF(BH$10=”yes”,BH12,0)+IF(BI$10=”yes”,BI12,0)+IF(BJ$10=”yes”,BJ12,0)+IF(BK$10=”yes”,BK12,0)+IF(BL$10=”yes”,BL12,0)+IF(BM$10=”yes”,BM12,0)+IF(BN$10=”yes”,BN12,0)+IF(BO$10=”yes”,BO12,0)+IF(BP$10=”yes”,BP12,0)+IF(BQ$10=”yes”,BQ12,0)+IF(BR$10=”yes”,BR12,0)+IF(BS$10=”yes”,BS12,0)+IF(BT$10=”yes”,BT12,0)+IF(BU$10=”yes”,BU12,0)+IF(BV$10=”yes”,BV12,0)+IF(BW$10=”yes”,BW12,0)+IF(BX$10=”yes”,BX12,0)+IF(BY$10=”yes”,BY12,0)+IF(BZ$10=”yes”,BZ12,0)+IF(CA$10=”yes”,CA12,0)+IF(CB$10=”yes”,CB12,0)+IF(CC$10=”yes”,CC12,0)+IF(CD$10=”yes”,CD12,0)+IF(CE$10=”yes”,CE12,0)+IF(CF$10=”yes”,CF12,0)+IF(CG$10=”yes”,CG12,0)+IF(CH$10=”yes”,CH12,0)+IF(CI$10=”yes”,CI12,0)+IF(CJ$10=”yes”,CJ12,0)+IF(CK$10=”yes”,CK12,0)+IF(CL$10=”yes”,CL12,0)+IF(CM$10=”yes”,CM12,0)+IF(CN$10=”yes”,CN12,0)+IF(CO$10=”yes”,CO12,0)+IF(CP$10=”yes”,CP12,0)+IF(CQ$10=”yes”,CQ12,0)+IF(CR$10=”yes”,CR12,0)+IF(CS$10=”yes”,CS12,0)+IF(CT$10=”yes”,CT12,0)+IF(CU$10=”yes”,CU12,0)+IF(CV$10=”yes”,CV12,0)+IF(CW$10=”yes”,CW12,0)+IF(CX$10=”yes”,CX12,0)+IF(CY$10=”yes”,CY12,0)+IF(CZ$10=”yes”,CZ12,0)+IF(DA$10=”yes”,DA12,0)+IF(DB$10=”yes”,DB12,0)+IF(DC$10=”yes”,DC12,0)+IF(DD$10=”yes”,DD12,0)+IF(DE$10=”yes”,DE12,0)+IF(DF$10=”yes”,DF12,0)+IF(DG$10=”yes”,DG12,0)+IF(DH$10=”yes”,DH12,0)+IF(DI$10=”yes”,DI12,0)+IF(DJ$10=”yes”,DJ12,0)+IF(DK$10=”yes”,DK12,0)+IF(DL$10=”yes”,DL12,0)+IF(DM$10=”yes”,DM12,0)+IF(DN$10=”yes”,DN12,0)+IF(DO$10=”yes”,DO12,0)+IF(DP$10=”yes”,DP12,0)+IF(DQ$10=”yes”,DQ12,0)+IF(DR$10=”yes”,DR12,0)+IF(DS$10=”yes”,DS12,0)+IF(DT$10=”yes”,DT12,0)+IF(DU$10=”yes”,DU12,0)+IF(DV$10=”yes”,DV12,0)+IF(DW$10=”yes”,DW12,0)+IF(DX$10=”yes”,DX12,0)+IF(DY$10=”yes”,DY12,0)+IF(DZ$10=”yes”,DZ12,0)+IF(EA$10=”yes”,EA12,0)+IF(EB$10=”yes”,EB12,0)+IF(EC$10=”yes”,EC12,0)+IF(ED$10=”yes”,ED12,0)+IF(EE$10=”yes”,EE12,0)+IF(EF$10=”yes”,EF12,0)+IF(EG$10=”yes”,EG12,0)+IF(EH$10=”yes”,EH12,0)+IF(EI$10=”yes”,EI12,0)+IF(EJ$10=”yes”,EJ12,0)+IF(EK$10=”yes”,EK12,0)+IF(EL$10=”yes”,EL12,0)+IF(EM$10=”yes”,EM12,0)+IF(EN$10=”yes”,EN12,0)+IF(EO$10=”yes”,EO12,0)+IF(EP$10=”yes”,EP12,0)+IF(EQ$10=”yes”,EQ12,0)+IF(ER$10=”yes”,ER12,0)+IF(ES$10=”yes”,ES12,0)+IF(ET$10=”yes”,ET12,0)+IF(EU$10=”yes”,EU12,0)+IF(EV$10=”yes”,EV12,0)+IF(EW$10=”yes”,EW12,0)+IF(EX$10=”yes”,EX12,0)+IF(EY$10=”yes”,EY12,0)+IF(EZ$10=”yes”,EZ12,0)+IF(FA$10=”yes”,FA12,0)+IF(FB$10=”yes”,FB12,0)+IF(FC$10=”yes”,FC12,0)+IF(FD$10=”yes”,FD12,0)+IF(FE$10=”yes”,FE12,0)+IF(FF$10=”yes”,FF12,0)+IF(FG$10=”yes”,FG12,0)+IF(FH$10=”yes”,FH12,0)+IF(FI$10=”yes”,FI12,0)+IF(FJ$10=”yes”,FJ12,0)+IF(FK$10=”yes”,FK12,0)+IF(FL$10=”yes”,FL12,0)+IF(FM$10=”yes”,FM12,0)+IF(FN$10=”yes”,FN12,0)+IF(FO$10=”yes”,FO12,0)+IF(FP$10=”yes”,FP12,0)+IF(FQ$10=”yes”,FQ12,0)+IF(FR$10=”yes”,FR12,0)
With regard to the monthly calendar, which I personally think is brilliant…how can I remove the #NAME error that occurs on the non valid dates for that month and more importantly could someone tell me how I could achieve this for a yearly calendar as I have to update the holiday form every year and at the moment I am having to do this manually….not that IS UGLY…
Hi,
can I get a download of data where I can be able to work with such complex formulas?
I’m just a new Excel student and I’m passionate about trying new things