Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Index (?) And/Or Arrays (2003)

    I am attempting to estimate sales data for the upcoming year based on a prior years actuals. My delima is that in column A, I have the months of November 06-October 07 in text (MM/YYYY), in that order. In column B, I have the actual sales figures for those months. I want to use those figures, rounded to the nearest -3, and insert in column D (Column C has the months of January-December 2008 listed in calendar year order). The problem is column C starts with January 2008, whereas column A starts with November 2006. Is there a way with the Index and/or array function(s) to put a formula in each of the twelve cells in column D that will look and see if the months in columns A and C match, then insert the contents of column B into column D, rounded to the nearest -3?
    Thanks in advance.

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Index (?) And/Or Arrays (2003)

    See the sample file...
    Attached Files Attached Files
    Regards,
    Rudi

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Index (?) And/Or Arrays (2003)

    Thanks, Rudi. A slight twist to my facts. The November 06-October 07 months (text) are in column B; the actual sales data are in column C, and the January 08-December 08 month names are in column Y, and the place where I want the data to be placed is column Z. What do I need to change in your formula to make it work?
    Thanks again for your help.

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Index (?) And/Or Arrays (2003)

    OK...

    In column Z put this formula :
    =ROUND(IF(MATCH(MONTH(Y1:Y12),MONTH(B1:B12),0),IND IRECT("C"&MATCH(MONTH(Y1:Y12),MONTH(B1:B12),0))),-3)

    It is an array function, so you must confirm entry with CTRL+SHIFT+ENTER. Also just edit the row values as mine only goes up to row 12!
    Regards,
    Rudi

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Index (?) And/Or Arrays (2003)

    I tried, no results. Here is the file.
    Attached Files Attached Files

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Index (?) And/Or Arrays (2003)

    Hmmm...the formula is putting the data into the wrong row. I have put in a row function to pull the current row number and getting results, but still testing the results.
    =ROUND(IF(MATCH(MONTH(Y160:Y171),MONTH(B160:B171), 0),INDIRECT("C"&ROW())),-3)
    Regards,
    Rudi

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Index (?) And/Or Arrays (2003)

    Thanks. Interesting...

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Index (?) And/Or Arrays (2003)

    Or this array formula in Z160:Z171 (confirm with Ctrl+Shift+Enter):

    =ROUND(INDEX(C160:C171,MATCH(MONTH(Y160:Y171),MONT H(B160:B171),0)),-3)

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Index (?) And/Or Arrays (2003)

    Sorry...not working as ROW() pulls from current row except if I say ROW()+2 <img src=/S/wink.gif border=0 alt=wink width=15 height=15> [sly]
    Regards,
    Rudi

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Index (?) And/Or Arrays (2003)

    You got it!!! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Regards,
    Rudi

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Index (?) And/Or Arrays (2003)

    Hans,
    I don't know what I'm doing wrong, but here is the file with your suggested formula. For some reason, it is returning the same value for all the cells!
    Thanks again.
    Attached Files Attached Files

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Index (?) And/Or Arrays (2003)

    You need to select Z160:Z171, enter the formula and press Ctrl+Shift+Enter, not enter it in one cell as an array and then copy down.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Index (?) And/Or Arrays (2003)

    Thanks, Rory. I have never had to enter an array like that before! It had always worked in the past by entering it once, and then copying down. I wonder what makes this one different?

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Index (?) And/Or Arrays (2003)

    Since the formula contains MONTH(Y160:Y171), it refers to Y160:Y171 as a whole - this is one of the powerful things you can do with array formulas: enter a single formula in a range of cells.
    Note: you cannot edit such a formula for individual cells, only for the entire range. Neither can you delete it from individual cells, only from the entire range.

    It would also have been possible to use this array formula in Z160:

    =ROUND(INDEX($C$160:$C$171,MATCH(MONTH(Y160),MONTH ($B$160:$B$171),0)),-3)

    and fill down to Z171.

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Index (?) And/Or Arrays (2003)

    This one actually returns an array of values, as opposed to working on arrays but only returning one value. That's why you have to enter it in multiple cells simultaneously.
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

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