Help with complex formula

myergo

New Member
Joined
Feb 13, 2004
Messages
2
Here’s my Excel problem:

There are 5 organizations within the company.

Each organization has been asked to evaluate each of some 60 systems for each year 2004-2008 (5 years).

The evaluation is composed of 258 questions to which the rating organization responds with a score of 1,3,5,7,9, or NA.

So, at the simplest (I use this term loosely) level, I have a 5-column, 258-row sheet containing the responses. The column headings are the organization names (let’s say Red, Grn, Yel, Blu, and Tan) while the rows are the 258 questions.

However, each organization has a different weight assigned to it based on its importance in the evaluation of any particular system for any particular year. The combination (roll-up) of these weighted scores then constitutes the “company” evaluation.

In my example below, ideally Red is weighted as 50% of the total (or 0.5), Grn and Yel each weighted as 5% of the total (or 0.05), and Blu and Tan each weighted as 20% (or 0.20) of the total. I have displayed these weights on row 2.

So, to get my “roll-up” score in “A4” for the scores posted as shown in row 4, the formula is: “B4*B2+C4*C2+D4*D2+E4*E2+F4*F2” which yields 4.5+.25+.35+1.4+1 or 7.5. No problem yet.

However, if an organization chooses NOT to evaluate and responds NA to a question, the relative weightings must be recalculated to maintain the same relative value for each organization that does respond. So, in row 5, Blu has responded “NA.” This means that the “roll-up” is now composed of only 4 organizations whose total weight is only .8 based on the original weightings. I need to convert the weightings so that each responding organization’s answer is valued in the same proportion as originally determined. So, in the case of Red for row 5, its new weight is 0.5/0.8 or 0.625, Grn is 0.05/0.8 or 0.0625, Yel is 0.05/0.8 or 0.0625, and Tan is 0.2/0.8 or 0.25. So, the formula in “A5” to determine the roll-up of the four organizations responding is: “B5*0.625+C5*0.0625+D5*0.0625+F5*0.25 which yields 5.625+0.3125+0.3125+1.25 or 7.5. (Had I done it using the original weights I would have gotten 6.1!) The number responding organizations will vary from 5 to 1.

I need to have a formula that:
a. Looks at each row and determines which orgs are not responding.
b. Recalculates the appropriate weightings for each row as described above.
c. Completes the formula using the new weights to arrive at the correct value in the appropriate row cell in column A.
d. Repeats the process for each succeeding row until the range of 286 rows is complete.

Note: There are 61 worksheets, each with 258 questions to which this process must be applied.


A B C D E F
1 Red Grn Yel Blu Tan
2 .50 .05 .05 .20 .20
3
4 7.5 9 5 7 7 5
5 7.5 9 5 7 NA 5
6 7 7 NA 5 NA
7 5 NA 5 7 7
8 3 5 5 5 NA
9 5 NA NA 9 NA
etc.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Steve Hartman

Active Member
Joined
Feb 17, 2002
Messages
417
The solution isn't too painful :biggrin:

Put the following formula In cell G4: {=SUM(IF(B4:F4<>"NA",$B$2:$F$2))} This is an array formula. You don't type in the braces {}. When you have typed it in, press SHIFT CTRL and ENTER at the same time. Excel will put the braces in the formula for you and you'll know it is correct.

Then copy and paste this cell down column G

Then put this formula in A4: =IF(B4<>"NA",B4*$B$2/G4,0)+IF(C4<>"NA",C4*$C$2/G4)+IF(D4<>"NA",D4*$D$2/G4,0)+IF(E4<>"NA",E4*$E$2/G4,0)+IF(F4<>"NA",F4*$F$2/G4,0)

Then copy and paste this cell down column A.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,036
Messages
5,856,959
Members
431,841
Latest member
jaybeem

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top