Excel Fundamentals for Data Analysis

Working with numbers and dates Test your skills

ByAdmin

Week 2 >>> Working with numbers and dates Test your skills >>> Excel Fundamentals for Data Analysis

 

1.

A7,4
———————————————————————————————–
=VALUE(RIGHT(A27,4))
———————————————————————————————–
18/APRIL/08
———————————————————————————————–
=TODAY()
———————————————————————————————–
=TODAY()+30
———————————————————————————————–
=NOW()
———————————————————————————————–
=DAY(F1)
———————————————————————————————–
=MONTH(F1)
———————————————————————————————–
=YEAR(F1)
———————————————————————————————–
———————————————————————————————–
———————————————————————————————–
———————————————————————————————–
———————————————————————————————–
=EOMONTH(F1,0)
———————————————————————————————–

=EDATE(F1,1)

Similar Posts

Excel Fundamentals for Data Analysis | Online Course Support

The Customer Reference number is how we identify customers in our system. The BPAY reference appears to consist of the 9 digit School identifier, followed by the 5 digit Customer Reference.

ByAdmin

3. Question 3 The Customer Reference number is how we identify customers in our system. The BPAY reference appears to consist of the 9 digit School identifier, followed by the…

Excel Fundamentals for Data Analysis | Online Course Support

In column F we need to get the payment amount from the BPAY sheet, but you will notice it is being treated as text because of the “AU” at the front. Apply functions to remove the unwanted characters and convert to a number. Which of the following combinations of calculations could you use?

ByAdmin

8. Question 8 In column F we need to get the payment amount from the BPAY sheet, but you will notice it is being treated as text because of the…

Excel Fundamentals for Data Analysis | Online Course Support

You realise you could make your life a little easier and your solution more robust by working in a table. Convert the data in A1:Q69 to a table. Turn on the Total row and add a SUM total in the Balance column. Filter the table to only show payments made in October. What is the Total Balance?

ByAdmin

11. Question 11 You realise you could make your life a little easier and your solution more robust by working in a table. Convert the data in A1:Q69 to a…

Excel Fundamentals for Data Analysis | Online Course Support

You would also like to see a breakdown of total outstanding fees and arrears. Start by naming the other columns in the SYS DATA sheet. Back in the Upload sheet create a calculation in T10 to calculate the total Amount (in SYS DATA) where the type is fee_amt. Copy the formula down to get the total arrears_amt. What is the Total Arrears amount as shown in T11?

ByAdmin

10. Question 10 You would also like to see a breakdown of total outstanding fees and arrears. Start by naming the other columns in the SYS DATA sheet. Back in…

Excel Fundamentals for Data Analysis | Online Course Support

Where there is more than one student enrolled, customers are given a 5% sibling discount. This is applied to the fees up front but as part of the report you are required to calculate what this amount was.

ByAdmin

19. Question 19 Where there is more than one student enrolled, customers are given a 5% sibling discount. This is applied to the fees up front but as part of…