rsanders21
New Member
- Joined
- Aug 17, 2016
- Messages
- 4
All functions are to be left in the cells and are to calculate correctly when received by DOAS. This exercise is being evaluated on the use of functions
1. Tab ‘Raw Data’ has a column heading called State Entity Type in Column A in yellow. Write an Excel function in this column that calls the appropriate State Entity Name that matches the Sub Track Name
2. The Total Volume column contains the volume and the description in the same field. Create a separate column(s) and write an Excel function that separates the volume from the volume description
3. What is the total volume for the data set?
4. Write an Excel function that shows the smallest volume over the entire data set (exclude item numbers with zero or no volume)
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
1. Tab ‘Raw Data’ has a column heading called State Entity Type in Column A in yellow. Write an Excel function in this column that calls the appropriate State Entity Name that matches the Sub Track Name
2. The Total Volume column contains the volume and the description in the same field. Create a separate column(s) and write an Excel function that separates the volume from the volume description
3. What is the total volume for the data set?
4. Write an Excel function that shows the smallest volume over the entire data set (exclude item numbers with zero or no volume)
| Key ID | 1. State Entity Type | SUB TRACK CODE | SUB TRACK NAME | ITEM NUMBER | ITEM DESCRIPTION | VENDOR NAME | Product Category | COMMODITY DESCRIPTION | Supplier | Total Spend | Total Volume | Price |
| 1 | VP4AS | WSCA ST OF GA - TECHNICAL | 2V476 | Lamp,75a,A19,75 W | GENERAL ELECTRIC | Incandescent Lamps | LAMPS | 0020004371 | $ 9.12 | 48 EA | $ 0.19000 | |
| 2 | VP4AS | GEORGIA STATE UNIV | 4PL09 | Lamp,150par/Fl/Stg | GENERAL ELECTRIC | Incandescent Lamps | LAMPS | 0020004371 | $ 4.74 | 2 EA | $ 2.37000 | |
| 3 | VP4AS | GEORGIA TECH | 4V529 | Lamp,F36t12/Cw/Ho,45 W | GENERAL ELECTRIC | Fluorescent Linear L | LAMPS | 0020004371 | $ 34.97 | 13 EA | $ 2.69000 | |
| 4 | VP4AS | WSCA ST OF GA - TECHNICAL | 4V583 | Lamp,Hr175a39/Cp | GENERAL ELECTRIC | High Intensity Disch | LAMPS | 0020004371 | $ 4.87 | 1 EA | $ 4.87000 | |
| 5 | VP4AS | WSCA ST OF GA - CITY OF ATLANTA | 2V520 | Lamp,Lu250,250 W | GENERAL ELECTRIC | High Intensity Disch | LAMPS | 0020004371 | $ 31.17 | 3 EA | $ 10.39000 | |
| 6 | VP4AS | WSCA ST OF GA - HSING/BLDG AUTH | 2V712 | Lamp,Mvr250/U,250 W | GENERAL ELECTRIC | High Intensity Disch | LAMPS | 0020004371 | $ 26.61 | 0 EA | $ 8.87000 | |
| 7 | VP4AF | WSCA ST OF GA - DEFENSE/SAFETY | 2F095 | Lamp,F32tbx/A/4p,32 W | GENERAL ELECTRIC | Compact Fluorescent | LAMPS | 0020004371 | $ 59.43 | 8 EA | $ 7.42875 | |
| 8 | VP4AF | WSCA ST OF GA - LOCAL SCHOOLS | 2V529 | Lamp,Q300T3/CL,300 W | GENERAL ELECTRIC | Incandescent Lamps | LAMPS | 0020004371 | $ 27.96 | 12 EA | $ 2.33000 | |
| 9 | VP4AF | WSCA ST OF GA - DEFENSE/SAFETY | 3VK17 | Lamp,F34cw/Rs/Wm/Eco | GENERAL ELECTRIC | Fluorescent Linear L | LAMPS | 0020004371 | $ 498.00 | 600 EA | $ 0.83000 | |
| 10 | VP4AF | WSCA ST OF GA-ATLANTA PUB SCHOOLS | 4V450 | Lamp,15t6,T6,15 W | GENERAL ELECTRIC | Incandescent Lamps | LAMPS | 0020004371 | $ 25.56 | 36 EA | $ 0.71000 | |
| 11 | VP4AF | WSCA ST OF GA - DEFENSE/SAFETY | 4V453 | Lamp,25a/Cl,A19,25 W | GENERAL ELECTRIC | Incandescent Lamps | LAMPS | 0020004371 | $ 18.24 | 48 EA | $ 0.38000 | |
| 12 | VP4AF | WSCA ST OF GA - HUM RES/HLTHCARE | 5V059 | Lamp,500,Ps35,500 W | GENERAL ELECTRIC | Incandescent Lamps | LAMPS | 0020004371 | $ 23.52 | 12 EA | $ 1.96000 | |
| 13 | VP4AF | WSCA ST OF GA - DEFENSE/SAFETY | 5V613 | Lamp,60a/W/Ll,A19,60 W | GENERAL ELECTRIC | Incandescent Lamps | LAMPS | 0020004371 | $ 29.00 | 100 EA | $ 0.29000 | |
| 14 | VP4AF | UNIV OF GEORGIA | 5V616 | Lamp,75a/W/Ll,A19,75 W | GENERAL ELECTRIC | Incandescent Lamps | LAMPS | 0020004371 | $ 13.92 | 48 EA | $ 0.29000 | |
| 15 | VP4AJ | WSCA ST OF GA - MISC STATE | 1C800 | Lamp,F13dbx/4p,13 W | GENERAL ELECTRIC | Compact Fluorescent | LAMPS | 0020004371 | $ 2,329.60 | 160 EA | $ 14.56000 | |
| 16 | VP4AJ | 1C977 | Lamp,13 V,1383,20 W | GENERAL ELECTRIC | Miniature Lamps | LAMPS | 0020004371 | $ 310.60 | 20 EA | $ 15.53000 | ||
| 17 | VP4AJ | WSCA ST OF GA - MISC STATE | 1V170 | LAMP,F20T12/D,20 W | GENERAL ELECTRIC | Fluorescent Linear L | LAMPS | 0020004371 | $ 164.16 | 144 EA | $ 1.14000 | |
| 18 | VP4AJ | WSCA ST OF GA - NAT RES/AGRIC | 1V776 | Lamp,Fc12t9/Cw,32 W | GENERAL ELECTRIC | Circular and U-Shape | LAMPS | 0020004371 | $ 17.88 | 12 EA | $ 1.49000 | |
| 19 | VP4AJ | WSCA ST OF GA - NAT RES/AGRIC | 2D036 | Lamp,F26dbx,26 W | GENERAL ELECTRIC | Compact Fluorescent | LAMPS | 0020004371 | $ 601.20 | 100 EA | $ 6.01200 | |
| 20 | VP4AJ | WSCA ST OF GA - MISC STATE | 2V327 | Lamp,60a,A19,60 W | GENERAL ELECTRIC | Incandescent Lamps | LAMPS | 0020004371 | $ 91.20 | 480 EA | $ 0.19000 |
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
