Help with a simple formula
 

I'm new and trying to learn Excel so please excuse the simple questions. I
want to create a formula on a spread sheet that will subtract the number that
I will put in a new cell every month from a cell that never changes. This
cell - with the answer - sits below the spreadsheet.

PghPatti

If I understand you correctly, you will need 3 cells. One cell, say A1, is
the cell that never changes. The second cell, say B1, is the cell into
which you type a number. The third cell, say C1, is the cell that has the
answer of the second cell subtracted from the first cell. In the third cell
type:
=A1-B1
HTH Otto
"PghPatti" wrote in message
...
I'm new and trying to learn Excel so please excuse the simple questions. I
want to create a formula on a spread sheet that will subtract the number
that
I will put in a new cell every month from a cell that never changes. This
cell - with the answer - sits below the spreadsheet.

PghPatti

Thanks sdo much for your quick reply but I may not have explained myself
clearly. The second number will be in a different cell each month - at the
bottom of a column. I will want/need to leave the number from the previous
month as it is and enter a new number below that each month. What I will want
in a cell with the answer is the current difference between the monthly
number and the beginning number. Does that sound clear as mud - LOL!
Thanks for your help!

Pat

If you hit F4 in the formula or type $ before the letter and cell number for
the cell that never changes and it will create an absolute value for that
cell. You can copy that formula and the value of that cell in the formula
will not change. =$A$1*B1

"PghPatti" wrote:

I'm new and trying to learn Excel so please excuse the simple questions. I
want to create a formula on a spread sheet that will subtract the number that
I will put in a new cell every month from a cell that never changes. This
cell - with the answer - sits below the spreadsheet.

PghPatti

With the starting number in A1 and the monthly number the last entry in
column B then:

=A1-LOOKUP(2,1/B1:B65535,B1:B65535)

will subtract the latest monthly number from the starting number

Note that 65535 is one number short of a full column - the formula will not
work with full columns. If you have any other data in the monthly column
then reduce the 'B' column range to suit.

HTH

Sandy

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"PghPatti" wrote in message
...
Thanks sdo much for your quick reply but I may not have explained myself
clearly. The second number will be in a different cell each month - at the
bottom of a column. I will want/need to leave the number from the previous
month as it is and enter a new number below that each month. What I will
want
in a cell with the answer is the current difference between the monthly
number and the beginning number. Does that sound clear as mud - LOL!
Thanks for your help!

Pat

Now it's me that doesn't understand you. I guess I need to tell you - I'm
blonde - an older one to boot - so it takes some explaining for me. Let me
see if I can show you what I need and you can give me the formula.

A B C D
432 375 200
420 370 190
419 225 189
398 200 243

The number 432 will never change - actually none of the numbers in columns A
B or C will change once they are entered - but I will be always adding a new
number each month - and each year I will add a new column. The one cell in
column D should always be the difference between the last number in Column C
(which will always change) and the number in A1. I realize that every year
when I have to start a new column I will have to move the cell that I now
have in column D or maybe I can just move it down below the 12 rows.

Can you explain the formula that I need to put in there? I could just put
the formula in there but I'd really like to learn what I am doing. Thanks
bunches!

PghPatti

I don't really think that will work. Whenever you add a new cell you would
need to change the formula to reflect the new cell. It would be better to add
a new column to enter your formula and drag the formula down to complete the
math every month. A B C D formula
D results
432 375 200 =$A$1-C1 232
420 370 190 =$A$1-C2 242
419 225 189 =$A$1-C3 243
398 200
Hope this is what you meant.

"PghPatti" wrote:

Now it's me that doesn't understand you. I guess I need to tell you - I'm
blonde - an older one to boot - so it takes some explaining for me. Let me
see if I can show you what I need and you can give me the formula.

A B C D
432 375 200
420 370 190
419 225 189
398 200 243

