Calculate Avg of MIN neg values

[joey91]

Hello, I'm using Excel 2007.

I have a single column of negative numbers separated by various zeros. First I want to calculate the MIN (lowest) value of EACH GROUP of negative numbers.

So in the example below, the first group's MIN = -12, second group's MIN = -44, third group's MIN = -19, fourth's MIN = -1, etc. I'd prefer if even one lone negative number counts as a group, as long as it's separated from other negative numbers by at least one zero.

Secondly, I'd like to calculate the average of all those MIN values combined for a final number. I'm aware that I could just manually enter =MIN(A1:A5) for the first group, do that for each one and then calculate the average of all those values but I'd prefer an easier way (single formula?) if possible. Thanks for your help!

eg.
Column A
0
0
-5
-4
-12
0
-11
-4
-2
-44
0
0
0
-8
-19
0
-1
0

 

[GlennUK]

I think you'd have to do it in stages, using helper columns, like this:

Excel Workbook
	A	B	C	D	E
1	Amt	Group	Mins	Avg =	-19
2	0
3	0
4	-5	1	-12
5	-4	1
6	-12	1
7	0
8	-11	2	-44
9	-4	2
10	-2	2
11	-44	2
12	0
13	0
14	0
15	-8	3	-19
16	-19	3
17	0
18	-1	4	-1
19	0
Sheet3

 

[trunten]

Not that it matters now, but I spent too long on this to let it go to waste

Function getGroupAverage(ByRef rng As Range) As Variant
    If rng.Columns.Count > 1 Then Exit Function
    Dim c As Range
    Dim i As Integer
    Dim groupCount As Long
    Dim runningTotal As Double
    Dim currentSmallest As Double
    currentSmallest = 0#
    runningTotal = 0#
    For Each c In rng
        i = -1
        If c.Row = 1 Then i = 0
        If c.Value < currentSmallest Then currentSmallest = c.Value
        If (c.Value = 0 And c.Offset(i, 0) <> 0) Or _
            (c.Row = rng.Rows.Count And c.Value <> 0) Then
            groupCount = groupCount + 1
            runningTotal = runningTotal + currentSmallest
            currentSmallest = 0#
        End If
    Next c
    getGroupAverage = (runningTotal / groupCount)
End Function

 

[joey91]

Wow, thanks guys!

Glenn, everything works great until I enter the formulas for cell C2, C3, C4, etc, the entire C column changes to that single formula.

For example, in the formula for cell C2, =B2 doesn't change into =B3 in the formula for cell C3, etc. None of the values that are supposed to change for each new cell are doing so, any thoughts?

I also tried your macro Trunton (nice work), it seems to function except that the resulting average is incorrect. I used the =getGroupAverage(A2:A19), not sure if I'm doing it right.


Joey

 

[GlennUK]

Does it have to be a table? Can you convert it to a range? You don't get those kind of problems then.

On the other hand if it has to stay as a table, when you insert a formula and it auto creates calculated cells, you should get a lightning symbol to show that autocorrect has happened ... click on it and choose "Undo Calculated Column".

Play around with it and see if you can get it to work.

 

[joey91]

Not sure what I was doing wrong but I played around with it and it's working perfectly now, using your exact formulas above. Thanks a million Glenn!

 

[markmzz]

GlennUK,

I did some modifications in yours formulas. Look at this:


Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="FONT-WEIGHT: bold">Numbers</TD><TD style="FONT-WEIGHT: bold">Helper Column</TD><TD style="FONT-WEIGHT: bold">Helper Column</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">Avg</TD><TD style="TEXT-ALIGN: right">-19</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">0</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">0</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">-5</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">-12</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">-4</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">-12</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right">0</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right">-11</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">-44</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right">-4</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right">-2</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right">-44</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right">0</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: right">0</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: right">0</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: right">-8</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">-19</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="TEXT-ALIGN: right">-19</TD><TD style="TEXT-ALIGN: right">3</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="TEXT-ALIGN: right">0</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD style="TEXT-ALIGN: right">-1</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">-1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD style="TEXT-ALIGN: right">0</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>Sheet6


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E1</TH><TD style="TEXT-ALIGN: left">=AVERAGE(C2:C19)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B2</TH><TD style="TEXT-ALIGN: left">=IF(A2=0,"",IF(B1="",MAX($B1:B$2)+1,B1))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C2</TH><TD style="TEXT-ALIGN: left">{=IF((COUNTIF(B1:B2,B2)=1)*(B2<>""),MIN(($B$2:$B$19=B2)*($A$2:$A$19)),"")}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

</TD></TR></TBODY></TABLE>

Markmzz

 

[GlennUK]

Looks great. Thanks for the feedback.