Changing Column Reference in Formula

[RJB]

Creating a formula to find the lowest singular number in a column when the column reference changes. Anwere would be tie if more than one occurence of the lowest number is found.

RowCol.xls
	A	B	C	D	E
1	ColRef	Data	Data	Data	Data
2	1	4	2	7	4
3		3	3	8	4
4		2	4	9	5
5		1	5	10	6
6
7	Ifcol.Referenceis1answeris1
8	Ifcol.Referenceis2answeris2
9	Ifcol.Referenceis3answeris7
10	Ifcol.Referenceis4answerisTIE
Sheet1

 

[just_jon]

This is hideus. Do *not* look directly at the formula!

=IF(COUNTIF(OFFSET(A:A,0,A2,,1),MIN(OFFSET(A:A,0,A2,,1)))=1,MIN(OFFSET(A:A,0,A2,,1)),"Tie")

Where A2 holds your column number.

Check back often for better responses....

 

[RJB]

Not pretty but it does work. Now I am going to put it in a more difficult environment and see what happens.

thanks.

Anyone has a VBA idea I will take a look at it.

 

[just_jon]

Sub GetMin()
Dim r As Range, MyMin
Set r = Columns([A2].Value)
MyMin = Application.WorksheetFunction.Min(r)
If Application.WorksheetFunction.CountIf(r, MyMin) > 1 Then
    [A3] = "TIE"
Else
    [A3] = MyMin
End If
Set r = Nothing
End Sub

 

[RJB]

The formula in the above reply works great if columns/data are adjacent to one another. In the actual application the column reference is on a separate worksheet from the data sets and the data sets have empty cell between them as well as other columns in front of them.

RowCol.xls
	A	B	C	D	E	F	G	H
1	ColRef				Data	Data	Data	Data
2	3				4	2	7	4
3					3	3	8	5
4					2	4	9	5
5	Ifcol.Referenceis1answeris1				1	5	10	6
6	Ifcol.Referenceis2answeris1
7	Ifcol.Referenceis3answeris7
8	Ifcol.Referenceis4answerisTIE				Data	Data	Data	Data
9					4	5	8	4
10	Tie				3	3	8	4
11					2	4	9	5
12					5	5	10	6
13
14
15					Data	Data	Data	Data
16					4	1	8	4
17					3	3	8	7
18					2	4	9	5
19					6	5	10	6
Sheet1

 

[just_jon]

Will they always A] Start in the same column and, B] always have 1 blank column in between?

 

[RJB]

A=yes B=no

 

[just_jon]

Is everyhing in E condidered 1 data set?