Calculate Range (Max - Min) in a group of values

[dnessim]

Hi,

Here is another problem I am trying to figure out that I have not been able to search for a solution.

I have a list of values that are grouped, what I want is a formula to calculate the range (Max-Min)

Here is an example of my data

Group Name Value Range Calc (Max-Min)

GRP1 2 4

GRP1 4

GRP1 6

GRP2 1 2

GRP2 3

GRP3 7 6

GRP3 4

GRP3 2

GRP3 8

GRP4 2

GRP4 1

GRP4 3

GRP5 6 6


How do we loop thru my rows and return Max - Min for each group?

Thanks

Dave

 

[mercatog]

Hi


In C2 this array formula (validate pat Ctrl Shift Enter)

{=MAX(IF($A$2:$A$100=A2,$B$2:$B$100,""))-MIN(IF($A$2:$A$100=A2,$B$2:$B$100,""))}

Then drag to bottom

 

[Sajan]

@dnessim,

I interepreted your question to mean that the values for each group are on separate columns (similar to the setup below). i.e. I am assuming the low-high values for each row are not entered as text like "2 4", but in separate columns.

[pre]

Row#	A	B	C
1	Grp1	2	4
2	Grp1	4
3	Grp1	6
4	Grp2	1	2
5	Grp2	3
6	Grp3	7	6
7	Grp3	4
8	Grp3	2
9	Grp3	8
10	Grp4	2
11	Grp4	1
12	Grp4	3
13	Grp5	6	6
If so, I think @mercatog's MAX formula would work (after changing the data range B2:B100 to include B2:C100), but I think the MIN formula needs to be tweaked a bit as follows:


Assuming your summary looks as below:

Row#    A	B	C
Row#	Group	Max	Min
16	Grp1	6	2
17	Grp2	3	1
18	Grp3	8	2
19	Grp4	3	1
20	Grp5	6	6

[/pre]
The MAX formula is as below (entered into cell B16 and copied down)

=MAX(($A$1:$A$13=$A16)*($B$1:$C$13))

entered with Ctrl + Shift + Enter


The MIN formula would be (entered into cell C16 and copied down)

=MIN(IF(($A$1:$A$13=$A16), IF(ISNUMBER($B$1:$C$13), ($B$1:$C$13))))

entered with Ctrl + Shift + Enter


The ISNUMBER() check is to eliminate any blank cells from factoring in the MIN calculation, while allowing actual zero values in the input range.


Cheers,

Sajan.

 

[SirJB7]

@sajan

Hi!

Firstly I interpreted dnessim's question as you did, but then I realized that the values: 4 in C2 was the difference between Max and Min for Grp1 (first occurrence in row 2), 2 in C4 for Grp2, 6 in C6 for Grp3, 6 in C13 for Grp5.

I used mercatog'2 formula and retrieved the same values as posted by dnessim.

Regards!

 

[dnessim]

thanks !! learning alot on this forum. The min and max values are all in the same column, the range value that I want to calculate is in a seperate column. I need to add some logic to compensate for a single value, if I have a single value, then max - min will = 0 ,

so .. I need to count all values in a group and IF < 2 then orignal value = range value.

Does that make sense? or is there an easier way?

thanks

Dave

 

[shrivallabha]

This is mercatog formula applied through VBA. The use of arrays and functions is intentional (through VBA, dictionary can also be used).

[pre]

Public Sub MaxMinFormulaThroughVBA()
Dim varList() As Variant
Dim strAddr As String, strAddr2 As String
Dim lngSize As Long, i As Long, j As Long

strAddr = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Address
strAddr2 = Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row).Address

lngSize = Evaluate("=SUM(--(FREQUENCY(MATCH(" & strAddr & "," & strAddr & ",0),MATCH(" _
& strAddr & "," & strAddr & ",0))>0))")
ReDim varList(lngSize - 1, 1)

For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
With Application
If .IsError(.Match(Range("A" & i).Value, .Index(.Transpose(varList), 1, 0), 0)) Then
varList(j, 0) = Range("A" & i).Value
j = j + 1
End If
End With
Next i

For i = LBound(varList) To UBound(varList)
varList(i, 1) = Evaluate("=MAX(IF(" & strAddr & "=" & Chr(34) & varList(i, 0) & Chr(34) & "," & _
strAddr2 & "," & "))-MIN(IF(" & strAddr & "=" & Chr(34) & varList(i, 0) & Chr(34) & "," & _
strAddr2 & "," & "))")
Next i

Range("D2").Resize(UBound(varList) + 1, 2).Value = varList

End Sub

[/pre]

 

[SirJB7]

Hi, dnessim!


For groups having only one value you should change mercatog's formula from this:

=MAX(IF($A$2:$A$100=A14,$B$2:$B$100,""))-MIN(IF($A$2:$A$100=A14,$B$2:$B$100,""))

to this:

=MAX(IF($A$2:$A$100=A14,$B$2:$B$100,""))-IF(MAX(IF($A$2:$A$100=A14,$B$2:$B$100,""))=MIN(IF($A$2:$A$100=A14,$B$2:$B$100,"")),0,MIN(IF($A$2:$A$100=A14,$B$2:$B$100,"")))

