?

Log in

prev | next

Productive for the wrong reasons

I just got done spending about two point five hours revamping my DDR Records Excel spreadsheet.

Sorting the list alphabetically into two columns per page is nice, but it lacks flexibility if other sorts such as feet difficulty or dance points become more important.

So I decided to reorganize it. At first, my plan was just to be able to sort/print it in a few basic ways: either alphabetically, or by feet difficulty, or by number of greats, to work on SDGing. But then, in a fit of inspiration, i decided to have Excel automatically determine the dance points and grade in a way similar to [Aaron's DDR Calculator]. Basically, any time i enter my Perfects, Greats, Goods, Boos, Misses, OKs and NGs, it automatically gives me my percentage so i know how far off i am from getting a AA or, for certain hard tunes, an A. Then I can sort my records by that, which is slightly more accurate than SDGing.

It took me a while to get the syntax right for calculating the dance points. It ended up being a biiiiig run-on formula that looks like this:

VALUE=PRODUCT(100*(SUM((C2*2)+(D2)+(H2*6)-(F2*4)-(G2*8)))/(SUM(PRODUCT(SUM(C2:G2)*2)+(PRODUCT(SUM(H2:I2)*6)))))

and tbe formula for giving that a "letter grade" looks like this:

VALUE=IF(J3=100,"AAA",IF(J3>=93,"AA",IF(J3>=80,"A",IF(J3>=65,"B",IF(J3>45,"C","boo!")))))

things like this remind me of when i was in middle school learning how to program in BASIC. Either Excel math is just old-fashioned and fussy, or else i am.

In geeky loose PASCAL terminology: IF ($ExcelMath) != "old-fashioned and fussy" THEN ($Mendel) = "obselete programmer" ELSE ($Mendel) = "inefficient and inconcise programmer that uses ten times more code than he needs to solve a problem."

Either way, i think i lose.

Comments

( read spoken (4) — speak )
(Deleted comment)
lifeofmendel
Sep. 17th, 2003 11:48 am (UTC)
yeah, i tried doing it without all of that stuff, and for some reason i kept on getting weird results. As opposed to getting a percentage value from 0-100, i ended up getting values in the thousands. I think Excel was trying to auto-interpret something for me that I didn't want it to, but i was too lazy to try to figure that out, so i just went for "explain to excel in excess" idea instead.
(Deleted comment)
lifeofmendel
Sep. 17th, 2003 12:30 pm (UTC)
it's possible that that was the issue, but i think it might have had more to do with the fact that it just wasn't interpreting the formula properly... like maybe it wasn't dividing at all because outside of the PRODUCT command a / could mean something else.

it's also possible that i just frelled up my parenths. :)
( read spoken (4) — speak )

profile



welcome to the lifeofmendel

you can also find me here:

meSubscribe to me on YouTube

calendar

March 2017
S M T W T F S
   1234
567891011
12131415161718
19202122232425
262728293031