DavidCruz07
New Member
- Joined
- Mar 1, 2017
- Messages
- 4
(A) (B) (C) (D) (E) (F) (G) (H) (I) (J) (K)
Truck Small Big Total Result Truck Small Big Total Result
877336 146 3 149 5.7 877336 615 67 682 26.2
843501 1346 94 1440 55.4 311944 400 100 500 35.7
345201 390 7 397 28.4 839133 1331 125 1456 56.0
311944 500 50 550 39.3 307612 582 82 664 47.4
First row(A)= Truck number
Fourth Row(D)= Small + Big
Excel function I currently have
=SUM(B2+C2)
Fifth Row(E)= Fourth Row/14 when Truck number starts with "3*****"
Excel function I currently have
=IF(A1>"800000",D2/26,D2/14)
Fifth Row(E)=Fourth Row/26 when Truck number starts with "8*****"
Excel function I currently have
=IF(A1>"800000",D2/26,D2/14)
Red Text= When the same Truck number appears in both Table 1 (A:E) and Table 2 (G:K)
My question is, is there a formula I can use to add the Fourth row (D) data together from Table 1 and Table 2 and have it divided accordingly by 14 or 26 when the truck numbers appear the same under Table 1 and Table 2
This is what I want the function to do, Since table 1 and table 2 both have the truck number"877336" I want excel to add the values from both fourth row together (D2+J2), so 149+682= 831 and then since the truck number starts with "8*****" have it divided by 26 and display it on the fifth row(E and K) for both tables. so 831/26= 32. So the fifth row (E and K) would display 32 in both Table 1 and Table 2 for Truck "877336"
Similar as to truck number 311944 where it is under both table 1 and table 2. the function would add 550+500= 1050. and divide it by 14 since it starts with "3*****" and display 75 under Results (E and K)
Again I ONLY want the numbers to add when the truck numbers are the same, if not the same truck number then just proceed with the regular formula where it divides it by 14 or 26 depending on the truck number
ALSO Whenever there is a truck number that is the same in both table 1 and table 2 doesn't necessary mean they will both appear in the same column. for example truck number 877336 is position in
A2 in table 1 and G2 in table 2, which IS in same column. However this is not always the case, truck number 311944 is position in A5 and G3, which is in a DIFFERENT column. So I need the function to be able to work with that too. So for example position A4(truck number:311944) would search through G2:G5 for a the same truck number
I've been stuck on trying to get a function to work like this. If you need anymore details to solve my excel problem please let me know
Truck Small Big Total Result Truck Small Big Total Result
877336 146 3 149 5.7 877336 615 67 682 26.2
843501 1346 94 1440 55.4 311944 400 100 500 35.7
345201 390 7 397 28.4 839133 1331 125 1456 56.0
311944 500 50 550 39.3 307612 582 82 664 47.4
First row(A)= Truck number
Fourth Row(D)= Small + Big
Excel function I currently have
=SUM(B2+C2)
Fifth Row(E)= Fourth Row/14 when Truck number starts with "3*****"
Excel function I currently have
=IF(A1>"800000",D2/26,D2/14)
Fifth Row(E)=Fourth Row/26 when Truck number starts with "8*****"
Excel function I currently have
=IF(A1>"800000",D2/26,D2/14)
Red Text= When the same Truck number appears in both Table 1 (A:E) and Table 2 (G:K)
My question is, is there a formula I can use to add the Fourth row (D) data together from Table 1 and Table 2 and have it divided accordingly by 14 or 26 when the truck numbers appear the same under Table 1 and Table 2
This is what I want the function to do, Since table 1 and table 2 both have the truck number"877336" I want excel to add the values from both fourth row together (D2+J2), so 149+682= 831 and then since the truck number starts with "8*****" have it divided by 26 and display it on the fifth row(E and K) for both tables. so 831/26= 32. So the fifth row (E and K) would display 32 in both Table 1 and Table 2 for Truck "877336"
Similar as to truck number 311944 where it is under both table 1 and table 2. the function would add 550+500= 1050. and divide it by 14 since it starts with "3*****" and display 75 under Results (E and K)
Again I ONLY want the numbers to add when the truck numbers are the same, if not the same truck number then just proceed with the regular formula where it divides it by 14 or 26 depending on the truck number
ALSO Whenever there is a truck number that is the same in both table 1 and table 2 doesn't necessary mean they will both appear in the same column. for example truck number 877336 is position in
A2 in table 1 and G2 in table 2, which IS in same column. However this is not always the case, truck number 311944 is position in A5 and G3, which is in a DIFFERENT column. So I need the function to be able to work with that too. So for example position A4(truck number:311944) would search through G2:G5 for a the same truck number
I've been stuck on trying to get a function to work like this. If you need anymore details to solve my excel problem please let me know