The number 432 will never change - actually none of the numbers in columns A
B or C will change once they are entered - but I will be always adding a new
number each month - and each year I will add a new column. The one cell in
column D should always be the difference between the last number in Column C
(which will always change) and the number in A1. I realize that every year
when I have to start a new column I will have to move the cell that I now
have in column D or maybe I can just move it down below the 12 rows.

Can you explain the formula that I need to put in there? I could just put
the formula in there but I'd really like to learn what I am doing. Thanks
bunches!

PghPatti

All the "older" blondes I know are pretty foxy, so one really doesn't mind
explaining *anything* to them (relating to XL *of course*).

Since you're adding a new column every month, how about entering this
formula in A13, and then simply drag across the columns each month to copy :

=$A$2-LOOKUP(9.99999999999999E+307,A1:A12)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"PghPatti" wrote in message
...
Now it's me that doesn't understand you. I guess I need to tell you - I'm
blonde - an older one to boot - so it takes some explaining for me. Let me
see if I can show you what I need and you can give me the formula.

A B C D
432 375 200
420 370 190
419 225 189
398 200 243

The number 432 will never change - actually none of the numbers in columns
A
B or C will change once they are entered - but I will be always adding a
new
number each month - and each year I will add a new column. The one cell in
column D should always be the difference between the last number in Column
C
(which will always change) and the number in A1. I realize that every
year
when I have to start a new column I will have to move the cell that I now
have in column D or maybe I can just move it down below the 12 rows.

Can you explain the formula that I need to put in there? I could just put
the formula in there but I'd really like to learn what I am doing.
Thanks
bunches!

PghPatti

You should perhaps, change the $A$2 to $A$1.
My fingers always seem to get confused on the keyboard when addressing
blondes.<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...
All the "older" blondes I know are pretty foxy, so one really doesn't mind
explaining *anything* to them (relating to XL *of course*).

Since you're adding a new column every month, how about entering this
formula in A13, and then simply drag across the columns each month to copy
:

=$A$2-LOOKUP(9.99999999999999E+307,A1:A12)
--
HTH,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-

"PghPatti" wrote in message
...
Now it's me that doesn't understand you. I guess I need to tell you -
I'm
blonde - an older one to boot - so it takes some explaining for me. Let
me
see if I can show you what I need and you can give me the formula.

A B C D
432 375 200
420 370 190
419 225 189
398 200 243

The number 432 will never change - actually none of the numbers in
columns
A
B or C will change once they are entered - but I will be always adding a
new
number each month - and each year I will add a new column. The one cell
in
column D should always be the difference between the last number in
Column
C
(which will always change) and the number in A1. I realize that every
year
when I have to start a new column I will have to move the cell that I
now
have in column D or maybe I can just move it down below the 12 rows.

Can you explain the formula that I need to put in there? I could just
put
the formula in there but I'd really like to learn what I am doing.
Thanks
bunches!

PghPatti

llhughes:
Thanks sooo much - that last suggestion works just great. I really
appreciate it. My questiion is - in the Excel class I took we didn't do any
formulas with the dollar signs in them - what does that do. I tried the
formula without them - LOL - and as you would know - it didn't work - proving
to me that for whatever reason they are needed.

Ragdyer

Thanks! The neat thing about it on these boards is there is no
discrimination - LOL - when I say "older" I REALLY mean OLDER.

Seriously tho - I am learning so much just reading all the posts and answers
- you guys/gals are all just great to spend your time helping everyone - even
us newbies to Excel - and it is greatly appreciated.

PghPatti

I'm so sorry - I was just reading back in the posts and saw where you did
answer my question about the dollar sign. Thanks bunches!

"PghPatti" wrote:


llhughes:
Thanks sooo much - that last suggestion works just great. I really
appreciate it. My questiion is - in the Excel class I took we didn't do any
formulas with the dollar signs in them - what does that do. I tried the
formula without them - LOL - and as you would know - it didn't work - proving
to me that for whatever reason they are needed.