Who Knows Excel?
#12
Banned
Join Date: Nov 2010
Posts: 4,134
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.
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.
This is the formula that I entered using absolute cell reference. Have I entered it wrong? Thanks for your help.
=$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).
#15
Banned
Join Date: Nov 2010
Posts: 4,134
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:
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:
#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
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
Banned
Join Date: Nov 2010
Posts: 4,134
s
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.
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
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
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.
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.
Thread
Thread Starter
Forum
Replies
Last Post
auntpiggylpn
General Chit-Chat (non-quilting talk)
30
08-05-2012 09:43 PM
Lneal
General Chit-Chat (non-quilting talk)
4
10-17-2010 10:54 AM