Counting prob

[superkopite]

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

 

[Mikeopolo]

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+26>current)*(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

 

[superkopite]

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

 

[Mikeopolo]

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 i
assumes that this range is available.

I was using this method to cross-check my sumproduct formula.

Regards
Mik

 

[superkopite]

I bow down to your greatness.

 

[Mikeopolo]

Glad to be of help!

Mike

 

[daddylonglegs]

You could use this formula in E1 to calculate with one formula

=SUMPRODUCT(--(A6:A57+(A6:A57<=D1)*52>=D1+26),B6:B57)

 

[Bob Phillips]

Mike,

You can create an OR condition in SUMPRODUCT with +

current)),B6:B57)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[superkopite]

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

 

[Mikeopolo]

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

 

[daddylonglegs]

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.

 

[Bob Phillips]

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]

Hi James

Just for my own interest, i corrected my original sumproduct formula
following advice from the other posters:

=SUMPRODUCT(((weeks>current-26)*(weeks<=current)+(weeks>current+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

 

[Bob Phillips]

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)