Additional Instructions/Common Mistakes/Help with Formulas
Fonts
Aptos font fami
Additional Instructions/Common Mistakes/Help with Formulas
Fonts
Aptos font family was released in 2023 as the new default font for the Microsoft Office suite, replacing the previously used Calibri font. Aptos, originally named Bierstadt, is a sans-serif typeface developed by Steve Matteson.
The display versions are slightly narrower and more closely fitted, which makes it harder to read, especially on electronic devices. Narrow fonts are not ideal for reading longer texts, as it is too compressed. Additionally, Aptos does not “translate” well when an MS document is converted to Adobe PDF files. And as you know, documents are quite often distributed as PDF files.
Switch to Calibri or Verdana
one spreadsheet only – click on the Select All button (located at the intersection of column names and row numbers, shaped like a right triangle) or [Ctrl] + [A] shortcut key and select the desired font in the Font group
all workbooks – File->Options-> General-> When creating new Workbooks->Use this as the default font -> select the desired font by starting typing first few letters of the font
Titles
type ” Payroll Worksheet for Audio Manufacturing As of” in A1
type “12/30/2021” in A2; Do not change 12/30/2021. Type “as is”.
(5)-(8) Appropriate Excel built-in functions must be used. DO NOT use the Subtotal function
countA() – counts any kind of entries ( text, numbers, blank spaces, etc.)
count()- counts the number of numeric entries (including dates)
All $ amounts are to be formatted as Accounting. Do not use Currency formatting.
(1)-(3) – Type formulas in D7, E7, and F7. Check them, revisit and adjust them so they can be copied, and then copy them to the rest of the rows. You can select D7:F7 and copy all of them at once. Do not type formulas individually in D8:F13. It will result in a grade of 0.
(1) – Do not format with zero decimal places. For example 2.956 – will appear as 3. A person who worked less than 3 years is not entitled to 401K contributions. You need to “remove/truncate” the decimal portion. To do so, use the following formula. Do not use any other built-in Excel functions, most of them do not work in this situation. In D7 ( Years Worked) type = INT((C7-A1)/365) or TRUNC((C7-A1)/365), where
(C7-A1) – produces the number of days one worked
(C7-A1) /365 – produces a number of years one worked as a decimal number. For example 2.956
INT() – returns the nearest smallest whole number
INT(-3.876) will return -4
INT(3.876) returns 3
TRUNC() – truncates the decimal portion of the number and returns the whole number only
(2) and (3) – need to use IF () function
keep in mind X>0 and X>=1 are not the same. There is an infinite number of decimal numbers between 0 and 1. For example, if X=0.99 makes X>=1 False and X>0 True. In this assignment, years worked are always integers (whole numbers), and X>0 and X>=1 would produce the same results (there are no whole numbers between 0 and one). What if the employer decides to change conditions? Based on the above, it is better to implement a condition of “at least 10 years” as X>= 10 rather than X>9.
be sure to use assumptions in the formulas
Submit the spreadsheet with one tab only after you change the assumptions. The formulas are the most important. If they work- the spreadsheet works; do not create a new tab for new assumption values
After changing the assumption values, highlight all changes, not just the bonus and 401K contribution amounts.
Have you used B24 and B25 in other formulas? If yes, by changing values in B24 and B25, the results of formulas where B24 and B25 are used might have changed as well. These need to be highlighted as well.
Keep in mind conditional formatting takes precedence over any other formatting. It means that if you try to use Fill Color where conditional formatting is applied, it might look like the Fill Color does not work.