With Custom Modified Average Formula

Why replace the formula with a User Defined Function?

You could simplify the formula:

=CHOOSE(COUNT(A2:C2)+1, "", "", AVERAGE(A2:C2), AVERAGE(A2:B2))

Thanks for fixing my topic, I will definately be more careful in the future. I've been trying to figure this out all day and I've gotten annoyed so I overlooked my post.

Originally Posted by royUK

Why replace the formula with a User Defined Function?

The reason I want to replace the formula is because I have to use the formula over several different documents, all without consistent formatting. Which means to edit each formula for each sheet, I have to replace 12 cell references and scroll back through at least 36 cells in a row. Whereas If I replace it with the UDF, I will only have to replace 3 cell references each time. The data includes numbers for each month between 2010 and 1990, so the size of the spreadsheet is huge and the difference between making 12 and 3 cell references makes a huge difference in time. Doing this can greatly increase my productivity and thus increase the amount of non-working I have to do at work

Originally Posted by shg

You could simplify the formula:

=CHOOSE(COUNT(A2:C2)+1, "", "", AVERAGE(A2:C2), AVERAGE(A2:B2))

I'm having to average just 2 numbers that can be seperated throughout the spreadsheet, not a range of numbers.

=choose(count(a2,b2,c2)+1, "", "", average(a2,b2,c2), average(a2,b2))

You'd have to show me an example of that formula returning TRUE.

=CHOOSE(COUNT(A1,B1,C1)+1, "", "", AVERAGE(A1,B1,C1), AVERAGE(A1,B1)) (mine)

=CHOOSE(COUNT(B1,C1,D1)+1, "", AVERAGE(B1,D1), AVERAGE(B1,C1,D1), AVERAGE(B1,C1)) (yours)

Your formula averages B1 and D1 when there's only one number among the three cells, which doesn't follow your prior formula.

When is AVERAGE(B1,C1,D1) used?

When any two of the three cells have numbers. The non-numeric value is ignored by AVERAGE.