Microsoft Excel help

[MikelaC1]

This isnt actually a problem with the site, but since this seems to be the computer section, Ive put it here. I am trying to create a spreadsheet with Excel, each line will have several values in it, and I want the M column to do a sum for each line. I know there is a way to have it do it separtely for each line, but I cant seem to make the sheet input the formula for each line, other than manually entering for each line, and if its that ridiculous, then its of no use to me. Can anyone help me with this?

[Zherog]

Do your first row of values. Somewhere in Excel, depending on the version you use, will be a button that has the greek letter signa, That's a shortcut to the sum function. Clicking it will insert a sum function in your current cell, and it'll take it's best guess as to what you're summing; it will almost certainly sum your row if you're in M1 when you click it.

Now, put your mouse in the bottom right corner of M1. It'll change from the standard white "plus" to a black plus symbol. Once you have that symbol, click and drag down; it'll replicate the formula for you through all the cells you drag into, changing the cell as appropriate for the row you're on.

[MikelaC1]

:smallsmile::smallsmile::smallsmile:
I knew the Forum would help me...in record time too.

[Khedrac]

The other thing to remember with Excel formula is the use of $ and copy/paste or fill down.

=SUM(A1:L1)
is the formula for a cell with will sum the first row from column A to column L.
If this is in M1 (say) and copied to M2 then it would arrive as:
=SUM(A2:L2)
Copy it to N2 and you get
=SUM(B2:M2)
copy to L2 and it will error as A0 does not exist.

The $ locks that element of a cell when copied so
=SUM($A$1:$L$1)
is the formula for a cell with will sum the first row from column A to column L.
But it will not be changed by copying and sounds what might be happening to you here (not sure how, as usually you have to press F4 to get this)

=SUM(A$1:$L1)
is where this gets interesting - the first 1 and the L are locked.
so copy this from M1 to N2 and you get
=SUM(B$1:$L2)
which is now summing a block. Avoid this unless you want to experiment and are happy on working out what you are doing.

So if I have read your requirements correctly, go to cell M1 and enter
=SUM(A1:L1)
Then select the M column from M1 down as far as you want and press
control + D
this does the "Fill Down" copying the formula across all the selected rows.

[tomandtish]

