Excel Question / Formula Needed

[timbo343]

Hi All

 

Im after a massive favour. I need to create a spreadsheet with a running total but only to today's date. Please see the attachment. All the instructions are in there.

 

Thanks

 

Tim

Test.zip

[Michael]

The file is corrupt...

[EduTech]

Yh file is corrupt (no files to extract)

 

James.

[kmount]

Works fine for me, just can't do what he wants

[kmount]

Have uploaded it again to see if you guys can open mine.

blah.zip

[EduTech]

Gharr! now ur one is corrupt lol. whats going on

[kmount]

Weird ...

 

Only thing different between us is that I'm on linux.

 

 

EDIT: Try just renaming this one to xls from .zip.

another_go.zip

[EduTech]

Nope dont work.. it dont want me to help you lol sorry

[EduTech]

Yay! works now.. Stoopid IE 8

 

I am uninstalling that peice of crap right now! only lasted 3hrs and amount of problems unbelivable bloody M$

 

Works with FF

 

James.

[EduTech]

Ignore me i never read it right

[timbo343]

lol, seems like you guys are having trouble even before you have a look at the problem. I can see it been a very complex formula

 

Thanks

[timbo343]

Updated: File Attachment

 

Hopefully this one isnt corrupt!

 

EDIT: I cant upload a stable version. They are all corrupt when they are uploaded.

 

Ok I have uploaded a screen shot, hope it makes it a bit more clear

 

Thanks

Excel Sheet.zip

Edited August 4, 2008 by timbo343

[kmount]

I don't know what's up because it works fine for me

 

Screenie attached.

[EduTech]

for all you windows users, i think there is an issue with IE

 

If you use FF it works sound well did for me anyhow.

 

James.

[dhicks]

I need to create a spreadsheet with a running total but only to today's date.

 

You can do this with an IF formula in the columns to the right of the numbers you're typing in. If the given date is less than or equal to the current date (given by NOW()) then result is the value in the cell (or the negative of it, if you're looking at the "out" value), otherwise 0. I.e in F9 put:

 

=IF(A9

 

And if F10 put:

 

=IF(A10

 

Make sure you start off with the correct total - in F6 put:

 

=C6

 

And in C4, calculate your total:

 

=SUM(F6:F52)

 

Adjust the formula for columns G and H. Hide them afterwards if you want.

 

--

David Hicks

[m2d2]

As for actual problem, there are two possible solutions. You could enter the out figure with a minus then you would just do a simple SUM to get to the running total.

 

Alternatively, you could SUMIF so that the value in a particular column is only added to the running total if the word out appears in column B. As an example put the following in cell C4.

 

=SUMIF(B5:B1000,"=IN",C5:C1000)-SUMIF(B5:B1000,"=OUT",C5:C1000)

Edited August 4, 2008 by m2d2

[timbo343]

You can do this with an IF formula in the columns to the right of the numbers you're typing in. If the given date is less than or equal to the current date (given by NOW()) then result is the value in the cell (or the negative of it, if you're looking at the "out" value), otherwise 0. I.e in F9 put:

 

=IF(A9

 

And if F10 put:

 

=IF(A10

 

Make sure you start off with the correct total - in F6 put:

 

=C6

 

And in C4, calculate your total:

 

=SUM(F6:F52)

 

Adjust the formula for columns G and H. Hide them afterwards if you want.

 

--

David Hicks

 

 

THANKS!!!!!

[kmount]

Give that man a cookie, he makes it sound simple.

[m2d2]

@dhicks

I am sorry but using another column is cheating;).

 

I wanted to do the entire formula in one cell but I can't get MATCH() to return correct offset from A5 . I wanted to use this as upper bound of the range of each SUM().

[dhicks]

I am sorry but using another column is cheating

 

Hmm! It's the sort of thing where you wish Excel's formula language was more capable and could do something like map(array, function).

 

--

David Hicks

[TechSupp]

Its not a solution to the excel problem just a comment that I have had this problem when downloading zip files etc from edugeek (other sites seem fine). Downloads OK but can't open it as it says it is corrupt?

[kmount]

Interesting.. What browser are you using?

[EduTech]

Make a post in Site problems instead of us sorting it our here

 

James.