Specialized average formula needed

In order to avoid embedding 12 if statements, I want to take an average of a selection of cells based on what month it is. So I have a "now" function, and then a month function referencing the "now" cell so that I get the current month as a number 1 through 12. I have a row of expenses (C14:N14) and I just want the average from January up to the current month. The reason I can't take an average of the entire selection is because the users don't want to have to enter a 0 everytime there's nothing, they'd like to leave it blank, so the sheet needs to recognize what month we're up to. And the average function in Excel only takes an average of the data that is present, not the entire range. For example, if the data in the first cell was 15, second cell was 15, and third cell was blank, the average function returns 15 and not 10. So what I need is, for example, if it's March, the average will calculate =average(C14:E14) and so on. Any ideas? Maybe it's a simple change of formula instead. Thanks!

=AVERAGE($C14:INDEX($C14:$N14,1,MONTH(NOW())))

Use of INDEX in this context makes formula volatile.

Originally Posted by DonkeyOte

Use of INDEX in this context makes formula volatile.

Just to clarify....the INDEX function is NOT volatile.

But...these functions are: RAND, NOW, TODAY, OFFSET, CELL, INDIRECT

Ron, Aladin Akyurek has argued in the past that using INDEX in context of Range A1:INDEX(...) IS indeed volatile.

Here are a couple fo options:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Both confirmed with CTRL+SHIFT+ENTER not just ENTER... you will see { } brackets appear around the formula.

The first formula assumes you have dates in C13:N13.

EDIT: Dang forum server...is running slow again... it took forever to get the reply box to come up... so I am minutes off....

My understanding is that the INDEX function, when used as in 2nd part of
a range reference, while not volatile and therefore not impacting
performance, flags the cell as "dirty" and triggers an unexpected SAVE
prompt when closing the workbook, even if nothing has changed.
(How's THAT for a run-on sentence!)

That information is from Charles Williams website:
http://www.decisionmodels.com/calcsecretsi.htm

Ron, here is a thread in which Aladin makes that assertion:

http://www.mrexcel.com/forum/showthread.php?t=353675

though Colin_L (xtremeVBTalk) makes the point that the usual methods would imply it not to be the case... as a rule and long time member at Mr E I have a tendency to trust Aladin on such matters.

EDIT: just seen your link -- I will read through and pass up to the thread I referred to... should add I've read that site a thousand times and I always come away a little more confused about some things and clearer on others...

General consensus is that Index is not Volatile - one specific member of the XL community argues otherwise... it's not a big deal... and doesn't impact the Q as to whether or not the function works... the solutions offered will work regardless of Volatility.

For more info. on Volatility read the link as posted by Ron.

Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.

Moved to Functions Forum

This one:

=AVERAGE(IF($C$14:$N$14>0,$C$14:$N$14))

doesn't contain INDEX (or other volatile function)...

But needs to be confirmed with CSE keys as noted in my post....

It only averages cells within range if it is greater than 0 or not blank.

Ha ha -- so just goes to show sometimes an Array *could* be the most efficient approach ;-)
(... er, I'm not saying I'm adamant about INDEX... I'll leave that to the experts to argue)

A volatile function recalculates whenever ANY cell in the workbook
recalculates. Consequently, when many are used (typically 1000's), they can make
a workbook sluggish (change a cell...press ENTER...wait wait wait...results appear).
Non-volatile formulas only calculate when necessary (changes to
their directly or indirectly referenced cells, etc).

In your situation, I don't think volatility is really an issue you need to worry
about. I had only mentioned that INDEX is not volatile to address a technical
point.