Practice Exercises on Absolute and Mixed Cell References
In the previous blogs, we learned about Relative Cell Reference and Absolute Cell Refrence in Excel. This blog will provide some practice Exercises on Absolute and Mixed Cell References.
- Please find attached the sample dataset, it contains Courses, Fee and Early Bird discount of 10%
The value on Cell C3 (Fee after discount) is 900 and is calculated as =B3-(B3*E1).
Objective: You have to calculate C3 and autofill the remaining cells. if you get incorrect values or errors, correct the calculation on C3 and do autofill again to get the corrrect values as shown below.
2. Please find below sample dataset, it contains courses and Fees on specific days of the months. The courses get discounts too (Column E)
The value on Cell F2 (Net Fee 5th of Month) is 950 and is calculated as =B2-(B2*E2).
Objective: Calculate F2 and autofill this value horizontally and vertically, you will get an error. You have to fix the calculation in F2 and then autofill it horizontally and vertically to the get the correct values as shown below:
3. Please find below sample dataset, it contains Amount in dollars and conversion rate for different currencies.
The calculation in cell B4 (INR) is =A4*F2.
Objective: Autofill this calculation vertically and horizontally. If you get an error, fix the calculation in B4 and then autofill vertically and horizontally to ge the correct results as shown below:
These exercises will use the concepts of absolute and mixed cell references. You can place your calculations in the comments.
f you need solutions, please mention it in the comments, and I will provide them.
Explore additional insights like these and enhance your Excel proficiency with comprehensive knowledge from the book Excel Basics to Advanced.
Enhance your Excel skills by exploring MS Excel Blogs for more valuable tips.
About the Author
Chandraish Sinha has 20+ years of experience in Information Technology. He is an accomplished author and has published 11 books covering Business Intelligence related topics such as, Tableau, Power BI and Qlik. Checkout his Amazon Author profile.
His latest book Excel Basics to Advanced covers all the aspects of MS Excel and provides exercises for self-learning.
Similarly, his recent book, Dashboarding with Tableau, covers all the features in Tableau and includes exercises for self-learning.
He has implemented IT solutions in different domains viz. Pharmaceutical, Healthcare, Telecom, Financial and Retail.
He blogs regularly on various IT topics. Check them out in the links given below:
Kindly assist with solution of this exercise 2
In the second exercise, we are finding the Net Fee for different months i.e., Net Fee 5th of the Month etc.
On Cell F2, the provided calculation is B2 – (B2*E2). The objective of the calculations in these cell is to keep to values of the Column E constant.
To achieve this modify the calculation on cell F2 to B2 – (B2*$E2). The $ infront of the column E will keep the column constant.
After changing the F2 formula, autofill in all the remaining cells. This will give you correct results.
Hope this helps.