Crazy Excel skillz
Jul. 9th, 2009 01:35 pm![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
janestarzI hardly ever work with Excel. And I'm really not all that good at it. But it's so much fun trying to get something done when it actually works (after a few hours of twiddling and tweaking).
I'm writing a testscript but I also want some statistics about my testscript. Test managers love to see percentages. "We've finished nearly 50% of testing" sounds much better than "We've finished 28 of 58 test cases".
But test scripts are kind of hard. Once you've figured out how to set up all the steps and layout in Excel, it's hardly something that will work with the usual =SUM(E1:E51).
This little box cost me nearly an hour's worth of work (not counting the crazy colours that happen automatically). And that's by copying parts of it from another testscript I still had lying around. The hardest was the "To do" (te doen) - between each test script is an empty cell in that column. Which means that you could count all the empty cells in that column, but you'd end up with a testscript that can never be 100% completed!
I ended up with this: =AANTAL.ALS(F2:F91;"")-7
This basically translates to human as "Count all the cells in the range F2 until F91 that have nothing in them and then substract 7". There's seven testcases, so seven empty cells that work as a seperator. (I included the top cell, F2, into all my calculations to make this simpler)
I'm so proud I got this to work!
My next task is to create a seperate Summary tab in the Excel sheet that copies the values of the summary for each tab. And then to add all the testcases together from all the different tabs, and to calculate the percentage for the entire project. Whee!
I'm writing a testscript but I also want some statistics about my testscript. Test managers love to see percentages. "We've finished nearly 50% of testing" sounds much better than "We've finished 28 of 58 test cases".
But test scripts are kind of hard. Once you've figured out how to set up all the steps and layout in Excel, it's hardly something that will work with the usual =SUM(E1:E51).
This little box cost me nearly an hour's worth of work (not counting the crazy colours that happen automatically). And that's by copying parts of it from another testscript I still had lying around. The hardest was the "To do" (te doen) - between each test script is an empty cell in that column. Which means that you could count all the empty cells in that column, but you'd end up with a testscript that can never be 100% completed!
I ended up with this: =AANTAL.ALS(F2:F91;"")-7
This basically translates to human as "Count all the cells in the range F2 until F91 that have nothing in them and then substract 7". There's seven testcases, so seven empty cells that work as a seperator. (I included the top cell, F2, into all my calculations to make this simpler)
I'm so proud I got this to work!
My next task is to create a seperate Summary tab in the Excel sheet that copies the values of the summary for each tab. And then to add all the testcases together from all the different tabs, and to calculate the percentage for the entire project. Whee!
![[identity profile]](https://www.dreamwidth.org/img/silk/identity/openid.png){kind=small}
no subject
Date: 2009-07-09 01:25 pm (UTC)no subject
Date: 2009-07-09 01:28 pm (UTC)no subject
Date: 2009-07-09 01:50 pm (UTC)no subject
Date: 2009-07-09 03:02 pm (UTC)