jimtheeagle
New Member
- Joined
- Jan 15, 2013
- Messages
- 31
Hello kind Mr. Excel folks,
My main table looks like this:
<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>
<tbody>
</tbody>
The table I am trying to populate is:
<tbody>
</tbody>
The cell I need help with is the "unutilized current"
What should happen is it should reference the site e.g. EME, then subtract from the total number of spaces, e.g. Ext Offices (in EME) the number of "secure" rooms with the following calculation: sum the following: "5 days / week * 100%" + "3-4 days/ week * 70% + 1-2 days /week * 30% + less than 1 day/ week * 10%
Hope that all makes sense and thank you in advance.
My main table looks like this:
<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>
Site | Suite | Room # | Username | First Name | Last Name | Employee Name | Title | Room Type | Utilization | Assignment Status |
EME | 1100 | 11.01 | Chardonnay | Conference | Chardonnay Conference | Conference Room | ||||
EME | 1100 | 11.02 | Std Cube | Vacant, but furnished | ||||||
EME | 1100 | 11.03 | Std Cube | Vacant, but furnished | ||||||
EME | 1100 | 11.04 | rsmith | Rae | Smith | Rae Smith | Credit Manager | Std Ext Office | 5 days/ week | Secure |
EME | 1100 | 11.05 | hchang | Hieu | Chang | Hieu Chang | Data Analyst | Std Cube | 5 days/ week | Secure |
EME | 1100 | 11.06 | Crissy Field | Crissy Field Print | Print Station | |||||
EME | 1100 | 11.07 | tknoll | Tom | Knoll | Tom Knoll | Infrastructure Manager | Std Ext Office | 5 days/ week | Secure |
EME | 1100 | 11.08 | cwest | Chad | West | Chad West | Independent Contractor | Std Cube | 1-2 days / week | Secure |
EME | 1100 | 11.09 | Std Cube | Vacant, but furnished | ||||||
EME | 1100 | 11.10 | mgarcia | Melinda | Garcia | Melinda Garcia | HR Manager | Std Ext Office | 5 days/ week | Secure |
EME | 1100 | 11.11 | Std Cube | Vacant, but furnished | ||||||
EME | 1100 | 11.12 | tfreeman | Tiffany | Freeman | Tiffany Freeman | Accounting Assistant | Std Cube | 5 days/ week | Secure |
EME | 1100 | 11.13 | ajones | Angela | Jones | Angela Jones | Payroll Specialist | Std Ext Office | 5 days/ week | Secure |
EME | 1100 | 11.14 | pbrown | Priscilla | Brown | Priscilla Brown | Payroll Specialist | Std Cube | 5 days/ week | Secure |
EME | 1100 | 11.15 | vsmith | Valerie | Smith | Valerie Smith | Accounting Assistant | Std Cube | 5 days/ week | Secure |
EME | 1100 | 11.16 | Std Ext Office | Unfurnished |
<tbody>
</tbody>
The table I am trying to populate is:
Site | Total Capacity | Unutilized Current | Hires (hired but not started) | 6-Mth Hotlist estimates | Planned Remain.Cap. | ||||||||||||||||||||||
Sqft | # Ext Offices | # Int Offices | # Workstations | Seated Capacity | # Conf | Sqft/seat | # Ext Off | # Int Off | # Work | Open Seats | Open | # Ext Off | # Int Off | # Work | # Ext Off | # Int Off | # Work | Hotlist success | # Ext Off | # Int Off | # Work | Open | |||||
ALP | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 1 | 5 | 2 | 2 | 4 | 50% | -4 | -3 | -9 | #### | ||||||||||
ATL | 0 | 0 | 0 | 0 | 0 | 0 | |||||||||||||||||||||
BOG | 0 | 0 | 0 | 0 | 0 | 0 | |||||||||||||||||||||
BOS | 5,300 | 0 | 0 | 0 | 0 | 0 | |||||||||||||||||||||
BTM | 4,896 | 0 | 0 | 0 | 0 | 0 | |||||||||||||||||||||
CAL | 0 | 0 | 0 | 0 | 0 | 0 | |||||||||||||||||||||
CCI | 10,091 | 0 | 0 | 0 | 0 | 0 | |||||||||||||||||||||
CHA | 0 | 0 | 0 | 0 | 0 | 0 | |||||||||||||||||||||
CHI | 11,015 | 0 | 0 | 0 | 0 | 0 | |||||||||||||||||||||
CST | 6,118 | 0 | 0 | 0 | 0 | 0 | |||||||||||||||||||||
DAL | 3,354 | 0 | 0 | 0 | 0 | 0 | |||||||||||||||||||||
DCN | 57,483 | 0 | 0 | 0 | 0 | 0 | |||||||||||||||||||||
EME | 33,798 | 66 | 12 | 84 | 162 | 6 | 209 | ||||||||||||||||||||
HOU | 8,239 | 0 | 0 | 0 | 0 | 0 |
<tbody>
</tbody>
The cell I need help with is the "unutilized current"
What should happen is it should reference the site e.g. EME, then subtract from the total number of spaces, e.g. Ext Offices (in EME) the number of "secure" rooms with the following calculation: sum the following: "5 days / week * 100%" + "3-4 days/ week * 70% + 1-2 days /week * 30% + less than 1 day/ week * 10%
Hope that all makes sense and thank you in advance.