Need help with multiplying values for scoring analysis

Hi friends,

I’m trying to score ideas and calculate a weighted score, but I’m not sure how to execute this in Airtable (it would take me seconds in excel).

I have a list of ideas, and a score for each across several variables (see 1st image below). I have a 2nd table with the weights (see 2nd image below). I would like to write a formula in the Total Score column in the 1st table (3rd image) that multiplies each of the score variables with their corresponding values so that The total score reflects the weighted result.

So conceptually, I want to calculate like this – how the heck to I do this in Airtable?

Total Score = Sum { (Var1 * Var1_weight) + (Var2 * Var2_weight) + (Var3 * Var3_weight) } … etc.

I really appreciate any guidance you can provide.

I originally wanted to just bake the weights into the formula, but it wasn’t working. I realized that the issue was the variable columns were set as a picklist. I changed those columns to be a # number, which then allowed me to set my desired formula: ({Retention Score (5=best)} * .4) + ({LOE Score (5=easiest)} * .25) + ({Revenue Loss Score (5=lowest)} * .2) + ({Competitive Impact Score (5=best)} * .15)

I’m all set now!