Average Question

[JimS]

In my spreadsheet there are blank rows, which indicates a new day. It
might look like this: I have the blank rows, for neatness, and also
so I can have day totals in a different column. (The column here off
to the right.)

Bob Alaska $14
Bob Alaska $93
Bob Juno $23
Tom Alaska -$7 $123

Bob Seattle $44
Bob Seattle $93
Bob Portland -$144
Bob Alaska -$23 -$30

Tom Juno -$93
Tom Juno $35
Tom SanF $104
Bob Alaska $88 $134

Bob Alaska -$44 -$44

Tom Alaska $53 $53

Five separate days. Bob worked on four of those days. How can I
average Bob's daily results for Alaska? In other words, what is Bob's
daily average for Alaska?

He netted $128 in Alaska over the course of 5 days, so his average
dollars made in Alaska, per day, is $25.60.

 

[joeu2004]

How can I average Bob's daily results for Alaska?

One way, entering your data in the range A122 :

=SUMPRODUCT((A1:A22="Bob")*(B1:B22="Alaska"),C1:C22)
/ SUMPRODUCT((A1:A22="Bob")*(B1:B22="Alaska"))

The numerator totals all of C1:C22 where column A has "Bob" and column
B has "Alaska".

The denominator counts the number of times column A has "Bob" and
column B has "Alaska".

PS: The formula could also be written (among others):

=SUMPRODUCT(--(A1:A22="Bob"),--(B1:B22="Alaska"),C1:C22)
/ SUMPRODUCT(--(A1:A22="Bob"),--(B1:B22="Alaska"))


----- original posting -----

 

[Ron Rosenfeld]

In my spreadsheet there are blank rows, which indicates a new day. It
might look like this: I have the blank rows, for neatness, and also
so I can have day totals in a different column. (The column here off
to the right.)

Bob Alaska $14
Bob Alaska $93
Bob Juno $23
Tom Alaska -$7 $123

Bob Seattle $44
Bob Seattle $93
Bob Portland -$144
Bob Alaska -$23 -$30

Tom Juno -$93
Tom Juno $35
Tom SanF $104
Bob Alaska $88 $134

Bob Alaska -$44 -$44

Tom Alaska $53 $53

Five separate days. Bob worked on four of those days. How can I
average Bob's daily results for Alaska? In other words, what is Bob's
daily average for Alaska?

He netted $128 in Alaska over the course of 5 days, so his average
dollars made in Alaska, per day, is $25.60.

Another approach would be to use a Pivot Table.

Enter a header row such as
Name Location Amount

Then Insert/Pivot Table (Excel 2007) or the equivalent in earlier versions.

Drag Names to Row area; Locations to Column Area; Amt to Data area.

Set the Amt to be summarized by Average
Click on the Row labels to deselect <blank>

You can wind up with something like this:

(Averages) Locations

Names Alaska Juno Portland SanF Seattle
Bob $25.60 $23.00 -$144.00 $68.50
Tom $23.00 -$29.00 $104.00


--ron

 

[RagDyer]

Since you don't have a date column, will you always assume that there will
be a 5 day week?

If so, then try this:

=Sumproduct((A1:A25="Bob")*(B1:B25="Alaska")*C1:C25)/5

You might also use specific cells in your formula, therefore allowing
changes in people and/or locations to be more easily accomplished.

Say D1 for person, and D2 for locale ... then try:

=Sumproduct((A1:A25=D1)*(B1:B25=D2)*C1:C25)/5

 

[JimS]

Thank you, but I made one mistake. There are five days on the sheet,
but Bob only worked 4. He made $128/4, and the answer should be $32,
not $25.6.

A minor change has to be made to account for this. It's after the
divisor, I'm sure...can't put my finger on it.

Thanks again, you've been very helpful, Joeu.

 

[RagDyer]

If you're example is correct, where you have the possibility of more then
one entry per day, I think you'll need to add a date column in order to
return number of days worked.

 

[joeu2004]

Thanks again, you've been very helpful, Joeu.

In concept, perhaps. But I realized I made a number of mistakes.

