Friday, August 26, 2011

Excel Nerd Post

So in my (unwanted) hiatus from my desktop computer, I've started to revamp my Excel sim tracking file.  It's ridiculous how much I love to track my sim and neighbourhood stat.  And of course as I go, I'm always learning something new.  But now, I'm running into some...problems.

1) Nothing on my main sims page can actually be sorted because once I try, it messes up the formulas that count the totals (like sims by age group, gender, interests, etc).

The purple columns are my hidden columns that do my calculations to report to my summary page.  When I try to sort, it messes up my calculation for the purple columns.  This is just one section of them for this page.  While trying to be efficient (and also heralding my laziness), I use the titles in the calculations as well as the source for drop down lists for other columns.  I want to be able to sort the other information but leave the purple columns alone.

2) Trying to get the maximum of a column and report who this maximum value belongs to.  Currently, it gives me the maximum value in the column and then the lowest name in the name column.

A - "=MAX(D:D)"  Makes perfect sense, look in column D and look for the largest number.  It does that part fine

B - "=LOOKUP(C90,D:D,J:J)" And apparently this is where I went wrong somehow.  I'm trying to tell it do is to look at the result from formula A, find that row, and report the first name in the row.  Liam quite obviously is not right.  He's only at 118 not 226.

The result I'm looking for is for the "LOOKUP" to pull up Laurel's name.  Maybe I need to find a better forumla but I don't know what it is yet.

3) Other issue, getting formulas to could things without including the values of dead sims.  I found a work around for now, seen in the first picture.  See the "ma" and "fa" results?  I just wanted to be able to count the amount of males alive and the amount females alive.

Other things I'd like to do:
  • On another page, pull out the sims who are in a specific career track so they can be nice and orderly on another page.  I have something similar working with the sim owned business, since apparently they now have a "Business License"
  • I'm really starting to consider having a search page but that seems like it will take quite some time to figure out how to make that work
  • Condense the amount of pages I have in the file.  Do I really need 15 pages?  I so need to learn how to condense.
  • Seriously tweek the much nicer version of the sims page with the pictures so that if I change the info on one page, it will automatically update the other one.

1 comment:

  1. For #1, try selecting the section you want sorted and sort all of that (including all the columns so your formulas stay intact)

    For #2, try using Vlookup or Hlookup instead of just lookup

    For #3, try a countif formula

    Hope that helps, I love excel :)

    ReplyDelete