Excel 2000 HELP.........

[Allanv]

i need a formula and cant seem to work it out

i have 3 totals i need to reach

a = total delivered

b = total collected

c = balance


if i put say 150 in box A and it shows in Box B... ok so in b2 i put 60 collected its shown in c1 -60 not 90 remeianing?

this is because i have not put 150 in the a2 box as well. then i need another total ish

a-b=90 day1

so my balance is 90 now another del comes in so i put in A3 345 so in c3 it should show 435

a3+c2 = 435 right?

now day 3

balance in c3 = 435 but i now remove in b4 75

b4-c3 = 360

are you still with me ?

and at the bottom the grand total box it only shows whatever goes into the c colomn ??? why???


anyone help me please

 

[NeoV]

Allan, this is a bit confusing...

If you have total delivered, total collected, and balance, wouldn't your formula just be Delivered - Collected - balance?

Explain in more detail what you are trying to do...

 

[Allanv]

i was trying to get the balance field to be updateable

like id i have 60 in the balance would be 60 on the next line i deliver 10 for that lines balance field would show 50

and so on and so forth.


a b c

60 0 60
0 20 40
120 0 160


the balance field needs to be automated


so what would the formula be to have C as the balance but read if its a + or a - entry either i have added to the balance or taken away?

if its still a bit confusing i will upload a sample XLS sheet that i was working on @ work tommorow morning ok


and thanks for your assistance


Regards

Allanv


maybe my first post didnt explain its not the final total i am looking for more a running total of each transaction

 

[NeoV]

ok, here we go....column A is the delivered, column B is the collected, and lets skip a column, so column D is your balance

For row 2, the entries are 60,0 for the delivered and collected amounts, and in cell d2, your formula is simply A2-B2

For row 3, the entries are 0,120 for the delivered and collected amounts, and in cell d3, your formula is d2+ (a3-b3), and you can use this formula all the way down as long as you go.....the first one is the only one that doesn't work that way because there are no values above it....


let me know if you have any questions.

 

[Allanv]

thank you for your reply and it sort of works i am going to upload the file i am messing a bout with ... and how i would like it to look


here ya go


and many thanks for your time and help

 

[OZEE]

Put this is R6 of your spreadsheet, then copy it down...

=IF(OR(L6<>0,N6<>0),R4+L6-N6,"")

If both l6 and n6 (your delivered and collected values) are zero or blank (no entry), then the balance column will just show a blank. As soon as you enter a number for either the delivered or collected, the calculation result will be displayed.

 

[Allanv]

Ozee thank you very much, that solved the problem,

I would have never got that one where did you learn excel where ever it was they were good teachers and if you learnt it yourself then what a good teacher you are.... and thanks again..


NeoV.... thanks for your contribution and sory i didnt explain myself better, but thanks for your help..


you GUYS ARE GREAT