Archive for August, 2011

Using Excel Sumif or Vlookup Functions

Excel function tutorials

One of the questions that we have been receiving quite a bit in recent months has been regarding the difference between the Sumif and Vlookup functions in excel. They are fairly different but there is some confusion regarding their use. Let’s start off by explaining both:

Sumif function: Is used to sum the quantity (or any other data in a separate column) for a specific criteria. We already wrote about this function, you can find out more about the sumif and countif functions
Vlookup function: Is used to find a reference associated to a specific criteria

These are the formulas used:

=SUMIF(A:A,”Strawberry”,B:B)
=VLOOKUP(“Strawberry”,A:B,2,FALSE)

When do you use each one?

Vlookup

-If you only have one sample for each “fruit”
-If the data you are looking for is not a number
-If you want to easily spot a “missing fruit” – vlookup will give you a “N/A” result when a vlookup returns no results

Sumif

-If you have more than one sample
-If you want to avoid getting “N/A” errors that will screw up calculations in your spreadsheet

Does that explain it well enough?

How To Use The Offset Function In Excel

Excel function tutorials

If you are like me, you have a few excel spreadsheets that are becoming larger by the day. It can become very complex to keep up and you will often use functions such as the “match” function that we discussed last week. Another function that can help when you are using reference functions is the offset function. We used it in last week’s post but it was done rather quickly. Here is a better example:

As you can see, if each product has 2 regions, how could you find what those are and what the prices are? A vlookup would not work because of the fact that the result is on a different line. In order to proceed, we will use the match function but also the offset one.

Here is the formula for the offset function:

=OFFSET(reference, rows, cols, height, width)

In this case, we will need to find “orange” through the match function and then use the offset to find both locations. This could work on a much larger excel.

Where is it? =MATCH(“orange”,A:A,0)
Location #1 =OFFSET(A1,MATCH(“orange”,A:A,0),1)
Location #2 =OFFSET(A1,MATCH(“orange”,A:A,0)+1,1)

When you are unsure about how a file will evolve, if columns or ranges will be moved around, using the match or offset function will make it much easier to maintain the files over time.

Wall Street Panic Attack

Current events, Personal finance

This week, the global economies survived the United States debt fiasco only for markets to plunge off the proverbial cliff this Thursday.  Most markets, including the United States’ S&P 500 and the Dow Jones Industrial average, were down 3-5%.  Talks of a double-dip recession or an outright depression increased.  But how bad are things, really?  And what are the “safest” investments right now?  Finally, what will happen now that Standard and Poor’s has downgraded American Treasuries/debt?

The Current Mood 

A “contagian” of systemic financial trouble and a strong likelihood of default has affected most of the European Union.  But while traveling through the U.K., and Germany last week, two of the admittedly strongest economic European nations, I did not witness the signs of increasing poverty that I often do even on the eastern seaboard of the United States.  There were very few vacant shops, less homeless people, and nicer cars on the road.  Of course, this is a very subjective perspective.  European countries as diverse as Portugal, Italy, Ireland, and of course Greece, are on the road to default or knocking at the door.

Back home, in the United States, the once unthinkable has happened: one of the major credit reporting agencies, Standard and Poors, has dropped the U.S.A’s always sterling AAA credit rating down to AA+.   The real question, of course, is what affect does that have on the United States and the global economy?

Many people are aware that a downgrade in credit rating means that the United States will be seen as a riskier debtor, and that the U.S., will therefore have to pay a higher interest rate when borrowing.  There remains a fear that China will sell all of its treasury bonds and call for its debt–a move that could equal default for the United States.  But while researching this issue, I learned of another, equally frightful possibility…default by internal investors.

What I mean by “default by internal investors,” is this: most mutual funds and other investment vehicles require that most or all of the investments are contained in AAA investments.  Now that the United States has been downgraded by the Standard and Poor’s (with the other two agencies likely to follow suit)– this too could create a run on treasury bills/other U.S. investments that could lead to default. 

This is not dissimilar, perhaps, from what occurred with the hyperinflation of the Weimar Republic, in Germany.  The reparations due from World War I were demanded by the victors, and this caused a period of rapid–yes even extreme, inflation.   Of course, the United States’ greatest debtor, China, is none too happy with the downgrade either.

Considering the fact that United States employment rates hover above 9%, and that other major indicators such as the housing market remain stagnant at best; it is clear that the United States–and global economies are in a very difficult situation right now.

How to React 

I am not a financial expert, and I truly believe that how one should react is entirely tied to their own current situation.  What I am seeing from many investors I talk to or read about, however,  is that they are running to hard assets, such as gold, silver, real estate, or other commodities.  For this reason, gold and silver (although certainly not real estate), are at all-time highs.

