Results 1 to 3 of 3

Thread: Excel Problem - finding minimum value excluding errors or zeroes

  1. #1
    Senior Member
    Join Date
    Dec 2004
    Posts
    360
    Thanks
    27
    Thanked
    2 times in 2 posts
    • piggeh's system
      • Motherboard:
      • ASUS AiLife IP35
      • CPU:
      • Intel Q6600 G0
      • Memory:
      • OCZ 2x2GB DDR2 (5-4-4-18)
      • Storage:
      • 500GB Spinpoint
      • Graphics card(s):
      • Inno3D 8800GT
      • PSU:
      • Corsair HX520W
      • Case:
      • Antec 900 Case
      • Operating System:
      • Vista 64
      • Monitor(s):
      • Samsung 24" SM2494HS
      • Internet:
      • Virgin Cable

    Excel Problem - finding minimum value excluding errors or zeroes

    I have a spreadsheet which is a glorified version of the following



    I want to take the lowest value from the green columns and place in the orange section to the right, excluding any errors, blanks or zero values. Then, in the red section I want excel to retrieve the supplier (name section in blue) that the lowest price corresponds to.

    Any ideas please?! I tried nested if statements but run out of room (excel only allows 7 nested statements I believe) - there's quite a lot of suppliers and over 2000 rows of data.


  2. #2
    unapologetic apologist
    Join Date
    Nov 2005
    Location
    UK
    Posts
    1,954
    Thanks
    363
    Thanked
    275 times in 146 posts

    Re: Excel Problem - finding minimum value excluding errors or zeroes

    are the prices static values? if so, you could use an array function to combine the IF, MIN and ISERROR functions to evaluate all the green cells, and only extract the lowest

    {=MIN(IF(ISERROR(C3:J3)=FALSE,C3:J3))}

    note curly brackets on either end, otherwise it won't work. write calculation as normal, but press CTRL SHIFT ENTER to finish it (rather than just ENTER). Am assuming this is new to you, so forgive my assumption if it's incorrect.

    if the prices are formulas themselves (therefore sometimes creating N/A errors), you could use nested IFs with ISERROR function to hide all zeros and error messages in the price section, then the MIN function would work fine in the orange area

    2) you could then use a hlookup to return the appropriate supplier, but the layout would require some tinkering - it looks like you have some merged cells in the blue section, which doesn't help, and duplicating the name in row 15 would make it easier without compromising the appearance much.

    anyway, that's just off the top of my head.

    EDIT: been mucking around, trying to avoid working, and come up with an alternative solution to part 2, that doesn't require any editing for your existing table, but would work from an external vlookup table.

    Last edited by fuddam; 02-10-2008 at 04:27 PM.
    One can never stop saying Thank You

  3. Received thanks from:

    piggeh (03-10-2008)

  4. #3
    Senior Member
    Join Date
    Dec 2004
    Posts
    360
    Thanks
    27
    Thanked
    2 times in 2 posts
    • piggeh's system
      • Motherboard:
      • ASUS AiLife IP35
      • CPU:
      • Intel Q6600 G0
      • Memory:
      • OCZ 2x2GB DDR2 (5-4-4-18)
      • Storage:
      • 500GB Spinpoint
      • Graphics card(s):
      • Inno3D 8800GT
      • PSU:
      • Corsair HX520W
      • Case:
      • Antec 900 Case
      • Operating System:
      • Vista 64
      • Monitor(s):
      • Samsung 24" SM2494HS
      • Internet:
      • Virgin Cable

    Re: Excel Problem - finding minimum value excluding errors or zeroes

    Thanks fuddam - I get most prices up although zeroes are still taking an affect - by hiding do you mean substitute it with ""? At the moment I stripped some errors out although was trying to keep them in as its what the suppliers gave to me (would rather discuss with them their own data rather than stuff adjusted).

    Still - can get things moving with the above, many thanks for your help.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Finding new data from 2 excel files?
    By menthel in forum Software
    Replies: 0
    Last Post: 03-04-2008, 09:51 AM
  2. I have a very odd problem with my new build
    By Andaho in forum SCAN.care@HEXUS
    Replies: 17
    Last Post: 22-11-2007, 10:53 AM
  3. Quick, boring, excel noob problem
    By klarrix in forum General Discussion
    Replies: 4
    Last Post: 19-07-2004, 07:04 PM
  4. Excel Form Label Errors
    By Tomahawk in forum Software
    Replies: 4
    Last Post: 08-04-2004, 03:41 PM

Posting Permissions

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