Calculate Once

T

terryve

Each month I get a total for how much we spent in a category. However
I need the "difference" between the new total and the sum of th
previous months so I can plot the data and create a "monthly" statu
report to mgt.
Ex:
500,000 Total Spent to date (Updated Monthly)

Jan 100,000
Feb 0
Mar 1,000
Apr 250,00
May (I need this difference amount ea month in a formula)

Any ideas?

US Air Forc
 
F

Frank Kabel

Hi
maybe something like
$C$1-SUM($B$1:$B1)
if C1 contains your total value and column B the monthly values
 
D

Don Guillett

As an ex AF officer, I'm happy to help out the AF.
=totalspent-sum(months)
=a1-sum(a2:a13)
 
T

terryve

Thanks but my problem is that I need the equation in all the cells bu
when the total changes,; it is picked up in each cell. Is there a wa
to calculate only once? or something!

A D E F G

7 $50,00

8 Plan Actual DFAS
9 Oct-03 $- $-
10 Nov-03 $- $13,000 $20,000
11 Dec-03 $- $13,000 $-
12 Jan-04 $- $12,000 $12,000
13 Feb-04 $- $12,000 $-
14 Mar-04 $- $12,000 $10,000
15 Apr-04 $- $13,000 FALSE
16 May-04 $- $- FALSE
17 Jun-04 $8,000 $- FALSE
18 Jul-04 $8,000 $- FALSE
19 Aug-04 $8,000 $- $8,000
20 Sep-04 $8,000 $- FALSE


I tried this for cell G15:

=IF(MONTH(D15)=MONTH(today()),IF(SUM($G$9:G14)<$G$7-1,$G$7-SUM($G$9:G14),)
I drug this formula down the G col.

But when the month changes and the total in G7 increases (I tested thi
with a sample mo. cell)
each cell in G recalcs, I lose G15, and the diff value jumps to th
month I simulated.

When G7 was increased to $60,000 in Aug., I lost G15 for May and th
difference went to $18,000 in G19. Ugh!

Terry USA
 
D

Don Guillett

I guess I don't understand. Send me a SMALL workbook with clear
explanations. Why isn't the Defense Finance Accounting Service providing
this info to you?
 
Top