I have a table for each person that's periodically updated with essentially 3
columns: A = week ending dates, B= work hours, and C= travel hours. For any
given week, there may be hours logged under columns B and C, OR B or C, OR
neither. I need to calculate the average weekly hours for each person. I
think I need some sort of formula that combines COUNTIF and Blanks but I
can't seem to figure it out. I don't want to count a week twice when they
log both types of hours, and since they could log either type of hours and
not always one or the other, I think this is my problem spot. Hope someone
can help.
--
Thanks

v!v wrote:
I have a table for each person that's periodically updated with essentially 3
columns: A = week ending dates, B= work hours, and C= travel hours. For any
given week, there may be hours logged under columns B and C, OR B or C, OR
neither. I need to calculate the average weekly hours for each person. I
think I need some sort of formula that combines COUNTIF and Blanks but I
can't seem to figure it out. I don't want to count a week twice when they
log both types of hours, and since they could log either type of hours and
not always one or the other, I think this is my problem spot. Hope someone
can help.

Hi,

Could you show us a dozen rows of sample data? I think I understand your
problem but want to be sure.

Work hrs Travel hrs
7/31/08 42
8/2/08 25 18
8/9/08 32
8/16/08
8/23/08 15 15
8/30/08 18
Subtotal 114.00 51.00
# wks with hrs 4.00 3.00
Average weekly hrs 28.50 17.00
Total hrs (all) 165.00
# wks with hrs Formula? for this cell The answer should be 5 but how do I
get it?
Average overall weekly hrs then becomes a simple division with the total hrs
divided by what the # of wks with hours turns out to be. I'm guessing this
is one of the formulas that nested?


--
Thanks


"smartin" wrote:

v!v wrote:
I have a table for each person that's periodically updated with essentially 3
columns: A = week ending dates, B= work hours, and C= travel hours. For any
given week, there may be hours logged under columns B and C, OR B or C, OR
neither. I need to calculate the average weekly hours for each person. I
think I need some sort of formula that combines COUNTIF and Blanks but I
can't seem to figure it out. I don't want to count a week twice when they
log both types of hours, and since they could log either type of hours and
not always one or the other, I think this is my problem spot. Hope someone
can help.

Hi,

Could you show us a dozen rows of sample data? I think I understand your
problem but want to be sure.

v!v wrote:
Work hrs Travel hrs
7/31/08 42
8/2/08 25 18
8/9/08 32
8/16/08
8/23/08 15 15
8/30/08 18
Subtotal 114.00 51.00
# wks with hrs 4.00 3.00
Average weekly hrs 28.50 17.00
Total hrs (all) 165.00
# wks with hrs Formula? for this cell The answer should be 5 but how do I
get it?
Average overall weekly hrs then becomes a simple division with the total hrs
divided by what the # of wks with hours turns out to be. I'm guessing this
is one of the formulas that nested?



Here are a couple ideas for you.

The first is a one-step formula to get "# of weeks with hours". It is an
array formula, so you have to press Ctrl+Shift+Enter to commit it. (If
you forget the special keypress, the incorrect result 1 will display
using your sample data.)

=SUM(--(B2:B7+C2:C70))


An alternative solution: This two-step approach uses a helper column. I
placed the following in column E. These are regular worksheet functions
(not arrays):

=--(B2+C20)
(fill down)

At the bottom of the column, the result is given by
=SUM(E2:E7)

Note the second solution is doing the exact same thing as the array
formula, but in "stop action".

smartin wrote:
v!v wrote:
Work hrs Travel hrs
7/31/08 42
8/2/08 25 18
8/9/08 32
8/16/08
8/23/08 15 15
8/30/08 18
Subtotal 114.00 51.00
# wks with hrs 4.00 3.00
Average weekly hrs 28.50 17.00
Total hrs (all) 165.00
# wks with hrs Formula? for this cell The answer should be 5
but how do I get it?
Average overall weekly hrs then becomes a simple division with the
total hrs divided by what the # of wks with hours turns out to be.
I'm guessing this is one of the formulas that nested?



Here are a couple ideas for you.

The first is a one-step formula to get "# of weeks with hours". It is an
array formula, so you have to press Ctrl+Shift+Enter to commit it. (If
you forget the special keypress, the incorrect result 1 will display
using your sample data.)

=SUM(--(B2:B7+C2:C70))


An alternative solution: This two-step approach uses a helper column. I
placed the following in column E. These are regular worksheet functions
(not arrays):

=--(B2+C20)
(fill down)

At the bottom of the column, the result is given by
=SUM(E2:E7)

Note the second solution is doing the exact same thing as the array
formula, but in "stop action".

Sorry, I should have added one last version of the alternative solution,
which uses notation more folks will be familiar with. Again, in column E:

=B2+C20
(fill down)
and
=COUNTIF(E2:E7,TRUE)

Thanks Smartin! I like the one step formula. - that will the simplest to
incorporate. But let me make sure I understand what the steps are & why they
are done: 1) Since we're dealing with a couple of columns of data that our
formula has to involve, we're using an array function as indicated by the
special keystroke (ctrl/shift/enter) at the end. 2) If either col B or col C
on the same row have a value in them, the sum (indicated by the +) would be
greater than 0, therefore the (B2:B7+C2:C7 0) since the addition of the two
cells is done first and then compared to 0. 3) The next couple parts are
unfamiliar to me (I've never done arrays before if that's a good excuse.) -
from your alternative solution, do the two minus signs in front of part 2
(adding the rows & comparing to 0) automatically mean do this for each row
within the data set? And then for part 4) I don't get that if you're
calculating a COUNT, why are you saying SUM at the start of the formula? I
know that it works but I don't "get" the last couple steps.
Thanks


