How to Use Excel 2007 and Goal Seek to calculate what the minimum grade you need on a final exam

You need:

  • A list of all of your scores on previous assignments/tests (include your final as 0)
  • A list of point totals on all of your previous assignments/tests (include your final)
  • Excel

Step 1: Sum column A and Sum Column B. My formulas were =SUM(A2:A19) and =SUM(B2:B19)

Step 2: Divide using a formula. My formula was =A20/B20

You should now having something like this:

excel1

Step 3: Select the Data Tab. Select What if Analysis, and click Goal Seek

goalseek

Step 4: Fill in these values and click OK -

“Set Cell”: — this needs to be your divided total from Step 2.
“To value”: — I used .8 because I wanted a B, and that’s what our grading scale uses.
“By changing cell”: — select the final exam score cell that was set as 0.

goalseek2

Step 5: Study accordingly. Good luck!

Personal Budgeting with Mint.com

In January of 2008 I wrote about Mint.com.

I’ve had Mint quietly gathering data for the past 10 months and I’ve come up with a way to intelligently budget for 2009. The only work I’ve done is categorizing the transactions to make this whole exercise worth while.

Step 1: Export all Transactions from Mint into Excel. At the bottom of your “transactions” tab in Mint there’s a small link that says “export all N transactions”

Step 2: Make two additional worksheets. One labeled “Budget” and one labeled “Supplementary Information”. I also edited my initial worksheet to be called “transactions”.

The supplementary information was sort of a scratch pad that fed my “Budget” worksheet.

Step 3: Start calculating your average monthly expenses for relevant categories. For instance, what’s my average phone bill?

=AVERAGEIF(transactions!$F$2:$F$575, "*Phone*", transactions!$D$2:$D$575)

- You need to understand how excel’s averageif works.

- For once a month bills, this is easy… but say you want to know what your average monthly grocery expense. I visit the grocery store many times a month. This adds a layer of complexity.

=AVERAGEIF(transactions!F2:F575, "Groceries", transactions!D2:D575)

That’s my average grocery expense, but not my monthly. We’ll need to implement excel’s COUNTIF function as well.

=COUNTIF(transactions!F2:F575, "Groceries")

The formula for monthly grocery expenses I used is as follows:

((Average Grocery Transaction * Number of Grocery Transactions) / Months of Data Collected)

I also used this method for Restaurants/Fast Food and Wal*Mart visits. I grocery shop at Kroger and do other shopping at Wal*Mart for tracking purposes.

Step 4: Look at your monthly averages, and make up your mind.

I was alarmed at how much money eating at restaurants really costs. It never really gelled for me. I took it a step further and said there are 90 meals a month (breakfast / lunch / dinner – 30 days in a month) and found out my per meal expenditures for groceries and for restaurants. This also showed me another startling number – an average of how many meals a month I was eating at a restaurant.

I made a goal – eat out 5 times a month. This has an added cost of saying, I’ll have to eat groceries more. At a savings of $5 per meal, it’s worth it.

Follow

Get every new post delivered to your Inbox.