Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help writing function | Excel Worksheet Functions | |||
Function writing help | Excel Worksheet Functions | |||
WRITING A WHAT IF FUNCTION | Excel Worksheet Functions | |||
Normalize | Excel Worksheet Functions | |||
writing its own function | Excel Programming |