Too Many Nested Levels - What's the PROPER way to write this formula?

[teddystiles]

Not knowing many of the functions, I attempted to create a YTD Growth formula. Thing is, it needs to look at all 12 months and work regardless of whether the year is completely filled out yet. Here's what I did:

Please Login or Register  to view this content.

It basically starts to see if there's a number in December, and then keeps checking back to February. Once it finds a value, it subtracts it from the initial value (January) and calculates the YTD Growth.

Obviously, Excel doesn't like all the nestiness.

If anyone has any ideas, I'd appreciate it!

[TMS]

Suggest you post a sample workbook with some typical data and indicate tour expected results given whatever conditions may apply ... full year, part year, gaps, whatever.

Regards, TMS

[Vidya Palani]

This should do the trick.

Please Login or Register  to view this content.

[Special-K]

Nested IFs are fine until they get too many, then you should be considering creating a table and using lookups.

Try
=(INDEX(C4:M4,1,MATCH(TRUE,INDEX(ISBLANK(C4:M4),0,0),0)-1)-B4)/B4

This part

MATCH(TRUE,INDEX(ISBLANK(C4:M4),0,0),0)

finds the first blank in the row

This assumes that C4:M4 will either be a number or blank.

[teddystiles]

This should do the trick.

Please Login or Register  to view this content.

Wow. That. Worked. PERFECTLY.

I won't waste your time by asking you to explain, but rest assured that I'll be researching to figure out exactly what you did there.

THANK YOU!!!

[teddystiles]

This should do the trick.

Please Login or Register  to view this content.

Okay, I do have one question. I have several rows for which this worked great. However, I have a bottom row that sums up each column using the formula:

Please Login or Register  to view this content.

So, obviously, columns L and M (November and December) appear "empty" on this row because I haven't input any data for those months yet, but still have a hidden formula. I'm guessing that the formula provided is counting all the way out to M, settling on M as having a value, and then trying to calculate the YTD growth with an empty cell, leading to a #VALUE! error.

[Vidya Palani]

Try using Count instead of Counta.

Please Login or Register  to view this content.