Anybody Feel Excel have the limitation??? For Accounting activities

[Tiong999]

Hello everyone,

I feel frustrated by my limitation understanding with excel. I'm trying to programmed "simple" stock workbook in circulation mode. Not quite simple for me, i spent a week to make it done 90%, but when it almost completed, then mr excell let me know that im using circular referrence and that might cause formula corrupt.

Yessss....it got corrupted.

What i want to ask everyone here, does excel can work with accounting? My problem principle is like a programmed to do simple accounting circular, we have a thousand list of stock with unique treatment from the vendor, we put our maintain activities in daily journal, and trying to automated it to change the stock update to the stock ledger.

If Possible i want to submitted my workbook here, but i can't see how to insert it.

 

[mole999]

You can't add a workbook, but you can use a trusted cloud server to enable others to access (should they wish)

providing you can work within 15 decimal places then the excel should be able to accommodate your needs

a circular reference can be seen on the bottom left of your sheet normally so you know where to fix it

 

[Joe4]

You can also post screen images using the told mentioned here in Section B: http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

That is generally the preferred method, as many users and unable or unwilling to download files for security reasons.

 

[Tiong999]

Ok. Thanks for Both of moderator for your response, however i can't use the html make to insert my sheet.<br>How ever i can show u the image from my website, but in password protected page.<br>https://atmajayakos.wordpress.com/excel-image/<br>Password to open it  : mr.excel<br><br>As the image shown in im failed to look at index value of the -nth match and result error, also the previous result before array search begin, result for a change.<br><br>to make u easier copy paste the formula i performed is below:<br><br>IFERROR(IF(AND(INDEX($E$8:$E$13;SMALL(IF($G$8:$G$13=O8;ROW($G$8:$G$13)-ROW($G$7);"");ROW($A$1));1)="TP";INDEX($H$8:$H$13;SMALL(IF($G$8:$G$13=O8;ROW($G$8:$G$13)-ROW($G$7);"");ROW($A$1));1)="HIGH");MAX(P8;INDEX($B$8:$B$13;SMALL(IF($G$8:$G$13=O8;ROW($G$8:$G$13)-ROW($G$7);"");ROW($A$1));1)+INDEX($L$8:$L$13;SMALL(IF($G$8:$G$13=O8;ROW($G$8:$G$13)-ROW($G$7);"");ROW($A$1));1));IF(AND(INDEX($E$8:$E$13;SMALL(IF($G$8:$G$13=O8;ROW($G$8:$G$13)-ROW($G$7);"");ROW($A$1));1)="TP";INDEX($H$8:$H$13;SMALL(IF($G$8:$G$13=O8;ROW($G$8:$G$13)-ROW($G$7);"");ROW($A$1));1)="AKUM");P8+INDEX($L$8:$L$13;SMALL(IF($G$8:$G$13=O8;ROW($G$8:$G$13)-ROW($G$7);"");ROW($A$1));1);"Stop Until This"));"Then will result error")<br><br>Thanks for anyone could suggest me what to do or check. Thanks<img src="https://atmajayakos.wordpress.com/excel-image/" border="0" alt=""><br><br>

 

[Tiong999]

You can also post screen images using the told mentioned here in Section B: http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

That is generally the preferred method, as many users and unable or unwilling to download files for security reasons.

Hello,

I thought i have solved the problem, but i think i still missing something with my formula. Would u discuss it with me what is i mess with.
In simple way with simple data suppose i want to calculate journal into ledger with different approach when the system condition differ.

Array Formula
=IFERROR(IF(AND($D$8="HIGH";$E$8>=1);MAX(I8;INDEX(($B$8:$B$2222)+($E$8:$E$2222);(SMALL(IF(($C$8:$C$2222=$H$8)*($E$8:$E$2222>=1)*($D$8:$D$2222="HIGH");ROW($D$8:$D$2222)-ROW($B$7);"");J7))));IF(AND($D$8="AKUM";$E$8>=1);I8+INDEX($E$8:$E$2222;(SMALL(IF(($C$8:$C$2222=$H$8)*($E$8:$E$2222>=1)*($D$8:$D$2222="AKUM");ROW($D$8:$D$2222)-ROW($B$7);"");J7)));""));"")

Journal Column from B7:L12 ( Vertically )
B7 ( Date ) C7 ( Product ) H7 ( System ) L7 ( Day Added )
B8 14/Okt A HIGH 5
B9
B10
B11 20/Okt A AKUM 30
B12 04/Nop A HIGH 20

Ledger Column H7:M8 and so on ( Horizontally )
H7 ( Product ) I7 ( Date Started ) J7 (1) K7 (2) L7 (3)
H8 A 15/Okt ???? ????

I put array formula to J8 and drag copy it to the end row 8. Formula work fine, only when H column System is similar, but that not i want to formulate.

AnyOne please assist me what i missing?? i have headache already figure out what i messed.

Thanks for anyone comment.

 

[Tiong999]

i have struggle to find a way fixed my formula calculated the way i want, someone who have a time, please consider to look it

Array formula located at J5 from this :

=IFERROR(IF($C$5:$C$22<>$H$5;"";IF(INDEX($D$5:$D$2219;SMALL(IF($C$5:$C$2219=$H$5;ROW($C$5:$C$2219)-ROW($C$4);"");J4))="AKUM";I5+INDEX($E$5:$E$2219;SMALL(IF($C$5:$C$2219=$H$5;ROW($C$5:$C$2219)-ROW($C$4);"");J4));MAX(I5;INDEX(($B$5:$B$2219)+($E$5:$E$2219);(SMALL(IF($C$5:$C$2219=$H$5;ROW($C$5:$C$2219)-ROW($C$4);"");J4))))));"")

i need the formula could scan each product journal transaction from top to 'k' paramater, with whatever system from the index that meet criteria and make a calculation as the system logic.
If "HIGH" >> MID(last result ; recent date + data from column D)
If "AKUM" >> last result + data from colum D
I put yellow marked to test my formula work not right, i try to play with the input, and see what happened, then yesssss, still not right. My Formula only able to pick data from product the yellow marked cell, so when i change from A to B or C, that the only calculation count and result blank to another product ledger.

Thanks for any review

 

[Tiong999]

Sorry typo mistake above


<pre><code>If "HIGH" >> MAX(last result ; (recent date + data from column D)) >>formula put the highest from (I5 ; B5+E5)</pre></code>
<pre><code>If "AKUM" >> last result + data from colum D >> (I5+E5)</pre></code>