MTS

You need parentheses around parts of your formula:

hang on a minute

MTS

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.

Ilovemydogs

first person is b17, c17, etc. Use the same formula but but c18, d18 etc. and then next is c19, then next is c20

Lneal

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

MTS

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:

Fabriholic

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!!

raedar63

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.

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

MTS

sYou 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.

Lneal

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.