And while averages are fairly easy, WEIGHTED averages (if this becomes a need0 are a little more tricky. Feel free to PM me if you need that formula.

(I include this because sooner or later everyone working with spreadsheets needs to start doing averages, then start doing weighted averages).

Edit: Actually, since I've been asked for it twice already, here it is:

The formula is sumproduct and provides a weighted average. So for example, if you are selling objects in the amount and values below:

A B
1 5 $100
2 10 $200
3 3 $500
4 2 $700
5 1 $1000

A straight average of the value is $500. However, a weighted average is $304.76.

The formula:
=SUMPRODUCT(A1:A5,B1:B5)/SUM(A1:A5)

Obviously you change the cells as needed for your own spreadsheet. However, remember that you will always divide by the quantity (as represented by the A cells above)

[Kumori]

The other thing to remember with Excel formula is the use of $ and copy/paste or fill down.

=SUM(A1:L1)
is the formula for a cell with will sum the first row from column A to column L.
If this is in M1 (say) and copied to M2 then it would arrive as:
=SUM(A2:L2)
Copy it to N2 and you get
=SUM(B2:M2)
copy to L2 and it will error as A0 does not exist.

The $ locks that element of a cell when copied so
=SUM($A$1:$L$1)
is the formula for a cell with will sum the first row from column A to column L.
But it will not be changed by copying and sounds what might be happening to you here (not sure how, as usually you have to press F4 to get this)

=SUM(A$1:$L1)
is where this gets interesting - the first 1 and the L are locked.
so copy this from M1 to N2 and you get
=SUM(B$1:$L2)
which is now summing a block. Avoid this unless you want to experiment and are happy on working out what you are doing.

So if I have read your requirements correctly, go to cell M1 and enter
=SUM(A1:L1)
Then select the M column from M1 down as far as you want and press
control + D
this does the "Fill Down" copying the formula across all the selected rows.

I didn't know that about the $ sign. That'll be useful to me, thanks.

[Brother Oni]

A B
1 5 $100
2 10 $200
3 3 $500
4 2 $700
5 1 $1000

A straight average of the value is $500. However, a weighted average is $304.76.

The formula:
=SUMPRODUCT(A1:A5,B1:B5)/SUM(A1:A5)

Obviously you change the cells as needed for your own spreadsheet. However, remember that you will always divide by the quantity (as represented by the A cells above)

Edit: I was trying to wrap my head around your example, then I realised it was a formatting error. :smalltongue:

[MikelaC1]

I am using this spreadsheet to bring together all the point predictions for the upcoming NHL year in order to make my own pool picks. (yes, Im a pool nerd, sue me). So a sample line would be:
xxxxxxxMag A Mag B Mag C Mag D Mag E Mag F Mag G
Crosby 100 xxx 125 xx 109 xx 95 x 104 x 118 x 124

Is there a way to make, say, Mag A count for half, Mag B, C, D to count normal, and Mag E, F, G count double?

[tomandtish]

I am using this spreadsheet to bring together all the point predictions for the upcoming NHL year in order to make my own pool picks. (yes, Im a pool nerd, sue me). So a sample line would be:
xxxxxxxMag A Mag B Mag C Mag D Mag E Mag F Mag G
Crosby 100 xxx 125 xx 109 xx 95 x 104 x 118 x 124

Is there a way to make, say, Mag A count for half, Mag B, C, D to count normal, and Mag E, F, G count double?



=SUM(A1/2)+SUM(B1+C1+D1)+SUM((E1+F1+G1)*2)

..will total up the row in question using that format. So the row above totals 1071 with that formatting.

[Zherog]

He's not looking for a sum; he's looking for a weighted average of sorts. I think. A sum of a player's projected points on the year is pretty useless; a weighted average of the predicted points is useful for those who might have interest in such things.

[Brother Oni]

I am using this spreadsheet to bring together all the point predictions for the upcoming NHL year in order to make my own pool picks. (yes, Im a pool nerd, sue me). So a sample line would be:
xxxxxxxMag A Mag B Mag C Mag D Mag E Mag F Mag G
Crosby 100 xxx 125 xx 109 xx 95 x 104 x 118 x 124

Is there a way to make, say, Mag A count for half, Mag B, C, D to count normal, and Mag E, F, G count double?

Is the weighting factor constant throughout the rows or is it going to vary? Assuming the former:
=SUM((B2*0.5)+C2+D2+E2+(F2*2)+(G2*2)+(H2*2))/7

There's a more elegant way using SUMPRODUCT, but it involves ordering the data differently (this way also works if the weighting varies):

{table]{colsp=3}Crosby
Mag Type|Mag Value|Weighting
A|100|0.5
B|125|1
C|109|1
D|95|1
E|104|2
F|118|2
G|124|2
[/table]

Formula would be:
=SUMPRODUCT(B3:B9,C3:C9)/COUNT(A3:A9)

If it's possible to move the weighting column to somewhere out of the way AND the weightings are consistent, in which case you can keep your original formatting for ease of data entry:
{table]|Mag A|Mag B|Mag C|Mag D|Mag E|Mag F|Mag G
Weighting|0.5|1|1|1|2|2|2
Crosby|100|125|109|95|104|118|124[/table]

Formula would be:
=SUMPRODUCT(B3:H3,$C$2:$H$2)/COUNT($B$1:$H$1)

Edit: Sorry, the COUNT function is to well, count how many items of data you have, for easy expansion of the table.

[MikelaC1]

Dont need an average, the sum works perfectly. Once I get the sum, I sort by the sum total and then have my beginning draft order. Then I have to put in my opinion.

[MikelaC1]

Well, Im back looking for more help here. The problem I am now having is with sorting. I would like the sheet to sort from highest to lowest in the total column, but I cant seem to make it do so. The funny thing is, I can use the SORT function on any other column, but not the M one. In fact, when I click on sort, the M column isnt even an option to pick, unless my curser happened to be in the M column, in which case, its the ONLY option, and even clicking highest to lowest produces no change in the sort.

[Eldan]

Depends a bit on which version of Excel you have, I guess, but I'm sticking to the old ones.

Mark the entire column by clicking on the M. Then choose Data->Sort.

[tomandtish]

Well, Im back looking for more help here. The problem I am now having is with sorting. I would like the sheet to sort from highest to lowest in the total column, but I cant seem to make it do so. The funny thing is, I can use the SORT function on any other column, but not the M one. In fact, when I click on sort, the M column isnt even an option to pick, unless my curser happened to be in the M column, in which case, its the ONLY option, and even clicking highest to lowest produces no change in the sort.

Obviously without seeing your problem I can't say for sure. But one thing to watch out for is blank columns. It affects how you have to select information for sorting. For example, if you have information in columns A, B, C, and E, and column D is empty, then when you select column C and sort it will ask if you want to expand. However, it only expands as long as adjacent columns have information in them. So clicking on C, hitting sort, and saying "yes" to expand gets you a sort on A, B, and C, but not E, since the blank column D is in the way. Likewise, sorting on E doesn't do anything because expanding has blank calls on both sides (D and F).

So if your original formula had data in Columns A-K, a blank in L, and the sums in M, this could be causing the problem of it not selecting everything. As far as selecting columns, this can be avoided by selecting all the columns at the top from the beginning at once before clicking sort.

As for what you say does (or rather doesn't) happen when you sort on column M. Remember,(based on what you told us) column M is not actually numbers. It is a formula that is the sum of the row. So sorting M by itself isn't going to change anything. You have to have all the columns selected so the data that caused one number to be lower or higher is sorted as well. Otherwise, while it will technically rearrange the column, there's no noticeable difference because when the formula is recalculated for each row, it goes back to the original number.

That is, if the Sum in M4 is 5 and the Sum in M5 is 4, then sorting lower to higher technically moves the contents of M5 above M4. But what actually got moved was =SUM(A5:K5), which was automatically changed to =SUM(A4:K4). Translation: The visual value stays the same. Your sort had to actually move A5 - K5 as well for the value to actually move.

[Brother Oni]

I suspect tomandtish is correct in that you're sorting the formula rather than the value, thus there's no visual change.

One way of fixing it would be to copy column M, Paste -> Values to a spare column (say N), highlight all the columns then Data -> Sort by column N.

Unfortunately every time you'd updated the values, you'd have to repaste and resort so for a table you're constantly updating, it's not a very good solution. If it's a table you do maybe once a day or less, it's adequate.

[Zherog]

I was able to do pretty much exactly what Mikela is trying to do: I entered random values in columns A through E. In column F I used a sum formula for the previous five columns. I entered data into five rows, and used click-and-drag to extend the formula for all the rows. Then I sorted on F and had no problems.

I have 2010 version. I know Mikela has the "starter 2010" version. I don't know if that slight difference has an effect. But I was able to sort on the formula column itself - I didn't need to copy the value to another column.

[MikelaC1]

Copying the value to another column didn't work.

[MikelaC1]

Thanks to all for your help, Zherog was able to solve it.

[Zherog]

And for those who were curious, I did two things after Mikela sent me a copy.

1) I noticed that after all her data rows, she had about 100 or so "empty" rows that still had the data formula, resulting in a series of zeroes appearing. I deleted those.

2) Sorting then worked, but there were two things wrong. First, her column header got mixed into the results. Second, the data associated with the sum didn't sort - almost for sure because there were a lot of NULL values in column L. So to solve both these problems, I manually selected the data range to sort, starting with A2 and going to M-whatever-number-row-it-was.

Those two things resulted in a clean sort.

[tomandtish]

At the risk of stating the very obvious, if the column headers are in Row A, checking the "My data has headers" checkbox that is visible once you select sort should cause it to ignore row A when sorting.

Zherog is right that you shouldn't need to do a value paste if you've selected all the material and then sorted by column M. My comment was based on the assumption that what you described made it sound like only column M was being selected (esp. given the choice problems you were having earlier). That is, if only column M is highlighted in the first place, then any sort is of M only, so the other columns stay the same. Therefore values don't change. But as I said, I haven't seen the sheet so can't say for sure. Anyway, glad Zherog got it working for you.