Excel Formula needed

C

Coopster8

trying to get formula to keep track of inventory levels increasing and
decreasing weekly and show vareance...week 1(+/-) week 2, week 2 (+/-) week3
and keep running total of differance
 
B

Bob Phillips

I think you will need to be a load more specific than that.

Examples of data, expected results, what you have to date, what works, what
doesn't, etc.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
C

Coopster8

trying to track vareances in a data for 5 weeks. ie...week1 12k, week2 15,
diff of 3 k, but week3 28k diff of 13k, do care about week 1 number , week 4
10k diff of (18k), don't care about about week 2 and so on need total fig in
one cell, make sence now?
 
M

MyVeryOwnSelf

trying to track vareances in a data for 5 weeks. ie...week1 12k, week2
15, diff of 3 k, but week3 28k diff of 13k, do care about week 1
number , week 4 10k diff of (18k), don't care about about week 2 and
so on need total fig in one cell, ...
Click to expand...

Maybe the csv file below can be adapted to your needs.

------------- cut here -------------
,Inventory,Variance
week1,12000,
week2,15000,"=IF(B3="""","""",B3-B2)"
week3,28000,"=IF(B4="""","""",B4-B3)"
week4,10000,"=IF(B5="""","""",B5-B4)"
week5,,"=IF(B6="""","""",B6-B5)"
week6,,"=IF(B7="""","""",B7-B6)"




Total =,=$B$2+SUM(C:C),
 
C

Coopster8

maybe typed total figure wrong, total figure goes in g3, the week cells read
left to right, so it would be in b3,c3,d3,e3,f,3,, but in g3 i need answer of
diff everytime i put in a new week...ie c3-b3...d3-c3 don't care about b3
anymore...
 
M

MyVeryOwnSelf

... total figure goes in g3, the week
cells read left to right, so it would be in b3,c3,d3,e3,f,3,, but in
g3 i need answer of diff everytime i put in a new week...ie
c3-b3...d3-c3 don't care about b3 anymore...
Click to expand...

If I understand the problem statement now, maybe putting this in g3 would
help. (Combine all 3 lines into one formula.)

=IF(COUNT(B3:F3)>1,
OFFSET(A3,0,COUNT(B3:F3),1,1)-
OFFSET(A3,0,COUNT(B3:F3)-1,1,1),"")
 
C

Coopster8

Myveryownself, Thanks for the input, but it still didn't work, copy and
pasted it so I didn't type it wrong, but still no go, maybe YI think it can
be done, but just can't do it, maybe I'm not saying what I want to say just
thinking it. I think that you got the right idea of what I'm saying, it looks
like you understand me, but nothing works.....But Thank you anyways
 
C

Coopster8

Here is the form I'm working on....
=IF(COUNT(B3:F3)>1,OFFSET(A3,0,COUNT(B3:F3),1,1)-OFFSET(A3,0,COUNT(B3:F3)-1,1,1),"")
I put in Cell G3 (Variance) in MS Office 2003 pro, nothing happens.... The
only diff is I changed b3,c3,d3,e3,f3 and g3 to cells 8 changed in your
formula too? even the a3 to a8... I'm going to add new numbers in each
week...

(A) (B) (C) (D) (E)
(F) (G)
Department Week 1 Week 2 Week 3 Week 4 Week 5 Variance
21 $5 $8 $4 $- $- $-
22 $49,717 $- $- $- $- $-
23 $56,985 $- $- $- $- $-
24 $17,601 $- $- $- $- $-
25 $69,453 $- $- $- $- $-
26 $43,975 $- $- $- $- $-
27 $84,533 $- $- $- $- $-
28 $71,678 $- $- $- $- $-
29 $74,023 $- $- $- $- $-
30 $32,194 $- $- $- $- $-
59 $6,660 $- $- $- $- $-
Total: $506,824 $8 $- $- $- $-
 
M

MyVeryOwnSelf

Here is the form I'm working on....
(A) (B) (C) (D) (E)

