Any Excel gurus want to take a stab at this?

[armophob]

I tried posting in an Excel site and got blocked due to my 0 post count. So I return here to my favorite place to ask for advice.

Ok, I am building a time/schedule excel file to keep track of my hours and double check the math on our payroll at work. Long story short, they changed our payroll system and there are constant errors to keep up with.

Here are the parameters

1.sun-sat are divided into day and night hours giving 14 boxes B4 thru O4

2.if a box in row 6 is marked with a "y" below a specific day, then that indicates that day gets paid for "on call".

3.When the box in row 6 is blank then all of the following should be ignored

4. If that day has 0 hours when adding the night and day box, then the "on call" pay is 3.75 times the pay rate in box S5.

5. If that day has more than 0 hours when adding the day and night box, then the "on call" pay is 1.5 times the pay rate in box S5.

6.The sum of these days with these conditions is added in box Q6.

I have successfully accomplished the following formula.
My first question is can this be shortened or condensed?

=SUM((IF(AND((B6="y"),(B4+C4>0)),(S5*1.5),0))+(IF(AND((B6="y"),(B4+C4=0)),(S5*3.75),0))+(IF(AND((D6="y"),(D4+E4>0)),(S5*1.5),0))+(IF(AND((D6="y"),(D4+E4=0)),(S5*3.75),0))+(IF(AND((F6="y"),(F4+G4>0)),(S5*1.5),0))+(IF(AND((F6="y"),(F4+G4=0)),(S5*3.75),0))+(IF(AND((H6="y"),(H4+I4>0)),(S5*1.5),0))+(IF(AND((H6="y"),(H4+I4=0)),(S5*3.75),0))+(IF(AND((J6="y"),(J4+K4>0)),(S5*1.5),0))+(IF(AND((J6="y"),(J4+K4=0)),(S5*3.75),0))+(IF(AND((L6="y"),(L4+M4>0)),(S5*1.5),0))+(IF(AND((L6="y"),(L4+M4=0)),(S5*3.75),0))+(IF(AND((N6="y"),(N4+O4>0)),(S5*1.5),0))+(IF(AND((N6="y"),(N4+O4=0)),(S5*3.75),0)))

Now here are my roadblocks. I am trying to incorporate alpha characters into this.

1.If the box in row 6 is blank, but there is an alpha character in row 4 for each day, ignore the alpha character and continue adding the rest of the days with a "y" in row 6.

2. If there is an "A,B,or C" in either box of the day in row 4, then rule 4 above applies.

3. If there is a "D,E,or F" in either box of the day in row 4, then rule 5 above applies.

4.These are also summed up in box Q6.

Here is a screen shot to help. I changed my rate of pay for privacy. We would not be chatting about this if I made $5 an hour. :)

[jerry downing]

Could you attach an actual spreadsheet if it is not too large. It might make things easier to understand.

[armophob]

Could you attach an actual spreadsheet if it is not too large. It might make things easier to understand.

Never tried a file. Just photos. Here goes.

[BobaBird]

I'm much closer to dabbler than expert, but I would add a row or two to calculate the on-call rate (perhaps in row 9), then the final o/c pay calculation would be sum(B6*B9,D6*D9, ...).

To find the rate, I would use the latest big hammer I wield at everything, VLOOKUP. Build a little table somewhere below or off to the side, then use VLOOKUP in row 7 to check the entries of row 4. Row 8 would set the multiplier based on row 6. Row 9 would make the final determination based on the roadblocks, or could maybe be combined into row 8.

0 (o/c multiplier for row 4 having no alpha)
A 3.75
B 3.75
C 3.75
D 1.5
E 1.5
F 1.5

[armophob]

I'm much closer to dabbler than expert, but I would add a row or two to calculate the on-call rate (perhaps in row 9), then the final o/c pay calculation would be sum(B6*B9,D6*D9, ...).

