+ Reply to Thread
Results 1 to 5 of 5

IF SMALL function to ignore a date within a rage of other numbers

  1. #1
    Registered User
    Join Date
    08-17-2014
    Location
    NSW, Australia
    MS-Off Ver
    Excel 2010
    Posts
    27

    IF SMALL function to ignore a date within a rage of other numbers

    Hi,
    I have a spreadsheet for my workplace to search a range of suppliers to return which supplier has the cheapest price for the one product. I have a range of numbers (dollars) in a range (lets say H2:N2), but there are also dates within that range for the day that the price was quoted. What I am trying to create is a formula to show the smallest (cheapest) and second smallest (second cheapest) prices available. I have been using the SMALL function which works great for the cheapest supplier. However every now and then, there are no second smallest numbers. In other words, I haven't received any other pricing from any other suppliers yet. So the formula returns dates that are within that range. In short, I know how to get the cheapest value. But if the next cheapest value happens to be a date, then return "STC" (still to check)? See example spreadheet (Problem exists in E3).Example.xlsx

  2. #2
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: IF SMALL function to ignore a date within a rage of other numbers

    In E2 apply the below formula to be entered with ctrl+shift+enter

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Click just below left if it helps, Boo?ath?

  3. #3
    Registered User
    Join Date
    08-17-2014
    Location
    NSW, Australia
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: IF SMALL function to ignore a date within a rage of other numbers

    That works pretty well. But how would I change the formula, so that if I later added a price in one of the other suppliers, it would change to that price?

  4. #4
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: IF SMALL function to ignore a date within a rage of other numbers

    Yes it would change

  5. #5
    Registered User
    Join Date
    08-17-2014
    Location
    NSW, Australia
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: IF SMALL function to ignore a date within a rage of other numbers

    Nah it didn't work. I just chucked in a few more dollar figures and it didn't change! It stayed as "STC". How would I go about fixing that?
    Last edited by jamiegfinch; 09-22-2014 at 07:09 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Duplicates within a date rage
    By sori4289 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-11-2013, 05:48 AM
  2. Sum rage = criteria range in sumifs function
    By mj1118888 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-15-2013, 12:25 PM
  3. [SOLVED] How To Get A Date Rage Profit /Loss Figure
    By jrusso in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 11-12-2012, 07:50 PM
  4. Replies: 12
    Last Post: 04-24-2011, 05:45 PM
  5. Sum by date rage using multiple sheets
    By Michael in forum Excel General
    Replies: 3
    Last Post: 03-24-2006, 07:51 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1