Formula Assistance

I am attempting to automate my project list by scoring each project based on calculations in another sheet.

I would like to create a "Project Score" based on drop-downs in my project sheet. Sum=Complexity*weight + cost*weight + impact*weight +resources*weight + value*weight. The values of each category and weight are in another sheet. (pics attached for both sheets to reference).

Any assistance would be greatly appreciated.

Find more posts tagged with

Manufacturing

Formulas

Project Management

Comments

Paul Newcome

For this I will call the sheet where you are putting the formula "Target Sheet", and the sheet where your reference table is will be call "Reference Sheet".

This can be done using a series of VLOOKUPs within your SUM function.

We will also use the "Reference Another Sheet" option in the Formula Help Box (see below). Whenever you see {Reference Sheet Range 1}, that is where you will use this. To set this range, click on the "Reference Another Sheet" link when in the appropriate portion of the formula. You will then select both column of data in your Reference Sheet.

So give this a shot. All you should have to change from the below formula is where it says "Weight", simply enter the value you want each field weighted by...

=SUM(VLOOKUP(Complexity@row, {Reference Sheet Range 1}, 2, false) * Weight, VLOOKUP(Cost@row, {Reference Sheet Range 1}, 2, false) * Weight, VLOOKUP(Impact@row, {Reference Sheet Range 1}, 2, false) * Weight, VLOOKUP(Resources@row, {Reference Sheet Range 1}, 2, false) * Weight, VLOOKUP(Value@row, {Reference Sheet Range 1}, 2, false) * Weight)

j.green

Paul-

Man, I can't thank you enough. Bonehead moment, all along I was not putting the SUM and going straight for the vlookup. 2 full days on this! Thank you for saving my sanity, everything worked perfect.

Paul Newcome

No worries. I've had more than a couple of bonehead moments this week.

Glad we could get it figured out for you though.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…