formula for a calculation with a varying spreadsheet

[jordan11936]

I've been trying to figure this out for days but to no avail. Any help would be really apreciated and anything you could do to help would be awesome! I've tried an if statement with AND function with no luck either.

my spreadsheet looks like this:
A1 = "One", B1 = 17/08/2013, C1 = 1000.00
A2 = "Two", B2 = 16/08/2013, C2 = 500.00
A3 = "One", B3 = 17/07/2013, C3 = 1500.00
A4 = "Two", B4 = 13/07/2013, C4 = 400.00
A5 = "Two", B5 = 14/07/2013, C5 = 300.00

"One" represents the total that needs to be paid and "Two" represents the payments.

What I want to do is recognise when "One" is present and deduct "two" from that amount, but the whole spreadsheet is never the same (the data is being pasted from somewhere else) so it's really, really difficult to do that.

So I want to do =1000-500 (for that period, so in D2 it should say 500) and then =1500-400-300 (for the other period so in D5 it should say 800).

NB: Any payment ("two") that comes in after the 17th of the month will be carried forward to the next months "one"

Sorry for my horrible explanation. Thank you a lot!

[Richard Buttrey]

Hi and welcome to the forum. Unfortunately your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[jordan11936]

Done........

[Richard Buttrey]

Hi,

I think you'd better upload an example workbook with all permutations of the order of column A rows if relevant. Then add the results you expect to see with some notes explaining your calculation.

[ThirtyTwo]

I've attached a file for you to look at

On each line there is a "One" (debit) it shows the balance in a new column, adding in all the "Two" (credits) that have been received for that month

[newdoverman]

Maybe this will be of value in getting a solution.

[jordan11936]

I've attached a file for you to look at

On each line there is a "One" (debit) it shows the balance in a new column, adding in all the "Two" (credits) that have been received for that month

that's absolutely excellent! thank you very very much!

Couple of problems I hope you can address:

1) Sometimes, there can be 6 "Debits" in the same month with subsequent credits coming underneath so this will be something I have to deal with . Sorry for not mentioning it before!

2) Sometimes, there can be 10+ credits under the debit and I don't think the formula can account for that. The collumn can stretch down to 150 rows

3) to add another spanner into the works... In between the debits and the credits, there can be useless stuff, we'll call "three", "four" and "five" that will have figures and dates, but won't be relevant to the calculation.

Been trying to wrap my head around this in such a short time. Formulas be hard! I'm wondering if we can start it with =If(A2="Debit",....) obviously keeping in mind the varying nature of the spreadsheet.

Thanks again. You guys are my Excel hero's.

[ThirtyTwo]

that's absolutely excellent! thank you very very much!

Couple of problems I hope you can address:

1) Sometimes, there can be 6 "Debits" in the same month with subsequent credits coming underneath so this will be something I have to deal with . Sorry for not mentioning it before!

2) Sometimes, there can be 10+ credits under the debit and I don't think the formula can account for that. The collumn can stretch down to 150 rows

3) to add another spanner into the works... In between the debits and the credits, there can be useless stuff, we'll call "three", "four" and "five" that will have figures and dates, but won't be relevant to the calculation.

Been trying to wrap my head around this in such a short time. Formulas be hard! I'm wondering if we can start it with =If(A2="Debit",....) obviously keeping in mind the varying nature of the spreadsheet.

Thanks again. You guys are my Excel hero's.

1) Sometimes, there can be 6 "Debits" in the same month with subsequent credits coming underneath so this will be something I have to deal with . Sorry for not mentioning it before!

2) Sometimes, there can be 10+ credits under the debit and I don't think the formula can account for that. The collumn can stretch down to 150 rows


Row references have been removed so it now looks at the entire column

Please Login or Register  to view this content.

3) to add another spanner into the works... In between the debits and the credits, there can be useless stuff, we'll call "three", "four" and "five" that will have figures and dates, but won't be relevant to the calculation.

With this change it firstly adds up all the One's, then adds in all the Two's (the negatives)

Please Login or Register  to view this content.

This can be entered into F2, copy and pasted down

[newdoverman]

Can you post a more up to date example of your workbook showing the complications that you mention above?

Is all this activity on one account or are there several accounts involved here?

[jordan11936]

No problem ill do that tonight at8.30gmt when I get in from work.

Its thousands of accounts Im working with. Our current software doesnt show us the credit coming off tthe debit for the period ending 16th, just a running total.

[jordan11936]

Can you post a more up to date example of your workbook showing the complications that you mention above?

Is all this activity on one account or are there several accounts involved here?

here is an example of what I mean. the "I" column is what i want to replicate (with a formula) keeping in mind that B23:B25 is useless stuff.

The spreadsheet always changes, so "useless 5" could be in B3 next time. Also notice some Debits come in without Credits and 6 credits come in a row!

One more thing is that the credit's must have a "-" at the end of it which can be amended with a formula in the I column, I just forgot how!


Thanks guys for all the hard work

[newdoverman]

Am I correct in thinking that this worksheet is calculated from BOTTOM to TOP with all new entries at the top as it appears.

[jordan11936]

Am I correct in thinking that this worksheet is calculated from BOTTOM to TOP with all new entries at the top as it appears.

Correct. It's not a running total though - I have a formula for that. I need to calculate each month.