# Help with complex formula

#### myergo

##### New Member
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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
The solution isn't too painful

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.

Replies
9
Views
245
Replies
3
Views
1K
Replies
0
Views
398
Replies
6
Views
303
Replies
1
Views
297

1,202,976
Messages
6,052,871
Members
444,606
Latest member
rwmhr

### 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.

### Which adblocker are you using?

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

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