Hi.
I "need" to write a function that will populate Col. G values by doing the following:
(Note: Data Set 1 spans columns "A" thru "F", Data Set 2 spans columns "J" thru "O"....below is just a small sample of either)
1) For a given row in column "B", look-up matching values in column "O" and return a sum of corresponding column "K" values (related, Data Set 2);
2) Then multiply the result of the above by the ratio of matching cell's Col. "F" value to that of all relevant Col. "F" values in Data set 1 (essentially pro-rate the above derived total based on Col. "F" values.)
By way of example, the first two rows of Col. "B" (Data Set 1) have a value of "1". Accordingly, Data Set 2,
Col. "O" matching rows (those also containing value of "1") are 1 & 18...and their corresponding values in related Col. "K" are 17.43 for both cells, which sums to 34.86.
Next, the function needs to appropriate that value of 34.86 among Data Set 1 matching (Col. "B" value) rows as based on their Col. "F" ("'Original Duration(h)") values...which in this instance are 6 & 6 respectively (see cells F1 and F2) hence the result for each of these two rows in 17.43 [or (34.86/(6/12))]
Note that Columns "B" and "O" are actually formatted as TEXT (despite their numerical appearance.)
My attempt to get this to work currently looks like this:
=IF(AND(ISNUMBER(SEARCH(B1,O1,1)),MID(O1,FIND(B1,O1)+1,1)<>"."),(SUMIF($O$1:$O$31,B1,$K$1:$K$31)*(F72/SUMIF($B$1:$B$32,B1,$F$1:$F$32))),"")
a) since the two column over which the initial match is performed are text, I am trying to find a sub-string (Col. "B" value) within a text string (Col. "O" value). ISNUMBER(SEARCH()) portion should return a number if there is a match;
b) Since e.g. a value of "4" from Col. "B" matches up with both a "4" and a "4.5" from Col. "O", a second logical test is added via AND(), this test being MID() which confirms that the succeeding text character is not a ".";
c) If the above two conditions are satisfied, simple SUMIF()'s are used to take care of the math.
The above approach does not work, hence this post. I am inclined to think this needs be an array formula (the AND() logical tests operate on a single cell reference context, seems like that might be the issue?) whereas the SUMIFs take an array as their argument). IF anyone can point me in the right direction, I would greatly appreciate it, a huge THANK YOU in advance!!!
THE DATA SET:
<tbody>
</tbody>
I "need" to write a function that will populate Col. G values by doing the following:
(Note: Data Set 1 spans columns "A" thru "F", Data Set 2 spans columns "J" thru "O"....below is just a small sample of either)
1) For a given row in column "B", look-up matching values in column "O" and return a sum of corresponding column "K" values (related, Data Set 2);
2) Then multiply the result of the above by the ratio of matching cell's Col. "F" value to that of all relevant Col. "F" values in Data set 1 (essentially pro-rate the above derived total based on Col. "F" values.)
By way of example, the first two rows of Col. "B" (Data Set 1) have a value of "1". Accordingly, Data Set 2,
Col. "O" matching rows (those also containing value of "1") are 1 & 18...and their corresponding values in related Col. "K" are 17.43 for both cells, which sums to 34.86.
Next, the function needs to appropriate that value of 34.86 among Data Set 1 matching (Col. "B" value) rows as based on their Col. "F" ("'Original Duration(h)") values...which in this instance are 6 & 6 respectively (see cells F1 and F2) hence the result for each of these two rows in 17.43 [or (34.86/(6/12))]
Note that Columns "B" and "O" are actually formatted as TEXT (despite their numerical appearance.)
My attempt to get this to work currently looks like this:
=IF(AND(ISNUMBER(SEARCH(B1,O1,1)),MID(O1,FIND(B1,O1)+1,1)<>"."),(SUMIF($O$1:$O$31,B1,$K$1:$K$31)*(F72/SUMIF($B$1:$B$32,B1,$F$1:$F$32))),"")
a) since the two column over which the initial match is performed are text, I am trying to find a sub-string (Col. "B" value) within a text string (Col. "O" value). ISNUMBER(SEARCH()) portion should return a number if there is a match;
b) Since e.g. a value of "4" from Col. "B" matches up with both a "4" and a "4.5" from Col. "O", a second logical test is added via AND(), this test being MID() which confirms that the succeeding text character is not a ".";
c) If the above two conditions are satisfied, simple SUMIF()'s are used to take care of the math.
The above approach does not work, hence this post. I am inclined to think this needs be an array formula (the AND() logical tests operate on a single cell reference context, seems like that might be the issue?) whereas the SUMIFs take an array as their argument). IF anyone can point me in the right direction, I would greatly appreciate it, a huge THANK YOU in advance!!!
THE DATA SET:
Col. A | Col. B | Col. C | Col. D | Col. E | Col. F | Col. G | Col. H | Col. I | Col. J | Col. K | Col. L | Col. M | Col. N | Col. O |
Activity Name | Sort | (*)WBS Name | Building | Floor | Original Duration(h) | Hrs | Description | Total Labor Hrs | Bldg | Area | Sub Area | |||
DATA SET 1 | DATA SET 2 | |||||||||||||
Electrical Demo/Remove | 1 | Interior Demolition/Removal (Smith 2nd) | Smith | 2 | 6 | 17.43213 | DEMO | 17.43 | Smith | 2 | Corridor | 1 | ||
Electrical Riser(s) Demo/Remove | 1 | Interior Demolition/Removal (Smith 2nd) | Smith | 2 | 6 | 17.43213 | TEMPORARY | 17.43 | Smith | 2 | Corridor | 2 | ||
Temporary Power & Lighting | 2 | Renovation (Smith 2nd) | Smith | 2 | 6 | 82.80264 | CEILING ROUGH IN POWER | 65.37 | Smith | 2 | Corridor | 4&5 | ||
Wall Cut-Ins for Electric at Demising Walls | 3 | Residents Rooms (Smith 2nd) | Smith | 2 | 4 | 25.82538 | 14.81% | CEILING ROUGH IN DATA | 65.37 | Smith | 2 | Corridor | 4&5 | |
Fire Alarm Risers | 3 | Residents Rooms (Smith 2nd) | Smith | 2 | 2 | 12.91269 | 7.41% | CABLE TRAY | 21.79 | Smith | 2 | Corridor | 4.5&5.5 | |
Electrical Risers | 3 | Residents Rooms (Smith 2nd) | Smith | 2 | 4 | 25.82538 | 14.81% | WALL ROUGH | 65.37 | Smith | 2 | Corridor | 4&5 | |
Data/Comm. Risers | 3 | Residents Rooms (Smith 2nd) | Smith | 2 | 2 | 12.91269 | 7.41% | FIXTURES, DEVICES, CONTROLS | Smith | 2 | Corridor | |||
Electrical R.I. (Wall & Ceiling) | 3 | Residents Rooms (Smith 2nd) | Smith | 2 | 5 | 32.28173 | 18.52% | FIXTURES, DEVICES, CONTROLS | 43.58 | Smith | 2 | Corridor | 4.5&5.5 | |
Fire Alarm R.I. (Wall & Ceiling) | 3 | Residents Rooms (Smith 2nd) | Smith | 2 | 5 | 32.28173 | 18.52% | TECH RM - RACKS/LADDER TRAY | 108.95 | Smith | 2 | Corridor | 4.5&5.5 | |
Data/Comm. R.I. (Wall & Ceiling) | 3 | Residents Rooms (Smith 2nd) | Smith | 2 | 5 | 32.28173 | 18.52% | EQUIPMENT | 43.58 | Smith | 2 | Corridor | 4&4.5&5&5.5 | |
Electrical R.I. Inspection @ Ext. Wall | 0 | Residents Rooms (Smith 2nd) | Smith | 2 | 1 | FALSE | FIRE ALARM/SECURITY/RADIO/ICOMM | 32.69 | Smith | 2 | Corridor | 4&4.5&5&5.5 | ||
Pull Wire (Power/Data/Comm./F.A./Temp. Control/Etc.) | 3.5 | Residents Rooms (Smith 2nd) | Smith | 2 | 20 | 48.74117 | PHONE /DATA | 21.79 | Smith | 2 | Corridor | 4&4.5&5&5.5 | ||
Light Fixtures | 3.5 | Residents Rooms (Smith 2nd) | Smith | 2 | 5 | 12.18529 | CATV | 43.58 | Smith | 2 | Corridor | 4&4.5&5&5.5 | ||
Devices (Power/Data/Comm./Etc.) | 3.5 | Residents Rooms (Smith 2nd) | Smith | 2 | 5 | 12.18529 | PULL WIRE /TERMINATE | 43.58 | Smith | 2 | Corridor | 4.5&5.5 | ||
Electrical Testing | 3.5 | Residents Rooms (Smith 2nd) | Smith | 2 | 3 | 7.311175 | FIRE STOP | 6.54 | Smith | 2 | Corridor | 4&4.5&5&5.5 | ||
Fire Alarm Devices | 3.5 | Residents Rooms (Smith 2nd) | Smith | 2 | 5 | 12.18529 | PRETEST AND FA TESTING | Smith | 2 | Corridor | 4.5&5.5 | |||
Wall Cut-Ins for Electric at Demising Walls | 4 | Core/Corridors (Smith 2nd) | Smith | 2 | 4 | 0 | SMITH 2R | 17.43 | Smith | 2 | Residential | 3&3.5 | ||
Fire Alarm Risers | 4 | Core/Corridors (Smith 2nd) | Smith | 2 | 2 | 0 | DEMO | 17.43 | Smith | 2 | Residential | 1 | ||
Electrical Risers | 4 | Core/Corridors (Smith 2nd) | Smith | 2 | 4 | 0 | TEMPORARY | 65.37 | Smith | 2 | Residential | 2 | ||
Data/Comm. Risers | 4 | Core/Corridors (Smith 2nd) | Smith | 2 | 2 | 0 | CEILING ROUGH IN POWER | 65.37 | Smith | 2 | Residential | 3 | ||
Electrical R.I. (Wall & Ceiling) | 4 | Core/Corridors (Smith 2nd) | Smith | 2 | 5 | 0 | CEILING ROUGH IN DATA | 65.37 | Smith | 2 | Residential | 3 | ||
Fire Alarm R.I. (Wall & Ceiling) | 4 | Core/Corridors (Smith 2nd) | Smith | 2 | 5 | 0 | WALL ROUGH | 43.58 | Smith | 2 | Residential | 3 | ||
Data/Comm. R.I. (Wall & Ceiling) | 4 | Core/Corridors (Smith 2nd) | Smith | 2 | 5 | 0 | FIXTURES, DEVICES, CONTROLS | Smith | 2 | Residential | ||||
Electrical R.I. Inspection @ Ext. Wall | 0 | Core/Corridors (Smith 2nd) | Smith | 2 | 1 | FALSE | FIXTURES, DEVICES, CONTROLS | 43.58 | Smith | 2 | Residential | 3.5 | ||
Pull Wire (Power/Data/Comm./F.A./Temp. Control/Etc.) | 4.5 | Core/Corridors (Smith 2nd) | Smith | 2 | 15 | 0 | FIRE ALARM/SECURITY/RADIO/ICOMM | 32.69 | Smith | 2 | Residential | 3&3.5 | ||
Light Fixtures | 4.5 | Core/Corridors (Smith 2nd) | Smith | 2 | 5 | 0 | PHONE /DATA | 10.90 | Smith | 2 | Residential | 3&3.5 | ||
Electric Panels | 4.5 | Core/Corridors (Smith 2nd) | Smith | 2 | 2 | 0 | CATV | 65.37 | Smith | 2 | Residential | 3&3.5 | ||
Ladder Racks | 4.5 | Core/Corridors (Smith 2nd) | Smith | 2 | 2 | 0 | PULL WIRE /TERMINATE | 43.58 | Smith | 2 | Residential | 3.5 | ||
Fire Alarm Panels | 4.5 | Core/Corridors (Smith 2nd) | Smith | 2 | 2 | 0 | FIRE STOP | 6.54 | Smith | 2 | Residential | 3&3.5 | ||
Terminations | 4.5 | Core/Corridors (Smith 2nd) | Smith | 2 | 10 | 0 | DATA TESTING | 4.36 | Smith | 2 | Residential | 3.5 | ||
Technology Room Work | 4.5 | Core/Corridors (Smith 2nd) | Smith | 2 | 5 | 0 | FA PRETEST | 1.09 | Smith | 2 | Residential | 3.5 | ||
Fire Alarm Devices | 5.5 | Bathrooms (Smith 2nd) | Smith | 2 | 2 | 0 |
<tbody>
</tbody>