Excel Fundamentals for Data Analysis

Working with numbers and dates Test your skills

ByAdmin September 3, 2021September 23, 2021

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

A7,4

———————————————————————————————–

=VALUE(RIGHT(A27,4))

———————————————————————————————–

18/APRIL/08

———————————————————————————————–

=TODAY()

———————————————————————————————–

=TODAY()+30

———————————————————————————————–

=NOW()

———————————————————————————————–

=DAY(F1)

———————————————————————————————–

=MONTH(F1)

———————————————————————————————–

=YEAR(F1)

———————————————————————————————–

2 January 2017

10 March 2017

28 February 2017

28 December 2016

———————————————————————————————–

=EOMONTH(F1,0)

———————————————————————————————–

=EDATE(F1,1)

Excel Fundamentals for Data Analysis | Online Course Support

You have been asked by a school to help automate their accounts processing. Every few days they receive a text file from the bank detailing payments received (BPAY sheet). This information must be matched to the system data that has been downloaded into Excel (SYS DATA sheet) and a report produced that will be uploaded to the school system.
ByAdmin September 3, 2021September 3, 2021

1. Question 1 You have been asked by a school to help automate their accounts processing. Every few days they receive a text file from the bank detailing payments received…

Read More You have been asked by a school to help automate their accounts processing. Every few days they receive a text file from the bank detailing payments received (BPAY sheet). This information must be matched to the system data that has been downloaded into Excel (SYS DATA sheet) and a report produced that will be uploaded to the school system.
Excel Fundamentals for Data Analysis | Online Course Support

Using Excel 2019, the problem in question 1 could have been solved using which of the following: (Multiple answers may apply.)
ByAdmin September 3, 2021September 3, 2021

2. Question 2 Using Excel 2019, the problem in question 1 could have been solved using which of the following: (Multiple answers may apply.) 1 / 1 point CONCAT Yes, that…

Read More Using Excel 2019, the problem in question 1 could have been solved using which of the following: (Multiple answers may apply.)
Excel Fundamentals for Data Analysis | Online Course Support

To be eligible for discount customers must have paid before the Discount Due Date and must have paid at least the full balance owing less the discount amount, i.e.
ByAdmin September 3, 2021September 3, 2021

18. Question 18 To be eligible for discount customers must have paid before the Discount Due Date and must have paid at least the full balance owing less the discount…

Read More To be eligible for discount customers must have paid before the Discount Due Date and must have paid at least the full balance owing less the discount amount, i.e.
Excel Fundamentals for Data Analysis | Online Course Support

Which of the following functions (on their own) could you have used to achieve the outcome in Question 4?
ByAdmin September 3, 2021September 3, 2021

5. Question 5 Which of the following functions (on their own) could you have used to achieve the outcome in Question 4? 1 / 1 point SUBSTITUTE, TRIM, or LEFT SUBSTITUTE,…

Read More Which of the following functions (on their own) could you have used to achieve the outcome in Question 4?
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 September 3, 2021September 3, 2021

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…

Read More 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?
Excel Fundamentals for Data Analysis | Online Course Support

The calculation in Column G has been done, it calculates the outstanding balance for each payee by adding up the fee, arrears, and other charges from the SYS DATA sheet. Quite a lot of them are showing 0 though. The reason is the calculation is using named ranges that do not include all the data on the SYSDATA sheet. Change the Named Ranges Amount and Cust_Ref to include all the data in those columns. The balances should now be correct. What is the total balance in T7?
ByAdmin September 3, 2021September 3, 2021

9. Question 9 The calculation in Column G has been done, it calculates the outstanding balance for each payee by adding up the fee, arrears, and other charges from the…

Read More The calculation in Column G has been done, it calculates the outstanding balance for each payee by adding up the fee, arrears, and other charges from the SYS DATA sheet. Quite a lot of them are showing 0 though. The reason is the calculation is using named ranges that do not include all the data on the SYSDATA sheet. Change the Named Ranges Amount and Cust_Ref to include all the data in those columns. The balances should now be correct. What is the total balance in T7?

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

Similar Posts