Filetype Excel XLSX | Posted on 18 Aug 2022 | 3 years ago
Authentication
digital resources
237x Filetype XLSX File size 0.06 MB Source: www1.udel.edu
Sheet 1: Intro
| IT Client Support & Services | ||||||||||||||
| Excel 2007: Using Formulas and Functions | ||||||||||||||
| During this session you will learn about: | ||||||||||||||
| •Basic mathematical operators | ||||||||||||||
| •Relative and absolute cell references | ||||||||||||||
| •Using Functions | ||||||||||||||
| •Using the IF function | ||||||||||||||
| •Finding the right function | ||||||||||||||
| •Fixing errors | ||||||||||||||
| •Working with range names |
| Sum, Average, Max, Min to perform simple mathematical calculations | ||||||||
| F14 - Home, Editing, AutoSum, Enter | ||||||||
| Use the sum function to add a column of numbers | ||||||||
| Account | Fund | PCardLast4Digits | Descr | Journal ID | Amount | |||
| 120200 | OPBAS | 2013 | REG - Reg Earns | PAY0114466 | 1,578.00 | |||
| 120200 | OPBAS | 2013 | REG - Reg Earns | PAY0113699 | 1,578.00 | |||
| 120200 | OPBAS | 2013 | Adm. Prof.- Oth ERN | PAY0113699 | 99.11 | |||
| 143440 | OPBAS | 2013 | Adm. Prof.- Oth ERN | PAY0114466 | 99.11 | |||
| 143440 | OPBAS | 2013 | REG - Reg Earns | PAY0113699 | 1,940.54 | |||
| 143440 | OPBAS | 4454 | REG - Reg Earns | PAY0114466 | 1,940.54 | |||
| 143440 | OPBAS | 4454 | REG - Reg Earns | PAY0113699 | 3,077.96 | |||
| Create the sum function in B21 (don't include the Account row values), then drag to copy to C21:H21 range | ||||||||
| Account | 120200 | 120200 | 120200 | 143440 | 143440 | 143440 | 143440 | |
| 7,644.00 | 6,476.00 | 123.00 | 322.00 | 343.00 | 555.00 | 2,533.00 | ||
| 4,566.00 | 7,555.00 | 766.00 | 566.00 | 544.00 | 3,445.00 | 564.00 | ||
| 1,578.00 | 1,578.00 | 99.11 | 99.11 | 1,940.54 | 1,940.54 | 3,077.96 | ||
| * Tip: | Use Status Bar to quickly calculate values in a selected range. | |||||||
| Highlight range, look at Message Bar on bottom | ||||||||
| Right-click Status Bar to choose available functions |
| Override relative behavior with absolute cell references by inserting the $ symbol (may use F4) | |||||||
| D8 = (B8+C8)/2 | Copying D8 to D9:D21 changes formulas to use correct relative addresses to perform the same function for each row | ||||||
| Create total in G22 | |||||||
| Create formula in H8=(E8-F8)/$G$22 using absolute address for total | |||||||
| Copying H8 to H9:H21 changes numerator but uses total absolute address to use the same total for each row | |||||||
| Project | YR1 Budget | YR2 Budget | Average Year Budget | Budgeted in Project to Date | Spent in Project to Date | Left to Spend | % Left of Total Left |
| ABCD37239707000 | 1,000.00 | 500.00 | 1,500 | 999 | 501 | ||
| WXYZ37211307000 | 2,822.00 | 2,942.00 | 5,764 | 2,821 | 2,943 | ||
| ABCD37241208000 | - | 4,851.00 | 4,851 | 345 | 4,506 | ||
| ABCD37240708000 | - | - | 7,000 | 6,149 | 851 | ||
| QQQQ37238507000 | 7,390.00 | 29,808.00 | 37,198 | 20,323 | 16,875 | ||
| ABCD37238707000 | - | - | 500 | 288 | 213 | ||
| ABCD37239307000 | 19,442.00 | 20,269.00 | 39,711 | 36,597 | 3,114 | ||
| QQQQ37212707000 | 600.00 | 300.00 | 900 | 500 | 400 | ||
| WXYZ37239607000 | 5,810.42 | 500.00 | 6,310 | 5,810 | 500 | ||
| QQQQ37238907000 | 2,000.00 | 500.00 | 2,500 | - | 2,500 | ||
| ABCD37240608000 | - | - | - | 240 | (240) | ||
| QQQQ37212807000 | 5,810.42 | 500.00 | 6,310 | 5,810 | 500 | ||
| WXYZ37239907000 | 5,514.00 | - | 5,514 | 5,514 | - | ||
| WXYZ37239407000 | 15,372.00 | 16,025.00 | 31,397 | 21,300 | 10,097 | ||
| * Note: Remember order of operations - calculate total first | |||||||
| * Tip: Accounting format for cells shows dash for 0 value. |
no reviews yet
Please Login to review.
