| 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? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average price.
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 :-( "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? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average price.
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. "Alojz" wrote: 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 :-( "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? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average price.
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? |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average price.
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. "chrisnsmith" wrote: 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? |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average price.
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$100)),1)+SUM(-FLOOR($C$1:$C$10,10)/40+($C$1:$C$10)/8)/SUM(--($C$1:$C$100))*8,0) Deleting everything what refers to B cells is not sufficient. "Alojz" wrote: 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. "chrisnsmith" wrote: 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? |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average price.
I've tried all the code posted in this thread and none seem to work.
"Alojz" wrote: 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$100)),1)+SUM(-FLOOR($C$1:$C$10,10)/40+($C$1:$C$10)/8)/SUM(--($C$1:$C$100))*8,0) Deleting everything what refers to B cells is not sufficient. "Alojz" wrote: 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. "chrisnsmith" wrote: 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? |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average price.
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. "chrisnsmith" wrote: I've tried all the code posted in this thread and none seem to work. "Alojz" wrote: 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$100)),1)+SUM(-FLOOR($C$1:$C$10,10)/40+($C$1:$C$10)/8)/SUM(--($C$1:$C$100))*8,0) Deleting everything what refers to B cells is not sufficient. "Alojz" wrote: 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. "chrisnsmith" wrote: 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? |
| 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) | |||
Linear Mode
