need help writing normalize function
 

I am having trouble writing a normalization function. Basically, I would
like to choose 2 ranges, the first with values, and the second to have
normalized values filled in. If my first range is A1:A20, the formula should
be (placed in B1):

=A1/max(A$1:A$20)

, and filled from B1:B20.

I would like this function to be flexible in terms of the size of the
ranges, with equal size for both being mandatory. In the end, I would like
the values to be converted into %s.

wazooli

need help writing normalize function
 

Wazooli

It looks to me that you are trying to fnd a percentage of the total of each
value entered into column A and that the result should change if more data
are entered into column A.
This will work provided column A contains raw data only (eg do not perform a
sum of column A values within column A)

=IF(ISBLANK(A1),"",(A1/SUM(A:A)))

Enter it at B1.
Drag or copy down as far as you like.
Format column B as a percentage

HTH
Steve

"Wazooli" wrote in message
...
I am having trouble writing a normalization function. Basically, I would
like to choose 2 ranges, the first with values, and the second to have
normalized values filled in. If my first range is A1:A20, the formula
should
be (placed in B1):

=A1/max(A$1:A$20)

, and filled from B1:B20.

I would like this function to be flexible in terms of the size of the
ranges, with equal size for both being mandatory. In the end, I would
like
the values to be converted into %s.

wazooli

need help writing normalize function
 

I know how to do this using worksheet functions. The point of this exercise
is to try and teach myself how to do something simple in VBA. I would
eventually like to assign a menu button to this function, so I can simply
select the areas, hit the button, and the values get filled in for me.

wazooli

"Steve" wrote:

Wazooli

It looks to me that you are trying to fnd a percentage of the total of each
value entered into column A and that the result should change if more data
are entered into column A.
This will work provided column A contains raw data only (eg do not perform a
sum of column A values within column A)

=IF(ISBLANK(A1),"",(A1/SUM(A:A)))

Enter it at B1.
Drag or copy down as far as you like.
Format column B as a percentage

HTH
Steve

"Wazooli" wrote in message
...
I am having trouble writing a normalization function. Basically, I would
like to choose 2 ranges, the first with values, and the second to have
normalized values filled in. If my first range is A1:A20, the formula
should
be (placed in B1):

=A1/max(A$1:A$20)

, and filled from B1:B20.

I would like this function to be flexible in terms of the size of the
ranges, with equal size for both being mandatory. In the end, I would
like
the values to be converted into %s.

wazooli

need help writing normalize function
 

Wazooli

You will need to add some code tp check if the last cell in A contains a
formula and zap it, but this should get you started:

Sub InsertFormula()
Dim formulaAddress
Dim rowCount
' sum column a
Worksheets("Sheet1").Range("A65536").Select
Selection.End(xlUp).Select
formulaAddress = Selection.Offset(1, 0).Address
Range(Selection, Selection.End(xlUp)).Select
'add a formula to row B
rowCount = Selection.Rows.Count
Range(formulaAddress).Formula = "=sum(" & Selection.Address & ")"

For i = 1 To rowCount
Range("B" & i).Formula = "=A" & i & "/" & formulaAddress
Next i

Range("B:B").Select
Selection.NumberFormat = "0.00%"

End Sub

Steve

"Wazooli" wrote in message
...
I know how to do this using worksheet functions. The point of this
exercise
is to try and teach myself how to do something simple in VBA. I would
eventually like to assign a menu button to this function, so I can simply
select the areas, hit the button, and the values get filled in for me.

wazooli

"Steve" wrote:

Wazooli

It looks to me that you are trying to fnd a percentage of the total of
each
value entered into column A and that the result should change if more
data
are entered into column A.
This will work provided column A contains raw data only (eg do not
perform a
sum of column A values within column A)

=IF(ISBLANK(A1),"",(A1/SUM(A:A)))

Enter it at B1.
Drag or copy down as far as you like.
Format column B as a percentage

HTH
Steve

"Wazooli" wrote in message
...
I am having trouble writing a normalization function. Basically, I
would
like to choose 2 ranges, the first with values, and the second to have
normalized values filled in. If my first range is A1:A20, the formula
should
be (placed in B1):

=A1/max(A$1:A$20)

, and filled from B1:B20.

I would like this function to be flexible in terms of the size of the
ranges, with equal size for both being mandatory. In the end, I would
like
the values to be converted into %s.

wazooli

need help writing normalize function
 

The basic code could be as simple as:

Sub Btn_click()
set rng = Selection
rng.Offset(0, 1).Formula = "=" & _
rng(1).Address(0, 0) & _
"/SUM(" & rng.Address & ")"
rng.Offset(0, 1).NumberFormat = "0.00%"
End Sub

but you might want to do some checking.

Sub Btn_click()
Dim rng As Range
Set rng = Selection
If rng.Areas.Count 1 Then Exit Sub
If rng.Columns.Count 1 Then Exit Sub
If Application.Count(rng) = 0 or Application.Max(rng) _
= 0 Then
MsgBox "No numbers"
Exit Sub
End If
rng.Offset(0, 1).Formula = "=" & _
rng(1).Address(0, 0) & _
"/SUM(" & rng.Address & ")"
rng.Offset(0, 1).NumberFormat = "0.00%"
End Sub

--
Regards,
Tom Ogilvy


"Wazooli" wrote in message
...
I know how to do this using worksheet functions. The point of this
exercise
is to try and teach myself how to do something simple in VBA. I would
eventually like to assign a menu button to this function, so I can simply
select the areas, hit the button, and the values get filled in for me.

wazooli

"Steve" wrote:

