That was a typo on my behalf... sorry about that... i have
not tried it yet, but i hope your formula will work.

Thankx

-----Original Message-----
As long as the data is sorted by column A, this formula in
E1 and copied down
gives the results you list EXCEPT for row 3:

If I understand correctly, in E3, you subtract the value
in D3, so the formula
is (1*2)-4.5, not -6.03.

Was this a typo on your part, or do I misunderstand what
you want.

Anyway, this is the formula I came up with:


=VLOOKUP(A1,$A$1:$C$9000,2,0)*VLOOKUP(A1,$A$1:$C$9 000,3,0)-D1


On Mon, 18 Oct 2004 19:42:44 -0700, "Himu"
wrote:

Here is a simplified version of a table i made in excel:
A B C D E
1 1 1 2 3
2 1 2 4 5
3 1 6 6.03 4.5
4 2 2 3 4
5 2 2 5 3.33
6 3 3 3 34
7 4 5 6 3
8 4 1 2 3
9 4 8 3 9.9
10 4 5 9 2.2
11 4 1 9 5
12 4 7 4 1

Iwant to generate a column E. Whenever A1=1 then B=1 and
C=2(which is the first row) so for all A=1 E should use B=1
and C=2 and generate BC-D (note D is the corresponding
value in each E column cells).

So E1=((1*2)-3)=-1, E2=((1*2)-5)=-3; E3=((1*2)-6.03)=-4.03
E4=((2*3)-4)=2, E5=((2*3)-3.33)=2.67, E6=((3*3)-34)=-25,
E7=((5*6)-3)=27, E8=((5*6)-3)=27, E9=((5*6)-9.9)=20.1,
E10=((5*6)-2.2)=27.8, E11=((5*6)-5)=25, E12=((5*6)-1)=29

I hope there is a solution to this problem as my real table
has over 9000 rows and over 100 zones.

Thank you.

.