First, you wanted the divisor to be the number of days, not the number
of times Bob had results for Alaska. The number of days is SUMPRODUCT
(--ISBLANK(A1:A22))/2+1. That assumes there are 2 blank rows between
each day, as in your example.

But then you write:

Bob only worked 4. He made $128/4

That's a little harder -- maybe a lot harder. Off-hand, I don't have
a general solution.


----- original posting -----

 

[JimS]

Actually, I do have a date column, but there is a wrinkle. I only
insert the date once for each day so that it looks like this: (And
although it looks like there are two rows between days, on the
spreadsheet there is only one.)

Date Name Location Result Day Total

1/9/09 Bob Alaska $14
Bob Alaska $93
Bob Juno $23
Tom Alaska -$7 $123

1/10/09 Bob Seattle $44
Bob Seattle $93
Bob Portland -$144
Bob Alaska -$23 -$30

1/12/09 Tom Juno -$93
Tom Juno $35
Tom SanF $104
Bob Alaska $88 $134

1/13/09 Bob Alaska -$44 -$44

1/16/09 Tom Alaska $53 $53

 

[Dave Peterson]

I would remove the row separator and the day total and add the dates to each
row.

Debra Dalgleish shares some techniques to fill those empty cells here:

http://contextures.com/xlDataEntry02.html
http://www.contextures.com/xlVideos01.html#FillBlanks

But if you don't like the look of that, you could use Conditional formatting to
hide the duplicate categories.

Debra shows how here:
http://contextures.com/xlCondFormat03.html#Duplicate

=======
After you do that, you could use a pivottable or even data|subtotals to get
summary reports.

 

[JimS]

Thanks, Dave I'll study these.

I would remove the row separator and the day total and add the dates to each
row.

Debra Dalgleish shares some techniques to fill those empty cells here:

http://contextures.com/xlDataEntry02.html
http://www.contextures.com/xlVideos01.html#FillBlanks

But if you don't like the look of that, you could use Conditional formatting to
hide the duplicate categories.

Debra shows how here:
http://contextures.com/xlCondFormat03.html#Duplicate

=======
After you do that, you could use a pivottable or even data|subtotals to get
summary reports.

 

[JimS]

I would remove the row separator and the day total and add the dates to each
row.

Assuming I did this, I have over 600 rows of data, is there an easy
way to remove all of those blank rows?

 

[RagDyeR]

This is convoluted, but it allows your datalist to display exactly as it is
now.

Follow Dave's links to Debra's site and fill in the empty rows with dates,
*including* the empty rows between days.

Change everything to values with Paste Special.
Follow the links and hide the duplicate dates using Conditional Formatting.

Using your example as a model,
Enter Name to calc in F1,
Enter Location to calc in F2,
And try this *array* formula:

=SUMPRODUCT((B2:B19=F1)*(C2:C19=F2)*D219)/COUNT(1/FREQUENCY(IF((B2:B19=F1)*(C2:C19=F2),MATCH(A2:A19,A2:A19,0)),ROW(1:18)))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

Adjust the ranges to your datalist, and change the Row() function at the end
to denote total rows in the range, i.e.,Row(1:600).

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I would remove the row separator and the day total and add the dates to
each
row.

Assuming I did this, I have over 600 rows of data, is there an easy
way to remove all of those blank rows?

 

[Dave Peterson]

You could sort the data by the date and name. Then the blank rows should be
together. If they sort to the top, it'll be easy to delete them.

If they sort to the bottom, you can just ignore them.

You could also apply a data|filter|autofilter to a column that always has data
if the row is used. Then shoe the visible rows and delete those.

You may want to select the visible rows
hit f5 (or ctrl-g)
Special|visible cells only
then delete the rows.

 

[RagDyer]

Since my first suggestion used Sumproduct to eliminate an array entry, I
just copied it over while working on the revised formula.

However, since the second part of the new formula needs to be *array*
entered anyway, we might as well drop the Sumproduct and just use Sum:

=SUM(((B2:B19=F1)*(C2:C19=F2)*D219)/COUNT(1/FREQUENCY(IF((B2:B19=F1)*(C2:C19=F2),MATCH(A2:A19,A2:A19,0)),ROW(1:18))))