Welcome to the Quilting Board!

Already a member? Login above
OR
To post questions, help other quilters and reduce advertising (like the one on your left), join our quilting community. It's free!

# Thread: Who Knows Excel?

1. I am stumped, can anyone help me? I am doing an assignment that requires me to find the weighted average of a students four exams. I am asked to use absolute cell references. When I enter the formula for one student I get the correct answer according to the book. However I am asked next to use autofill to complete the other students grades and that's my problem. It does not work.

I am in college and don't know what I am doing wrong since excel is very new to me. How do I find out what I am doing wrong? Can anyone help this student?

2. I use excel a lot. I will try and help. I PM'd you

3. We are in the same boat, I am in statistics right now and if I only knew how to work excel it would be sooooo much easier.

Is that bridge near Lynchburg?

4. Hope someone was able to help you...excel was very hard for me to learn about and I really haven't used it since I took an Office class. Good luck!

5. You use a \$ to do an absolute cell reference...ie \$B\$7.

6. So this is more an Excel question than a math one, right?

Are you trying to copy the formulas to another location, and that's what's not working?

Without seeing it or more information, I'd guess you're having ABSolute issues. So this may be relevant. Or it may not be. :mrgreen:

When you use absolutes, you're attempting to lock in both or one of a cell's column or row location.

So if the formula in cell D10 is =\$A\$1, no matter where you copy or move it to on any other cell on your spreadsheet, it will always look for the value in cell A1.

Now, if the formula was =\$A1, and you copied if from cell10
to, say, cell D11, it would still be locked in to column A, but would now be looking at the value one down (since D11 is located one cell down from D10).

Same goes for =A\$1. If you copied that formula from cell D10 to cell D11, it would still look at the contents of cell A1. However, if you copied it to cell E10, then it would look at the value in cell B1.

Now, you can change each formula manually. But that's not fun or efficient.

When you set the initial formulas to figure out whatever it - totals/weighted avgs/%, - look at what would remain constant from student to student - would it be the info in a column or a row? And then lock that in with a \$.

When you hit F4 to make an cell ABS in your formula, it will do a double \$x\$1 first. Hit F4 again, and it locks only the row. Hit it again and it locks only the column. Once more, and it unlocks it all back to x1.

It takes a little getting used to, but if you've figured out what the difference is between COPY and MOVE, then it should make sense.

Good luck!

7. =\$B17*\$C8+\$C9*\$C17+\$C10*\$D17+\$C11*\$E17

This is the formula that I entered using absolute cell reference. Have I entered it wrong? Thanks for your help.

8. can you post a picture of the data?

And you need () around some of the formulas to break it up.

9. Originally Posted by Lneal
=\$B17*\$C8+\$C9*\$C17+\$C10*\$D17+\$C11*\$E17

This is the formula that I entered using absolute cell reference. Have I entered it wrong? Thanks for your help.
The values that have the weights should have two \$. So say that the one value (b17, c17, d17, e17) is the student's grade and the other value (c8, c9, c10, and c11) is the weight, the weight will be the same for each student. It should be:

=\$C\$8*\$B17+\$C\$9*\$C17+\$C\$10*\$D17+\$C\$11*\$E17

(BTW - I reversed the values in the first two so that they are consistent).

10. Chemistry 303
First Semester Scores
Posted 12/20/2010

Students

Class Summary Exam Weight Median Maximum Minimum
Exam 1 20%
Exam 2 20%
Exam 3 20%
Final Exam 40%
Overall 100%

Student Scores Top Ten Overall Scores
Student ID Exam 1 Exam 2 Exam 3 Final Exam Overall
390-120-2 84.0 80.0 83.0 72.0
390-267-4 98.0 92.0 91.0 99.0
390-299-8 54.0 56.0 51.0 65.0
390-354-3 98.0 95.0 90.0 94.0
390-423-5 83.0 83.0 74.0 77.0
390-433-8 52.0 63.0 58.0 53.0
390-452-0 97.0 98.0 93.0 91.0
390-485-7 87.0 77.0 83.0 87.0
390-648-6 94.0 91.0 92.0 97.0
390-699-6 74.0 75.0 50.0 64.0
391-260-8 96.0 84.0 95.0 96.0
391-273-8 73.0 75.0 78.0 74.0
391-315-1 89.0 89.0 73.0 82.0
391-373-1 99.0 94.0 85.0 93.0
391-383-6 92.0 93.0 96.0 80.0
391-500-8 81.0 88.0 78.0 88.0
391-642-7 72.0 80.0 83.0 86.0

I need to find the weighted average of the first students four exams and then use auto fill to complete the remainder.

11. You need parentheses around parts of your formula:

hang on a minute

12. Here you go:

Try this :

timing - oops, I was modifying the format of your first formula, but this one works.

=(\$C\$6*\$B17)+(\$C\$7*\$C17)+(\$C\$8*\$D17)+(\$C\$9*\$E17)

Then next two students should be 94.2 and 54.2 - (i went a little blind copying the scores)

This formula can now be copied straight down the column.
The only thing that's "floating" is the row number.
And the student ID changes on each row.

13. Originally Posted by qbquilts
Originally Posted by Lneal
=\$B17*\$C8+\$C9*\$C17+\$C10*\$D17+\$C11*\$E17

This is the formula that I entered using absolute cell reference. Have I entered it wrong? Thanks for your help.
The values that have the weights should have two \$. So say that the one value (b17, c17, d17, e17) is the student's grade and the other value (c8, c9, c10, and c11) is the weight, the weight will be the same for each student. It should be:

=\$C\$8*\$B17+\$C\$9*\$C17+\$C\$10*\$D17+\$C\$11*\$E17