To find the rate, I would use the latest big hammer I wield at everything, VLOOKUP. Build a little table somewhere below or off to the side, then use VLOOKUP in row 7 to check the entries of row 4. Row 8 would set the multiplier based on row 6. Row 9 would make the final determination based on the roadblocks, or could maybe be combined into row 8.

0 (o/c multiplier for row 4 having no alpha)
A 3.75
B 3.75
C 3.75
D 1.5
E 1.5
F 1.5

Before I can even try it, the first hurdle is that any alpha in row 4 gives #VALUE. So first I need to add to the original formula to let it know how to act with an alpha. Then I can move on to what to to with each. But I will keep it in mind.

[BobaBird]

My thinking was to deal with the row 4 alpha, and maybe also the row 6 o/c indicator, before you get to the final formula which could then be simplified by the removal of the conditions. Then if the A-F multiplier values ever get changed you make one edit to the little table rather than finding every place it's hard-coded into each step of a long calculation.

Would the row 4 alpha code appear by itself in the cell [ A ] in place of the hours, or with the number of hours for that work code [ A 8.0 ] ?

I'm just looking at this as a learning excercise, so I'd also like to hear other approaches to this, especially if it means mine is way off-base. I might be able to play with it at work Friday night.

[armophob]

My thinking was to deal with the row 4 alpha, and maybe also the row 6 o/c indicator, before you get to the final formula which could then be simplified by the removal of the conditions. Then if the A-F multiplier values ever get changed you make one edit to the little table rather than finding every place it's hard-coded into each step of a long calculation.

Would the row 4 alpha code appear by itself in the cell [ A ] in place of the hours, or with the number of hours for that work code [ A 8.0 ] ?

I'm just looking at this as a learning excercise, so I'd also like to hear other approaches to this, especially if it means mine is way off-base. I might be able to play with it at work Friday night.

I appreciate the effort. The alpha is to represent vacation, sick days, and such so they will be in place of numerals.

[Garyunc]

Replace all of the groups adding the 2 cells like this one (B4 + C4>0) with code like this IFERROR(B4+C4,IFERROR(B4+0,C4))

[armophob]

Replace all of the groups adding the 2 cells like this one (B4 + C4>0) with code like this IFERROR(B4+C4,IFERROR(B4+0,C4))

Ok, I did that and this is the result if I did it correct.

=SUM((IF(AND((B6="y"),(IFERROR(B4+C4,IFERROR(B4+0,C4)))),(S5*1.5),0))+ (IF(AND((B6="y"),(B4+C4=0)),(S5*3.75),0))+(IF(AND((D6="y"),(IFERROR(D4+E4,IFERROR(D4+0,E4)))),(S5*1.5),0))+ (IF(AND((D6="y"),(D4+E4=0)),(S5*3.75),0))+(IF(AND((F6="y"),(IFERROR(F4+G4,IFERROR(F4+0,G4)))),(S5*1.5),0))+ (IF(AND((F6="y"),(F4+G4=0)),(S5*3.75),0))+(IF(AND((H6="y"),(IFERROR(H4+I4,IFERROR(H4+0,I4)))),(S5*1.5),0))+ (IF(AND((H6="y"),(H4+I4=0)),(S5*3.75),0))+(IF(AND((J6="y"),(IFERROR(J4+K4,IFERROR(J4+0,K4)))),(S5*1.5),0))+ (IF(AND((J6="y"),(J4+K4=0)),(S5*3.75),0))+(IF(AND((L6="y"),(IFERROR(L4+M4,IFERROR(L4+0,M4)))),(S5*1.5),0))+ (IF(AND((L6="y"),(L4+M4=0)),(S5*3.75),0))+(IF(AND((N6="y"),(IFERROR(N4+O4,IFERROR(N4+0,O4)))),(S5*1.5),0))+ (IF(AND((N6="y"),(N4+O4=0)),(S5*3.75),0)))


That gives me #NAME as a result even with just numerals.

[rebkell]

I can't follow all that, but see if this link might help when you run into #VALUE.

http://www.updatexp.com/excel-value-error.html