"smartin" wrote:

smartin wrote:
v!v wrote:
Work hrs Travel hrs
7/31/08 42
8/2/08 25 18
8/9/08 32
8/16/08
8/23/08 15 15
8/30/08 18
Subtotal 114.00 51.00
# wks with hrs 4.00 3.00
Average weekly hrs 28.50 17.00
Total hrs (all) 165.00
# wks with hrs Formula? for this cell The answer should be 5
but how do I get it?
Average overall weekly hrs then becomes a simple division with the
total hrs divided by what the # of wks with hours turns out to be.
I'm guessing this is one of the formulas that nested?



Here are a couple ideas for you.

The first is a one-step formula to get "# of weeks with hours". It is an
array formula, so you have to press Ctrl+Shift+Enter to commit it. (If
you forget the special keypress, the incorrect result 1 will display
using your sample data.)

=SUM(--(B2:B7+C2:C70))


An alternative solution: This two-step approach uses a helper column. I
placed the following in column E. These are regular worksheet functions
(not arrays):

=--(B2+C20)
(fill down)

At the bottom of the column, the result is given by
=SUM(E2:E7)

Note the second solution is doing the exact same thing as the array
formula, but in "stop action".

Sorry, I should have added one last version of the alternative solution,
which uses notation more folks will be familiar with. Again, in column E:

=B2+C20
(fill down)
and
=COUNTIF(E2:E7,TRUE)

v!v wrote:
Thanks Smartin! I like the one step formula. - that will the simplest to
incorporate. But let me make sure I understand what the steps are & why they
are done: 1) Since we're dealing with a couple of columns of data that our
formula has to involve, we're using an array function as indicated by the
special keystroke (ctrl/shift/enter) at the end. 2) If either col B or col C
on the same row have a value in them, the sum (indicated by the +) would be
greater than 0, therefore the (B2:B7+C2:C7 0) since the addition of the two
cells is done first and then compared to 0. 3) The next couple parts are
unfamiliar to me (I've never done arrays before if that's a good excuse.) -
from your alternative solution, do the two minus signs in front of part 2
(adding the rows & comparing to 0) automatically mean do this for each row
within the data set? And then for part 4) I don't get that if you're
calculating a COUNT, why are you saying SUM at the start of the formula? I
know that it works but I don't "get" the last couple steps.
Thanks


Hi again vv, I am glad you are catching on!

1) correct
2) correct again
Good job grasping the above. I think that's that hardest part to
understand, really.

3) The array returns a gob of TRUE and FALSE values. TRUE if the sum of
B and C is nonzero, FALSE otherwise. You can see this happening in the
second alternate solution. The double -- is a handy way to convert TRUE
to 1 and FALSE to 0. This is not so much an array trick as a way to
convert boolean values to something we can do simple math with*. This
leads us to...
4) Right. Now that our TRUE/FALSE array has been converted to 1s and 0s,
we just need to SUM the 1s, since a 1 now essentially means B or C is
nonzero.

* I learned a lot about this idea from this site. It's well worth
checking out, but seems to be down at the moment:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Ohhhhhhhh! I get it! Thanks sooo much. Your explanation made all the
difference - I had read a lot of the other posts on COUNTs but the "higher"
math threw me - (I don't think they had that level when I was in school.) I
can dazzle them at work once again. Glad I found this site as it's hard to
look up something that's alittle beyond the basic Excel intermediate level if
you don't know what to call it. I'll check out the website at work (dealing
with dialup here at home and maybe by then, it will be available again.)
Thanks again - guess I'm done here.
--
Thanks


"smartin" wrote:

v!v wrote:
Thanks Smartin! I like the one step formula. - that will the simplest to
incorporate. But let me make sure I understand what the steps are & why they
are done: 1) Since we're dealing with a couple of columns of data that our
formula has to involve, we're using an array function as indicated by the
special keystroke (ctrl/shift/enter) at the end. 2) If either col B or col C
on the same row have a value in them, the sum (indicated by the +) would be
greater than 0, therefore the (B2:B7+C2:C7 0) since the addition of the two
cells is done first and then compared to 0. 3) The next couple parts are
unfamiliar to me (I've never done arrays before if that's a good excuse.) -
from your alternative solution, do the two minus signs in front of part 2
(adding the rows & comparing to 0) automatically mean do this for each row
within the data set? And then for part 4) I don't get that if you're
calculating a COUNT, why are you saying SUM at the start of the formula? I
know that it works but I don't "get" the last couple steps.
Thanks


Hi again vv, I am glad you are catching on!

1) correct
2) correct again
Good job grasping the above. I think that's that hardest part to
understand, really.

3) The array returns a gob of TRUE and FALSE values. TRUE if the sum of
B and C is nonzero, FALSE otherwise. You can see this happening in the
second alternate solution. The double -- is a handy way to convert TRUE
to 1 and FALSE to 0. This is not so much an array trick as a way to
convert boolean values to something we can do simple math with*. This
leads us to...
4) Right. Now that our TRUE/FALSE array has been converted to 1s and 0s,
we just need to SUM the 1s, since a 1 now essentially means B or C is
nonzero.

* I learned a lot about this idea from this site. It's well worth
checking out, but seems to be down at the moment:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html