Two Excel questions

Discussion in 'Mac Basics and Help' started by Smileyguy, Jul 1, 2008.

  1. Smileyguy macrumors 6502

    Joined:
    Nov 29, 2004
    #1
    Hi folks,

    I'm working on a fairly big spreadsheet of data on the mileage and carbon emissions of cars (It contains data on pretty much every car on the market here in Ireland, so it's pretty hefty). I need to do two things, and I'm wondering if Excel is up to the task.

    1. Currently the data is organised in a simple list alphabetically by car brand. However, I need to create a second list ranking the cars from the lowest emissions to the highest. The emission figures are in column G - is there a function I can use to automatically do this?

    I presume there is a solution to the above, the real reason for starting this thread though is the following....

    2. Based on its carbon emissions, every car in Ireland is assigned a 'carbon rating' of A, B, C, D, E or G, with A representing the most efficient and G representing the least. Each of these ratings corresponds to a particular rate of motor tax.

    For example:

    A = less than 120 grams of carbon per kilometre = €100 annual motor tax
    B = 121-140 grams of carbon per kilometre = €150 annual motor tax

    I have a column with an A-G figure for every car, but I also need to set up another column with the figure for motor tax beside it. Is there any way to do this automatically without going through thousands of cars and doing it manually?

    I hope my questions were clear-ish. Thanks in advance for any help...
     
  2. NAG macrumors 68030

    NAG

    Joined:
    Aug 6, 2003
    Location:
    /usr/local/apps/nag
    #2
    1. Select the columns you need to organize (all your data set).

    Data (menu) > Sort (menu item) > Select the primary column to organize by (emissions column) and then select the secondary (probably the name/maker column).

    2. You can set up nested if then statements but you can only do up to 7. You can also set up a user defined function that uses cases instead but I'm not sure if 2008 supports that. You can then just fill down and all the references will auto fill.
     
  3. JNB macrumors 604

    JNB

    Joined:
    Oct 7, 2004
    Location:
    In a Hell predominately of my own making
    #3
    First question: Data>Sort
     

    Attached Files:

  4. Smileyguy thread starter macrumors 6502

    Joined:
    Nov 29, 2004
    #4
    I've figured number 1 out no problem now, thanks a million guys.

    If you have the time, could you or anybody else shed any more light on how to go about number two? I'm using Office X I should have mentioned.

    Thanks very much.
     
  5. NAG macrumors 68030

    NAG

    Joined:
    Aug 6, 2003
    Location:
    /usr/local/apps/nag
    #5
    Here is a tutorial.

    NOTE: VBA support was dropped in Office 2008 (will probably be back in the next version of office though) so this method won't work if you upgrade to that version.
     
  6. GGJstudios macrumors Westmere

    GGJstudios

    Joined:
    May 16, 2008
    #6
    1. You can sort by any column you like. Simply select the range to be sorted, then Data > Sort.

    2. You can do nested IF statements, but if you have a large number of possibilities, it's better to to a =VLOOKUP with a table.
     
  7. swiftaw macrumors 603

    swiftaw

    Joined:
    Jan 31, 2005
    Location:
    Omaha, NE, USA
    #7
    Two ways:

    1. Use the IF function, nested a few times.

    If(ratingcell="A", tax for A, IF(ratingcell="B", tax for B, IF(ratingcell="C", tax for C, IF(ratingcell="D", tax for D, IF(ratingcell="E", tax for E, tax for G)

    2. Use a lookup table, then the LOOKUP function
    away from the main data, create two columns, one containing the letter A,B,C,D,E,G, one containing the corresponding tax value. Then, in the cell for each car, use the LOOKUP function. =LOOKUP(cell containing emission rating for current car, cells containing ratings in lookup table, cells containing taxes in lookup table)
     
  8. Smileyguy thread starter macrumors 6502

    Joined:
    Nov 29, 2004
    #8
    Okay, I've been trying the second problem using the LOOKUP solution suggested. I've created a simple A-G table with the corresponding tax values separate to the main data, and have used the function suggested by swiftaw:

    =LOOKUP(cell containing emission rating for current car, cells containing ratings in lookup table, cells containing taxes in lookup table)

    However, the problem I'm having is that for these three data values, I need the first to fill down as I do, but the second two to remain constant. As it stands, all three values automatically fill down, which means I can only use the function for six rows at a time.

    Thanks for all the help - this has been really useful
     
  9. swiftaw macrumors 603

    swiftaw

    Joined:
    Jan 31, 2005
    Location:
    Omaha, NE, USA
    #9
    Suppose that your lookup table is containd in the columns AA and AB, rows 1 thru 6.

    Suppose your rating for each car is stored in column D, and tax in column E, starting with row 2 and moving on down.

    So for cell E2. the entry would be =LOOKUP(D2, $AA$1:$AA$6, $AB$1:$AB$6)

    If you copy and past this down column E everything should work, the first entry (D2) would change to D3, D4, D5, etc as needed, but the other entries would remain fixed.
     
  10. Smileyguy thread starter macrumors 6502

    Joined:
    Nov 29, 2004
    #10
    Beautiful, thanks a zillion. This has saved me lots of time.

    It's been years since I've need to use Excel with any depth. I
     
  11. NAG macrumors 68030

    NAG

    Joined:
    Aug 6, 2003
    Location:
    /usr/local/apps/nag
    #11
    To setup a constant use $ symbol eg $A$1 sets the constant to column A row 1 (ie it will always reference cell A1 no matter what way you fill).
     
  12. Smileyguy thread starter macrumors 6502

    Joined:
    Nov 29, 2004
    #12
    It works beautifully. Thanks so much.


    Out of curiosity, can Numbers do all this stuff?

    One last quick question: Is it possible to use the LOOKUP function with ranges of values?

    For example, let's say I hadn't manually assigned each car an A to G rating based on its emissions, and I wanted to do this with LOOKUP. Lettered ratings correspond to emissions as follows:

    < 120g per kilometre: A
    120g-140g per kilometre: B
    121g-155g per kilometre: C
    156g-170g per kilometre: D

    and so on...

    Could I do this automatically? I've tried setting up a LOOKUP table where one column has the different ranges of emissions, and the other has the letters. I've tried representing the ranges as either 121:140 and 121...140, but neither has worked.

    It's not too important, but I'd be keen to know if there's a way to do this.
     
  13. logana macrumors 65816

    logana

    Joined:
    Feb 4, 2006
    Location:
    Scotland
    #13
    Yes - you have to set up the table so the values go from lowest at the top to highest at the bottom - but don't use ranges use the highest number in the range eg 120,140,155 etc and then Excel will look down the lookup table until it finds the number that is not lower than itself (if you follow - if the value is 139 it will go down the table as far as the 140 value and then pickup the value in the cell next to it in the table - that's the normal way to arrange the table).

    Great fun !!
     
  14. Consultant macrumors G5

    Consultant

    Joined:
    Jun 27, 2007
    #14
    If you are doing such work, you should take some courses. Even a basic, inexpensive class from community college covers all these and more.
     
  15. NAG macrumors 68030

    NAG

    Joined:
    Aug 6, 2003
    Location:
    /usr/local/apps/nag
    #15
    To answer the Numbers question, Numbers can do this (and in a similar way too, uses the same conventions).

    Numbers isn't quite as good as Excel for graphing though (little rough as well as no support for error bars).
     

Share This Page