How to sum up weighed normalizations of columns?

Question

Say, I have a sheet with the following (simplified) structure with absolute values:

What I want to achieve is to get a table with the normalized values (i.e. the maximum is equivalent to 1. In my example in value_1, 4 is maximum and hence 1 and 2 would equal 0.5). Finally, I want to weigh value_1 and value_2 and get a normalized total (maximum number = 1)

Of course, my real sheet has many rows and a couple more columns, so creating new columns in the same sheet is not what I want.

Here is the simplified solution (with an example weigh (0,6-0,4):

Now the question is:

How can I get that solution with a large sheet with hundreds of rows and dozens of columns?

I tried to create a Pivot table and I could get sorts of normalized results for each column:

• Make the column values and use Show values as... > % of column results (see next screenshot, sorry, German version of Excel)
• I could not get the weighed total with this version though

Answer 1

Option 1 - using formula in additional sheet

• Duplicate your sheet and do the calculations in the second one. (duplication is good as you have the same headers and range of data as in the original one).
• If you put weights in first row and headers are in the second one then your formula in A3 will look like: =old_sheet!A3/max(old_sheet!A3:A<last row>)/A1
• now you only need to summarize rows to get the normalized weighted value

Option 2 - changing original data

• insert 3 new rows at the top of the data
• enter in the new rows: max value of the column, weight, max * weight
• select your 3rd row and press CRTL+C, select all your values and select paste special - divide.
• summarize the rows