Author Topic: Complicated formula  (Read 2136 times)

dniezby

  • Jr. Member
  • **
  • Posts: 79
    • View Profile
Complicated formula
« on: August 16, 2011, 04:47:01 PM »
Ok, Sensei Cory...

Can you figure out this formula? I don't know how to apply this to the template.

This is formula has to calculate the number of players entered in the event, award everyone 20 points just for playing but also figure out how many places to pay out.

I have the formula from a spreadsheet if you could figure out a translation of the formula.

IF($A7-B$6*0.3<0.51,20*(SQRT(SQRT(B$6)*(B$6)/$A7)),20)+20

Is is a small chart of the points payout. Top row = # of entries  - Column 1 = Positions paid out.


Magic_fubu

  • Hero Member
  • *****
  • Posts: 1035
    • View Profile
Re: Complicated formula
« Reply #1 on: August 16, 2011, 05:05:22 PM »
I'm going to try to work on it, at least until I either come up with a formula, or I see someone else come up with one. Just to be clear, the 20 for playing is already incorporated into the points earned for tourney, if I'm not mistaken?
My cowboys shot down your rockets
---
If you send a request to me please send that you got me from here w/your TD name to confirm. Thanks!

Magic_fubu

  • Hero Member
  • *****
  • Posts: 1035
    • View Profile
Re: Complicated formula
« Reply #2 on: August 16, 2011, 05:14:47 PM »
let's try this...
Code: [Select]
if(r-n*0.3<.51, 20*(sqrt(sqrt(n*n/r)), 20)+20
I'm basing it off of what you have in your post, and assuming that the rankings are the A column, while the # of entrants are what's going across. I'm not sure if the square root function is the same in TD as it is in excel, but I shall take a look.

EDIT - just did a search of 'sqrt' and it looks like it should work.... So assuming that I made the right assumptions, the formula should work. (n is number of entries, while r is the rank the player earned) Should it not, let me know and I (or someone else if I'm not on) can tweak it for you. :)
« Last Edit: August 16, 2011, 05:16:58 PM by Magic_fubu »
My cowboys shot down your rockets
---
If you send a request to me please send that you got me from here w/your TD name to confirm. Thanks!

dniezby

  • Jr. Member
  • **
  • Posts: 79
    • View Profile
Re: Complicated formula
« Reply #3 on: August 16, 2011, 06:48:10 PM »
I pasted it in the points for playing and when I went to test it, it says "Formula has an error".

Corey Cooper

  • Administrator
  • Hero Member
  • *****
  • Posts: 6216
    • View Profile
Re: Complicated formula
« Reply #4 on: August 16, 2011, 09:28:08 PM »
It's just missing a closing parenthesis:

if(r-n*0.3<.51, 20*(sqrt(sqrt(n*n/r)), 20))+20
                                         ^
                                         |
                                         
here

Although I didn't look at the details of Magic_fubu's solution.  I'll have to look more closely tomorrow.  But at a quick glance I found it a bit confusing since your formula references specific rows and columns in the spreadsheet (A7, for example), but your screen snippet doesn't show the row/column headers, making it difficult to pinpoint row 7, column A.

dniezby

  • Jr. Member
  • **
  • Posts: 79
    • View Profile
Re: Complicated formula
« Reply #5 on: August 16, 2011, 11:50:21 PM »
Sorry Corey. The rows first column is A and 1...So the blank space top left A1

BTW, I entered this attempt at the formula and it gave EVERY player 420 after 9th place.  It got the number of positions right though. It should pay 9 places points and the rest 20...That it did correctly.


Corey Cooper

  • Administrator
  • Hero Member
  • *****
  • Posts: 6216
    • View Profile
Re: Complicated formula
« Reply #6 on: August 17, 2011, 10:27:02 AM »
I'm still confused, but based on Magic_fubu's interpretation:

round(if(r - (n * 0.3) < 0.51, 20 * (sqrt(sqrt(n) * n / r)) + 20, 20))

I think that's right.  Magic_fubu had the gist of it right, but just missed a couple of parentheses.  That "+ 20" in the original formula was in the wrong place, too, I believe.  Regardless, this appears to match the grid you supplied.

Magic_fubu

  • Hero Member
  • *****
  • Posts: 1035
    • View Profile
Re: Complicated formula
« Reply #7 on: August 17, 2011, 11:12:31 AM »
I'm still confused, but based on Magic_fubu's interpretation:

round(if(r - (n * 0.3) < 0.51, 20 * (sqrt(sqrt(n) * n / r)) + 20, 20))

I think that's right.  Magic_fubu had the gist of it right, but just missed a couple of parentheses.  That "+ 20" in the original formula was in the wrong place, too, I believe.  Regardless, this appears to match the grid you supplied.

I appreciate the assist, Corey, as I'm not quite up to par on making formulas.. All I did was try to make the proper substitutions of the excel formula into the TD formula.... Trying to get better with this formula stuff   8)
My cowboys shot down your rockets
---
If you send a request to me please send that you got me from here w/your TD name to confirm. Thanks!

Corey Cooper

  • Administrator
  • Hero Member
  • *****
  • Posts: 6216
    • View Profile
Re: Complicated formula
« Reply #8 on: August 17, 2011, 01:22:03 PM »
All I did was try to make the proper substitutions of the excel formula into the TD formula....

Precisely the part that confused me.  :)

dniezby

  • Jr. Member
  • **
  • Posts: 79
    • View Profile
Re: Complicated formula
« Reply #9 on: August 17, 2011, 08:34:26 PM »
You are a formula genius.  It seems to be working.

You just amaze me with these things. LOL...I bow to you again.

Thanks....again.