# Nesting Index formula HELP

1. ## 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.  Register To Reply

2. ## 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  Register To Reply

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  Register To Reply

4. ## 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.  Register To Reply

5. ## 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.  Register To Reply

6. ## Re: Nesting Index formula HELP

(How to) Upload a Workbook directly to the Forum
• 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).
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
Ensure to disable any Workbook Open/Autorun macros before attaching!  Register To Reply

7. ## 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  Register To Reply

8. ## Re: Nesting Index formula HELP

Cool!   Register To Reply

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