which will work for both cases, multiple values per group (equal or different) and only one value per group.


Regards!

 

[mercatog]

Hi everyone

Here proposition of code using simple array variable loop (If a group with a single row, the result would be the value itself). The only constraint is that the groups must be grouped

[pre]

'This will correctly work if groups are grouped
Sub Extrema()
Dim Mn As Double, Mx As Double
Dim i As Long, io As Long
Dim Flag As Boolean
Dim Rng As Range
Dim Gp As String
Dim Tb

'Adapte to your range and sheet (Group in column A, Values in column B)
Set Rng = Sheet1.Range("A2:C100")
Tb = Rng.Value

Gp = Tb(1, 1): Mn = Tb(1, 2): Mx = Tb(1, 2): io = 1

For i = 2 To UBound(Tb, 1)
If Tb(i, 1) = Gp Then
If Tb(i, 2) > Mx Then Mx = Tb(i, 2)
If Tb(i, 2) < Mn Then Mn = Tb(i, 2)
Tb(i, 3) = Empty
Flag = True
Else
Tb(io, 3) = Mx - IIf(Not Flag, 0, Mn)
Gp = Tb(i, 1): Mn = Tb(i, 2): Mx = Tb(i, 2): io = i
Flag = False
End If
If Not Flag Then Tb(io, 3) = Tb(i, 2)
Next i

Rng.Value = Tb
Set Rng = Nothing
End Sub[/pre]

Array Formula {=MAX(IF($A$2:$A$100=A2,$B$2:$B$100,""))-IF(COUNTIF($A$2:$A$100,A2)>1,MIN(IF($A$2:$A$100=A2,$B$2:$B$100,"")),0)}

 

[SirJB7]

@mercatog

Hi!

Luckily you don't use a Russian Excel version... ha ha ha!

Please note you used Feuill1 instead of Sheet1. May I suggest to use "Worksheets(1)" in this cases, so you might forget about translations? It sometimes happens to me with "Hoja1"

Regards!

 

[mercatog]

@Sir SirJB7

Exact as always! but I wrote a comment for adapting. Soon I should change my Office to English version


Subjectively, in vba, I prefer to code with the CodeName of the worksheets better (a little bitte) than the name or the index.


PS: How is my english?

PS2: I edited the code and nox Sheet1 written

 

[SirJB7]

@mercatog


Hi!


You don't have to switch to an English version because of this site, I never intended to suggest that.


Subjectively too, in VBA I prefer to qualify everything with constants, define ranges and use them. Check this as an example:


-----

[pre]

Option Explicit

Sub Example()
'
' constants
'  sample
Const ksWsSampleName = "Hoja1"
Const ksRngSampleName = "Rango1"
Const kiCodeColumn = 1
Const kiDescriptionColumn = 2
'  other sheet
Const ksWsOtherSheetName = "Hoja2"
Const ksRngOtherSheetName = "Rango2"
'
' declarations
Dim rngSample As Range, rngOtherSheet As Range
Dim I As Long
'
' start
'  initialize
Initialize
'  ranges
Set rngSample = Worksheets(ksWsSampleName).Range(ksRngSampleName)
Set rngOtherSheet = Worksheets(ksWsOtherSheetName).Range(ksRngOtherSheetName)
'
' process
With rngSample
For I = 1 To .Rows.Count
.Cells(I, kiCodeColumn).Value = I
.Cells(I, kiDescriptionColumn).Value = CStr(I)
Next I
End With
'
' end
'  ranges
Set rngSample = Nothing
Set rngOtherSheet = Nothing
'  terminate
Terminate
'  beep
Beep
'
End Sub

Sub Initialize()
With Application
.EnableEvents = False
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
End Sub

Sub Terminate()
With Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

[/pre]
-----


Regards!


PS: Your English it's quite good, I think, don't forget it isn't my native language. I'd say that it looks a lot like my French


PS2: Procedures Initialize and Terminate are separated because of testing purposes, when you stop and finish the execution or when it crashes, you simply write Terminate at Immediate window pane. When testing and debugging are done, I'm too lazy to incorporate them within the main code.

 

[mercatog]

@ SirJB7


Yes! here is professional code, and also the names choosen for the variables and constants! Bravo. Not like me, names without sens.


Regards!

 

[SirJB7]

@mercatog

Hi!

Thanks for your kind words but these is just a nutshell and the important thing is the nut itself. Adapting your code naming conventions and structure or adopting another one is the easiest part, the hard (and not always available part) is building the inside nut contents. And you don't seem to have a lot of problems doing so.

Regards!

PS: Well, if we forget these cases and it consequences:

http://chandoo.org/forums/topic/comparing-1-column-on-1-sheet-to-another-column-on-another-sheet#post-38676

 

[dnessim]

Hi all,

Thanks again for taking the time to help me with my problem.

Thankyou for taking the time to translate !!!

Dave