Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Excel IF function for a logical question containing a range of named values?

  • 26-11-2010 10:49am
    #1
    ailsybird
    Registered Users, Registered Users 2 Posts: 17 ✭


    Hi,
    I've got a huge database (about 37000 rows, 25 columns) I've been working on for the last few weeks, but am stuck on how to do the following -

    Each row corresponds to an individual client record. I need to group the rows by the data in column D. Data in column D is logged as specific items (for example apples, pears, cabbage, milk). However I need to group this by one of four types of purchase (vegetables, condiments, fruit, other).

    In another sheet I have all the groupings ie. apples, pears etc ('Sheet2'!A1:A20) are fruit, cabbage, carrots etc ('Sheet2'!A21:A40) and so on.

    I tried to use a nested IF function eg
    IF(D1='Sheet2'!A1:A20,"fruit",IF(D1='Sheet2'!A21:A40,"vegetable",IF(D1='Sheet2'!A41:A60,"condiment",IF(D1='Sheet2'!A61:A80,"other"))))
    but it gives only #VALUE!

    I checked that it wasn't my annotation by inserting rather exact cell references (eg 'Sheet2'!A16) and this works fine, it's only when you're asking it to look at a range of data that the function fails.

    Any ideas?
    Thanks
    ailín
    Tagged:


Comments

  • #2
    bnt
    Registered Users, Registered Users 2 Posts: 13,076 ✭✭✭✭bnt


    First of all, you're right that the function is failing because you're trying to do an IF comparison on a range, and I'm pretty sure that it needs to be on a single cell or value. The best way I know to do what you want is to use a VLOOKUP function.

    In your example, since you have 'Sheet2'!A1:A20 with the fruits, you can put "fruit" in each cell in 'Sheet2'!B1:B20, and then the same for the vegetables in 'Sheet2'!B21:B40, and so on down. Then a function like this will return "fruit" or "vegetable" etc.
    =VLOOKUP(D1,Sheet2!$A$1:$B$80,2,FALSE)

    Notes: the "FALSE" means "only exact matches" i.e. it has to match exactly or it will return #N/A. If you leave that out, it will return approximate matches, which you might not want - your choice. The $ signs will ensure that the references to the table don't change if you copy that cell down the table.

    PS: is a tomato a fruit or a vegetable?

    You are the type of what the age is searching for, and what it is afraid it has found. I am so glad that you have never done anything, never carved a statue, or painted a picture, or produced anything outside of yourself! Life has been your art. You have set yourself to music. Your days are your sonnets.

    ―Oscar Wilde predicting Social Media, in The Picture of Dorian Gray



  • #3
    rossc007
    Registered Users, Registered Users 2 Posts: 627 ✭✭✭rossc007


    Hi Ailin,

    Not sure why your using Excel at all, cant you just run a query to do the grouping for you?

    Do the Specific Items(apples, pears etc) have a relational link to the purchase type(vegetables, fruit etc)?


  • #4
    bnt
    Registered Users, Registered Users 2 Posts: 13,076 ✭✭✭✭bnt


    The OP didn't say that the data was extracted from another database, from reading the question I thought that Excel was the database. Well, Excel works just fine as a "flat file" database, if you ask me. It has Grouping facilities built in (Data menu/tab), it's just that the criteria for grouping need to be in the table before you can do that, and solving this problem can fill that gap. ;)

    You are the type of what the age is searching for, and what it is afraid it has found. I am so glad that you have never done anything, never carved a statue, or painted a picture, or produced anything outside of yourself! Life has been your art. You have set yourself to music. Your days are your sonnets.

    ―Oscar Wilde predicting Social Media, in The Picture of Dorian Gray



  • #5
    ailsybird
    Registered Users, Registered Users 2 Posts: 17 ✭ailsybird


    Thanks bnt, VLOOKUP is exactly what I was looking for. Works absolutely beautifully!

    rossc007, yes the datasheet has been in Excel format all along, I'm kinda stuck working with it for the forseeable future.

    That old tomato issue...ambiguous vegetability-it goes in salads and on pizza, but yet classed as a fruit. It's a metro-vegetable. :)


Advertisement