Multi Level Sum using small

TheNoocH · Jul 2, 2009

I currently have these formulas for each value of 3-10 (in column C)...this is a formula to help create a golf handicap...

=(COUNTIF(E$3:E5,">0")>=$C5,(SUM(SMALL(E$3:E5,{1,2,3}))/$C5-35)*0.8,0)

...

=IF(COUNTIF(E$3:E15,">0")>=$C15,(SUM(SMALL(E$3:E15,{1,2,3,4,5,6,7,8,9,10}))/$C15-35)*0.8,0)

Column C houses a number from 3-10...I'm thinking I want to get rid of this column and build the logic into the formula...

I think what I want to do is count column E...
The logic would be:
if count =3,4,5 then sum the lowest 3 values and divide by 3...
if count =6,7 then sum the lowest 4 values and divide by 4...
if count =8 then sum the lowest 5 values and divide by 5...
if count =9 then sum the lowest 6 values and divide by 6...
if count =10 then sum the lowest 7 values and divide by 7...
if count =11 then sum the lowest 8 values and divide by 8...
if count =12 then sum the lowest 9 values and divide by 9...
if count =13 then sum the lowest 10 values and divide by 10...

Now I'm sure i could do a nested IF statement but I'm looking to see if there is a better way...any help would be great...

this is what is currently there for 1 player...i'm thinking of getting rid of col C and using a count to figure out as stated above...this page has all calculations pulled off a data input sheet...these formulas work perfectly until somebody misses a week...so i have to try and build logic in to capture that...

Excel Workbook

B

C

D

E

F

G

2

Week

MinGames

Act

Adj

Pts

Hdc

3

1

48

47

9

4

2

52

51

14

5

3

48

47

16

11

6

4

3

54

6

11

7

5

3

48

1

10

8

6

4

48

12

10

9

7

4

49

7

10

8

5

46

10

11

9

6

48

9

10

12

10

7

46

44

11

9

13

11

8

0

10

14

12

9

0

10

15

13

10

0

11

Monday Calcs (2)

Cell Formulas
Range	Formula
G5	=IF(COUNTIF(E$3:E5,">0")> =$C5,(SUM(SMALL(E$3:E5,{1,2,3}))/$C5 -35)*0.8,0)
G6	=IF(COUNTIF(E$3:E6,">0")> =$C6,(SUM(SMALL(E$3:E6,{1,2,3}))/$C6 -35)*0.8,0)
G7	=IF(COUNTIF(E$3:E7,">0")> =$C7,(SUM(SMALL(E$3:E7,{1,2,3}))/$C7 -35)*0.8,0)
G8	=IF(COUNTIF(E$3:E8,">0")> =$C8,(SUM(SMALL(E$3:E8,{1,2,3,4}))/$C8 -35)*0.8,0)
G9	=IF(COUNTIF(E$3:E9,">0")> =$C9,(SUM(SMALL(E$3:E9,{1,2,3,4}))/$C9 -35)*0.8,0)
G10	=IF(COUNTIF(E$3:E10,">0")> =$C10,(SUM(SMALL(E$3:E10,{1,2,3,4,5}))/$C10 -35)*0.8,0)
G11	=IF(COUNTIF(E$3:E11,">0")> =$C11,(SUM(SMALL(E$3:E11,{1,2,3,4,5,6}))/$C11 -35)*0.8,0)
G12	=IF(COUNTIF(E$3:E12,">0")> =$C12,(SUM(SMALL(E$3:E12,{1,2,3,4,5,6,7}))/$C12 -35)*0.8,0)
G13	=IF(COUNTIF(E$3:E13,">0")> =$C13,(SUM(SMALL(E$3:E13,{1,2,3,4,5,6,7,8}))/$C13 -35)*0.8,0)
G14	=IF(COUNTIF(E$3:E14,">0")> =$C14,(SUM(SMALL(E$3:E14,{1,2,3,4,5,6,7,8,9}))/$C14 -35)*0.8,0)
G15	=IF(COUNTIF(E$3:E15,">0")> =$C15,(SUM(SMALL(E$3:E15,{1,2,3,4,5,6,7,8,9,10}))/$C15 -35)*0.8,0)

Domenic · Jul 2, 2009

Try...

Code:

G3, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(COUNT($E$3:E3)>=3,(AVERAGE(SMALL($E$3:E3,
   ROW(INDIRECT("1:"&LOOKUP(COUNT($E$3:E3),{3,6,8,9,10,11,12,13},
      {3,4,5,6,7,8,9,10})))))-35)*0.8,"")

If, however, the data contains zero's which need to be excluded, try the following instead...

Code:

G3, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(COUNTIF($E$3:E3,">0")>=3,(AVERAGE(SMALL(IF($E$3:E3>0,$E$3:E3),
   ROW(INDIRECT("1:"&LOOKUP(COUNTIF($E$3:E3,">0"),
      {3,6,8,9,10,11,12,13},{3,4,5,6,7,8,9,10})))))-35)*0.8,"")

Note that in both cases the formula will average the lowest 10 values for counts greater than 13.

TheNoocH · Jul 2, 2009

Dominec that works perfectly...thanks so much...i just wrote a UDF that I just got working also...but since there isn't any code in the workbook yet i think i'll use your array formula....thanks so much...

here's my UDF...

Code:

Function hdcp(rng As Range) As Double
   j = 0: i = 0: mySum = 0
   Select Case WorksheetFunction.Count(rng)
   Case 3, 4, 5
       For i = 1 To 3
           j = WorksheetFunction.Small(rng, i)
           mySum = mySum + j
       Next i
       hdcp = (mySum / 3 - 35) * 0.8
   Case 6, 7
       For i = 1 To 4
           j = WorksheetFunction.Small(rng, i)
           mySum = mySum + j
       Next i
       hdcp = (mySum / 4 - 35) * 0.8
   Case 8
       For i = 1 To 5
           j = WorksheetFunction.Small(rng, i)
           mySum = mySum + j
       Next i
       hdcp = (mySum / 5 - 35) * 0.8
   Case 9
       For i = 1 To 6
           j = WorksheetFunction.Small(rng, i)
           mySum = mySum + j
       Next i
       hdcp = (mySum / 6 - 35) * 0.8
   Case 10
       For i = 1 To 7
           j = WorksheetFunction.Small(rng, i)
           mySum = mySum + j
       Next i
       hdcp = (mySum / 7 - 35) * 0.8
   Case 11
       For i = 1 To 8
           j = WorksheetFunction.Small(rng, i)
           mySum = mySum + j
       Next i
       hdcp = (mySum / 8 - 35) * 0.8
   Case 12
       For i = 1 To 9
           j = WorksheetFunction.Small(rng, i)
           mySum = mySum + j
       Next i
       hdcp = (mySum / 9 - 35) * 0.8
   Case 13
       For i = 1 To 10
           j = WorksheetFunction.Small(rng, i)
           mySum = mySum + j
       Next i
       hdcp = (mySum / 10 - 35) * 0.8
   End Select
End Function

Domenic · Jul 2, 2009

You're very welcome! Note that the array formula contains INDIRECT, which is a volatile function. So you may want to compare the formula with the UDF in terms of efficiency.

TheNoocH · Jul 2, 2009

Dominec...i will check it out...i simplified the UDF thanks to help in this thread...but if i change all the formulas to your array formula and there isn't any noticable slowing I might just stay with it...it for my girlfriend's dad who is older and i don't want to have him to mess with enabling the macros and such...

http://www.mrexcel.com/forum/showthread.php?p=1986591#post1986591

TheNoocH · Jul 2, 2009

Dominec...quick question...i changed the formulas...now when i'm in the workbook it showing CALCULATE in the status bar...i double checked and the Calculation options are set to AUTOMATIC and I even pressed F9 to force an update and it still shows in the status bar...any thoughts?

In XL 2007 I hit Calculate Now and Calculate sheet now it's gone...but if i save, close then reopen CALCULATE reappears...

Domenic · Jul 2, 2009

TheNoocH said:
Dominec...quick question...i changed the formulas...now when i'm in the workbook it showing CALCULATE in the status bar...i double checked and the Calculation options are set to AUTOMATIC and I even pressed F9 to force an update and it still shows in the status bar...any thoughts?

In XL 2007 I hit Calculate Now and Calculate sheet now it's gone...but if i save, close then reopen CALCULATE reappears...

Have a look here...

TheNoocH · Jul 2, 2009

Domenic...thanks...i guess long and short of it is that is does the calcs...do you think it's because it's a volitile formula?

Domenic · Jul 2, 2009

TheNoocH said:
Domenic...thanks...i guess long and short of it is that is does the calcs...do you think it's because it's a volitile formula?

Doubt it... You can always test it by replacing...

ROW(INDIRECT("1:"&LOOKUP(.....)))

with

{1,2,3}

Multi Level Sum using small

TheNoocH

Well-known Member

Domenic

MrExcel MVP

TheNoocH

Well-known Member

Domenic

MrExcel MVP

TheNoocH

Well-known Member

TheNoocH

Well-known Member

Domenic

MrExcel MVP

TheNoocH

Well-known Member

Domenic

MrExcel MVP

Similar threads

Share this page

Multi Level Sum using small

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock