Results 1 to 5 of 5

Thread: Logical forumlae in Excel

  1. #1
    Environ'mentalist Zadock's Avatar
    Join Date
    Nov 2007
    Location
    Pembroke
    Posts
    1,386
    Thanks
    104
    Thanked
    101 times in 83 posts
    • Zadock's system
      • Motherboard:
      • Asus Z77
      • CPU:
      • Intel Core i5 3570K
      • Memory:
      • Corsair Corsair Vengeance 8Gb (1600Mhz)
      • Storage:
      • Samsung 500GB HD501LJ Spinpoint T, SATA300, 7200 rpm
      • Graphics card(s):
      • XFX HD6950 2GB
      • PSU:
      • Corsair 520W HX Series Modular Powersupply
      • Case:
      • Antec Nine Hundred
      • Operating System:
      • Windows 7 64 HP
      • Monitor(s):
      • Samsung 27" LED
      • Internet:
      • BT

    Logical forumlae in Excel

    I'm attempting to create a forumlae in excel that will automatically display the following words according to the result of a calculation.

    <0.5 = poor
    0.5 – 0.59 = below average
    0.6 – 0.69 = average
    0.7 – 0.79 = good
    > 0.8 = excellent

    My first try was this:

    =IF(L2<=0.5,"Poor",IF(L2<0.6,"Below Average",IF(L2>0.6,"Average",IF(L2>=0.7,"Good",IF(L2>=0.8,"Excellent")))))

    which obviously doesn't work but this is the kind of line I'm following.

    Any ideas or suggestions greatly appreciated, I'm new to excel's logic functions.

    Is it possible to create a logical forumlae that uses the score ranges like the ones above?

    Cheers

    P
    ___________________________________________________________

    System 1: Case: Antec 900 Motherboard: Asus Z77 CPU: Core i5 3570K @3.4GHz RAM:8Gb DDR3 1600Mhz GFX: XFX AMD Radeon 6950 2Gb (Cayman) HDD: Samsung Spinpoint 500GB O/S: Windows 7 64bit Home Premium

    System 2: Lenovo Ideapad S205: AMD E350 APU (1.6Ghz), 2Gb 1066Mhz DDR3, Radeon HD6310 (integrated), 250Gb HDD, Windows 7 64Bit Home Premium

    System 3:Asus Eee 901: 12Gb Ubuntu 10.10 Gnome Desktop edition


  2. #2
    Master Browser PeteSmith's Avatar
    Join Date
    Aug 2004
    Location
    LV-426
    Posts
    661
    Thanks
    44
    Thanked
    45 times in 42 posts
    • PeteSmith's system
      • Motherboard:
      • ABIT IP35 Pro
      • CPU:
      • Q6600 G0
      • Memory:
      • 4x1GB 8500 Crucial Ballistix
      • Storage:
      • System: Crucial 128GB M4 SSD Storage: 2x500GB WD5000AAKS
      • Graphics card(s):
      • GTX 650
      • PSU:
      • Corsair HX 620
      • Case:
      • Antec P182
      • Operating System:
      • Windows 10
      • Monitor(s):
      • Dell 2407WFP-HC

    Re: Logical forumlae in Excel

    If you want to use a range use the OR function in your formula, eg =IF(OR(L2>0.5,L2<0.59),"below average") Then continue to nest the other IF statements. It won't be pretty but it should work.
    Main PC : Abit IP35-Pro | Q6600 G0 @ 3GHz | 4x1GB Crucial Ballistix 8500 | MSI N460 GTX HAWX | 1 x Crucial M4 128GB SSD | Antec 182 | Corsair HX620 | Dell 2407WFP-HC | Windows 10 x64
    Server : HP MicroServer N40L | 16 GB RAM | 4 X 1 GB Samsung Spinpoint F1 | ESXi 6.0 NAS : Synology DS-408 - 4 x 3GB WD RED HTPC : Acer Revo RL70 | LibreElec

  3. Received thanks from:

    Zadock (19-11-2008)

  4. #3
    I R Toff Pandi! TAKTAK's Avatar
    Join Date
    Mar 2008
    Location
    Vergon6
    Posts
    7,449
    Thanks
    549
    Thanked
    1,012 times in 747 posts
    • TAKTAK's system
      • Motherboard:
      • ASUS ROG STRIX B450-F GAMING
      • CPU:
      • Ryzen 7 3700X
      • Memory:
      • 16GB Corsair Vengeance LPX 3200MHz
      • Storage:
      • 500GB Samsung 970 EVO
      • Graphics card(s):
      • 5700 XT 50th Anniversary
      • PSU:
      • Be Quiet SFX-L 600W
      • Case:
      • Lian Li PC-O11 Mini
      • Operating System:
      • Windows 10
      • Monitor(s):
      • LG Ultrawide
      • Internet:
      • 200Mb FTTP

    Re: Logical forumlae in Excel

    why not use a vlookup command?
    Post Counts and Other Rewards, Rules, Folding@Home, Fans: Push vs Pull vs Push-Pull, Corsair PSU OEMs.

    Quote Originally Posted by razer121 View Post
    Would you like me to enter you? it would be my pleasure
    TAKTAK.co.uk

  5. #4
    48:45:58:55:53
    Join Date
    Feb 2007
    Location
    Durham
    Posts
    218
    Thanks
    3
    Thanked
    17 times in 5 posts
    • Mousemat's system
      • Motherboard:
      • MSI P965 NEO-F
      • CPU:
      • Intel C2D E6320
      • Memory:
      • 2x Patriot 1GB PC6400
      • Storage:
      • 2.5TB Samsung
      • Graphics card(s):
      • ATi Radeon X1950 PRO
      • PSU:
      • 480W XClio-SCF
      • Case:
      • Antec P182
      • Operating System:
      • Windows 7
      • Monitor(s):
      • LG L226WTQ
      • Internet:
      • 24Mbs ADSL2+

    Re: Logical forumlae in Excel

    Your problem is that you changed from using < values to > values, so you get a match on more than one.


    =IF(L2<=0.5,"Poor",IF(L2<0.6,"Below Average",IF(L2<0.7,"Average",IF(L2<0.8,"Good",IF(L2>=0.8,"Excellent","Value Not Found")))))


    There's the corrected version, but setting up a table of values and using vlookup would be better

  6. #5
    Environ'mentalist Zadock's Avatar
    Join Date
    Nov 2007
    Location
    Pembroke
    Posts
    1,386
    Thanks
    104
    Thanked
    101 times in 83 posts
    • Zadock's system
      • Motherboard:
      • Asus Z77
      • CPU:
      • Intel Core i5 3570K
      • Memory:
      • Corsair Corsair Vengeance 8Gb (1600Mhz)
      • Storage:
      • Samsung 500GB HD501LJ Spinpoint T, SATA300, 7200 rpm
      • Graphics card(s):
      • XFX HD6950 2GB
      • PSU:
      • Corsair 520W HX Series Modular Powersupply
      • Case:
      • Antec Nine Hundred
      • Operating System:
      • Windows 7 64 HP
      • Monitor(s):
      • Samsung 27" LED
      • Internet:
      • BT

    Re: Logical forumlae in Excel

    Cheers folks will try your suggestions... I usually just do data analysis in excel so anythign more than that is a learning curve.

    I'll let you know how I get on.

    Update: Thanks to Mousemat, your forumla worked a treat, and thanks to the rest of the folks that contributed to opening my eyes to the useful functions of excel that I've over looked. You can always count on Hexus for knowledgable helpful folks. Cheers!
    Last edited by Zadock; 20-11-2008 at 11:20 AM. Reason: Update
    ___________________________________________________________

    System 1: Case: Antec 900 Motherboard: Asus Z77 CPU: Core i5 3570K @3.4GHz RAM:8Gb DDR3 1600Mhz GFX: XFX AMD Radeon 6950 2Gb (Cayman) HDD: Samsung Spinpoint 500GB O/S: Windows 7 64bit Home Premium

    System 2: Lenovo Ideapad S205: AMD E350 APU (1.6Ghz), 2Gb 1066Mhz DDR3, Radeon HD6310 (integrated), 250Gb HDD, Windows 7 64Bit Home Premium

    System 3:Asus Eee 901: 12Gb Ubuntu 10.10 Gnome Desktop edition


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel Vlookup not working (sort of)
    By piggeh in forum Help! Quick Relief From Tech Headaches
    Replies: 9
    Last Post: 03-09-2010, 04:35 PM
  2. Finding new data from 2 excel files?
    By menthel in forum Software
    Replies: 0
    Last Post: 03-04-2008, 09:51 AM
  3. .NET Reading Multiple Excel Files
    By Shadow_101 in forum Software
    Replies: 5
    Last Post: 23-11-2007, 03:22 PM
  4. Excel + Database + Automated
    By Kezzer in forum Software
    Replies: 4
    Last Post: 29-03-2007, 04:54 PM
  5. Replies: 6
    Last Post: 12-08-2005, 08:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •