| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average price.
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average price.
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average price.
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average price.
Just replace 0 with -1 as in the formula below;
=AVERAGE(ROUNDDOWN(A1:A10,-1)) other instructions remain the same "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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average price.
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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average price.
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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average price.
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average price between dates | Excel Worksheet Functions | |||
| Average price between dates | Excel Worksheet Functions | |||
| average price | Excel Worksheet Functions | |||
| calculate/convert volume price to monthly average price | Excel Worksheet Functions | |||
| How do I get the average price per bid for an individual month? | Excel Discussion (Misc queries) | |||
Hybrid Mode
