Tuesday, September 4, 2012

yogi_Compute In Column G Average Of values In Column C Based On Standings In Column D

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #733   Sep 04, 2012     www.energyefficientbuild.com.

user meph2u said: (http://productforums.google.com/forum/?zx=alk42vs49d24#!category-topic/docs/spreadsheets/sBi3-oPax2M)
How to lookup up values depending on Averaging when Duplicates (ties) exist
Two questions....
1) is there a simpler or more elegant way of stripping the "T"s from column D than the formula in E3  =arrayformula(if(left(D3:D)="T",Value(replace(D3:D,1,1,"")),D3:D))

2) the real reason for this post - how can I fill out Column G based on the values in Column C when ties are averaged (see examples in column H)?
-------------------------------------------------------------------------------------------
following is a solution to the problem


4 comments:

  1. Keep up the good work. I know from experience it's not always easy!
    cloud hosting service

    ReplyDelete
  2. Of course this works perfectly and it is elegant.

    I don't understand why repeating the search range as the criteria counts duplicates. Do you have the time to provide a conceptual foundation for this so I can generalize and use in other places?

    I broke the formula apart to try to understand better, but there is a key conceptual piece I am missing.

    https://docs.google.com/spreadsheet/ccc?key=0Atrb4wVf_dsSdHZuanFISjRQOFgxcmdJTW81d3hiZ3c#gid=0

    Thank you

    Meph

    ReplyDelete
  3. Hi Meph:

    The part of the fromula ...

    countif(D3:D,D3:D)

    says count the number of times entries D3:D occur in range D3:D

    I hope this helps.


    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete
  4. Yes, I think I understand. Thank you!

    ReplyDelete