(BTW - I reversed the values in the first two so that they are consistent).
first person is b17, c17, etc. Use the same formula but but c18, d18 etc. and then next is c19, then next is c20

14. Thank you so Much!! I was literally crying over this one and now I can understand it! If it wasn't for my age I think things would click a little sooner!! :D

15. The parens are really important.

Here's an example:

=4+2*5+3

If you just put that in a cell, then Excel thinks the answer is 17.

But what if I really meant
=(4+2)*(5+3)

or

=(4+2)*5+3

So you needed to group your numbers by function so they could be performed in the order you wanted.

I'm glad you're not crying anymore. ;-)
Please feel free to give a yell if you have any more questions.
I dream in spreadsheets. :mrgreen:

16. I am such a visual person I would actually need to get into Excel to do this. (and it frustrates me that I can't) Hope your problem is solved!!

17. MTS you are a genious in my eyes I am in a statistics class online right now and it is kicking my butt cause I can't work excel.

18. I am trying to understand the PMT function in Excel. Here's what I don't know........
When using a loan grid such as increments of years and loan amounts, is it necessary to use absolute cell references in the PMT function? Does this make sense to anyone what I am asking?

10 yr 20yr 30yr
\$210,000
\$220,000
\$230,000

19. s
Originally Posted by Lneal
I am trying to understand the PMT function in Excel. Here's what I don't know........
When using a loan grid such as increments of years and loan amounts, is it necessary to use absolute cell references in the PMT function? Does this make sense to anyone what I am asking?

10 yr 20yr 30yr
\$210,000
\$220,000
\$230,000
You never HAVE to use F4 \$\$ absolute value references.

What that function does is make it easier to copy formulas from one cell to another (or many) without having to retype or change the formula because the cell location of some of the fixed information changes relative to the new formula location.

And who wants to do that? Might as well go back to green sheets and a Monroe calculator. (Although, I LOVED that calculator to bits.)

So just as I explained upthread, you need to figure out what is constant in the formula and lock that in.

You didn't mention an interest rate, so I'm using 0.
And I'm calculating an annual payment, not monthly

In your example, the # years in Row1 would be locked when figuring out the #payments, and the loan amount in ColumnA would be locked in for PV/principal of the loan.

So the formula would look like this in cell B2 (10yrs/\$210000).

=PMT(0,B\$1,\$A2,0,0)

That could now be copied to the other cells and would work correctly.

If you want monthly payments, you would just use B\$1*12.

You could leave the 0,0 (fv,type) at the end off if you wanted as the defaults are what you would be using anyway.

eta: I changed to formulas to reflect starting at the top corner - so the years are in row 1. Hope that doesn't/didn't confuse you.

20. What I am trying to do is put a formula in one cell and then copy it to the other cells. The interest is 6.5 and it is compounded monthly (12times per yr) The monthly payment is \$1800.

I entered =-PMT(\$E\$5/\$E\$6,D\$9*12,\$C11 this made it correct but I also entered in the cell above....=-PMT(E\$5/E6,\$D\$9*E6,\$10. Although this last entry gives me the correct answer according to the book (and so does the first entry) I can not seem to use auto fill to complete my grid.
This is so hard for me to grasp and MTS thanks for your explanation, it helps.

21. Originally Posted by Lneal

=-PMT(\$E\$5/\$E\$6, D\$9*12, \$C11) this made it correct

=-PMT(E\$5/E6, \$D\$9*E6, \$C10).
Why are the answers correct?

Because the formulas are correct. Not pretty, but correct.

-The rate is calculating off the same cells even though the \$\$ are different,

-The #payments is also calc correctly even though you have 12 in one and E6 in the other. As the value in E6 is 12.......see?

-And as you're referencing the correct PV/loan amounts, each formula would calculate correctly - C10 and C11.

A little tip:
Sometimes, with convoluted formulas - and this isn't even half bad - I will go to the cell with the forumla, EDIT and put a ' in front to make it a label. That way I can really study it, instead of looking up at the bar on top. To make it a formula again, just delete the '.

Try this formula:
=-PMT(\$E\$5/\$E\$6,D\$9*12,\$C11)

This should copy correctly.
I'm assuming row 9 has the #years.

The difference to your first one is that you locked in BOTH the column and the row to figure your rate, but not in the second.
So if you copied the first one, you'd be ok because I'm assuming that's the ONLY place you the 6.5% and 12.
However, if you copy your 2nd one, that formula is going to shift depending on where you move it.

So since the data only appears in those 2 cell on your spreadsheet, you have to LOCK those locations.

And I don't mind helping, but all that information in your second post would have been helpful in the first post.
Saves everyone time. ;-) :thumbup:

22. Thanks so Much for your help again! I just needed a genius in Excel to explain it :D :D I am getting it though, just takes longer for a 54yr old going to college.

:thumbup:

23. Oh I hope someone can help! :D

I am now working with charts in excel and I don't understand this problem........... I am told to insert a data table without legend keys below a 3-D chart. I am asked to do this so the reader is not confused about the relative sizes of the different columns. My question is how do I get that information?

I followed the directions to use the Data Table button in the Labels group on the Chart Tools Layout tab. I am not getting a data table.

24. I'm assuming you've already made the chart.

Right click on the chart area.
Select Chart Options
Tab next to Data Labels is Data Table.
Click on Data Table.
Click on "Show Data Table"

Click on Legend Tab
Remove check for Show Legend

In a perfect world, this should display the data table but not the legend.

Personally, I think this is visually more confusing than the legend.
But what do I know? ;-)

25. Got it! :thumbup: :thumbup:

Page 1 of 2 1 2 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

SEO by vBSEO ©2011, Crawlability, Inc.