Excel dont count if and

humonculi

New Member
Joined
May 29, 2015
Messages
4
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"
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

humonculi

New Member
Joined
May 29, 2015
Messages
4
Basically if the same out from the Same account (positive amount)
Is cancels the 1 negative
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hello

How about something like this:


ABC
1
AmountCodeNotes
2$3,179.00120ok
3$315.00150ok
4$3,178.00160ok
5$3,150.00170ok
6$3,150.00150ok
7$320.00150ok
8-$3,150.00150Correction
9$256.00120ok
10$564.00120ok
11-$256.00120Correction
12-$444.00170Credit
13-$156.00120Credit
14-$3,150.00170Correction

<tbody>
</tbody>



Array Formulas
CellFormula
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
 
Last edited:

humonculi

New Member
Joined
May 29, 2015
Messages
4
Hello

How about something like this:


ABC
1AmountCodeNotes
2$3,179.00120ok
3$315.00150ok
4$3,178.00160ok
5$3,150.00170ok
6$3,150.00150ok
7$320.00150ok
8-$3,150.00150Correction
9$256.00120ok
10$564.00120ok
11-$256.00120Correction
12-$444.00170Credit
13-$156.00120Credit
14-$3,150.00170Correction

<tbody>
</tbody>



Array Formulas
CellFormula
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,447
Messages
5,642,209
Members
417,262
Latest member
andrewd1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top