Want to do something in Excel. Help!

[CajoleJuice]

So I have a fucking take-home cryptogram to do for a class, and there's some calculations I need to do to figure out something. I really do not want to drudge through it; I rather do it through Excel somehow. Unfortunately, I do not know Excel at all.

I've got numbers in rows 1-4, and columns A-Z. I need to calculate (A1*A2 + B1*B2 + ... + Z1*Z2), and then shift the rows over and do (A1*B2 + B1*C2 + ... + Z1*A2) and do that for all 26 letters. And then do that for rows 3 and 4 as well.

You'd pretty much be my hero if you could help me.

[Skiptastic]

Yeah, dude, I tried to make something in Excel for this. It just became a huge sloppy mess.

I'd just drudge through the cryptogram to be honest.

[CajoleJuice]

Damn

I'm pretty much just as reliable as Excel, but it takes so long.

But trying to figure out how to do it in Excel is taking longer. Guess you're right.

Your first post was that! Just for me.

[Skiptastic]

You know I got nothing but love for ya, Cajole. 

[Solo]

Cant you just use F4 to hold A1, A2, etc. constant (or conversely type $A$1 instead of A1), then just copy and paste the cells?

[CajoleJuice]

I don't even know what that means.

I'm just gonna do it manually, fuck it.

[Skiptastic]

Nah, see, I tried that, but with the summing game, it makes doing the $A$1 game pretty much impossible.

If he just wanted the sums of A1*A2 etc., then A1*A3 etc., then A1*A4 etc. it would be a snap. Just drag the formula across the columns and then sum them. But needing the sums with all the different ways he's talking about, moving down rows, then by each column, it throws a huge wrench in those plans.

[Solo]

Ok, say you had in cell A3 a formula =A1*A2, alright? If you copied cell A3 into cell B3, it would automatically shift to =B1*B2. Following me so far?

But if you wrote in A3 a formula =$A$1*A2, then when you copy and paste A3 into B3, C3, and so on, instead of getting =B1*B2, =C1*C2, etc, youll get =$A$1*B2, =$A$1*C2, etc. The dollar signs hold the number instead of shifting it, which sounds like what you want.

[CajoleJuice]

What Skip said, Solo.

[Solo]

Nevermind then  :'(

[CajoleJuice]

Thanks for trying.

[FatalT]

You need to use absolute cell references by using F4 or the dollar signs like Solo said.

Basically, here's the formula for what you wanted first:

=($A$1*A2)+($B$1*B2)+($C$1*C2)+($D$1*D2)+($E$1*E2)+($F$1*F2)+($G$1*G2)+($H$1*H2)+($I$1*I2)+($J$1*J2)+($K$1*K2)+($L$1*L2)+($M$1*M2)+($N$1*N2)+($O$1*O2)+($P$1*P2)+($Q$1*Q2)+($R$1*R2)+($S$1*S2)+($T$1*T2)+($U$1*U2)+($V$1*V2)+($W$1*W2)+($X$1*X2)+($Y$1*Y2)+($Z$1*Z2)

Here's the formula for what you wanted when you shift the rows over:

=($A$1*B2)+($B$1*C2)+($C$1*D2)+($D$1*E2)+($E$1*F2)+($F$1*G2)+($G$1*H2)+($H$1*I2)+($I$1*J2)+($J$1*K2)+($K$1*L2)+($L$1*M2)+($M$1*N2)+($N$1*O2)+($O$1*P2)+($P$1*Q2)+($Q$1*R2)+($R$1*S2)+($S$1*T2)+($T$1*U2)+($U$1*V2)+($V$1*W2)+($W$1*X2)+($X$1*Y2)+($Y$1*Z2)+($Z$1*A2)

If you can tell me how rows 3 and 4 kinda start, I could make those formulas too.

[CajoleJuice]

[Solo]

SWEAR TO MEEEEEEEEEEEEEEEEEEE

[Skiptastic]

Good luck doing that 24 more times FatalT!

[CajoleJuice]

He seems to be doing it quick! He already got the whole first row (but I had already found the answer myself anyhow). Wasn't quite as tedious as I thought, because the answer gets more obvious with a larger cryptogram.

[FatalT]

Done. It really wasn't that difficult, I just used find and replace on word to change absolute cell references for each cell for the two other rows.

[CajoleJuice]

FatalT

[FatalT]

Here's the spreadsheet if anyone else wants to see it. I had to put it in a zip file because for some reason when it was just the excel document, it wouldn't let you access it.

www.birdnest.org/hoodj3/cajole.zip