| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average first n numbers in a range (there may be less than n numbe
I want average the first six numbers in a series of rows.
The series may include blanks, zeros, alpha charachters. There may not be six numbers in the series If there are less than six I still want the average , if there are more than six, I just want to average the first six. I have tried UDF methods and VB code, but can't get the right result. Any sane suggestions? Thnaks -- Mike A. M. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average first n numbers in a range (there may be less than n numbe
Assuming the six rows you want to average are in columns A-F, this
formula works: =SUM(A2:F2)/SUMPRODUCT(--(ISNUMBER(A2:F2))) This adds columns A thru F (blanks and alphas are ignored), then counts and divides by the number of numeric entries in the range. Does that do it for you? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average first n numbers in a range (there may be less than n n
Dave O, I have not made clear the environment
the data may look like this Row - 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 ................. Data xx 30 31 32 44 xx xx xx 32 30 xx 21 xx xx xx xx .......... xx xx xx xx xx 32 xx 12 xx xx xx xx xx xx xx xx xx xx ................. xx xx 21 31 21 xx xx xx xx xx 34 34 34 54 21 23 35 ............... where xx is possibly blank,zero, alpha etc. Some rows will have less than six values and some more Those with less than six I just want the average Those with more than 6 I want the average of the first 6 There could be up to 150 entries in each row with various inputs. thanks Mike A. M. "Dave O" wrote: Assuming the six rows you want to average are in columns A-F, this formula works: =SUM(A2:F2)/SUMPRODUCT(--(ISNUMBER(A2:F2))) This adds columns A thru F (blanks and alphas are ignored), then counts and divides by the number of numeric entries in the range. Does that do it for you? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average first n numbers in a range (there may be less than n n
I could only figure this out with VBA code. This is adjustable for the
number of expected columns and rows, but then the results are hardcoded into column EV, the total for the line, EW, the number of elements, and EX, the formula the derives the average. Sub Avg6() Dim LineTotal As Double Dim Elements As Byte Dim UpTo6 As Byte Dim ColumnCount As Byte Dim RowCount As Byte Dim K As Byte ColumnCount = 150 'adjust as necessary RowCount = 2 'adjust as necessary Range("a1").Select 'change this starting address as appropriate Do Until ActiveCell.Address = "$A$" & RowCount + 1 For K = 0 To ColumnCount - 1 If IsNumeric(ActiveCell.Offset(0, K).Value) And ActiveCell.Offset(0, K).Value < "" Then Elements = Elements + 1 LineTotal = LineTotal + ActiveCell.Offset(0, K).Value UpTo6 = UpTo6 + 1 If UpTo6 = 6 Then GoTo Found6: End If Next K Found6: Range("ev" & Selection.Row).Value = LineTotal Range("ew" & Selection.Row).Value = Elements Range("ex" & Selection.Row).Formula = "=ev" & Selection.Row & "/ew" & Selection.Row Elements = 0 LineTotal = 0 UpTo6 = 0 ActiveCell.Offset(1, 0).Select Loop End Sub |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average first n numbers in a range (there may be less than n numbe
I'm *sure* there's an easier way, but this is a quick and dirty way
(array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =IF(COUNT(B1:IV1), SUMPRODUCT(--(IF(ISNUMBER(B1:IV1), COLUMN(B1:IV1), 9E+307) <= SMALL(IF(ISNUMBER(B1:IV1), COLUMN(B1:IV1), 9E+307), 6)), --(IF(ISNUMBER(B1:IV1), COLUMN(B1:IV1), 9E+307) < 9E+307), B1:IV1) / MIN(6, COUNT(B1:IV1)), "N/A") In article , "Mike A. M." wrote: I want average the first six numbers in a series of rows. The series may include blanks, zeros, alpha charachters. There may not be six numbers in the series If there are less than six I still want the average , if there are more than six, I just want to average the first six. I have tried UDF methods and VB code, but can't get the right result. Any sane suggestions? Thnaks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average first n numbers in a range (there may be less than n n
Different interpretation...
Assuming that you want to exclude zero values as well, try... =AVERAGE(IF(B1:INDEX(B1:IV1,SMALL(IF(ISNUMBER(B1:I V1),IF(B1:IV10,COLUMN( B1:IV1)-COLUMN(B1)+1)),MIN(COUNTIF(B1:IV1,"0"),6)))0,B1: INDEX(B1:IV1,SM ALL(IF(ISNUMBER(B1:IV1),IF(B1:IV10,COLUMN(B1:IV1)-COLUMN(B1)+1)),MIN(COU NTIF(B1:IV1,"0"),6))))) or =AVERAGE(SUBTOTAL(9,OFFSET(B1:IV1,,SMALL(IF(ISNUMB ER(B1:IV1),IF(B1:IV10, COLUMN(B1:IV1)-COLUMN(B1))),ROW(INDIRECT("1:"&MIN(COUNTIF(B1:IV1, "0"),6) ))),,1))) Both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. The latter includes volatile functions and will prolong the re-calculation process. Hope this helps! In article , "Mike A. M." wrote: Row - 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 ................. Data xx 30 31 32 44 xx xx xx 32 30 xx 21 xx xx xx xx .......... xx xx xx xx xx 32 xx 12 xx xx xx xx xx xx xx xx xx xx ................ xx xx 21 31 21 xx xx xx xx xx 34 34 34 54 21 23 35 ............... where xx is possibly blank,zero, alpha etc. Some rows will have less than six values and some more Those with less than six I just want the average Those with more than 6 I want the average of the first 6 There could be up to 150 entries in each row with various inputs. thanks Mike A. M. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average first n numbers in a range (there may be less than n n
Thanks JE
-- Mike A. M. "JE McGimpsey" wrote: I'm *sure* there's an easier way, but this is a quick and dirty way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =IF(COUNT(B1:IV1), SUMPRODUCT(--(IF(ISNUMBER(B1:IV1), COLUMN(B1:IV1), 9E+307) <= SMALL(IF(ISNUMBER(B1:IV1), COLUMN(B1:IV1), 9E+307), 6)), --(IF(ISNUMBER(B1:IV1), COLUMN(B1:IV1), 9E+307) < 9E+307), B1:IV1) / MIN(6, COUNT(B1:IV1)), "N/A") In article , "Mike A. M." wrote: I want average the first six numbers in a series of rows. The series may include blanks, zeros, alpha charachters. There may not be six numbers in the series If there are less than six I still want the average , if there are more than six, I just want to average the first six. I have tried UDF methods and VB code, but can't get the right result. Any sane suggestions? Thnaks |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average first n numbers in a range (there may be less than n n
Dave O
Spot on, this seems to work fine (there are some strange "additional" outputs, but I think I have a way of cleaning up the code to work specifically on the data set I have. Many thanks - Mike A. M. "Dave O" wrote: I could only figure this out with VBA code. This is adjustable for the number of expected columns and rows, but then the results are hardcoded into column EV, the total for the line, EW, the number of elements, and EX, the formula the derives the average. Sub Avg6() Dim LineTotal As Double Dim Elements As Byte Dim UpTo6 As Byte Dim ColumnCount As Byte Dim RowCount As Byte Dim K As Byte ColumnCount = 150 'adjust as necessary RowCount = 2 'adjust as necessary Range("a1").Select 'change this starting address as appropriate Do Until ActiveCell.Address = "$A$" & RowCount + 1 For K = 0 To ColumnCount - 1 If IsNumeric(ActiveCell.Offset(0, K).Value) And ActiveCell.Offset(0, K).Value < "" Then Elements = Elements + 1 LineTotal = LineTotal + ActiveCell.Offset(0, K).Value UpTo6 = UpTo6 + 1 If UpTo6 = 6 Then GoTo Found6: End If Next K Found6: Range("ev" & Selection.Row).Value = LineTotal Range("ew" & Selection.Row).Value = Elements Range("ex" & Selection.Row).Formula = "=ev" & Selection.Row & "/ew" & Selection.Row Elements = 0 LineTotal = 0 UpTo6 = 0 ActiveCell.Offset(1, 0).Select Loop End Sub |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average first n numbers in a range (there may be less than n n
Thanks Domenic
-- Mike A. M. "Domenic" wrote: Different interpretation... Assuming that you want to exclude zero values as well, try... =AVERAGE(IF(B1:INDEX(B1:IV1,SMALL(IF(ISNUMBER(B1:I V1),IF(B1:IV10,COLUMN( B1:IV1)-COLUMN(B1)+1)),MIN(COUNTIF(B1:IV1,"0"),6)))0,B1: INDEX(B1:IV1,SM ALL(IF(ISNUMBER(B1:IV1),IF(B1:IV10,COLUMN(B1:IV1)-COLUMN(B1)+1)),MIN(COU NTIF(B1:IV1,"0"),6))))) or =AVERAGE(SUBTOTAL(9,OFFSET(B1:IV1,,SMALL(IF(ISNUMB ER(B1:IV1),IF(B1:IV10, COLUMN(B1:IV1)-COLUMN(B1))),ROW(INDIRECT("1:"&MIN(COUNTIF(B1:IV1, "0"),6) ))),,1))) Both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. The latter includes volatile functions and will prolong the re-calculation process. Hope this helps! In article , "Mike A. M." wrote: Row - 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 ................. Data xx 30 31 32 44 xx xx xx 32 30 xx 21 xx xx xx xx .......... xx xx xx xx xx 32 xx 12 xx xx xx xx xx xx xx xx xx xx ................ xx xx 21 31 21 xx xx xx xx xx 34 34 34 54 21 23 35 ............... where xx is possibly blank,zero, alpha etc. Some rows will have less than six values and some more Those with less than six I just want the average Those with more than 6 I want the average of the first 6 There could be up to 150 entries in each row with various inputs. thanks Mike A. M. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF than statement in a range of numbers? please help? | Excel Discussion (Misc queries) | |||
| Average of numbers within a range meeting certain criteria | Excel Worksheet Functions | |||
| Can I sum or average a range with more than 1 condition? | Excel Discussion (Misc queries) | |||
| average positive numbers | Excel Discussion (Misc queries) | |||
| Can you average data in 1 column based on a range of values in another? | Excel Worksheet Functions | |||
Linear Mode
