My head hurts from crazy excel formulas

Kguku · Post by **Kguku** » September 9, 2004, 1:56 am

I thought I would share my woes with you:

=IF($Y$23=0,IF(B25="","",IF(B37<$H$2,(B37-$H$2),IF(SUM(B28:B36)=0,IF(B37>$H$2,B37-$H$2,0),IF(SUM(B28:B36)=(B37-$H$2),0,
IF(SUM(B28:B36)>(B37-$H$2),IF((B37-$H$2)-SUM(B28:B36)<=-$H$2,-$H$2,(B37-$H$2)-SUM(B28:B36)),(B37-$H$2)-SUM(B28:B36)))))),IF($Y$23=1,IF(B8="","",B20)))

=IF(B20="","",IF(SUM(C11:C19)=C20,0,IF(C20-SUM(C11:C19)<0,"CHECK",(C20-SUM(C11:C19)))))

=IF($X$6=1,$H$2,IF(C8="","",IF(B8>C8,((C8+24)-B8)-D8,(C8-B8)-D8)))

=IF(B20="","",IF(B20="Auto","",IF(B20<=7.5,0,IF(B20>=7.75,IF($G$2=37.5,IF((B20-7.5)<0,0,(B20-7.5)),IF(B20>=8,IF((B20-8)<0,0,(B20-8)),0)),0))))

=IF($G$2<37.5,IF($Q20-37.5-$AE11>SUM(R10:R19),($Q20-37.5-$AE11),SUM(R10:R19)),SUM(R10:R19))

=IF(N8="",0,IF(N8="Stat",1+Y10,IF(ISTEXT(N8),1,0)))

=IF($F$2="CAS",1,0)

=IF(X10=0,0,IF(N8="Sick",1,IF(N8="Vac",2,IF(N8="Stat",3,IF(N8="Ber",4,5)))))

=IF(Z10=2,L10,0)

=IF(Z10=1,L10,0)

=IF(Z10=4,L10,IF(Z10=5,L10,0))

=IF($X10=1,$H$2,0)

=COUNT(C8,F8,I8,L8,O8)

=IF($K$2<5,IF($K$2-$AA12<0,SUM(Z6:Z10)/3,(SUM(Z6:Z10)/3)-($K$2-$AA12)),"")
[/quote]

Enjoy!

EDIT: Fixing spacing so it doesn't break the layout

Jaxomer · Post by **Jaxomer** » September 9, 2004, 2:58 am

Winnow · Post by **Winnow** » September 9, 2004, 3:50 am

I see a ")" that is in the wrong place.

XunilTlatoani · Post by **XunilTlatoani** » September 9, 2004, 10:39 am

i feel your pain

Post by **Sylvus** » September 9, 2004, 5:21 pm

Why are you tracking employee time on an excel spreadsheet?!

Neost · Post by **Neost** » September 9, 2004, 5:34 pm

Is that how they figure vv's?

Kguku · Post by **Kguku** » September 9, 2004, 6:12 pm

Sylvus wrote:Why are you tracking employee time on an excel spreadsheet?!

It's for submission purposes to the person who does the inputting in to peoplesoft, it's in 2 week blocks and there's no point in writing a database for strictly cosmetic submissions to someone else.

The spreadsheet was fine in it's original form, but it seems the person who gets the submission wants a fancier submission page so the other departments can use it.

Tenuvil · Post by **Tenuvil** » September 10, 2004, 11:44 am

Excel formulas like that are the big win. I have crap like that in my training log spreadsheet to calculate calories burned based on weight, exercise time and speed (=IF(H66,0.0141*EXP(0.092*G66)*H66*(F66*60),"")) as well as formulas to convert time entered in hours minutes and seconds in separate cells to decimal time, then back to hours minutes and seconds for totals and averages. Oh and the formulas suppress zero values by nesting everything in an if statement with the false argument being "". Good stuff.

Kguku · Post by **Kguku** » September 10, 2004, 12:46 pm

I managed to get back to it last night during my fun double shift I got to work and I think I finished it, woo, testing happens tonight to see how many fucked up bugs I can encounter!

Neost · Post by **Neost** » September 11, 2004, 11:13 am

My oldest daugher's boyfriend just took a 2nd job helping a friend of his manage a pizza place. He decided to build a spreadsheet to help the day cook figure out how much dough to make each day.

It looked almost as complicated as the one above.

Problem was, the first time they used it they made enough dough for 3 or 4 days of operation. They had fucking dough stashed everywhere they could find to hide it.

So much for "l33t xcl skillz". If I have to smell/look at/hear about another damn pizza i'm gonna fucking kill someone.

On the other hand, if I stop in to eat all I pay for is the beer I drink.

Spang · Post by **Spang** » September 11, 2004, 1:54 pm

dough a day or 2 after you make it tastes better anyways!