Excel C1L8 Totals Formulas

  1. Open your ExcelC1L2yourname that you completed before.
  2. It should look like the following picture:
  3. Example
  4. Select E4 and enter the following formula: =B3+B4
  5. Select B3 and enter 10 students
  6. Select B4 and enter 4 staff
  7. Select E7 and enter the following formula: =sumif(A$7:A$34,D7,B$7:B$34)*$E$4
  8. Select E7 and use the fill handle copy the formula you just entered down through E22
  9. Notice that not all of the formulas work. Some of them have 0. This is because items in column D don't match exactly with the items in column A. For example Oatmeal (Packets) in D10 doesn't match Oatmeal in A13.
  10. Select Column E and right click and insert a new column between Column D and Column E
  11. In D10 type Oatmeal and in E10 type (Packets)
  12. In D11 remove the (32oz) part and type (32oz) in E11
  13. Fix the rest of the Columns like we have in the last two steps to get all of the formulas working.
  14. Notice that all of the formulas have a number except for F22 which is for Gatorade.
  15. Select Row 25 and insert a new row
  16. Select cell A25 and Type Gatorade and hit Tab
  17. In cell B25 Type 1
  18. Notice that F22 now has a value in it.
  19. Auto Fit Column D and Column E
  20. Change the formula in F19 to the following: =SUMIF(A$7:A$35,D19,B$7:B$35)*$F$4/12  We need to do this because we are buying cases which have 12 per case.
  21. We can't buy partial cases of so we will change the formula to this: =ROUNDUP(SUMIF(A$7:A$35,D19,B$7:B$35)*$F$4/12,0)
  22. We also don't need 14 32oz packets of raisins and craisins. So we will change the formula in F11 to this:  =ROUNDUP(SUMIF(A$7:A$35,D11,B$7:B$35)*$F$4/12,0)
  23. Change the formula in F12 to this: =ROUNDUP(SUMIF(A$7:A$35,D12,B$7:B$35)*$F$4/12,0)
  24. Select B3 and change the number of students to 16
  25. Notice that all of the numbers changed in your total shopping list
  26. Select D6 and change it to Totals(Shopping List)
  27. Turn in your Excel File



COPYRIGHT © 2023-2024 LEARNSOFTWARETODAY.COM