#1
|
|||
|
|||
Calculating Weekend Allowance
Hi all,
I'm stuck with a calculation on weekend allowance. Workers would be paid a set allowance if they have worked on Sundays. I have a spreadsheet set up to calculate the payment for an entire month. The dates lie on D2:AH2 and the number of hours worked per person are directly below, (e.g. D6:AH6,D7:AH7, etc). The number of hours worked would show 'A' if the person if absent. To calculate the number of Sundays worked, I have tried SUMPRODUCT(--(D6:AH6>0),--(WEEKDAY($D$2:$AH$2)=1)), but it seems to give me 4 every time. Any ideas? |
#2
|
|||
|
|||
Can you upload an example file, with just four personnel?
If you can, I will see what I can do! |
#3
|
|||
|
|||
Sure thing.
|
#4
|
|||
|
|||
Separate Requirement Into Separate Weeks?
Good Morning,
Well, it took some thinking about because I do not know how to deal with arrays. Attached you will find a file, that although not an elegant solution, is a least a solution to your problem. What I have done is created a range for Week_01, then using a horizontal look up, looked down to see whether the Sunday cell was an 'A' or zero and giving a result of 0, or 1. You will need to do the same for the remaining weeks. Hope this helps. |
#5
|
|||
|
|||
Usually. it is a bad idea to mix in a table numbers and text
Maybe you can get rid of the "A" in this table, and your formula will work, with a little modification your formula evaluates the range , and counts how many cells are >0 (and no mattes what values you have in D9:AH9... =SUMPRODUCT(--(D9:AH9>0);--(WEEKDAY($D$3:$AH$3)=1)) that is why, you get 4 as the result, for excel the "A" cells are >0 , and the result means that there are 4 cells in the sum range for sunday... try instead of "A" leave the cells blank (and make your conditional formatting formula to mark the blanks with red), and remove >0 from formula, you will get the number of hours worked in sundays... try this: =SUMPRODUCT(--(D7:AH7);--(WEEKDAY($D$3:$AH$3)=1)) |
#6
|
|||
|
|||
@nutrastat: That works!
And yeah - The letters are actually causing the troubles with the formula. I'll see if I can change it without breaking something. Thanks all! |
#7
|
|||
|
|||
well, i guess you can mark your thread as solved...
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Calculating dates | Daria11 | Word VBA | 1 | 06-08-2011 06:54 PM |
calculating points and division in excel | edward masoya | Excel | 1 | 05-09-2011 06:31 AM |
Calculating age at date | jamierbooth | Excel | 2 | 02-22-2011 03:35 AM |
Calculating Form Fields in Microsoft Word | wubba80 | Word | 1 | 06-25-2010 12:42 AM |
Code to auto-forward weekend messages | Whang | Outlook | 0 | 06-16-2010 02:18 PM |