Average price.

[chrisnsmith]

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?

 

[Sheeloo]

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]

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]

Just replace 0 with -1 as in the formula below;
=AVERAGE(ROUNDDOWN(A1:A10,-1))

other instructions remain the same

 

[Alojz]

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)

 

[Alojz]

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)

 

[chrisnsmith]

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]

As far as I know, empty rows do not make problem with my formula, months are
different story and have to be incorporated.
Let's assume commodity months are in col E. Number of actual month I want to
calculate the average for is in D14, formula is:

=round(2*floor(sum((-floor($C$1:$C$10,10)*(month($D$1:$D$10)=D14)/40+($C$1:$C$10)/8)*$B$1:$B$10*(month($D$1:$D$10)=D14))/sum(($B$1:$B$10)*(month($D$1:$D$10)=D14)),1)+sum((-floor($C$1:$C$10,10)/40+($C$1:$C$10)/8)*$B$1:$B$10*(month($D$1:$D$10)=D14))/sum(($B$1:$B$10)*(month($D$1:$D$10)=D14))*8,0)

perhaps can be shorter, but it's 2 AM here and I have to sleep as going work
in the morning :-(

 

[Alojz]

I have to correct myself, my assumption for months column is D, not E. In
months col, there should be date, I mean something like MM/DD/YY, e.g. date
as number, not text, month will be extracted by formula. In D14 is the number
of month, e.g. 1 for Jan, 2 for Feb and so on.

 

[chrisnsmith]

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.

 

[Alojz]

Either use my last formula which requests month in col D. Or, use my previous
one, which u can insert directly to c11
=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) hit ctrl+shift+enter

insert it to c22, too. Press F2 and adopt the arrays, which are nice
visible, drag and drop each one from 1:10 to 12:21, expand to 12:22, 12:23 if
needed, to include the whole month, then press ctrl+shift+enter again.

I guess in col B there are amounts (weights), that's why I made weighted
average. If u need simple average, then delete from formula everything what
refers to B cells.

 

[Alojz]

For simple average the formula inserted in C11 should be:

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

Deleting everything what refers to B cells is not sufficient.

 

[chrisnsmith]

I've tried all the code posted in this thread and none seem to work.

 

[Alojz]

well, not really sure what u mean by "none seem to work", my formulas
calculate averages for sure and they are correct, see the sample file I
uploaded to:

http://ash001.sweb.cz/avepx.xls

N.B. all formulas must be array-entered, see averages in col G, I guess u
can either go for one in G15 or G16 (as I still do not know what ur values in
col B are (weights / not weights?). I inserted blank col A to make the table
nicer, that is why my formulas in sample spreadsheet have different
references but logic is the same as for those formulas I posted here.