Maintaining records of Student's Fee collection and tracking the same is a vital work in any school. In case of small schools often they do not use any software and mainly maintain the records on manually. Therefore to find any information like student wise, date wise etc may appear time consuming for the concerned person of that school. However using excel, one can maintain the aforesaid record very smartly. To give you the flavor of excel application, here I have designed a reporting format on Fee Collection, which mainly capture student wise collection status in any particular date as well enable to summarize class wise collection status at any given time period. Please download the file. Read the Help section, clear dummy records, key in your records and generate Report accordingly.
Have you ever tried VLOOKUP function to match multiple criteria? At least I had the idea of using INDEX and MATCH function to search Multiple criteria and use VLOOKUP for single criteria. Recently I have learned that to search on multiple criteria VLOOKUP function with a combination of CHOOSE can be used effectively. The formula would be like =VLOOKUP(Criteria1&Criteria2...,CHOOSE({1,2},Criteria Range1&Criteria Range2...,Lookup Range),2,0) press CTRL+SHIFT+ENTER key. Still have doubts? Ok, let refer the following example: Suppose you want to get the price of any item with certain make and size. For example you wish to find the price of Tab having size 6.5" which is made by iball at cell no I6. To do the same you need to write the function as
=VLOOKUP(I2&I3&I4,CHOOSE({1,2},C3:C14&D3:D14&E3:E14,F3:F14),2,0) and press CTRL+SHIFT+ENTER You can try it using your own criteria and notice the amazing power of VLOOKUP One user in a forum of Excel Help asked solution for the following: "I have an Excel worksheet in which I log my flight hours. There are two primary columns: Column A is the date of my flight and column B is the number of hours flown for that particular flight. There can be multiple flights per day, so multiple rows per day. I am continually adding rows to the flight log. I need a way to calculate the number of hours flown over the last X days, where X could be 183 (6 months) or 365 (12 months). Any guidance would be much appreciated. —Forrest Voss " Answer: Using SUMIFS function the above can be solved. Please see the attached file.
|
|