1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel formula help

Discussion in 'Business Applications' started by newbee56, Sep 29, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. newbee56

    newbee56 Thread Starter

    Joined:
    Sep 29, 2006
    Messages:
    3
    I am looking to check and entire row of data up to (200 entries)
    For number that is less than one cell and greater then another cell
    then enter the value of the cell if yes and skip if no creating a list

    I am using excel 2000
    Any ideas? :cool:
     
  2. maxflia10

    maxflia10

    Joined:
    Feb 24, 2003
    Messages:
    331
    I don't quite understand the enter the of the cell if yes and skip if no creating a list part, but

    =IF(AND(A1 > $C$1,A1 < $C$2),A1,"")

    Where C1 and C2 house the criteira and copied down.
     
  3. newbee56

    newbee56 Thread Starter

    Joined:
    Sep 29, 2006
    Messages:
    3
    Thank you that was very help full ... now the issue is... I would like to list the frist 6 that meet that critera?? I will need to run it several times using two different values
    i.e.
    More than 3 but more less than 3.45 then more than 3.45 but less than 4.5 and so on

    Thank you
     
  4. djangojazz

    djangojazz

    Joined:
    Apr 11, 2006
    Messages:
    301
    =IF(A1:A6="criteria",>3,IF(A1:A6="criteria",<3.45,0))

    You can imbed if,then,else functions I believe up to 7 functions. It would be eaiser to help if you show what you are trying to do.
     
  5. blaqDeaph

    blaqDeaph

    Joined:
    Nov 22, 2005
    Messages:
    869
    Yup, but I heard that they were gonna remove the 7 level limit in Office2007. Just to be clear, it's not limited to 7 functions, but 7 levels.

    IF(IF(IF(IF(IF(IF(IF( is 7 levels. The number of functions you can have is much more.
     
  6. djangojazz

    djangojazz

    Joined:
    Apr 11, 2006
    Messages:
    301
    Quite right, I was trained up to Excel 2003. I'm beat testing 2007, I have never pushed anything past anything beyond 6 embedded functions or 7 levels either myself but be by my guess for a basic grade score test :

    assuming you slot cell A2 with a grade from a 0 to 100 score:

    =If(A2=100,"A+",IF(A2>90,"A",If(A2>80,"B",IF(A2>70,"C",If(A2>60,"D",IF(A2>60,"F",IF(A2>50,"very bad",If(A2>40,"incredibly bad",IF(A2>30,"you're not even trying",IF(A2>20,"You're pretty dumb, and that's generous",IF(A2<=19,"Just give up","")))))))))))

    I have heard the the limit was 7 but of course that's what I was told, not what I tried. Try this one and tell me if you have a function that you ACTUALLY NEED more than 7 embedded without doing a macro or higher level function. I'm sure there are needs our there to go that high but at that point I'm sure there are macro's or other functions that must be able to do what you are seeking faster.

    Generally I'm a pretty simple statician, if I ever embed different fuctions together it's usually only 2 or 3 levels deep at most. More power to those Exel wiz's that can go 7 layers deep with 2 or 3 functions per level. At that point I just have to wonder: "What's any practical use in that? Couldn't you have just done that with a Macro or VB app?"
     
  7. Gurnerworld

    Gurnerworld

    Joined:
    Dec 18, 2000
    Messages:
    124
    Hi,

    Rather than creating long nested IF statements, I'd consider using the VLOOKUP function. You can add as many different arguments as you like and it's much easier to do.

    For a simple explanation:

    If you've got a column of scores in column A and you want to add a grade in column B, you need to do the following.

    Create a separate table (for example in cells D3:E6) which has the score thresholds in one column and the appropriate grades in the next column. In this case if your score thresholds were 0-40 = fail, 41-75 = pass and 76-100 = Distintion, then your table would look like this:

    0 Fail
    41 Pass
    76 Distinction

    The VLOOKUP formula will look for the cell you're evaluating (say A1 in this case) and try to find it in the first column of the lookup table above. When it finds it, it will return the value from whichever column of your table that you want (in this case we want the value from column 2).

    The formula would look like this:

    =VLOOKUP(A1,$D$3:$E$6$,2)

    That's quite difficult to explain without using an example, but if you want more info, then just let me know.
     
  8. newbee56

    newbee56 Thread Starter

    Joined:
    Sep 29, 2006
    Messages:
    3
    Ok this is great and using the grade analogy say you have 200 students and you want to list only
    the 6 students who have the gpa of 3.5 to 4.0 no more than 6 and
    the 6 students that have the gpa of 2.8- to 3.5 and
    the 6 students that have the gpa of 2.0 to 2.8

    so you would have 3 colums of 6 names

    any ideas?
     
  9. djangojazz

    djangojazz

    Joined:
    Apr 11, 2006
    Messages:
    301
    Sure if column B is the student column and their are 200 students all with different grades in column C you could merely do an "auto filter" to get drop down arrows for that. To do it another way with a calculation you could do a macro I think would be eaiser than doing multiple forumulas. You would basically want to specify a row for criteria whereas a formula is good for doing a "this cell, this relation, equals this" kind of thing. When you want to do a whole new set of data that is relational a macro is good.

    I'm just good at the formulas I use but it sounds like an autofilter would be the easiest: "Select all the columns you want, click "data" tab at tab, select auto filter, buttons will show selections of criteria"(If you want to make it even easier you could make a 3rd column for mass listing groups so that 2.8-3.5 and 2.0-2.8 Excel sees as an actual cell identity. So... instead of having to look for 2.8, 2.7, etc. There would only be the choices you specify. Try it out or try a macro.
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/505411

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice