Squad xp calculator help (excel)

Paytah - Lost City
Paytah - Lost City Posts: 161 Arc User
edited February 2012 in General Discussion
trying to make a squad xp calculator using the formula here but the product isnt turning out right i probably just messed up in the initial formula somewhere but i cant find it :/ perhaps an archer can help b:chuckle the current value in the xp field is the base xp for frozen heads. but its saying that im going to get 22k xp unhypered... im sure thats incorrect. feel free to tweak the sheet to make better
sheet is right hereb:victory

~EDIT~ its been fixed thanks traz :D should i fix it up and make it look pretty and post it somewhere for everyones use?

~EDIT2~ traz did for me XD use this one :D

~EDIT3~ SylenThunder made a wonderful version here
Post edited by Paytah - Lost City on
«1

Comments

  • _UnGodly_ - Lost City
    _UnGodly_ - Lost City Posts: 92 Arc User
    edited February 2012
    I'm sure Asterelle could put you on the right path. Or the cleric thats good with the fancy numbers. b:chuckle
    [SIGPIC][/SIGPIC]
  • Traz - Dreamweaver
    Traz - Dreamweaver Posts: 2,843 Arc User
    edited February 2012
    What it should be:

    XP = (5,020 * 1 * 1.4 * 90) / 170
    XP = (632,520) / 170
    XP = 3,720.7
    XP = 3,721

    What you are doing:

    XP = (5,020 * 1 * (90-88) * (1.4 * 3) * 90) / 170
    XP = (5,020 * 1 * (2) * (4.2) * 90) / 170
    XP = (3,795,120) / 170
    XP = 22,324.2
    XP = 22,324
    BladedZero - Sanctuary
    "Also I don't think actually playing through the game helps your skill either."

    -And we wonder why this game has gone to hell b:cute
  • Asterelle - Sanctuary_1381265973
    Asterelle - Sanctuary_1381265973 Posts: 7,881 Arc User
    edited February 2012
    _UnGodly_ - Lost City wrote: »
    I'm sure Asterelle could put you on the right path. Other the cleric thats good with the fancy numbers. b:chuckle

    Someone else came up with that equation. I just put it in the wiki. If someone finds it to be incorrect feel free to correct it.
    [SIGPIC][/SIGPIC]
    Refining Simulator - aster.ohmydays.net/pw/refiningsimulator.html (don't use IE)
    Genie Calculator - aster.ohmydays.net/pw/geniecalculator.html - (don't use IE)
    Socket Calculator - aster.ohmydays.net/pw/socketcalculator.html
  • Paytah - Lost City
    Paytah - Lost City Posts: 161 Arc User
    edited February 2012
    Traz - Dreamweaver wrote: »
    What it should be:

    XP = (5,020 * 1 * 1.4 * 90) / 170
    XP = (632,520) / 170
    XP = 3,720.7
    XP = 3,721

    What you are doing:

    XP = (5,020 * 1 * (90-88) * (1.4 * 3) * 90) / 170
    XP = (5,020 * 1 * (2) * (4.2) * 90) / 170
    XP = (3,795,120) / 170
    XP = 22,324.2
    XP = 22,324

    thanks :D its been fixed now :D
  • Traz - Dreamweaver
    Traz - Dreamweaver Posts: 2,843 Arc User
    edited February 2012
    Paytah - Lost City wrote: »
    thanks :D its been fixed now :D

    It's actually still wrong you shouldn't have 170 as the total levels from all squad members. You forgot the level 90 at the bottom to add into the other two 85s in squad.

    Have fun with it :)
    BladedZero - Sanctuary
    "Also I don't think actually playing through the game helps your skill either."

    -And we wonder why this game has gone to hell b:cute
  • KhaliShi - Lost City
    KhaliShi - Lost City Posts: 3 Arc User
    edited February 2012
    Traz - Dreamweaver wrote: »
    It's actually still wrong you shouldn't have 170 as the total levels from all squad members. You forgot the level 90 at the bottom to add into the other two 85s in squad.

    Have fun with it :)

    well i was showed up O.O... go with that one guys b:chuckle

    btw... im paytah, to lazy to switch avatars
  • Paytah - Lost City
    Paytah - Lost City Posts: 161 Arc User
    edited February 2012
    im curious tho... in the sheet by traz is there a way to make C7 input the value needed according to what the value in G7 is?
  • Traz - Dreamweaver
    Traz - Dreamweaver Posts: 2,843 Arc User
    edited February 2012
    Paytah - Lost City wrote: »
    im curious tho... in the sheet by traz is there a way to make C7 input the value needed according to what the value in G7 is?

    I haven't figured out a way to do so -YET- but that's way to much work for me to try. Nested IFs for excel can only go to 7 otherwise you need to do some hardcore work arounds with VLOOKUP to make it work.
    BladedZero - Sanctuary
    "Also I don't think actually playing through the game helps your skill either."

    -And we wonder why this game has gone to hell b:cute
  • Decus - Dreamweaver
    Decus - Dreamweaver Posts: 5,033 Arc User
    edited February 2012
    _UnGodly_ - Lost City wrote: »
    I'm sure Asterelle could put you on the right path. Or the cleric thats good with the fancy numbers. b:chuckle

    b:sad
    Proving that not only archers do math since 2009. b:victory
    Current math challenge: pwi-forum.perfectworld.com/showthread.php?t=1029711&page=45
    [SIGPIC][/SIGPIC]
    "Any skills that can be used to kill you will interrupt BB when successful." -truekossy | "...Sage archers are kind of like Mac owners. They are proud of the weirdest and most unnecessary things." -Aesthor | "We ALL know Jesus doesn't play PWI. He may have suffered a lot for humanity, but he'd NEVER punish himself this way." -Abstractive | "I approve of bananas." -SashaGray
  • Paytah - Lost City
    Paytah - Lost City Posts: 161 Arc User
    edited February 2012
    Traz - Dreamweaver wrote: »
    I haven't figured out a way to do so -YET- but that's way to much work for me to try. Nested IFs for excel can only go to 7 otherwise you need to do some hardcore work arounds with VLOOKUP to make it work.

    i suppose its not to much to ask to have people just refer to the chart b:chuckle
  • Traz - Dreamweaver
    Traz - Dreamweaver Posts: 2,843 Arc User
    edited February 2012
    Paytah - Lost City wrote: »
    i suppose its not to much to ask to have people just refer to the chart b:chuckle

    Heaven forbid something isn't given to them completely on a silver platter. b:avoid
    BladedZero - Sanctuary
    "Also I don't think actually playing through the game helps your skill either."

    -And we wonder why this game has gone to hell b:cute
  • Asterelle - Sanctuary_1381265973
    Asterelle - Sanctuary_1381265973 Posts: 7,881 Arc User
    edited February 2012
    If the nesting limit for if statements is just 7 can't you just break up the if statement across multiple cells?


    Like A2 = if(level diff > 0, B2, C2)

    B2 = if(lev diff < 3, 1.0, if(lev diff < 5, 0.8 if (lev diff < 8, 0.6 ......
    C2 = if(lev diff > -3, 1.0, if (lev diff > -5, 1.2, if (lev diff ......
    [SIGPIC][/SIGPIC]
    Refining Simulator - aster.ohmydays.net/pw/refiningsimulator.html (don't use IE)
    Genie Calculator - aster.ohmydays.net/pw/geniecalculator.html - (don't use IE)
    Socket Calculator - aster.ohmydays.net/pw/socketcalculator.html
  • Traz - Dreamweaver
    Traz - Dreamweaver Posts: 2,843 Arc User
    edited February 2012
    Asterelle - Sanctuary wrote: »
    If the nesting limit for if statements is just 7 can't you just break up the if statement across multiple cells?


    Like A2 = if(level diff > 0, B2, C2)

    B2 = if(lev diff < 3, 1.0, if(lev diff < 5, 0.8 if (lev diff < 8, 0.6 ......
    C2 = if(lev diff > -3, 1.0, if (lev diff > -5, 1.2, if (lev diff ......

    Afraid not. I didn't feel like putting in an hours worth of work. It's the only thing besides levels people need to enter.

    b:nosebleed
    BladedZero - Sanctuary
    "Also I don't think actually playing through the game helps your skill either."

    -And we wonder why this game has gone to hell b:cute
  • _UnGodly_ - Lost City
    _UnGodly_ - Lost City Posts: 92 Arc User
    edited February 2012
    Decus - Dreamweaver wrote: »
    b:sad

    I was referring to you! I couldn't remember your name when I posted.. b:surrender
    [SIGPIC][/SIGPIC]
  • Paytah - Lost City
    Paytah - Lost City Posts: 161 Arc User
    edited February 2012
    i tried to test it but i think theres some sort of modifier were missing in the equation because on higher lvl mobs (like bosses) the game reports something like 59k gained but its true value was 158k and the calcuated number 160k. aside from that its fairly accurate b:pleased
  • Traz - Dreamweaver
    Traz - Dreamweaver Posts: 2,843 Arc User
    edited February 2012
    Paytah - Lost City wrote: »
    i tried to test it but i think theres some sort of modifier were missing in the equation because on higher lvl mobs (like bosses) the game reports something like 59k gained but its true value was 158k and the calcuated number 160k. aside from that its fairly accurate b:pleased

    like which? give me numbers and which mob and i'll check it.
    BladedZero - Sanctuary
    "Also I don't think actually playing through the game helps your skill either."

    -And we wonder why this game has gone to hell b:cute
  • Paytah - Lost City
    Paytah - Lost City Posts: 161 Arc User
    edited February 2012
    those particular numbers were on diabolic shocktrooper
  • SylenThunder - Twilight Temple
    SylenThunder - Twilight Temple Posts: 7,908
    edited February 2012
    Set G3 to =MAX(C10:C15)
    and G4 to =SUM(C10:C15)
    and G6 to =COUNTIF(C10:C15,">0")
    and those fields will fill themselves in.

    I'm working on a formula for C7 to auto-fill.
    [SIGPIC][/SIGPIC]
  • Traz - Dreamweaver
    Traz - Dreamweaver Posts: 2,843 Arc User
    edited February 2012
    SylenThunder - Sanctuary wrote: »
    Set G3 to =MAX(C10:C15)
    and G4 to =SUM(C10:C15)
    and G6 to =COUNTIF(C10:C15,">0")
    and those fields will fill themselves in.

    I'm working on a formula for C7 to auto-fill.

    the first 3 where like that, some ******* keeps entering in regular numbers. b:angryb:angryb:angry
    BladedZero - Sanctuary
    "Also I don't think actually playing through the game helps your skill either."

    -And we wonder why this game has gone to hell b:cute
  • SylenThunder - Twilight Temple
    SylenThunder - Twilight Temple Posts: 7,908
    edited February 2012
    Traz - Dreamweaver wrote: »
    the first 3 where like that, some ******* keeps entering in regular numbers. b:angryb:angryb:angry

    Later I'll work on making a web form out of it to prevent that.
    [SIGPIC][/SIGPIC]
  • Traz - Dreamweaver
    Traz - Dreamweaver Posts: 2,843 Arc User
    edited February 2012
    SylenThunder - Sanctuary wrote: »
    Later I'll work on making a web form out of it to prevent that.

    I could have just started with that, but that's to much effort. I only spent 10 minutes on google docs doing that excel sheet. b:pleased
    BladedZero - Sanctuary
    "Also I don't think actually playing through the game helps your skill either."

    -And we wonder why this game has gone to hell b:cute
  • SylenThunder - Twilight Temple
    SylenThunder - Twilight Temple Posts: 7,908
    edited February 2012
    I used to have some software for turning something like that into a html form but can't find it atm. I'll poke around for it later.
    [SIGPIC][/SIGPIC]
  • Traz - Dreamweaver
    Traz - Dreamweaver Posts: 2,843 Arc User
    edited February 2012
    SylenThunder - Sanctuary wrote: »
    I used to have some software for turning something like that into a html form but can't find it atm. I'll poke around for it later.

    file -> download as -> html

    b:chuckle

    couldn't help myself b:avoid
    BladedZero - Sanctuary
    "Also I don't think actually playing through the game helps your skill either."

    -And we wonder why this game has gone to hell b:cute
  • SylenThunder - Twilight Temple
    SylenThunder - Twilight Temple Posts: 7,908
    edited February 2012
    Traz - Dreamweaver wrote: »
    file -> download as -> html

    b:chuckle

    couldn't help myself b:avoid

    HAHAAA no I mean an actual form entry page with locked fields and open entry boxes. That method won't let you change anything LOL
    [SIGPIC][/SIGPIC]
  • Traz - Dreamweaver
    Traz - Dreamweaver Posts: 2,843 Arc User
    edited February 2012
    SylenThunder - Sanctuary wrote: »
    HAHAAA no I mean an actual form entry page with locked fields and open entry boxes. That method won't let you change anything LOL

    I know, I just couldn't resist. b:chuckle
    BladedZero - Sanctuary
    "Also I don't think actually playing through the game helps your skill either."

    -And we wonder why this game has gone to hell b:cute
  • Paytah - Lost City
    Paytah - Lost City Posts: 161 Arc User
    edited February 2012
    you may want to make it uneditable but still downloadable or something... stop the trolls b:surrender
    or make it editable but not saveable or something
  • Traz - Dreamweaver
    Traz - Dreamweaver Posts: 2,843 Arc User
    edited February 2012
    Paytah - Lost City wrote: »
    you may want to make it uneditable but still downloadable or something... stop the trolls b:surrender

    History is kept of all version of changes, takes 3 seconds to roll it back :P
    BladedZero - Sanctuary
    "Also I don't think actually playing through the game helps your skill either."

    -And we wonder why this game has gone to hell b:cute
  • Paytah - Lost City
    Paytah - Lost City Posts: 161 Arc User
    edited February 2012
    Traz - Dreamweaver wrote: »
    History is kept of all version of changes, takes 3 seconds to roll it back :P

    that works then. b:victory
  • Aruthra - Lost City
    Aruthra - Lost City Posts: 3 Arc User
    edited February 2012
    Traz - Dreamweaver wrote: »
    History is kept of all version of changes, takes 3 seconds to roll it back :P

    wouldnt it be easier not to have to roll it back O.o
  • Traz - Dreamweaver
    Traz - Dreamweaver Posts: 2,843 Arc User
    edited February 2012
    Aruthra - Lost City wrote: »
    wouldnt it be easier not to have to roll it back O.o

    Why would I bother typing all those equations back out it would take way longer then a 3 second rollback.

    b:puzzled
    BladedZero - Sanctuary
    "Also I don't think actually playing through the game helps your skill either."

    -And we wonder why this game has gone to hell b:cute

Categories