Finding out values depending on zone

[Himu]

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.

 

[Myrna Larson]

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$9000,3,0)-D1

 

[Himu]

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$9000,3,0)-D1

 

[Paul Robinson]

Hi
Copy the Sub below into a code module.
Sub below assumes your data is ordered in column A, like the data you
have presented.
Select the data in columns A to D (no header row) and run this Sub.
Repeat for other blocks of data.

regards
Paul

Public Sub ColE_Name()
Dim MyData As Variant, i As Long
Dim ArrayB() As Double, ArrayC() As Double, ArrayE() As Double
Dim VariantE As Variant
MyData = Selection.Value
RowsInMyData = UBound(MyData, 1)
ReDim ArrayB(1 To RowsInMyData)
ReDim ArrayC(1 To RowsInMyData)
ReDim ArrayE(1 To RowsInMyData, 1 To 1)

ArrayB(1) = MyData(1, 2)
ArrayC(1) = MyData(1, 3)
ArrayE(1, 1) = ArrayB(1) * ArrayC(1) - MyData(1, 4)
For i = 2 To RowsInMyData
If MyData(i, 1) = MyData(i - 1, 1) Then
ArrayB(i) = ArrayB(i - 1)
ArrayC(i) = ArrayC(i - 1)
Else
ArrayB(i) = MyData(i, 2)
ArrayC(i) = MyData(i, 3)
End If
ArrayE(i, 1) = ArrayB(i) * ArrayC(i) - MyData(i, 4)
Next i
Selection.Cells(1, 5).Resize(RowsInMyData, 1).Value = ArrayE
End Sub