Wazooli

It looks to me that you are trying to fnd a percentage of the total of
each
value entered into column A and that the result should change if more
data
are entered into column A.
This will work provided column A contains raw data only (eg do not
perform a
sum of column A values within column A)

=IF(ISBLANK(A1),"",(A1/SUM(A:A)))

Enter it at B1.
Drag or copy down as far as you like.
Format column B as a percentage

HTH
Steve

"Wazooli" wrote in message
...
I am having trouble writing a normalization function. Basically, I
would
like to choose 2 ranges, the first with values, and the second to have
normalized values filled in. If my first range is A1:A20, the formula
should
be (placed in B1):

=A1/max(A$1:A$20)

, and filled from B1:B20.

I would like this function to be flexible in terms of the size of the
ranges, with equal size for both being mandatory. In the end, I would
like
the values to be converted into %s.

wazooli

need help writing normalize function
 

I put sum and it should have been max:


rng.Offset(0, 1).Formula = "=" & _
rng(1).Address(0, 0) & _
"/MAX(" & rng.Address & ")"

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
The basic code could be as simple as:

Sub Btn_click()
set rng = Selection
rng.Offset(0, 1).Formula = "=" & _
rng(1).Address(0, 0) & _
"/SUM(" & rng.Address & ")"
rng.Offset(0, 1).NumberFormat = "0.00%"
End Sub

but you might want to do some checking.

Sub Btn_click()
Dim rng As Range
Set rng = Selection
If rng.Areas.Count 1 Then Exit Sub
If rng.Columns.Count 1 Then Exit Sub
If Application.Count(rng) = 0 or Application.Max(rng) _
= 0 Then
MsgBox "No numbers"
Exit Sub
End If
rng.Offset(0, 1).Formula = "=" & _
rng(1).Address(0, 0) & _
"/SUM(" & rng.Address & ")"
rng.Offset(0, 1).NumberFormat = "0.00%"
End Sub

--
Regards,
Tom Ogilvy


"Wazooli" wrote in message
...
I know how to do this using worksheet functions. The point of this
exercise
is to try and teach myself how to do something simple in VBA. I would
eventually like to assign a menu button to this function, so I can
simply
select the areas, hit the button, and the values get filled in for me.

wazooli

"Steve" wrote:

Wazooli

It looks to me that you are trying to fnd a percentage of the total of
each
value entered into column A and that the result should change if more
data
are entered into column A.
This will work provided column A contains raw data only (eg do not
perform a
sum of column A values within column A)

=IF(ISBLANK(A1),"",(A1/SUM(A:A)))

Enter it at B1.
Drag or copy down as far as you like.
Format column B as a percentage

HTH
Steve

"Wazooli" wrote in message
...
I am having trouble writing a normalization function. Basically, I
would
like to choose 2 ranges, the first with values, and the second to
have
normalized values filled in. If my first range is A1:A20, the
formula
should
be (placed in B1):

=A1/max(A$1:A$20)

, and filled from B1:B20.

I would like this function to be flexible in terms of the size of
the
ranges, with equal size for both being mandatory. In the end, I
would
like
the values to be converted into %s.

wazooli

need help writing normalize function
 

Thanks Tom - works great, and thanks to John Walkenbach, it couldn't be
easier to attach it to a toolbar. Thanks again.

wazooli

"Tom Ogilvy" wrote:

I put sum and it should have been max:


rng.Offset(0, 1).Formula = "=" & _
rng(1).Address(0, 0) & _
"/MAX(" & rng.Address & ")"

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
The basic code could be as simple as:

Sub Btn_click()
set rng = Selection
rng.Offset(0, 1).Formula = "=" & _
rng(1).Address(0, 0) & _
"/SUM(" & rng.Address & ")"
rng.Offset(0, 1).NumberFormat = "0.00%"
End Sub

but you might want to do some checking.

Sub Btn_click()
Dim rng As Range
Set rng = Selection
If rng.Areas.Count 1 Then Exit Sub
If rng.Columns.Count 1 Then Exit Sub
If Application.Count(rng) = 0 or Application.Max(rng) _
= 0 Then
MsgBox "No numbers"
Exit Sub
End If
rng.Offset(0, 1).Formula = "=" & _
rng(1).Address(0, 0) & _
"/SUM(" & rng.Address & ")"
rng.Offset(0, 1).NumberFormat = "0.00%"
End Sub

--
Regards,
Tom Ogilvy


"Wazooli" wrote in message
...
I know how to do this using worksheet functions. The point of this
exercise
is to try and teach myself how to do something simple in VBA. I would
eventually like to assign a menu button to this function, so I can
simply
select the areas, hit the button, and the values get filled in for me.

wazooli

"Steve" wrote:

Wazooli

It looks to me that you are trying to fnd a percentage of the total of
each
value entered into column A and that the result should change if more
data
are entered into column A.
This will work provided column A contains raw data only (eg do not
perform a
sum of column A values within column A)

=IF(ISBLANK(A1),"",(A1/SUM(A:A)))

Enter it at B1.
Drag or copy down as far as you like.
Format column B as a percentage

HTH
Steve

"Wazooli" wrote in message
...
I am having trouble writing a normalization function. Basically, I
would
like to choose 2 ranges, the first with values, and the second to
have
normalized values filled in. If my first range is A1:A20, the
formula
should
be (placed in B1):

=A1/max(A$1:A$20)

, and filled from B1:B20.

I would like this function to be flexible in terms of the size of
the
ranges, with equal size for both being mandatory. In the end, I
would
like
the values to be converted into %s.

wazooli