Excel

Unlocking a Password Protected Excel Sheet /  by Alex Hill

It happens every now and then: 

Someone has a really neat (or broken) formula in an Excel document they share with you, but because the sheet has been locked you cannot see the code.

After a bit of digging on the web, this seems like the best solution.

Press Alt+F11

Then enter:

Sub PasswordBreaker()
    'Breaks worksheet password protection.
    Dim i As Integer, j As Integer, k As Integer
    Dim l As Integer, m As Integer, n As Integer
    Dim i1 As Integer, i2 As Integer, i3 As Integer
    Dim i4 As Integer, i5 As Integer, i6 As Integer
    On Error Resume Next
    For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
    For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
    For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
    For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
    ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
        Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
        Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
    If ActiveSheet.ProtectContents = False Then
        MsgBox "One usable password is " & Chr(i) & Chr(j) & _
            Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
            Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
         Exit Sub
    End If
    Next: Next: Next: Next: Next: Next
    Next: Next: Next: Next: Next: Next
End Sub

Run the code and the sheet will be unlocked!

 

Excel Voodoo: Adding Every Nth Row/Column Together [With Breakdown] /  by Alex Hill

Ever wanted to add every Nth column together in Excel?

=SUMPRODUCT($D$10:$D$249*(MOD(ROW($D$10:$D$249),3)=1))

Easy! but what is going on here? I'll post the technical explanation below, but all you need to know is the last two numbers are the key to the whole thing!

Changing the 3

Fill in the blank: "I want to add every ____th cell together." Whatever is in that blank, that should be the number you use instead of 3.

Changing the 1

The 3 divides the cells into blocks of 3, but then you have to tell Excel which number to look at in each block. There is a lot of technical work behind this (see below) but in the interest of keeping life simple, most small formulas can be guess-and-checked.

The number replacing 1 will always be less than the number replacing 3. Start with 0 and look at the result. Is that number close to what you think the result should be?

The alternative (more reliable) method is to take the first cell the formula is searching for (in this case D10) and divide that number (10) by the number replacing 3. Now time for some long division! 3x3=9, so when dividing by 10 there is a remainder of 1. If you wanted the formula to start on D10 the last number in the formula should be 1. Starting on D11 should be 2, and D12 should be 0 (Because there is not a remainder!). Sounds crazy right? That's because this is pretty much Excel Voodoo as far as the average Joe is concerned.

“the row function of coarse returns the row # of a given cell reference.
D10, row = 10...
Simple enough.

By putting it in sumproduct ROW(D10:D249) creates an array of row #s
{10,11,12,13...etc...,249}

Mod returns the remainder after a devision.
Examples,
MOD(10,3) = 1 because..
3 can be devided into 10 3 times
3X3 = 9
10-9 = 1

MOD(11,3) = 2
3 can be devided into 11 3 times
3X3 = 9
11-9 = 2

MOD(12,3) = 0
3 can be devided into 12 4 times
3X4 = 12
12-12 = 0

so MOD({10,11,12,13...etc...,249},3) becomes an array of the results of each mod.
{1,2,0,1,2,0,1,2,0...etc}

So now you have
=SUMPRODUCT($D$10:$D$249*({1,2,0,1,2,0,1,2,0...etc}=1))

Then it tests if each result of the mod = 1, and returns TRUE or False for each
=SUMPRODUCT($D$10:$D$249*({True,false,false,true,false,false,etc..}))

Then multiplying that array of true/false results by the numbers in D10:D249 coerces the true/false to 1/0 (True = 1, False = 0)
=SUMPRODUCT($D$10:$D$249*({1,0,0,1,0,0,1,0,0,etc}))

Then finally the sumproduct does what it does, and sums the result of the array of multiplications between each value in D10:D249 and the corresponding 1 or 0 in the array.”
— http://www.mrexcel.com/forum/excel-questions/621629-sum-every-nth-row.html