Excel Functions and Formulas
I got stuck on excel formulas and functions the other day, it took me some time to get what I wanted. I have a little knowledge of "single entry book keeping", I am saying this term over my head but, basically what I wanted accomplished was, having a column for income and the other for expenses, and another column to show the updated balance. Money in this updated balance column is subtracted when I make an entry in the expenses column and added if an entry is made in the income column. It was a learning curve for me and too I was also using Google Spreadsheets instead of MS Excel.
=IF(OR(A3,B3),IF(A3,C2+A3,IF(B3,C2-B3)))
What intended above. If an entry is made in A3 OR B3, this makes the statement true which then runs the second arguement of the initial IF statement IF(A3,C2+A3,IF(B3,C2-B3). If the entry is made in A3, then add the value to C2. If the entry is made in B3, subtract the value from C2.
Some background knowledge on OR|IF functions. A function in programming is a block of code that can be reused. We blindly use the function without paying attention to the code that runs under the hood and think that it is magic that happens.These functions are built into excel but we can also build our own custom functions.
The OR function takes more than one arguments that are the logics or conditions that need to be tested when the function is called. If any of the logics evaluate to true , a true value is given or else false is given.
领英推荐
OR(1>3,10>4) , this will give TRUE
OR(1<0,2<1,3<2), this will give FALSE
The IF functions takes in 3 arguements, the condition or logic, what is run if the logic is true and what is run if the logic is false.
IF(1>2,'Runs this if true','Runs this if false'), the result of calling this function is 'Run this if false' since the condition is false.
For the overall formula above, I had to nest the functions to come up with what I wanted.