Learning Excel formulas

By some factors destined or determined, I started the exploration of the more advanced Excel formulas in order to create a working spreadsheet that will allow my son to record the accounting data of his music tuition business.

It is easy to make a worksheet just for a purpose of keeping the students' prepaid tuition fee and the amount they used after a lesson is delivered. I named this sheet "Lesson log". It consisted of 12 columns of Date, StudentID, StuName, ...AmountPaid, etc. I only realized after I have involved in the creation for one of the most basic accounting book, I naturally began to have new ideas of upgrading the function of my self-made worksheet.

The basic worksheet was aimed to instantly resolve Chenny's dire need for a ledger book. However, three or four weeks later, I noticed the Lesson log should have a column that fetches the balance amount from last record of the same student and calculates further by adding up his new payment and deducting one unit of fee if he had a lesson on that day. This requires a calculation for the Balance field in the worksheet. It sounds simple, but is very challenging to me because I was still new to the formulas configuration. I was virtually illiterate in this skill, so I started my self-taught process through Google on the internet. I believed there must be people asking for help with the formula they want to carry out a certain way of calculation.

My assumption was right, there were heaps of people like me asking all sorts of function the Excel can do, and there were at the meantime people who were knowledgeable on this area and were willing to teach. So I had read quite a lot of replies made to questions. For the Balance to behave like I have described above, I will have to learn functions of Index, If, Large, Row, Match; also learn their syntax and the formula comprises of several functions that will carry out a complicated calculation and bring up a result you expect to obtain.

Below is an example of my attempt to create a formula in the right end of the table. I want the formula to fetch from its current row of the Balance column the data of the same student ID. For instance,

ID	Fee charged	Payment received	Balance
38	0.00	40.00	40.00
114	0.00	20.00	20.00
95	0.00	60.00	60.00
114	0.00	60.00	60.00
38	20.00		20.00
95	20.00	20.00	-40.00
114	0.00	60.00	120.00
88	20.00		60.00
113	20.00		-20.00

I got stuck at this attempt trying to get a formula to proceed the calculation on the Balance column. Because the row number already grew to 400 plus, it is too big a job to calculate manually. Apart from the reality it is too tedious to calculate manually, it is eye soring to search for the immediate last record of the same student. For example I am entering record of student #113 who attended a class today. I want the table to display his balance after this delivered lesson. He did not make payment, and his last record indicate a 0 in balance, so the balance today should be -20.00.

I searched the web intensively. Because of reading a lot of the discussion posts and I am getting more familiar with the most used functions, I felt I had my own ideas for this Balance column.

Having determined to make the formula to do a series of calculation, I used my learned knowledge to write  a few formulas. First of all I defined the names of the columns for easier reference in writing the formula. This can be found in the Data section on the menu bar and select "Define names". $A$2:$D$11 is referred to as SelectAll; $A$2:$A$11 is referred to StuID; $B$2:$B$11 is referred to FeeCharged; $C$2:$C$11 is referred to Payment; and $D$2:$D$11 is referred to Balance.

After innumerable trial and error. I had finally come up with one like this. Although I found other formulas that did the same calculation, but I liked to use this one that came about because of my effort.

=IFERROR(INDEX(SelectAll, MAX(if(StuID=$A2,1)*ROW($D$2:$D$11)),1),"")


--to be continued--

Enlightenment and electronic learning

Probably sometime from year 2010, I developed a hobby of learning the basics of electronic following my previous learning subjects of Buddhism, flute, writing in English. I found these learning subjects interesting and enjoyable and most importantly, they suit my health condition.

There is a popular perception of life. They say life is an ongoing process of learning, learning to make life easier, more pleasure and less or free of suffering. And after I have learned some basics about Buddhism, the utmost happy goal that surpasses any happy category of things you can name of is the mastery of our mind. I believe this is a truth because I have learned from reading, broadcasting, or chats that the very successful leaders in their fields of career publicly spoke about their feeling of lack of happiness. Without doubt, money is not the cause of happiness. But what is?

Apparently, materialism is not the answer. So I searched the psychological side of our identity for the answer. We were taught "helping others is the cause of happiness" during our schooling period. That is right, it indeed makes one happy when you help someone to solve a problem, do a task or share something good with them. But the feeling of being happy won't stay long. So we keep on searching and contemplating on this question, what is the cause of happiness.

Then, I jump to a conclusion, now that happiness or adversity are psychological reflections of our mind, so the search for the answer should be turning from outside to inside.

The question has been with me all the time. I believe the purpose of our lives is nothing but seeking for the suitable practice in order to attain a long lasting or even an eternal happiness. Although our lives when seen from the surface are seeking for different objects, by theory, that seeking for something is believed by the individual seeker to bring them a happy outcome. However, this process of seeking and pursuing is not guaranteed to be successful every time.

In a dawn meditation sometime ago, an idea emerged from the depth of my conciousness. It seemed to be possible to work for me. It is relating to my self-taught electronic basics.

From the past three years involvement of repairing broken household electronic devices free for friends who are willing to let me try. I had gained some knowledge about the nature of the components as well as the circuit board. Some of the cases could not find anything wrong but just no response to the power applied. My curiousity drove me to study how the incoming current moved; how each of the components worked; did some trial and error, gradually I figured out the problematic part, got it replaced by a new one from eBay and got its function back.

This process took long time because of the necessity in experimenting, components searching, waiting for the ordered parts, but this is how I learn and get to know the behaviors of the circuit board. Once in a meditation I had, the thoughts were runing everywhere as it used to be. I felt annoyed, frustrated and also felt shameful because of being unable to master the meditation skill. However, I sit through till the end-time of my one hour session was up.

I was struggling with the restless mind that time, when the annoyance and frustration were escalating higher and higher, and I was almost intending to skip the rest of the session, a new thought stumbled in. my mind screen displayed how I worked in the garage during the dawn time. The few cases I had done and most satisfied were replayed, suddenly that reviewing seemed to be telling me the analogy between the similarity of the circuit board and the biological brain. They are powered by different types of energy, but they are working in the same way. The circuit relys on the movement of each of the components to play their role to get their functions demonstrated. If somewhere in the circuit a chip or a small capcitor goes a bit out of order, the device will show its abnormality. This is absolutely the same as our brain. For an instance, I am a Parkinson's Disease patient. My symptoms such as involuntary tremor, slow momement, stiffness, emacinating and etc are indicating some part of my brain are having problem. This associating thoughts I experienced that day was indeed a kind of  enlightenment to me.

Since my literally enlightenment experience mentioned above, I always embrace a hope of locating a mechanism in my brain with me into the hour long meditation. Folks. I know you are yelling "don't sit with any attachnent, chap", but this reflects my eagerness in attaining a great leap in my quest of a controllable and duplicatable enlightenment experience through meditation practice.

--the end