Excel dont count if and

[humonculi]

I'm doing some homework on excel and I've came across a little problem. I have a list of companies transactions (not a real companies) and there seems to be a little problem with the books. The companies didn't write what was a credit and what was a mistake. How to fin what is a mistake is simple. Credit= negative amount on top so a credit would be
-3,179.00 $ 120(account number)
41,684.88 $ 240(account number)
87.50 $ 320(account number)
3,495.75 $ 460(account number)
And a mistake would have
3,179.00 $ 120 (account number)
(-3,179.00 $) 120 (account number) <------- a correction
A correction under but with the same account and the same amount (not always right after, sometimes further)


(-507.50 $) 320(account number) <------------- a credit
41,684.88 $ 240 (account number)
87.50 $ 320(account number)
3,495.75 $ 460(account number)
My question is, is there a a formula to calculate
if the amount above is positive is there an equivalent negative amount with the same amount somewhere in the list and it cancels its self out and a counter that restarts




Basically i need a way to have all the negatives (corresponding amount and corresponding account) that come before the debits count as "credit" and have all the other negatives that come after be marked as "mistake"

 

[SpillerBD]

B3: = If (A3+A2=0,"Mistake")

 

[humonculi]

B3: = If (A3+A2=0,"Mistake")

The thing is its not necessarily right above some times its a few rows above

 

[humonculi]

Basically if the same out from the Same account (positive amount)
Is cancels the 1 negative

 

[Andrew Fergus]

Hello

How about something like this:

	A	B	C
1	Amount	Code	Notes
2	$3,179.00	120	ok
3	$315.00	150	ok
4	$3,178.00	160	ok
5	$3,150.00	170	ok
6	$3,150.00	150	ok
7	$320.00	150	ok
8	-$3,150.00	150	Correction
9	$256.00	120	ok
10	$564.00	120	ok
11	-$256.00	120	Correction
12	-$444.00	170	Credit
13	-$156.00	120	Credit
14	-$3,150.00	170	Correction

<tbody>
</tbody>

Array Formulas Cell Formula C2 {=IF(A2<0,IF(ISNA(MATCH(0,IF(B$1:B1=B2,A2+A$1:A1),0)),"Credit","Correction"),"ok")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

This won't correctly handle an instance where a credit is entered with the same value as a correction previously entered. The formula in C3 can be copied down.

I trust this helps
Andrew

 

[humonculi]

Hello

How about something like this:

	A	B	C
1	Amount	Code	Notes
2	$3,179.00	120	ok
3	$315.00	150	ok
4	$3,178.00	160	ok
5	$3,150.00	170	ok
6	$3,150.00	150	ok
7	$320.00	150	ok
8	-$3,150.00	150	Correction
9	$256.00	120	ok
10	$564.00	120	ok
11	-$256.00	120	Correction
12	-$444.00	170	Credit
13	-$156.00	120	Credit
14	-$3,150.00	170	Correction

<tbody>
</tbody>

Array Formulas Cell Formula C2 {=IF(A2<0,IF(ISNA(MATCH(0,IF(B$1:B1=B2,A2+A$1:A1),0)),"Credit","Correction"),"ok")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

This won't correctly handle an instance where a credit is entered with the same value as a correction previously entered. The formula in C3 can be copied down.

I trust this helps
Andrew

OMFG i didn't think i was possible but you are the best