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!

 

[jkinnick]

Yes this looks like it works!!! Thank you so much for helping me clean this formula up

 

[etaf]

you are welcome

 

[jkinnick]

Hi,

I have recognized an issue with the formula. It is not utilizing the holiday table in cells AS3:AU8 correctly. When the percentages go up, you should see the labor dollars in the matching holiday months in columns AA:AL go up as well. Right now, they are going down instead of up when the percentages go up. Will you please take a look and help me to solve this?

Thanks

 

[etaf]

ok , so this bit

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.

IF(AA$3=1 AND some sort of calculation to identify if a holiday

hows the holiday shown in AS3 and AU8 - thats 3 columns ???

What formula should it use if there is an holiday
Do we just use PART2

if so - than part1.1
will be IF ( AA$3=1 and some logic to test to see if we are in a month thats also a holiday