Top ten ordering in Excel based on complex team rules -

-

i have excel spreadsheet in format similar following...

| name  | club | status | score | | fred  |    | gent   | 145   | | bert  |    | gent   | 150   | | harry |    | gent   | 195   | | jim   |    | gent   | 150   | | clare |    | lady   | 99    | | simon |    | junior | 130   | | john  |  b   | junior | 130   |    :    : | henry |  z   | gent   | 200   |

i need convert table list of "top ten" teams. rules are

  • each team score taken sum of 4 members of club.
  • these totals should of best 4 scores except...
    • each team must consist of @ least 1 junior or lady

for example in table above team score club 625 not 640 take scores harry(190), bert(150), jim(150), , simon(130). not take fred's(145) score give gents.

my question is, can done series of excel formula, or need resort using more procedural?

ideally solution needs automatic in team selections, don't want have create separate hand crafted formula each team. not have neatly ordered list of each clubs members. although generate list via calculation sheet.

public function topten(club string, scores range)      dim long     dim vascores variant     dim blady boolean     dim lcnt long     dim ltotal long      vascores = filteronclub(scores.value, club)     vascores = sortonscore(vascores)      = lbound(vascores, 2) ubound(vascores, 2)         if lcnt = 3 , not blady             if vascores(3, i) <> "gent"                 ltotal = ltotal + vascores(4, i)                 blady = true                 lcnt = lcnt + 1             end if         else             ltotal = ltotal + vascores(4, i)             lcnt = lcnt + 1             if vascores(3, i) <> "gent" blady = true         end if          if lcnt = 4 exit     next      topten = ltotal  end function  private function filteronclub(vascores variant, sclub string) variant      dim long, j long     dim atemp() variant      = lbound(vascores, 1) ubound(vascores, 1)         if vascores(i, 2) = sclub             j = j + 1             redim preserve atemp(1 4, 1 j)             atemp(1, j) = vascores(i, 1)             atemp(2, j) = vascores(i, 2)             atemp(3, j) = vascores(i, 3)             atemp(4, j) = vascores(i, 4)         end if     next      filteronclub = atemp  end function  private function sortonscore(vascores variant) variant      dim long, j long, k long     dim atemp(1 4) variant      = 1 ubound(vascores, 2) - 1         j = ubound(vascores, 2)             if vascores(4, i) < vascores(4, j)                 k = 1 4                     atemp(k) = vascores(k, j)                     vascores(k, j) = vascores(k, i)                     vascores(k, i) = atemp(k)                 next k             end if         next j     next      sortonscore = vascores  end function

use =topten(h2,$b$2:$e$30) h2 contains club letter.


Comments

Post a Comment

Popular posts from this blog

c++ - How do I get a multi line tooltip in MFC -

-
right now, have tool tip pops when hover on edit box. problem tool tip contains multiple error messages , in 1 long line. need have each error message on own line. error messages contained in cstring new line seperating them. my existing code below. bool ontooltiptext(uint, nmhdr* pnmhdr, lresult* presult) { assert(pnmhdr->code == ttn_needtexta || pnmhdr->code == ttn_needtextw); // need handle both ansi , unicode versions of message tooltiptexta* pttta = (tooltiptexta*)pnmhdr; tooltiptextw* ptttw = (tooltiptextw*)pnmhdr; // tchar szfulltext[256]; cstring strtiptext=_t(""); uint nid = pnmhdr->idfrom; if (pnmhdr->code == ttn_needtexta && (pttta->uflags & ttf_idishwnd) || pnmhdr->code == ttn_needtextw && (ptttw->uflags & ttf_idishwnd)) { // idfrom hwnd of tool nid = ::getdlgctrlid((hwnd)nid); } //m_errprojaccel[ch] contains 1 or more error messages each...
Read more

asp.net - In javascript how to find the height and width -

-
i having .aspx , masterpage page. in masterpage loading aspx page . in aspx page design using generating table structure using code-behind server in javascript how find height , width . regards, kumar if you're looking table height , width, should it var tbl = document.getelementbyid('tbldemo'); alert(tbl.offsetwidth + '\n' + tbl.offsetheight);
Read more

c# - DataTable to EnumerableRowCollection -

-
i have datatable according client requirement can contain invalid date "00/00/1999",null,empty string.(typed datset) when collect them enumerables wish convert of invalid forms empty string. (i.e) enumerablerowcollection<datarow> query = order in _table.asenumerable() select new { orderdate= string.isnullorempty(convert.tostring(order.field<datetime>("orderdate")) || (how check date invalid date) ? string.empty : order.field<datetime>("orderdate") } how check whether date valid date or not? datetime.tryparse...like given current code: datetime date; enumerablerowcollection<datarow> query = order in _table.asenumerable() select new { orderdate= string.isnullorempty(convert.tostring(order.field<datetime>("orderdate")) || !datetime.tryparse(convert.tostring(order.field<datetime...
Read more