(F) (G)
Department Week 1 Week 2 Week 3 Week 4 Week 5
Variance
21 $5 $8 $4 $- $- $-
22 $49,717 $- $- $- $- $-
23 $56,985 $- $- $- $- $-
24 $17,601 $- $- $- $- $-
25 $69,453 $- $- $- $- $-
26 $43,975 $- $- $- $- $-
27 $84,533 $- $- $- $- $-
28 $71,678 $- $- $- $- $-
29 $74,023 $- $- $- $- $-
30 $32,194 $- $- $- $- $-
59 $6,660 $- $- $- $- $-
Total: $506,824 $8 $- $- $- $-
Click to expand...

What an adventure! Each posting reveals new nuggets of treasure ;-)

I posted a csv file that hopefully has useful ideas in it here:
http://www.mediafire.com/?bextvatmmm4

I was most unsure what's expected for the variance of the total.
 
C

Coopster8

Hi Myveryownself,
I would once again to thank you for all your hard work in this formula you
have been doing for me, but there is one more nugget to find before you get
the whole treasure. ready for the clue, or the last piece of the puzzle?
READY???

Each dept is going to need the Variance.... I typed in what you sent me in
that csv? file and saw what you did in your example, and it worked except. I
can't go down each line, I need the answer to stay in that line...... ie...
your dept 71, need each variance to stay in G14, for each week after week not
step down each week in diff dept.... makes since?

better trying to say, week 2 - week 1 = Variance (G14), week3 - week 2
=Variance (G14), week 4 - week 3 = Variance, week 5 - week 4 = Variance (G14)
, same in Dept 72...week 2 - week 1 = Variance (G14), week3 - week 2
=Variance (G14), week 4 - week 3 = Variance, week 5 - week 4 = Variance (G14)

To answer you final question, and get the treasure!!!!!! ;-}

I have to turn in a report for my DM, weekly of my clearance dollars going
up or down for all my depts. what I'm doing when it goes up. and why it went
up. Trying to set up this report so when YOU figure it out, I can send him
this and keep also a monthly tracking of the figures too...
Thank for all your help
Kurt
 
M

MyVeryOwnSelf

Each dept is going to need the Variance.... I typed in what you sent
me in that csv? file and saw what you did in your example, and it
worked except. I can't go down each line, I need the answer to stay in
that line...... ie... your dept 71, need each variance to stay in
G14, for each week after week not step down each week in diff dept....
makes since?
Click to expand...

First of all, the CSV file was not to type in. Double-click on the icon of
a csv file, and it opens in excel.

I believe that the CSV file posted yesterday does what you say. Just now I
typed 22 into F14 and the value in G14 changed accordingly.

In the posted file, rows 14 to 17 are different departments. There isn't
any "step down" for a department. There are fewer entries for different
departments to test that the formulas work in a general way.

Here is link to my true file, ...
Click to expand...

Please don't post Excel spreadsheet "xls" files. They can contain macros
that can contain viruses and lots of people won't open them. Instead, if
the situation arises in the future, do these two steps in Excel:

1. Cause formulas to show by using
Tools > Options > View
and checking the box for "Formulas."

2. Use
File > Save as
and for the "Save as type" choose "CSV." CSV files are plain text files
with no macros (open one in Windows Notepad to see), but they can still be
opened in Excel. Of course, there are lots of Excel features CSV files
can't preserve.

I have to turn in a report for my DM, weekly
Click to expand...

Hope you now have the info to do this.
 
C

Coopster8

Myveryownself,
I see what you mean by the CSV File, I'm not a computer person, but enjoy
working with excel, wish i knew more, always keep making forms and trying new
things with it, never even heard of CSV files, until you said something about
them.... I want to say Thanks for your help on this long thread over this
formula that YOU and only you figured out!!!!
Super Job!!!! Keep it up, helping people like myself learning new things and
figuring things out!!!!!!!!!!!
Kurt
 
M

MyVeryOwnSelf

... Thanks for your help

Your welcome. I'd been looking for an excuse to learn about OFFSET() in
Excel, and your question provided it.
 
C

Coopster8

Just a quick note to say thanks again, just got off phone with MS, the only
problem was doing wrong withy our formula was I was putting Zero in each
block instead of hitting space bar, other than that MS said that it was a
real good formula.. Sorry for the head ache...But thanks for your help!!!!
Kurt
 
Top