0
I need to have a multiplier in a single cell and then average the column.
ex.
I have a value to which I am assigning a cell, lets say 100. Within that cell I want an automatic multiplier, say 5. So that when I type 100, the result will be 500.
I then want to average that cell with other cells that have been multiplied in the same column, is this possible?
Or is there an easier way then to put a formula in each cell?
Matthew S.
Posted 2013-11-18T08:41:52.450
Reputation: 1
2
Are only certain cells in the column multiplied or are all of them? Is there a reason the multiplication has to happen in the same column you enter the data?
It is a trivial problem if you use multiple columns; remember that you can fill in multiple cells with a formula by dragging the small black square on the right of a cell to where you want the same equation for that row or column or highlight down and use Ctrl+D.
Here are some links on multiplication and averaging:
J_D
Posted 2013-11-18T08:41:52.450
Reputation: 68
All the cells will be multiplied by their own specific integer. One may be * 5, another * 4. I then want to average the resulting values in the column.
I found this, is this a good way to do it? It doesn't multiply when you hit enter though, you have click outside, then back into the cell for it to calculate.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" And Target.Value <> "" Then Target.Value = Target.Value * 5 End If End Sub – Matthew S. – 2013-11-18T09:08:24.340
1VBA might work but I tend to use that for data processing rather than spreadsheet work - excel isnt really designed to change data entered in its own cell it tries to force you not to do this and any workaround tends to be clunky and mistake prone. The simplest solution is to add a second column with each multiplier on its own; =(A14), =(A27) etc then average that. If presentation is a concern then the entry column can be hidden when you come to deliver any reports or the calculated values extracted. – J_D – 2013-11-18T10:05:32.433
@MatthewS. you should use multiple columns as J_D says. For example keep your original values in ColA, your multipliers in ColB (so can easily be unique for each row) and then in ColC it's just =A1*B1. Then you can just find the average of ColC – Dan – 2013-11-19T08:33:42.767
1if i understand you correctly you want a formula that operates on a single cell. multiplying the numbered entered by an amount? second step sounds quite possible, but manipulating data entered into a cell is not possible without doing some VBA programming. Standard way of entering data in excel will overwrite the formula. why not just add a column and go with what excel is designed to do? – Joop – 2013-11-18T09:08:29.753
I need it to be clean and neat, i am doing scouting data for prospects and i need to attach a number to each trait and i am using the multiplier to weigh each trait, to which it will all be averaged into a final value. – Matthew S. – 2013-11-18T09:16:54.440
While what you want seems clean, it is actually cleaner to show your calculation. It doesn't have to show on the same sheet though. I will often use 1 sheet for data entry, then another to show the results of my calculations in the format that I want. – guitarthrower – 2013-11-18T16:44:41.287