Complex Double If / And formula Help Needed

[jkinnick]

Hi,

I need help with getting a rather complex double if/and formula working correctly. I am stumped. In the attached spreadsheet we are looking at cell AA157. In the formulas I am setting up requirements using the if & and formulas together that say the job numbers in cells U157 & B157 must match, as well as the 'hours category' in cell V157 must be 'Night'. If these align then perform the series of operations shown next in the formula (which ends up being a budgeted dollar amount that is based on October '18 hours, wage with expected growth, days in month, and hours reductions for the day before a holiday, holiday, and day after a holiday in 2019 months that have a holiday).

If the two job numbers do not match the formula should always return a 0. At the end of the formula, you will see a second if/and piece that changes the operations being performed if the 'hours category' is 'Day'. Day hours are not affected by holidays so this is the reason for the need for the change in formula.

If I can elaborate better or more clearly let me know. This a for a budget for a janitorial company where we have night and day janitors. Thanks in advance for your help!

 

[etaf]

No attached - as this forum you can not attach anything - you need to link or use a tool to show the spreadsheet here
however, let me try in words to put the formula - and you may see the issue you have

=IF( AND ( U157 = B157 , V157 = "NIGHT") , perform the NIGHT formula , IF ( AND ( U157 = B157 , V157 = "DAY" ) , perform the DAY formula , 0 ) )

 

[jkinnick]

Hi, Thank you I did not realize I wasn't allowed to post an attachment. Here is a Google Drive link to the file I am referring to: https://drive.google.com/file/d/1LVhNzjvIrDX-QqUokppHJDoDLGjulu64/view?usp=sharing

Please let me know if you can figure out where I am going wrong in the formula.

Thanks!

 

[etaf]

remember we do not know the detail and posting such a large spreadsheet - makes it harder for us to get into and spend to much time working out the spreadsheet a small sample would be better
Also

If these align then perform the series of operations shown next in the formula (which ends up being a budgeted dollar amount that is based on October '18 hours, wage with expected growth, days in month, and hours reductions for the day before a holiday, holiday, and day after a holiday in 2019 months that have a holiday).

ANYWAY
here is the formula in AA157

=IF(AND($U157=$B157,$V157="night"),((($W157*$Z157*(1+$AM$12))/23)*AA$2)+IF(AA$3=1,-((($W157*$Z157*(1+$AM$12))/23)*$AS$7)-((($W157*$Z157*(1+$AM$12))/23)*$AT$7)-((($W157*$Z157*(1+$AM$12))/23)*$AU$7),IF(AND($U157=$B157,$V157="DAY"),((($W157*$Z157*(1+$AM$12))/23)*AA$2),0)),((($W157*$Z157*(1+$AM$12))/23)*AA$2))

breaking this OUT
=IF(
AND($U157=$B157,$V157="night"),

This is the TRUE bit - as you have a ,
((($W157*$Z157*(1+$AM$12))/23)*AA$2)

Now you ADD if AA3 =1
+IF(AA$3=1
,
-((($W157*$Z157*(1+$AM$12))/23)*$AS$7)-((($W157*$Z157*(1+$AM$12))/23)*$AT$7)-((($W157*$Z157*(1+$AM$12))/23)*$AU$7),

what if AA3 does not =1 - what result do you want

IF(AND($U157=$B157,$V157="DAY"),
((($W157*$Z157*(1+$AM$12))/23)*AA$2),0)),((($W157*$Z157*(1+$AM$12))/23)*AA$2))

seems a lot missing

Whats the formula needed if

AND($U157=$B157,$V157="night", AA$3 = 1 )

Whats the formula if AA3 does not = 1

then for DAY all you want is
IF(AND($U157=$B157,$V157="DAY"),
then this formula
((($W157*$Z157*(1+$AM$12))/23)*AA$2),0)),((($W157*$Z157*(1+$AM$12))/23)*AA$2))

otherwise 0

 

[jkinnick]

remember we do not know the detail and posting such a large spreadsheet - makes it harder for us to get into and spend to much time working out the spreadsheet a small sample would be better
Also

