Welcome to the Quilting Board!

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

Page 1 of 2 1 2 LastLast
Results 1 to 25 of 27

Thread: Who Knows Excel?

  1. #1
    Super Member Lneal's Avatar
    Join Date
    Oct 2009
    Location
    Ohio
    Posts
    1,088
    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. #2
    Super Member Ilovemydogs's Avatar
    Join Date
    Dec 2009
    Location
    White Mountains, AZ
    Posts
    2,707
    Blog Entries
    8
    I use excel a lot. I will try and help. I PM'd you

  3. #3
    Super Member raedar63's Avatar
    Join Date
    Jul 2010
    Location
    Ohio
    Posts
    4,045
    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. #4
    Super Member patchsamkim's Avatar
    Join Date
    Oct 2010
    Location
    Fox Valley Wisconsin
    Posts
    1,937
    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. #5
    Super Member jljack's Avatar
    Join Date
    Apr 2007
    Location
    North Carolina - But otherwise, NOTW
    Posts
    8,073
    Blog Entries
    9
    You use a $ to do an absolute cell reference...ie $B$7.

  6. #6
    MTS
    MTS is offline
    Banned
    Join Date
    Nov 2010
    Posts
    4,301
    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. #7
    Super Member Lneal's Avatar
    Join Date
    Oct 2009
    Location
    Ohio
    Posts
    1,088
    =$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. #8
    MTS
    MTS is offline
    Banned
    Join Date
    Nov 2010
    Posts
    4,301
    can you post a picture of the data?

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

  9. #9
    Senior Member qbquilts's Avatar
    Join Date
    Mar 2011
    Posts
    800
    Quote 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. #10
    Super Member Lneal's Avatar
    Join Date
    Oct 2009
    Location
    Ohio
    Posts
    1,088
    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. #11
    MTS
    MTS is offline
    Banned
    Join Date
    Nov 2010
    Posts
    4,301
    You need parentheses around parts of your formula:

    hang on a minute

  12. #12
    MTS
    MTS is offline
    Banned
    Join Date
    Nov 2010
    Posts
    4,301
    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. #13
    Super Member Ilovemydogs's Avatar
    Join Date
    Dec 2009
    Location
    White Mountains, AZ
    Posts
    2,707
    Blog Entries
    8
    Quote Originally Posted by qbquilts
    Quote 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. #14
    Super Member Lneal's Avatar
    Join Date
    Oct 2009
    Location
    Ohio
    Posts
    1,088
    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. #15
    MTS
    MTS is offline
    Banned
    Join Date
    Nov 2010
    Posts
    4,301
    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. #16
    Senior Member Fabriholic's Avatar
    Join Date
    Feb 2009
    Location
    IL
    Posts
    456
    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. #17
    Super Member raedar63's Avatar
    Join Date
    Jul 2010
    Location
    Ohio
    Posts
    4,045
    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. #18
    Super Member Lneal's Avatar
    Join Date
    Oct 2009
    Location
    Ohio
    Posts
    1,088
    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. #19
    MTS
    MTS is offline
    Banned
    Join Date
    Nov 2010
    Posts
    4,301
    s
    Quote 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. #20
    Super Member Lneal's Avatar
    Join Date
    Oct 2009
    Location
    Ohio
    Posts
    1,088
    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. #21
    MTS
    MTS is offline
    Banned
    Join Date
    Nov 2010
    Posts
    4,301
    Quote 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. #22
    Super Member Lneal's Avatar
    Join Date
    Oct 2009
    Location
    Ohio
    Posts
    1,088
    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. #23
    Super Member Lneal's Avatar
    Join Date
    Oct 2009
    Location
    Ohio
    Posts
    1,088
    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. #24
    MTS
    MTS is offline
    Banned
    Join Date
    Nov 2010
    Posts
    4,301
    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. #25
    Super Member Lneal's Avatar
    Join Date
    Oct 2009
    Location
    Ohio
    Posts
    1,088
    Got it! :thumbup: :thumbup:

Page 1 of 2 1 2 LastLast

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.