Finding out values depending on zone

  • Thread starter Thread starter Himu
  • Start date Start date
H

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.
 
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
 
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
Click to expand...
 
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
 
Back
Top