+ Reply to Thread
Results 1 to 8 of 8

Nesting Index formula HELP

  1. #1
    Registered User
    Join Date
    10-02-2019
    Location
    Atlanta, GA
    MS-Off Ver
    2013 @ Work / 365 @ Home
    Posts
    3

    Nesting Index formula HELP

    Hi, I originally posted on Excelguru, but I am not getting any traction. I am hoping I can get an answer here, even if the answer is "Its not possible".

    I need some help with a formula where I want to start the calculation range when the first cell with a value is found. I format this data so the cells in Yellow and Blue Cells are truly empty.

    I do not want to include in my final calculation range the blue cells. I need the final calculation range to start at the first value found in any row from Column B:M. I only want to multiple the average of the final calculation range by(*) the number of Yellow (Blank) Cells in the that range.

    Here is sample data:
    FormulaHelp.JPG

    Using =INDEX($B2:$M2,MATCH(FALSE,ISBLANK($B2:$M2),0)) - I find the first value in the Row 2 - H2.

    How do I nest the index formula and add formula logic so the formula will start a new range at the first value found in any row, i.e. H2 in Row 2 below, with the new range of (H2:M2)?

    Constants
    *The last cell in any range I use for this calculation is Column M.
    *The dates are from oldest to newest going left to right.

    So I need to combine:
    1) =INDEX($B2:$M2,MATCH(FALSE,ISBLANK($B2:$M2),0)) to find the first cell with a value in any row
    2) Then create a new range starting at the cell found in #1 above
    3) Then calculate =COUNTBLANK(New Range)*AVERAGE(New Range)

    However, I am open to any solution, including Macros/VBA.

    Thanks for any help.
    Last edited by RiODiGiE; 10-02-2019 at 04:58 PM. Reason: Needed to update formula

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,077

    Re: Nesting Index formula HELP

    You say you had no traction on Excelguru, but you did get responses and you didnít really give it much time.

    For anyone thinking of helping, hereís rather link: https://www.excelguru.ca/forums/show...new-range-in-a
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    10-02-2019
    Location
    Atlanta, GA
    MS-Off Ver
    2013 @ Work / 365 @ Home
    Posts
    3

    Re: Nesting Index formula HELP

    Hi,

    This is my first time ever posting a question on forums. I am not familiar with the "norms". I apologize that I didn't know how much time to give it. It is close for me and I need the answer ASAP.
    Again, no disrespect meant to either forum. The replies I got on excelguru were not answered based on my actual question, that is what I meant by not getting traction. I thought this forum would be a better place to ask my question.

    Kind Regards

  4. #4
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,009

    Re: Nesting Index formula HELP

    Hi -

    I'm not sure why Average() won't work for you, but if you want to create a dynamic range that starts and the first value and ends at the last value, try this:

    =AVERAGE(OFFSET(B4,0,MATCH(FALSE,ISBLANK(B4:N4),0)-1,1,COLUMN(N:N)-MATCH(FALSE,ISBLANK(B4:N4),0)))

    The OFFSET function creates a "floating" range relative to cell B2 (as you copy the formula down, the relative anchor changes by row (so B3, B4, etc.)

    Your original formula for the MATCH() function does indeed find the first cell with a number.

    Then we just count the number of columns from Column N to the first data entry in the row.

    Therefore, the OFFSET function creates an array of numbers and blanks within the confines of your first data point and column N.

    Since it's an array, it is a simple matter to substitute COUNTBLANK() instead of AVERAGE to count the number of blank cells in the array.

    Finally, you are probably already aware this is an array formula, so you will need to finish pasting the formula into your spreadsheet with Ctrl-Alt-Enter.

    Hope this helps.
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  5. #5
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,009

    Re: Nesting Index formula HELP

    One more thing - my formula is written for row 4, so paste that into row 4 of your spreadsheet and copy up or down as needed.

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    10,620

    Re: Nesting Index formula HELP

    (How to) Upload a Workbook directly to the Forum
    (please do not post pictures or links to worksheets)
    • Click Advanced next to "Post Quick Reply" button at the bottom right of the editor box.
    • Scroll down until you see "Manage Attachments",
    • Click the "Choose" button at the upper left (upload from your computer).
    • Select your file, click "open", click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Close the Attachment Manager window.
    • Click "Submit Reply"
    Note: Please do not attach password protected workbooks/worksheets
    Ensure to disable any Workbook Open/Autorun macros before attaching!
    Ben Van Johnson

  7. #7
    Registered User
    Join Date
    10-02-2019
    Location
    Atlanta, GA
    MS-Off Ver
    2013 @ Work / 365 @ Home
    Posts
    3

    Thumbs up Re: Nesting Index formula HELP

    Thank you, Thank you, Thank you Loginjmor,

    Your formula is exactly what I needed. I was researching Offset() yesterday, but didn't have the time to figure out how to cobble all the pieces together into a cohesive working formula.

    The problem wasn't Average(), but rather Countblank(), If you look at the Ranges in the Cells colored orange in Column P, you will see that I had to customize the range to get the result I needed.

    With a little modification of your formula as you suggested, the formula works perfectly. Here is the final formula:

    {=IF(O6="yes",COUNTBLANK(OFFSET($C6,0,MATCH(FALSE,ISBLANK($C6:$O6),0)-1,1,COLUMN(O:O)-MATCH(FALSE,ISBLANK(C6:O6),0)))*AVERAGE(C6:N6),0)}

    sample data 1.jpg

    I am attaching the spreadsheet with bigger dataset and the formula in action in case that helps anyone else.

    Thanks again!

    RiO
    Attached Files Attached Files

  8. #8
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,009

    Re: Nesting Index formula HELP

    Cool!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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