ANYWAY
here is the formula in AA157

=IF(AND($U157=$B157,$V157="night"),((($W157*$Z157*(1+$AM$12))/23)*AA$2)+IF(AA$3=1,-((($W157*$Z157*(1+$AM$12))/23)*$AS$7)-((($W157*$Z157*(1+$AM$12))/23)*$AT$7)-((($W157*$Z157*(1+$AM$12))/23)*$AU$7),IF(AND($U157=$B157,$V157="DAY"),((($W157*$Z157*(1+$AM$12))/23)*AA$2),0)),((($W157*$Z157*(1+$AM$12))/23)*AA$2))

breaking this OUT
=IF(
AND($U157=$B157,$V157="night"),

This is the TRUE bit - as you have a ,
((($W157*$Z157*(1+$AM$12))/23)*AA$2)

Now you ADD if AA3 =1
+IF(AA$3=1
,
-((($W157*$Z157*(1+$AM$12))/23)*$AS$7)-((($W157*$Z157*(1+$AM$12))/23)*$AT$7)-((($W157*$Z157*(1+$AM$12))/23)*$AU$7),

what if AA3 does not =1 - what result do you want

IF(AND($U157=$B157,$V157="DAY"),
((($W157*$Z157*(1+$AM$12))/23)*AA$2),0)),((($W157*$Z157*(1+$AM$12))/23)*AA$2))

seems a lot missing

Whats the formula needed if

AND($U157=$B157,$V157="night", AA$3 = 1 )

Whats the formula if AA3 does not = 1

then for DAY all you want is
IF(AND($U157=$B157,$V157="DAY"),
then this formula
((($W157*$Z157*(1+$AM$12))/23)*AA$2),0)),((($W157*$Z157*(1+$AM$12))/23)*AA$2))

otherwise 0

Thank you, I think we're getting closer. I have replaced the file with just a few examples so that the file size is much smaller and easier to navigate. To your question, "Whats the formula if AA3 does not = 1", I want the result to be 0. So, basically if AA3 is not 1, I do not want the formula to subtract anything else out relating to utilizing the holiday table in AS3:AU8. A 0 in AA3 would result in this section '+IF(AA$3=1,-((($W157*$Z157*(1+$AM$12))/23)*$AS$7)-((($W157*$Z157*(1+$AM$12))/23)*$AT$7)-((($W157*$Z157*(1+$AM$12))/23)*$AU$7),IF(AND($U157=$B157,$V157="DAY")' equaling 0.

 

[etaf]

so we could simplify a little more

=IF(AND($U157=$B157,$V157="night"),((($W157*$Z157*(1+$AM$12))/23)*AA$2)+IF(AA$3=1,-((($W157*$Z157*(1+$AM$12))/23)*$AS$7)-((($W157*$Z157*(1+$AM$12))/23)*$AT$7)-((($W157*$Z157*(1+$AM$12))/23)*$AU$7),IF(AND($U157=$B157,$V157="DAY"),((($W157*$Z157*(1+$AM$12))/23)*AA$2),0)),((($W157*$Z157*(1+$AM$12))/23)*AA$2))


=IF(AND($U157=$B157,$V157="night", AA$3=1), ((($W157*$Z157*(1+$AM$12))/23)*AA$2)-((($W157*$Z157*(1+$AM$12))/23)*$AS$7)-((($W157*$Z157*(1+$AM$12))/23)*$AT$7)-((($W157*$Z157*(1+$AM$12))/23)*$AU$7),

So thats the Night and AA3 =1
and if none of these then 0 is the result
so next the DAY formula

,IF(AND($U157=$B157,$V157="DAY"),((($W157*$Z157*(1+$AM$12))/23)*AA$2),0)),((($W157*$Z157*(1+$AM$12))/23)*AA$2))

Now we just need a zero
,0
and thats should be it BUT

=IF(AND($U157=$B157,$V157="night", AA$3=1), ((($W157*$Z157*(1+$AM$12))/23)*AA$2)-((($W157*$Z157*(1+$AM$12))/23)*$AS$7)-((($W157*$Z157*(1+$AM$12))/23)*$AT$7)-((($W157*$Z157*(1+$AM$12))/23)*$AU$7),IF(AND($U157=$B157,$V157="DAY"),((($W157*$Z157*(1+$AM$12))/23)*AA$2),0)),((($W157*$Z157*(1+$AM$12))/23)*AA$2)), 0 ))

