Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding out values depending on zone
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding out values depending on zone
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding out values depending on zone
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. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding out values depending on zone
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 "Himu" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table, display a value underneath another value in the values zone. | Excel Discussion (Misc queries) | |||
convert time from one zone to other zone in excel | Excel Worksheet Functions | |||
Sum values depending in values next column | Excel Discussion (Misc queries) | |||
Sum values depending on prefefined criterias | Excel Discussion (Misc queries) | |||
How to conditionally sum values depending on values in other colums | Excel Programming |