+ Reply to Thread
Results 1 to 5 of 5

Which formula would I use to check numbers are ascending?

  1. #1
    Registered User
    Join Date
    10-20-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Which formula would I use to check numbers are ascending?

    I am trying to create a formula to check that a list of numbers is ascending. For example, over a number of years, I might record the number of, say, letters I post. I want a formula to check that I am sending increasing numbers every year, returning a value of YES or NO. I have tried an IF formula but am told I have too many formulae (Excel 2003).

    Is there a way around this problem?

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Which formula would I use to check numbers are ascending?

    Not entirely clear - a sample file might help

    It might prove to be the case that something like:

    Please Login or Register  to view this content.
    would work - where A1:A4 holds your list
    (a1 being first entry and A4 being last with expectation that each value should be bigger than (or equal to) the last)

    Modify ranges to suit but keep as lean as possible when using SUMPRODUCT (it's not efficient) - entire column references are not permitted in XL2003

  3. #3
    Registered User
    Join Date
    10-20-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Which formula would I use to check numbers are ascending?

    I'm not sure that solves the problem. Let me give an example.

    2010 20
    2009 18
    2008 14
    2007 13
    2006 8
    2005 5

    Is the number constantly increasing: YES

    2010 20
    2009 18
    2008 14
    2007 15
    2006 8
    2005 5

    Is the number constantly increasing: NO.

    Therefore a sumproduct calculation will not necessarily pick up on the one year where there was a cut. If the list is 100 long for instance, then the formula does not become lean, as you suggest, in any case.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Which formula would I use to check numbers are ascending?

    The formula provided works only I assumed years in reverse order and that values were in Col A rather than B.

    All you need do is reverse the < operator to > and revise the ranges (to reflect Col B)

    Where the result of the SUMPRODUCT is anything other than 0 then there is a drop at some point in proceedings.

  5. #5
    Registered User
    Join Date
    10-20-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Which formula would I use to check numbers are ascending?

    Worked perfectly. Thank you for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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