vlookup/sum if help

[Guest]

Not sure of the best way to describe this, but im trying to create a sort of "sliding" vlookup and/or sumif formula. What I need is a formula that can accomplish the following: The first cell in a row (a3, for example) will report a value based on the lookup value in a2, my specified range, and the column index number 1 (for example). Then I need to be able to copy the formula over the next cell (b3), and it adds the lookup value from a2 again, except this time the column index is column 2, instead of column 1. Thats easy enough on its own, but the trick is, in addition to that, I need it to add in the lookup value from b2 also, with the column index number 1 applied to just his value.

Following this, I will copy the forumla to cell c3, and it will add the lookup value in a2 with a column index of 3, the lookup value from b2 with a column index of 2, and the lookup value from c2, with a column index of 1. This repeats out to the right indefinetly.

I suspect this is possible with some relatively simple vlookup and/or sumif combination. Just cant figure out what it is. Im new to this forum, so I appolgize is this has already been discussed. Thanks for your time.

 

[Scott R]

Can you post some simplified inputs and expected outputs for the first couple of occurences. That would help.

 

[Guest]

No problem. Thanks for your help. I think is what you're looking for.
col.A col.b col.c col.d
row 1 30% 20% 15% 10%
row 2 1000 2000 500 100
row 3 300 800 700 530

The formula im looking for is the one that goes in row 3. The output for cell a3 is 30% times 1000. The output for cell b3 is 30% times 2000 PLUS 20% times 1000. c3 is 30% times 500 PLUS 20% times 2000 PLUS 15% times 1000. And so on. I know I could write a massive series of if statements, but this is going to go out dozens of columns to the right, so I want to avoid that if at all possible. Let me know if you need any other information. Thanks again.

 

[Guest]

[No message]

 

[Scott R]

What you need is the equivalent of:
SUMPRODUCT($A1:D1,D2:$A2) in cell D3. Unfortunately Excel won't accept the 2nd argument and converts the entry to
SUMPRODUCT($A1:D1,$A2:D2).

Anybody else have any ideas on how to reverse the order of $A2:D2?

 

[Aladin Akyurek]

On 2002-02-28 10:09, Anonymous wrote:
No problem. Thanks for your help. I think is what you're looking for.
col.A col.b col.c col.d
row 1 30% 20% 15% 10%
row 2 1000 2000 500 100
row 3 300 800 700 530

The formula im looking for is the one that goes in row 3. The output for cell a3 is 30% times 1000. The output for cell b3 is 30% times 2000 PLUS 20% times 1000. c3 is 30% times 500 PLUS 20% times 2000 PLUS 15% times 1000. And so on. I know I could write a massive series of if statements, but this is going to go out dozens of columns to the right, so I want to avoid that if at all possible. Let me know if you need any other information. Thanks again.

Given that A1:C3 houses

{0.3,0.2,0.15,0.1;
1000,2000,500,100;
300,800,700,530}

the sample data you provided, along with the expected results in row 3,

in C1 enter:

=SUMPRODUCT($A$1:A1,N(OFFSET($A$2,0,COLUMN($A$2)+COLUMNS($A$2:A2)-COLUMN($A$2:A2)-1)))

Drag this across as far as needed.

My thanks to Scott R for requesting the sample data and intuiting that the first array must be multiplied by the reverse of the second array and also to Juan who had to deal with a sufficiently similar problem.

Aladin