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.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,040
Members
414,357
Latest member
Gemma_R

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