Help with a simple formula

[PghPatti]

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

[Otto Moehrbach]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[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

[llhughes]

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

[Sandy Mann]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[PghPatti]

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]

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

[Ragdyer]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Ragdyer]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>

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

[PghPatti]

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

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

[RickBurda]

Patty, the dollar sign "$A1" signifies an Absolute Mode, where "A1" is relative mode. For instance, if the cell references $A1 appears in a formula contained in cell D3 that is drag-copied to cells in the same column, all the cell references will start with $A. In other words, it is the same value ("A1 vs. $A1 or $A$1) its just a way of wheather or not what happens if you drag and copy to other cells. If you have a formula in A3 that reads "=$a$1" and you copy this cell anywhere in the sheet, the result (and formula) will be the same ("=$a$1"). But if you have a formula in A3 of "=$a1" and copy it to another cell, say A4, the formula will add the number of cells from the distance of the start point to the formula, in this case 1. so the formula would read "=$A2". If you move over a column or two, say C3 and pasted it, the formula would show "=$A3". Summary, it keeps the same letter/number that is in the formula, no matter where you paste it. Hope this helped you.

Rick