Not sure about the brackets as i have just copied . BUT
Whats this formula for
((($W157*$Z157*(1+$AM$12))/23)*AA$2))

can you break out the formula a little more with a logic table

the formula for Night

the formual for NIGHT and AA3 =1

the formula for DAY

if none of these are TRUE then 0
or is there a different formula -

lets see the logic - maybe much easier

 

[jkinnick]

Okay, so in totality here is what the formula is saying,

Part 1: 'IF(AND($U157=$B157,$V157="Night"),((($W157*$Z157*(1+$AM$12))/23)*AA$2)' if the two job numbers match and the hours category is night, then multiply hours excl. OT x payrate w/ growth x 1 + the growth rate, then divide this by 23 (work days in Oct. '18) x work days in Jan. '19. This gives the forecasted labor dollars. [The next step in Part 1.1 reduces this total based on whether there is a holiday in the month or not (1 or 0 in row 3)]

Part 1.1: '+IF(AA$3=1,-((($W157*$Z157*(1+$AM$12))/23)*$AS$7)-((($W157*$Z157*(1+$AM$12))/23)*$AT$7)-((($W157*$Z157*(1+$AM$12))/23)*$AU$7)' If there is a holiday in the month, we want to reduce the dollars calculated in Part 1 above on the day before the holiday, holiday, and day after holiday based on the percentages in the holiday table AS3:AU8. If there is a 1 in row 3, we want to reduce the dollars using this formula, if there is a 0 in row 3 there is no holiday in this month and we want this portion of the formula to result in a 0, as we do not need any additional reduction when there is no holiday in a given month, February for example.

Part 2: 'IF(AND($U157=$B157,$V157="Day"),((($W157*$Z157*(1+$AM$12))/23)*AA$2)' The reason for adding another If(And portion of the formula is because we cannot reduce hours for day shifts even when there is a holiday. So this part is essentially the same logic as Part 1, we just have no need for Part 1.1 when the Hours Category is 'Day'.

If the two job numbers do not match, then the end result of the overall formula should always be 0.

Hope this all makes sense. Looking forward to your response

 

[etaf]

sorry just being sure I understand the logic fully
IF
$U157=$B157 . and $V157="Night" and AA$3=1 you want

((($W157*$Z157*(1+$AM$12))/23)*AA$2) -((($W157*$Z157*(1+$AM$12))/23)*$AS$7)-((($W157*$Z157*(1+$AM$12))/23)*$AT$7)-((($W157*$Z157*(1+$AM$12))/23)*$AU$7)

IF AA3 does NOT =1
you want

((($W157*$Z157*(1+$AM$12))/23)*AA$2)

IF
$U157=$B157 . and $V157="=DAY" you want

((($W157*$Z157*(1+$AM$12))/23)*AA$2)

you dont care about AA3

Otherwise anything else is 0

is that correct ?

 

[jkinnick]

Yes, this is all correct what you've written out. Just to be clear, if those two job numbers do not match, [$U157=$B157], the end result should always be 0.

 

[etaf]

=IF(AND($U157=$B157,$V157="Night",AA$3=1),((($W157*$Z157*(1+$AM$12))/23)*AA$2)-((($W157*$Z157*(1+$AM$12))/23)*$AS$7)-((($W157*$Z157*(1+$AM$12))/23)*$AT$7)-((($W157*$Z157*(1+$AM$12))/23)*$AU$7),IF(AND($U157=$B157,$V157="Night",AA$3<>1),((($W157*$Z157*(1+$AM$12))/23)*AA$2),IF(AND($U157=$B157,$V157="DAY"),((($W157*$Z157*(1+$AM$12))/23)*AA$2),0)))

does that work ?