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
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" <[email protected]> wrote in message
news:[email protected]...
>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
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" <[email protected]> wrote in message
> news:[email protected]...
> >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
>
>
>
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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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
>>
>>
>>
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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > >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
> >
> >
> >
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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >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
> > >
> > >
> > >
>
>
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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > >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
> > > >
> > > >
> > > >
> >
> >
>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks