Author Topic: IF Formulas  (Read 3320 times)

edjango

  • Newbie
  • *
  • Posts: 4
    • View Profile
IF Formulas
« on: December 09, 2011, 03:46:32 AM »
Hello all, I've read several posts and none helped me.

On my league for the next year, I would like to provide points using percentage per ranking. I was able to do it with two parameters only. I would like to have several elses. I just upgraded mine TD to v3 thinking that this would allow me to do it. But the formulas structure remain the same.

Can someone help me on this issue?

We're thinking on this.

Factor is 15. if < 9 players registered 1st = 15*9*0.30, if < 18 14 players registered 5th = 15*14*0.015, if < 27 25 players registered 3rd = 15*25*0.125, and so on.

I have to do it using excel. But I wonder if I could track using the TD instead. Is it possible?!

Best Regards,
- Fernando.

Corey Cooper

  • Administrator
  • Hero Member
  • *****
  • Posts: 6216
    • View Profile
Re: IF Formulas
« Reply #1 on: December 09, 2011, 11:37:33 AM »
Factor is 15. if < 9 players registered 1st = 15*9*0.30, if < 18 14 players registered 5th = 15*14*0.015, if < 27 25 players registered 3rd = 15*25*0.125, and so on.

OK...

Quote
if < 9 players registered 1st = 15*9*0.30

OK, there's the points for 1st place.  Does no one else receive points?

Quote
if < 18 14 players registered 5th = 15*14*0.015

Uhm, which is it, if less than 18 players or less than 14 players?  I might remind you that 9 is less than 18 and 14 so a tournament of 8 players, for example, would make the previous expression true as well as this one.  Which one would apply?  And again, there's the points for 5th place (why 5th?).  Does no one else receive points?

Quote
if < 27 25 players registered 3rd = 15*25*0.125

Repeat my last paragraph.

edjango

  • Newbie
  • *
  • Posts: 4
    • View Profile
Re: IF Formulas
« Reply #2 on: December 12, 2011, 06:16:33 AM »
Hi Corey, I think I did not explain myself clear here...

Let me try this:

This is in excel the formula that I'm using now:

=IF(I23<=9,B23*C23*I23,IF(I23<=18,B23*D23*I23,IF(I23<=27,B23*E23*I23,IF(I23<=36,B23*F23*I23,IF(I23<=45,B23*G23*I23,IF(I23>45,B23*H23*I23,"Mais Fórmula")))))

Where: B column is the factor (which is .15), C to H columns are the percents and I column is the number of registered players.

Until 9 players use:
Column C
30.00% - 1st
20.00% - 2nd
13.00% - 3rd
10.00% - 4th
8.00% - 5th
7.00% - 6th
5.00% - 7th
4.00% - 8th
3.00% - 9th

Until 18 players use:
Column D
29.00% - 1st
18.50% - 2nd
13.00% - 3rd
10.00% - 4th
8.00% - 5th
7.00% - 6th
5.50% - 7th
4.50% - 8th
3.50% - 9th
1.00% - 10th to 18th

Until 27 use:
Column E
28.00% - 1st
18.00% - 2nd
12.50% - 3rd
10.00% - 4th
8.00% - 5th
7.00% - 6th
6.00% - 7th
4.50% - 8th
3.50% - 9th
1.50% - 10th to 18th
1.00% - 19th to 27th

Until 36 use:
Column F
27.25% - 1st
17.75% - 2nd
12.50% - 3rd
10.00% - 4th
8.00% - 5th
7.00% - 6th
6.00% - 7th
4.50% - 8th
3.50% - 9th
1.75% - 10th to 18th
1.25% - 19th to 27th
0.50% - 28th to 36th

Until 45 use:
Column G
26.75% - 1st
17.50% - 2nd
12.50% - 3rd
10.00% - 4th
8.00% - 5th
7.00% - 6th
6.00% - 7th
4.50% - 8th
3.75% - 9th
1.75% - 10th to 18th
1.25% - 19th to 27th
0.75% - 28th to 36th
0.25% - 37th to 45th

Above 45 use:
Column H
26.50% - 1st
17.50% - 2nd
12.50% - 3rd
10.00% - 4th
8.00% - 5th
7.00% - 6th
6.25% - 7th
4.50% - 8th
3.75% - 9th
1.50% - 10th to 18th
1.00% - 19th to 27th
0.75% - 28th to 36th
0.50% - 37th to 45th
0.25% - above 45th.

I hope this might explain it better... we didn't had any tournaments with more than 36 players, but I think that next year we'll be receiving more players :)

Corey Cooper

  • Administrator
  • Hero Member
  • *****
  • Posts: 6216
    • View Profile
Re: IF Formulas
« Reply #3 on: December 12, 2011, 01:51:45 PM »
This is definitely a much better explanation.  How about just attaching your spreadsheet?

edjango

  • Newbie
  • *
  • Posts: 4
    • View Profile
Re: IF Formulas
« Reply #4 on: December 15, 2011, 02:13:05 AM »
Yes, for sure... thanks...

Here it is:

Most of the spreadsheet is in Portuguese. It should open on "Valores" tab at I23 cell. This is where I insert the number of players at our league.

Thank you very much.

Best Regards,
- Fernando.

Corey Cooper

  • Administrator
  • Hero Member
  • *****
  • Posts: 6216
    • View Profile
Re: IF Formulas
« Reply #5 on: December 15, 2011, 02:54:30 PM »
It's big and ugly, but here you go:

assign("factor", 15);
assign("percent",
  if(n < 10, switch(r, 1, 30.00, 2, 20.00, 3, 13.00, 4, 10.00, 5, 8.00, 6, 7.00, 7, 5.00, 8, 4.00, 9, 3.00),
    if(n < 19, switch(r, 1, 29.00, 2, 18.50, 3, 13.00, 4, 10.00, 5, 8.00, 6, 7.00, 7, 5.50, 8, 4.50, 9, 3.50, 10, 1.00, 11, 1.00, 12, 1.00, 13, 1.00, 14, 1.00, 15, 1.00, 16, 1.00, 17, 1.00, 18, 1.00),
      if(n < 28, switch(r, 1, 28.00, 2, 18.00, 3, 12.50, 4, 10.00, 5, 8.00, 6, 7.00, 7, 6.00, 8, 4.50, 9, 3.50, 10, 1.50, 11, 1.50, 12, 1.50, 13, 1.50, 14, 1.50, 15, 1.50, 16, 1.50, 17, 1.50, 18, 1.50, 19, 1.00, 20, 1.00, 21, 1.00, 22, 1.00, 23, 1.00, 24, 1.00, 25, 1.00, 26, 1.00, 27, 1.00),
        if(n < 37, switch(r, 1, 27.25, 2, 17.75, 3, 12.50, 4, 10.00, 5, 8.00, 6, 7.00, 7, 6.00, 8, 4.50, 9, 3.50, 10, 1.75, 11, 1.75, 12, 1.75, 13, 1.75, 14, 1.75, 15, 1.75, 16, 1.75, 17, 1.75, 18, 1.75, 19, 1.25, 20, 1.25, 21, 1.25, 22, 1.25, 23, 1.25, 24, 1.25, 25, 1.25, 26, 1.25, 27, 1.25, 28, 0.50, 29, 0.50, 30, 0.50, 31, 0.50, 32, 0.50, 33, 0.50, 34, 0.50, 35, 0.50, 36, 0.50),
          if(n < 46, switch(r, 1, 26.75, 2, 17.50, 3, 12.50, 4, 10.00, 5, 8.00, 6, 7.00, 7, 6.00, 8, 4.50, 9, 3.75, 10, 1.75, 11, 1.75, 12, 1.75, 13, 1.75, 14, 1.75, 15, 1.75, 16, 1.75, 17, 1.75, 18, 1.75, 19, 1.25, 20, 1.25, 21, 1.25, 22, 1.25, 23, 1.25, 24, 1.25, 25, 1.25, 26, 1.25, 27, 1.25, 28, 0.75, 29, 0.75, 30, 0.75, 31, 0.75, 32, 0.75, 33, 0.75, 34, 0.75, 35, 0.75, 36, 0.75, 37, 0.25, 38, 0.25, 39, 0.25, 40, 0.25, 41, 0.25, 42, 0.25, 43, 0.25, 44, 0.25, 45, 0.25),
            switch(r, 1, 26.50, 2, 17.50, 3, 12.50, 4, 10.00, 5, 8.00, 6, 7.00, 7, 6.25, 8, 4.50, 9, 3.75, 10, 1.50, 11, 1.50, 12, 1.50, 13, 1.50, 14, 1.50, 15, 1.50, 16, 1.50, 17, 1.50, 18, 1.50, 19, 1.00, 20, 1.00, 21, 1.00, 22, 1.00, 23, 1.00, 24, 1.00, 25, 1.00, 26, 1.00, 27, 1.00, 28, 0.75, 29, 0.75, 30, 0.75, 31, 0.75, 32, 0.75, 33, 0.75, 34, 0.75, 35, 0.75, 36, 0.75, 37, 0.50, 38, 0.50, 39, 0.50, 40, 0.50, 41, 0.50, 42, 0.50, 43, 0.50, 44, 0.50, 45, 0.50, 0.25)
          )
        )
      )
    )
  )
);
round(n * factor * percent / 100)

edjango

  • Newbie
  • *
  • Posts: 4
    • View Profile
Re: IF Formulas
« Reply #6 on: December 16, 2011, 02:26:38 AM »
Corey, thank you very much... I'm really sorry that I give you that much work! :)

Anyway, it's working great!

I wish you and your family all the best at this holiday season.

Best Regards,
- Fernando.

Corey Cooper

  • Administrator
  • Hero Member
  • *****
  • Posts: 6216
    • View Profile
Re: IF Formulas
« Reply #7 on: December 16, 2011, 01:52:28 PM »
Not that much work.  Really was more of a test of how skilled I am using a text editor than at creating formulas.  :)