Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting prob
hiya guys and gals I have a big problem. I need to make a spreadsheet for work. i'll try to explain as best as i can. Cells A6 to A64 contain the numbers 1 to 52, these represent the weeks of the year. In cells B6 to B64 any hours that a staff member has been off will be recorded. Cell C1 contains a member of staffs contracted hours. Cell D1 contains the current week number. Cell E1 need to count how many hours (from B6 to B64) were off, but only the last 26 weeks. For example if the current week is 30 it would need to calculate from wk 4 to wk 30 but if the current wk is week 5 it would need to add week 1 to wk 5 and wk 31 to wk 52. I have tried loads of ways of doing this and just end up confusing myself to the point of suicide! I need HELP!!!!!!!!!!!!!! Please to the sake of my blood pressure, help me find a solution! Regards James -- superkopite ------------------------------------------------------------------------ superkopite's Profile: http://www.excelforum.com/member.php...o&userid=29496 View this thread: http://www.excelforum.com/showthread...hreadid=503604 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting prob
Hi, I suggest the following method... btw, the numbers 1 to 52 will go in to cells A6 to A57. And if the current week is 5, then you want the hours from weeks 32 (not 31) to 52, and 1 to 5. I suggest using range names, to make the formula easier to understand. So name the week numbers "weeks" Name the cell with the current week "current" (To name a cell or range, select it then type the name in to the box on the left of the formula bar which has the cell ref in it. Then in cell E1 type: =SUMPRODUCT(((weeks(current-26))*(weeks<=current))*(weeks+26current)*(B6:B57) ) (copy and pasting this text would be easier) This is an undocumented use for Sumproduct, and is very useful for multiple criteria. It works roughly as follows: If: (The week number is greater than (current week less 26) and The week number is less than or equal to the current week ) and The week number + 26 is greater than the current week ) then add the corresponding value in column B, rows 6 to 57. Hope it works for you... Regards Mike -- Mikeopolo ------------------------------------------------------------------------ Mikeopolo's Profile: http://www.excelforum.com/member.php...o&userid=18570 View this thread: http://www.excelforum.com/showthread...hreadid=503604 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting prob
Thanks that is alot simpler to understand than what i was trying. However this formula doesn't work if the week number is less than 26. But i think the answer is close. Cheers James -- superkopite ------------------------------------------------------------------------ superkopite's Profile: http://www.excelforum.com/member.php...o&userid=29496 View this thread: http://www.excelforum.com/showthread...hreadid=503604 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting prob
Hi James Sorry about that, I didn't notice in later testing. The sumproduct conditions are additive, ie only AND. You need something slightly different. Try pasting this formula into C6, then copy/paste in C7 thru C57: =IF(OR(AND(A6(current-26),A6<=current),A6(current+26)),B6,0) Then in E1 sum this range of cells. Not as elegant as the single cell formula, but correct; of course it assumes that this range is available. I was using this method to cross-check my sumproduct formula. Regards Mike -- Mikeopolo ------------------------------------------------------------------------ Mikeopolo's Profile: http://www.excelforum.com/member.php...o&userid=18570 View this thread: http://www.excelforum.com/showthread...hreadid=503604 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting prob
I bow down to your greatness. -- superkopite ------------------------------------------------------------------------ superkopite's Profile: http://www.excelforum.com/member.php...o&userid=29496 View this thread: http://www.excelforum.com/showthread...hreadid=503604 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting prob
Glad to be of help! Mike -- Mikeopolo ------------------------------------------------------------------------ Mikeopolo's Profile: http://www.excelforum.com/member.php...o&userid=18570 View this thread: http://www.excelforum.com/showthread...hreadid=503604 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting prob
You could use this formula in E1 to calculate with one formula =SUMPRODUCT(--(A6:A57+(A6:A57<=D1)*52=D1+26),B6:B57) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=503604 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting prob
Mike,
You can create an OR condition in SUMPRODUCT with + =SUMPRODUCT(((weeks<=current)*(weekscurrent-26))+((weekscurrent+26)*(weeks current)),B6:B57) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Mikeopolo" wrote in message ... Hi James Sorry about that, I didn't notice in later testing. The sumproduct conditions are additive, ie only AND. You need something slightly different. Try pasting this formula into C6, then copy/paste in C7 thru C57: =IF(OR(AND(A6(current-26),A6<=current),A6(current+26)),B6,0) Then in E1 sum this range of cells. Not as elegant as the single cell formula, but correct; of course it assumes that this range is available. I was using this method to cross-check my sumproduct formula. Regards Mike -- Mikeopolo ------------------------------------------------------------------------ Mikeopolo's Profile: http://www.excelforum.com/member.php...o&userid=18570 View this thread: http://www.excelforum.com/showthread...hreadid=503604 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting prob
Thanks Daddy and Bob I tried those solutions too and all three work! Gosh i went from not being able to work out a formula to being spoilt for choice!!! in the end i went with Mikeopolo my simple mind could just about conprehend this one!!!! thanks alot James -- superkopite ------------------------------------------------------------------------ superkopite's Profile: http://www.excelforum.com/member.php...o&userid=29496 View this thread: http://www.excelforum.com/showthread...hreadid=503604 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting prob
Thank you both for your postings, I learn more each day. I'd appreciate your explanations for the conditions you have used. Could you also explain the -- ? I know only that it forces the treatment of numbers as numbers, and that without it (in this case) the formula returns zero. Regards Mike -- Mikeopolo ------------------------------------------------------------------------ Mikeopolo's Profile: http://www.excelforum.com/member.php...o&userid=18570 View this thread: http://www.excelforum.com/showthread...hreadid=503604 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting prob
In the formula I posted =SUMPRODUCT(--(A6:A57+(A6:A57<=D1)*52=D1+26),B6:B57) the part (A6:A57+(A6:A57<=D1)*52=D1+26) returns an array of TRUE/FALSE values something like {TRUE;FALSE;TRUE....etc..} the -- coerces this array to 1/0 values, e.g. {1,0,1....etc} SUMPRODUCT then multiplies the corresponding value in each array and adds the results so you get (1*B6)+(0*B7)+(1*B8)....etc. the effect is to add the value of B in every row where the criteria is fulfilled. Of course you don't need to use --, you could also use +0 or *1, in short a mathematical operation that doesn't change the value. -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=503604 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting prob
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Mikeopolo" wrote in message ... Thank you both for your postings, I learn more each day. I'd appreciate your explanations for the conditions you have used. Could you also explain the -- ? I know only that it forces the treatment of numbers as numbers, and that without it (in this case) the formula returns zero. Regards Mike -- Mikeopolo ------------------------------------------------------------------------ Mikeopolo's Profile: http://www.excelforum.com/member.php...o&userid=18570 View this thread: http://www.excelforum.com/showthread...hreadid=503604 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting prob
Hi James Just for my own interest, i corrected my original sumproduct formula following advice from the other posters: =SUMPRODUCT(((weekscurrent-26)*(weeks<=current)+(weekscurrent+26))*(B6:B57)) I've put a bracket around the whole set of conditions, replaced one * with + (meaning OR), and corrected an error in the 3rd condition. This should now work. I thought the first two conditions should be bracketed (they are AND'd), but the * symbol looks like it has a higher priority than the + symbol, so brackets are not required. Regards Mike -- Mikeopolo ------------------------------------------------------------------------ Mikeopolo's Profile: http://www.excelforum.com/member.php...o&userid=18570 View this thread: http://www.excelforum.com/showthread...hreadid=503604 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting prob
Mike
BODMAS Brackets, Order, Division, Multiplication, Addition, Subtraction. Learnt that as a babe <G -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Mikeopolo" wrote in message ... Hi James Just for my own interest, i corrected my original sumproduct formula following advice from the other posters: =SUMPRODUCT(((weekscurrent-26)*(weeks<=current)+(weekscurrent+26))*(B6:B57 )) I've put a bracket around the whole set of conditions, replaced one * with + (meaning OR), and corrected an error in the 3rd condition. This should now work. I thought the first two conditions should be bracketed (they are AND'd), but the * symbol looks like it has a higher priority than the + symbol, so brackets are not required. Regards Mike -- Mikeopolo ------------------------------------------------------------------------ Mikeopolo's Profile: http://www.excelforum.com/member.php...o&userid=18570 View this thread: http://www.excelforum.com/showthread...hreadid=503604 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional counting with Excel | Excel Worksheet Functions | |||
counting rows with same values for multiple values | New Users to Excel | |||
Counting rows, then counting values. | Excel Discussion (Misc queries) | |||
Counting names in a column but counting duplicate names once | Excel Discussion (Misc queries) | |||
Counting... | Excel Worksheet Functions |