I am trying to normalize data sets in Excel to total 100%. I am wondering if there is a formula that can do this without having to correct each cell.
Printable View
I am trying to normalize data sets in Excel to total 100%. I am wondering if there is a formula that can do this without having to correct each cell.
Let's say the numbers are in G1:G20.
- In some blank cell (say Z1) enter =SUM(G1:G20)
- Copy Z1;keep Z1 the active cell and use Edit|Paste Special >Value
- Copy Z1; select G1:G20 and use Edit > Paste Special > Divide. Your numbers no add to 1; format them % if you wish OR in step ! use =SUM(G1:G20)/100 and now you numbers all add to 100
Hmmm, when I look at your values so, then it looks to me like, so if those already normalized, since they all lie between 0 and 1. Otherwise, you can easily apply your formula described in an auxiliary column and thus calculate the normalized data - by hand then you have to enter only the first formula and end up copying the entire column down. It exactly look like (it) as the data you plan to do so and how did you imagine the result.