Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averging non-zero cells only
Hi everyone, I'll give an example to show you what I want to do. I have the following data: 0 1.2179 0 0 0 0 0 0 1.2140 0 0 0 1.2115 1.2165 I want to average the first 3 numbers that are non-zero. So the result of the 1st should be 1.2179+1.2140+1.2115 /3. I need the results of these to be in the 1st row somewhere else. The rows with zeros, and non-zeros keep changing, so you might get 10 0's in a row followed by 10 non-zero numbers. I just need to extract the first 3 non-zero numbers then average them I'm good with the basic functions, but I can't figure this out Any help at all would be most appreciated. Thanks Zak -- Alienator ------------------------------------------------------------------------ Alienator's Profile: http://www.excelforum.com/member.php...o&userid=31013 View this thread: http://www.excelforum.com/showthread...hreadid=506866 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averging non-zero cells only
A little convoluted but works
=AVERAGE((INDIRECT("A"&SMALL(IF(A1:A1000<0,ROW(A1 :A1000)),1))),INDIRECT("A"&SMALL(IF(A1:A1000<0,RO W(A1:A1000)),2)),INDIRECT("A"&SMALL(IF(A1:A1000<0 ,ROW(A1:A1000)),3))) array entered(ctrl+shift+enter) assuming that the data is in the range a1:a1000 "Alienator" wrote in message ... Hi everyone, I'll give an example to show you what I want to do. I have the following data: 0 1.2179 0 0 0 0 0 0 1.2140 0 0 0 1.2115 1.2165 I want to average the first 3 numbers that are non-zero. So the result of the 1st should be 1.2179+1.2140+1.2115 /3. I need the results of these to be in the 1st row somewhere else. The rows with zeros, and non-zeros keep changing, so you might get 10 0's in a row followed by 10 non-zero numbers. I just need to extract the first 3 non-zero numbers then average them I'm good with the basic functions, but I can't figure this out Any help at all would be most appreciated. Thanks Zak -- Alienator ------------------------------------------------------------------------ Alienator's Profile: http://www.excelforum.com/member.php...o&userid=31013 View this thread: http://www.excelforum.com/showthread...hreadid=506866 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averging non-zero cells only
Say the range you mentioned was into A1:A14, you can enter the following into
B1: =IF(A1,1,0) Then the following into B2: =IF(SUM(B$1:B1)=3,0,IF(A2,1,0)) and copy it all the way down to B14. Then in C1, use: =SUMIF(B1:B14,1,A1:A14)/SUM(B1:B14) It might not be ideal (you need an extra column), but it does the work. If your list is very very long (say 1500 rows), the sum in column B might be long to run towards the bottom. One way around it would be to change the formula in B2 (and the ones below it) by: =IF(B1=2,2,IF(SUM(B$1:B1)=3,2,IF(A2,1,0))) === Note that as soon as the sum of B1 to B_row-1_ is equal to 3, the formula returns a 2 to that cell, and the cells below it will then immediately return a 2 without passing by the sum(b$1:b_row-1_), accelerating the process quite a bit. If you wanted to sum more than only the first 3 (say the first 100) non-zero cells, simply change the 3 for a 100 in that formula. Obviously, you will need to change C1 as well: =SUMIF(B1:B14,1,A1:A14)/SUMIF(B1:B14,1) Hope that helps, FĂ©lix "Alienator" wrote: Hi everyone, I'll give an example to show you what I want to do. I have the following data: 0 1.2179 0 0 0 0 0 0 1.2140 0 0 0 1.2115 1.2165 I want to average the first 3 numbers that are non-zero. So the result of the 1st should be 1.2179+1.2140+1.2115 /3. I need the results of these to be in the 1st row somewhere else. The rows with zeros, and non-zeros keep changing, so you might get 10 0's in a row followed by 10 non-zero numbers. I just need to extract the first 3 non-zero numbers then average them I'm good with the basic functions, but I can't figure this out Any help at all would be most appreciated. Thanks Zak -- Alienator ------------------------------------------------------------------------ Alienator's Profile: http://www.excelforum.com/member.php...o&userid=31013 View this thread: http://www.excelforum.com/showthread...hreadid=506866 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averging non-zero cells only
See if one of these works for you:
With values (or blanks) in cells A1:A100 B1: =SUMPRODUCT((ROW(A1:A100)=SMALL(((A1:A100<0)*ROW( A1:A100))+((A1:A100=0)*10^10),{1,2,3}))*A1:A100)/3 OR B1: =SUMPRODUCT((ROW(A1:A100)=SMALL(((A1:A100<0)*ROW( A1:A100))+((A1:A100=0)*10^10),{1,2,3}))*A1:A100)/MIN(3,SUMPRODUCT(--(A1:A100<0))) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Alienator" wrote: Hi everyone, I'll give an example to show you what I want to do. I have the following data: 0 1.2179 0 0 0 0 0 0 1.2140 0 0 0 1.2115 1.2165 I want to average the first 3 numbers that are non-zero. So the result of the 1st should be 1.2179+1.2140+1.2115 /3. I need the results of these to be in the 1st row somewhere else. The rows with zeros, and non-zeros keep changing, so you might get 10 0's in a row followed by 10 non-zero numbers. I just need to extract the first 3 non-zero numbers then average them I'm good with the basic functions, but I can't figure this out Any help at all would be most appreciated. Thanks Zak -- Alienator ------------------------------------------------------------------------ Alienator's Profile: http://www.excelforum.com/member.php...o&userid=31013 View this thread: http://www.excelforum.com/showthread...hreadid=506866 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averging non-zero cells only
Here's another way...
=AVERAGE(SUBTOTAL(9,OFFSET(A1:A14,SMALL(IF(A1:A14 0,ROW(A1:A14)-MIN(ROW(A 1))),{1,2,3}),0,1))) ....confirmed with CONTROL+SHIFT+ENTER, not ENTER. Hope this helps! In article , Alienator wrote: Hi everyone, I'll give an example to show you what I want to do. I have the following data: 0 1.2179 0 0 0 0 0 0 1.2140 0 0 0 1.2115 1.2165 I want to average the first 3 numbers that are non-zero. So the result of the 1st should be 1.2179+1.2140+1.2115 /3. I need the results of these to be in the 1st row somewhere else. The rows with zeros, and non-zeros keep changing, so you might get 10 0's in a row followed by 10 non-zero numbers. I just need to extract the first 3 non-zero numbers then average them I'm good with the basic functions, but I can't figure this out Any help at all would be most appreciated. Thanks Zak |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do you "select locked cells" w/o "select unlocked cells"? | Excel Discussion (Misc queries) | |||
Counting empty cells within a range of cells | New Users to Excel | |||
Counting only active cells | Excel Discussion (Misc queries) | |||
Help adding text values | Excel Worksheet Functions | |||
Convert data type of cells to Text,Number,Date and Time | Excel Worksheet Functions |