When there is a widespread, international economic crises, it’s difficult to find any “safe investment.”  When a major agency such as the Standard and Poor’s downgrades the United States debt–causing the supposedly “safest” of investment (U.S. Treasuries) to be called into question, then it’s certainly one of the toughest times in recent history to know where to put your savings.

Conclusion 

It’s clear that the world-wide economic crises is not going to be resolved any time soon.  Since 2008, we have been dealing with what will likely later be referred to in history books as a Depression.  It is entirely possible that the future will be even more strained than the present.

How are you dealing with the latest bad news?  How are you investing your monies?  Thus far, I’m staying the course.  Will that be, in hindsight, a terrible mistake?

Let me know your thoughts on the global debt crises/systemic global financial issues.

Also, if anything I have written in this article is not factually correct, or if I missed anything in my research, please leave a comment to let me know.  Thanks.

 

 

 

 

 

 

 

 

 

Personal Finance Advice I Hate

Personal finance

I majored in English, not finance.  My graduate school degree only touches upon finance.  Other than the fact that I’ve read hundreds of personal finance books and written some 500+ posts on personal finance topics, I have no true expertise.  In other words, you might disagree with my list below.  That’s fine, because again, I am no expert.  Everyone’s finances are different, which is why the term “personal” comes first.  But with those caveats in mind, below is a list of some of the personal finance advice I hate.

1. More Education is Always a Good Thing – No!  It’s Not!  I know you don’t want to hear it, but from a purely financial point of view, many students today are spending more than their educations are worth.  I don’t need to go through the list of low ROI majors, because they’re well known.  Yet, despite this knowledge; parents, guidance counselors, family, and friends, continue with their propensity for encouraging prospective students to “follow their dreams.”  What good is following your dream into six figure student loan debt?  In my opinion, the entire education system in the United States needs to be re-examined.

2. Housing Will Always Come Back/Real Estate is a Great “Investment” – I suppose over a long enough period of time this is true, but when I look at the housing market, I can’t be anything but bearish.  Not just in the present moment, but for the forseeable future.  Yet I keep hearing people say the same thing: “I need to buy a house while the market is low.  It’s sure to come back!”  Well, says who?  Isn’t it called a “market correction” for a reason?  The economic and population trends appear to support future real estate doom.  When you factor in the interest, costs, repairs, and likely rising property taxes (despite decreased property values), then how can anyone really feel comfortable with real estate as an “investment”?

3. You Should Help Out Your Adult Kids When They’re Financially Struggling – Too many twenty and thirty-somethings today have been coddled by their parents their whole lives.  Continued coddling into adulthood creates a lack of independence and, in some cases, an inability to stand on one’s own two feet.  Maybe the only way to really learn how to be self-sufficient is to be forced to be self-sufficient.  In the best-selling book “The Millionaire Next Door,” this concept is coined as “Affirmative Action: Family Style.”  There will always be true emergencies, but be careful of this slippery slope.

4. Applying Too Much Psychology to Personal Finance (For Example: the Dave Ramsey SnowBall Method) – Dave Ramsey is famous for coining the “Snowball Method” of Debt repayment.  The basic idea is that you take all of your debts, and then start repaying the smallest balance first, pretty much regardless of the interest rates.  Simple math would say, however, that the most effective method is to begin repaying the highest interest rate debts first– but Ramsey’s theory is that people need “small wins” to stay motivated.  For me, the math is what it is.  Psychology in personal finance has its place, but not when it’s costing me money.

5. Unpaid Internships Are Often a Good Idea – The idea of working for another person/entity for free, outside of “do-gooder” volunteerism makes my stomach churn.  I know it’s required in certain fields (*cough* education), but the other day I read that one of the state’s Attorney’s General’s Office is seeking applicants for a one-year unpaid commitment from recent law graduates.  And of course I’m sure you have some idiot law school counselors saying things like: “well, that would look good on your resume.”  Perhaps that’s true, but at what cost?  At the cost of bringing down the entire system?

6. You Don’t Need Life Insurance Until You Have Children – I hear this said all the time as a blanket statement.  The fact of the matter is, certain married couples may wish/need to have life insurance.  This is true whether they have children or not.

7. It’s Impossible to Succeed In This Economy – I think that if we all keep trying our best, keep our options open, and stay within ourselves, that we have a chance to succeed–even in this terrible economy.  I don’t want to be told otherwise by anyone.  Particularly not some talking head millionaires on television.

Conclusion

What’s your personal finance pet peeves?  Who disagrees with mine?