Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there more efficient formula?
I just wrote the formula below and it computes the
weighted average of 4 values. If one or more values is null the % weight is proportionately redistrubuted over the non null values. The weighted percentages for the values are as follows: A=50% B=25% C=15% D=10% Please, let me know if you could write this in a more efficient way. Public Function WeightedAvg(A, B, C, D) 'Created by Diana Popovska on 08/21/03 Dim ValueA, ValueB, ValueC, ValueD, Total As Double 'Weighted percentages by default ValueA = 0.5 ValueB = 0.25 ValueC = 0.15 ValueD = 0.1 'If a percentage is higher than 1, only 100% is counted in the formula. 'It is already applied to A, B, C and D locally in excel. 'There are 16 possible combinations of A, B, C & D (2*2*2*2) 'Each case is examined below. 1 notes not null. 0 notates null. '1111 - CASE 1 If A 0 And B 0 And C 0 And D 0 Then WeightedAvg = ValueA * A + ValueB * B + ValueC * C + ValueD * D '0111 - CASE 2 ElseIf A = 0 And B 0 And C 0 And D 0 Then Total = ValueB + ValueC + ValueD WeightedAvg = (ValueB / Total) * B + (ValueC / Total) * C + (ValueD / Total) * D '0011 - CASE 3 ElseIf A = 0 And B = 0 And C 0 And D 0 Then Total = ValueC + ValueD WeightedAvg = (ValueC / Total) * C + (ValueD / Total) * D '0001 - CASE 4 ElseIf A = 0 And B = 0 And C = 0 And D 0 Then Total = ValueD WeightedAvg = (ValueD / Total) * D '0101 - CASE 5 ElseIf A = 0 And B 0 And C = 0 And D 0 Then Total = ValueB + ValueD WeightedAvg = (ValueB / Total) * B + (ValueD / Total) * D '0100 - CASE 6 ElseIf A = 0 And B 0 And C = 0 And D = 0 Then Total = ValueB WeightedAvg = (ValueB / Total) * B '0010 - CASE 7 ElseIf A = 0 And B = 0 And C 0 And D = 0 Then Total = ValueC WeightedAvg = (ValueC / Total) * C '0000 - CASE 8 ElseIf A = 0 And B = 0 And C = 0 And D = 0 Then WeightedAvg = 0 '1000 - CASE 9 ElseIf A 0 And B = 0 And C = 0 And D = 0 Then Total = ValueA WeightedAvg = (ValueA / Total) * A '1100 - CASE 10 ElseIf A 0 And B 0 And C = 0 And D = 0 Then Total = ValueA + ValueB WeightedAvg = (ValueA / Total) * A + (ValueB / Total) * B '1110 - CASE 11 ElseIf A 0 And B 0 And C 0 And D = 0 Then Total = ValueA + ValueB + ValueC WeightedAvg = (ValueA / Total) * A + (ValueB / Total) * B + (ValueC / Total) * C '1010 - CASE 12 ElseIf A 0 And B = 0 And C 0 And D = 0 Then Total = ValueA + ValueC WeightedAvg = (ValueA / Total) * A + (ValueC / Total) * C '1011 - CASE 13 ElseIf A 0 And B = 0 And C 0 And D 0 Then Total = ValueA + ValueC + ValueD WeightedAvg = (ValueA / Total) * A + (ValueC / Total) * C + (ValueD / Total) * D '1101 - CASE 14 ElseIf A 0 And B 0 And C = 0 And D 0 Then Total = ValueA + ValueB + ValueD WeightedAvg = (ValueA / Total) * A + (ValueB / Total) * B + (ValueD / Total) * D '1001 - CASE 15 ElseIf A 0 And B = 0 And C = 0 And D 0 Then Total = ValueA + ValueD WeightedAvg = (ValueA / Total) * A + (ValueD / Total) * D '0110 - CASE 16 ElseIf A = 0 And B 0 And C 0 And D = 0 Then Total = ValueB + ValueC WeightedAvg = (ValueB / Total) * B + (ValueC / Total) * C End If End Function |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is more efficient | Excel Discussion (Misc queries) | |||
Most efficient formula/combining multiple data cell ranges/seperat | New Users to Excel | |||
Efficient Array Formula Construction | Excel Discussion (Misc queries) | |||
is there a more efficient formula than... | Excel Worksheet Functions | |||
More efficient code | Excel Programming |