Multiplying column values?

Megan TW

Hi Community,

I have a sheet with section score values ("Ease Score", "Permanence Score", "Impact Score", "Cost Score") that I am trying to multiply to find an overall score ("Project Score"). Each section score column has a column formula with nested IFs to return the correct value, and I'm worried that's what is causing my "Project Score" value to return #INVALID OPERATION. Is it that? Something more simple? What am I missing?!

Paul Newcome

Try removing the quotes from around each of the numbers to be output. The quotes will output a text value which can't be multiplied by anything.

Paul Newcome

Can you provide the nested IF?

Megan TW

Sure. For "Impact Score" the nested IF is:

=IF(([Anticipated annual savings]@row) = "25K or Less", "1", IF(([Anticipated annual savings]@row) = "50K", "2", IF(([Anticipated annual savings]@row) = "75K", "3", IF(([Anticipated annual savings]@row) = "100K", "4", IF(([Anticipated annual savings]@row) = "200K", "5", IF(([Anticipated annual savings]@row) = "400K", "6", IF(([Anticipated annual savings]@row) = "500K", "7", IF(([Anticipated annual savings]@row) = "600K", "8", IF(([Anticipated annual savings]@row) = "750K", "9", IF(([Anticipated annual savings]@row) = "1M", "10"))))))))))

Paul Newcome

Try removing the quotes from around each of the numbers to be output. The quotes will output a text value which can't be multiplied by anything.

Megan TW

PAUL! YES! Thank you! This did the trick.

Paul Newcome

Happy to help. 👍️