+ Reply to Thread
Results 1 to 6 of 6

How do I count up to a certain value?

  1. #1
    Registered User
    Join Date
    04-12-2008
    Posts
    3

    How do I count up to a certain value?

    Hi,
    I could use some help with this one.

    I have a horizontal list of numbers that represent values for weeks 1 to 52. I know how to figure out the highest week's value (using the max formula) but don't know how to figure out what week it happened in. ie: I know in week 23 I had my highest number, but how do I write a formula that counts to the max value and tells me it's number 23????
    If anyone can help me figure this out, I would greatly appreciate it.
    Thanks

  2. #2
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    Hi, i had my figures in row 2 A:L and my week number in row 3 A:L and used this formula:
    =HLOOKUP(MAX(A2:L2),A2:L3,2,FALSE)
    it's a horizontal lookup which looksup the value of the max in the table A2:L3 and the 2 tells excel give me the value from the second row of the table and the false says find an exact match to the lookup value, changing it to true will give the next value without going over it!
    Not all forums are the same - seek and you shall find

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Are you actually showing the week numbers? If not then perhaps, with numbers in A2:AZ2

    =MATCH(MAX(A2:AZ2),A2:AZ2,0)

    or with week numbers in row 1

    =INDEX(A$1:AZ$1,MATCH(MAX(A2:AZ2),A2:AZ2,0))

  4. #4
    Registered User
    Join Date
    04-12-2008
    Posts
    3

    How do I count up to a certain value?

    Thank you very much, that's brilliant.
    It works perfectly for your example,but not sure how to apply it to mine. I will admit I am not that good with this.

    On mine, the week numbers are along the top row and I have multiple locations listed down on the left, so I would need to count backwards. Here is an example:

    1 2 3 4 5 6 7 <----week numbers
    001 3 19 23 2 21 54 4
    002 32 22 43 3 32 33 12


    I need 001 to say 54 is the highest and it happened in week 6
    for 002, it is 43 and it happened in week 3

    Thank you for taking the time to help me with this, if it works, it will be such a time saver.

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Which formulas did you try. The second formula I posted should work for you, place in row 2 and copy down

  6. #6
    Registered User
    Join Date
    04-12-2008
    Posts
    3

    How do I count up to a certain value?

    You are right, the second one did work. You must have posted it when I was replying to the first one.

    thank you very much!!!!!
    you are a life saver!!


+ 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