+ Reply to Thread
Results 1 to 13 of 13

UDF to calculate average according to condition, ignoring blanks

  1. #1
    Registered User
    Join Date
    02-03-2011
    Location
    VA
    MS-Off Ver
    Excel 2003
    Posts
    17

    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.
    Attached Files Attached Files
    Last edited by dmitry; 02-15-2011 at 09:43 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: UDF to calculate average according to condition, ignoring blanks

    Quote Originally Posted by dmitry View Post
    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%)

  3. #3
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: UDF to calculate average according to condition, ignoring blanks

    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
    Remember what the dormouse said
    Feed your head

  4. #4
    Registered User
    Join Date
    05-12-2008
    Posts
    4

    Re: UDF to calculate average according to condition, ignoring blanks

    To DonkeyOte:
    Yes, you are right. 47% is the correct answer in that case. I guess I gave too much thought to the subject, and exhausted my brain. There are a couple of other typos (correct averages for 1, 3, 5).
    To romperstomper:
    My apologies. Under no means I was going to go unnoticed. I expected that different experts go to different forums. How can I add links to my first message?

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: UDF to calculate average according to condition, ignoring blanks

    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
    Last edited by DonkeyOte; 02-12-2011 at 04:58 AM. Reason: typo in narrative

  6. #6
    Registered User
    Join Date
    02-03-2011
    Location
    VA
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: UDF to calculate average according to condition, ignoring blanks

    DonkeyOte, thanks a lot for your input. I liked your solution. It will give me some more understanding of array formulas.
    I will add conditions to handle blank cells.
    As for as I can see, the formula assumes that all the subordinates are located below the cell scrutinized. It is logical to expect this, but, I guess, I should make it explicit for the model users.
    Is that UDF entirely off the track?
    Thank you again

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: UDF to calculate average according to condition, ignoring blanks

    Quote Originally Posted by dmitry View Post
    As for as I can see, the formula assumes that all the subordinates are located below the cell scrutinized.
    Correct.

    Quote 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
    Last edited by DonkeyOte; 02-12-2011 at 12:50 PM.

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: UDF to calculate average according to condition, ignoring blanks

    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.
    Last edited by DonkeyOte; 02-12-2011 at 03:54 PM. Reason: typo in narrative

  9. #9
    Registered User
    Join Date
    02-03-2011
    Location
    VA
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: UDF to calculate average according to condition, ignoring blanks

    Thanks a lot to everyone!!!

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: UDF to calculate average according to condition, ignoring blanks

    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.

  11. #11
    Registered User
    Join Date
    02-03-2011
    Location
    VA
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: UDF to calculate average according to condition, ignoring blanks

    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.
    Last edited by dmitry; 02-14-2011 at 04:33 PM.

  12. #12
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: UDF to calculate average according to condition, ignoring blanks

    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...

  13. #13
    Registered User
    Join Date
    02-03-2011
    Location
    VA
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: UDF to calculate average according to condition, ignoring blanks

    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.
    Last edited by dmitry; 02-14-2011 at 05:54 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1