With Custom Modified Average Formula

I am attempting to create an averaging formula that will average 2 numbers my spreadsheet. Because of inconsistent data, I am having to take in 3 numbers, find the two most revelent numbers that are there, and average the two of them. If I am missing 2 or more of the numbers, I simply return an empty string.

Because I am having to replicate this formula on several different spreadsheets, I decided to make a User Defined Formula for it to make my life easier.

I am wanting to convert the following formula into a User Defined Formula:
=IF(AND(CELL1<>"",CELL2<>""),AVERAGE(CELL1,CELL2),IF(AND(CELL1<>"",CELL3<>""),AVERAGE(CELL1,CELL3),IF(AND(CELL2<>"",CELL3<>""),AVERAGE(CELL2,CELL3),"")))

I have absolutely no experience with the Module Programming and very little experience in Excel Formulas, but I have experience with programming languages, so I was able to work myself around the syntax. Here is my attempt on programming the formula:

Please Login or Register  to view this content.

This currently returns: #VALUE!


I assume my mistake is miniscule, but I can use help nonetheless.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Added this time

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Edited this time

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.

I'm running out of things to say. Do these answers look right to you?

Please Login or Register  to view this content.

The formula in D2 and down is

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