UDF to calculate average according to condition, ignoring blanks

Dear Experts,
The question is also posted on http://www.ozgrid.com/forum/showthread.php?t=150699, http://www.vbaexpress.com/forum/show...710#post235710, but no feedback so far.

I have a UDF calculating averages by getting data from subordinate WBS and ignoring blank cells. For instance, if 1.1 is broken down into 1.1.1 (10), 1.1.2 (12) and 1.1.3 (25), the formula finds the corresponding values for the subordinates of 1.1 and calculates the average ((10+12+26)/3 = 16).
If, however, 2.2 is broken down into 2.1 (50) and 2.2 (blank cell), than the average should be 50.

When I debug my UDF on, say, cell I40 (5.5, which has 3 subordinates 5.5.1 (82%), 5.5.2 (23%), and 5.5.3 (blank cell, that have a subordinate 5.5.3.1 (85%)), I would expect to have an average of 63.(3)%.
This is exactly what I get in the Immediate Window (see below) when I get values from the cells.

Please Login or Register  to view this content.

The UDF, however, returns me something different. In part because, I guess, it treats 5.5.3 as blank and does not assume that it may have subordinates. It also does not seem to be drawing results from other cells with the same UDF.
Please help to fix the UDF. Your help will be very much appreciated. I am attaching the file for your convenience.

Please Login or Register  to view this content.

Originally Posted by dmitry

I have a UDF calculating averages by getting data from subordinate WBS and ignoring blank cells. For instance, if 1.1 is broken down into 1.1.1 (10), 1.1.2 (12) and 1.1.3 (25), the formula finds the corresponding values for the subordinates of 1.1 and calculates the average ((10+12+26)/3 = 16).

First, given the above can you explain why the "correct average" for 1.1 is 17% [1.1.1] rather than 47% [ 1.1.1 + 1.1.3) /2 ] ?

I would have expected 1.1 to be the average of 1.1.1 (17%) and 1.1.3 (77%)

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this

You can edit your first post and simply add the URLs

FWIW, if you're prepared to have calculations work in an unusual/unconventional manner (ie cells at top reliant on results below*) then you can use native formulae for this
* e.g for 3 you must first calculate 3.1 to 3.n (below)

Please Login or Register  to view this content.

the above replicates your expected results

Originally Posted by dmitry

As for as I can see, the formula assumes that all the subordinates are located below the cell scrutinized.

Correct.

Originally Posted by dmitry

Is that UDF entirely off the track?

The logic of analysing WBS dependants first is as valid for the UDF as it is for the Array.

Because you are averaging the averages of dependants you must first calculate the averages of all direct dependants
(and their dependants and so on and so forth to base level - working from base level up)

Using your example set, let's take the example of 5 and calculating the %

The % for 5 is the average of the averages of it's direct dependants:

5.1, 5.2, 5.3, 5.4 & 5.5

However, 5.5 has dependants itself and as such the value for 5.5 is the average of those dependants' averages:

5.5.1, 5.5.2 & 5.5.3

However, 5.5.3 has dependants itself and is therefore the average of those dependants (in this case just a single dependant):

5.5.3.1

Does that make sense ?

You can't simply average the explicit values for all dependants as the number of dependants per WBS varies (as we would expect), doing so would distort the %

So in summary to calculate average of 5 you must:

1. calculate average of 5.5.3

such that you can:

2. calculate average of 5.5.1, 5.5.2 & 5.5.3 * as calculated above

such that you can:

3. calculate average of 5.1, 5.2, 5.3, 5.4 & 5.5 *as calculated above

and where 3. is the final result

UDF wise I *think* the below works but it's a sledgehammer and those more comfortable with recursive functions will be able to come up with a far more elegant (succinct) approach.

In my defence the below was written whilst watching a football (soccer) match on TV so I confess that it's not been thought through in any depth

Please Login or Register  to view this content.

called from a cell along the lines of:

Please Login or Register  to view this content.

note the pre-emptive IF - no need to calculate the UDF where a % value already exists for a given WBS.

The UDF assumes vertical vector source range for both WBS & Values - WBS order is not important.

Dmitry, if you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


Pleas also ensure that you update all of your cross posts accordingly.

Dear DonkeyOte,
I appreciate your help with my issue.

The formula exhibits strange behavior for the last entry of the data range. In my real-life database I have a blank cell for WBS 5.2.4.2 (meaning no activity was conducted for that item), the formula returns zero, instead of DIV/0. I need at least DIV/0 (it works well for charting as Excel ignores errors and does not assign zero values to them, as oppose to blank or "" cells).

I was trying to modify the formula to handle DIV/0 errors. However, the tests were not welcomed by the array formula.

This one below handles blank cells without any subordinates and returns "" for them:
=IF(AND(COUNTIF($A$3:$A$45,A3&".?")=0,ISBLANK(C3)),"",IF(ISNUMBER($C3),$C3, (AVERAGE(IF(LEFT($A4:$A$46,LEN($A3)+1)=$A3&".",IF(ISNUMBER(REPLACE($A4:$A$46,1,LEN($A3),"")+0),IF(ISNUMBER($L4:$L$46),$L4:$L$46)))))))

However, the formula still returns this error when it is trying to calculate the average when all the dependents are blank.

I was trying to insert a test inside the formula to return "" if AVERAGE gives a DIV/0 error (I assumed that this was where error was coming from), but Excel would not let me do that.

I also tried to put the entire formula to the test. This makes it twice as long, and I did not like it, but Excel would not let me do that too.

Could you advice how I can make this final touch? Thanks a lot in advance.

Using versions prior to XL2007 you have nesting limits which negate certain approaches

Please Login or Register  to view this content.

would work if you really need it...

Thank you very much. I do needed that.
Sorry to be a nuisance.
I am saving tomorrow morning to think about the logic of your formula. So far my eyes run to different sides when I look at it for more than 10 sec.