I apparently am still not explaining very well. I'll try again.


A B C
________________ ___
1 B 1 5306 |
2 B 1 5306 |
3 B 1 5306 |
4 B 1 5306 |
5 B 1 5306 |---- This area would contain for instance
commodity prices for the month of March.
6 B 1 5306 |
7 B 1 5306 |
8 B 1 5306 |
9 B 2 5312 |
10 B 1 5312 __|
11 Average ----Here would be the average price for March.
12 B 1 5306 |
13 B 1 5306 |
14 B 1 5306 |
15 B 1 5306 |
16 B 1 5306 |---- This area would contain for instance the
commodity prices for the month of July.
17 B 1 5306 |
18 B 1 5306 |
19 B 1 5306 |
20 B 2 5312 |
21 B 1 5312 __ |
22 Average ---- Here would be the average price for
July.

Again consider that this is only an example and the number of rows will be
many for each month. And the end of each segment is not set at a certain row.

"chrisnsmith" wrote:

I apologize for being misleading in my posting, but it was meant as only an
example.
The actual worksheet will be many rows long with an empty row occassionally
to delieniate between commodity months. The code should be pasted in the
empty row to average the prices for that commodity month or be able to
determine the empty row automatically.


"Alojz" wrote:

I did some maths and made my formula shorter:

=ROUND(2*FLOOR(SUM((-FLOOR($C$1:$C$10,10)/40+($C$1:$C$10)/8)*$B$1:$B$10)/SUM($B$1:$B$10),1)+SUM((-FLOOR($C$1:$C$10,10)/40+($C$1:$C$10)/8)*$B$1:$B$10)/SUM($B$1:$B$10)*8,0)

"Alojz" wrote:

may be something shorter exists, but this one works, too (I guess u need
weighted average with weights in col B):
=ROUND(FLOOR(SUM(B1:B10*FLOOR(C1:C10,10)/10+(C1:C10-FLOOR(C1:C10,10))/8)/SUM(B1:B10),10)*10+(SUM(B1:B10*FLOOR(C1:C10,10)/10+(C1:C10-FLOOR(C1:C10,10))/8)/SUM(B1:B10)-FLOOR(SUM(B1:B10*FLOOR(C1:C10,10)/10+(C1:C10-FLOOR(C1:C10,10))/8)/SUM(B1:B10),10))*8,0)
press ctrl+shift+enter (array formula)

"chrisnsmith" wrote:

I forgot to post a view of my worksheet. Maybe this will give you a better
idea of what I need.

A B C
________________
1 B 1 5306
2 B 1 5306
3 B 1 5306
4 B 1 5306
5 B 1 5306
6 B 1 5306
7 B 1 5306
8 B 1 5306
9 B 2 5312
10 B 1 5312

The last digit in column C represents eighths of a cent.
"Sheeloo" wrote:

Try in B1
=AVERAGE(ROUNDDOWN(A1:A10,0))
After typing or pasting the above press CTRL-SHIFT-ENTER instead of ENTER.
This will give you the average of integer part.

Assuming the above is in B1, then put this in C1
=AVERAGE(A1:A10) - B1
to get the average of fraction part.

Change 10 to the last row in your data...

"chrisnsmith" wrote:

Column three is a list of commodity prices that are quoted in eighths. The
far right number represents the 1/8 cent quote. Line one would be 560 and
6/8, line nine would be 531 and 2/8 and so on.
What I need to do is average the whole numbers in column C and average the
1/8 separately.

Can someone help?