• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculated Daily Journal into Ledger with multiple criteria and different calculation system

tiong999

Member
I Found my formula only able to calculated within the range that meet the first system formula scanned and ignore other, but when D column change to similar system, it will work fine.

I spent many scenario to find a way to solved it, but god knows i have skill limitation :(
I knew it must be something with SMALL function of 'k' or any other, i'm not sure, that why i seek for help. I Hope this forum can take me a further step how to solve it.

I Believe u understand what is the scenario of my goal.

Best Regards,
Tiong
 

Attachments

  • Book1.xlsx
    10.4 KB · Views: 15
Hi ,

That you are looking for,

=IFERROR(AGGREGATE(15,6,$B$5:$B$13/($C$5:$C$13=$H5)/($B$5:$B$13>$I5),COLUMNS($A$1:A1)),"")

David
 

Attachments

  • Book123.xlsx
    10.4 KB · Views: 3
Hi ,

That you are looking for,

=IFERROR(AGGREGATE(15,6,$B$5:$B$13/($C$5:$C$13=$H5)/($B$5:$B$13>$I5),COLUMNS($A$1:A1)),"")

David
Im sorry, but that is not what the formula should do.
In plain logic, 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
Today i did an improvement ( a tiny bit ), but that still something wrong. Within product cell C5, 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.

This is my Formula Improved that need someone could tell me where i'm wrong.

Thanks for the tip and improvement from anyone.
 

Attachments

  • Book1_Improvement.xlsx
    10.9 KB · Views: 3
Last edited:
Ok, i will calculated manually.

Product Ledger -- A
J5= 29/Okt/16 >> (B5+E5) >> "HIGH"
K5= 20/Nop/16 >> (B8+E8) >> "AKUM"
L5= 20/Nop/16 >> K5 >> "HIGH"

Product Ledger -- B
J6= 09/Nop/16 >> (B6+E6) >> "HIGH"
K6= 09/Nop/16 >> J6 >> "HIGH"
L6= 16/Nop/16 >> (B10+E10) >> "HIGH"

Product Ledger -- C
J7= 09/Nop/16 >> (I7+E11) >> "AKUM"
K7= 29/Nop/16 >> (J7+E12) >> "AKUM"
L7= 04/Des/16 >> (K7+E13 >> "AKUM"

Thanks in advanced for interesting in my case.
 
Hi ,

Did not really understand the logic !
I do not think the problem is complicated, but I could not understand.

David
 
Thanks David,

The logic is not complicated, what is lack with my formula, it was only able to take an array based on cell C5 that i put yellow marked, instead of the product ledger row H5; H6; H7; as it should be.

Let simplify the think, suppose u write down hundred transaction on daily basis with variety activity of many product (in Vertically ). U will want some ledger (in horizontally) to track your activity in product specified instead of general journal from top to the last stage by stage.

Do you think i explained in english bad as u can't understand me?
I'm sorry if that is how u feel :)
 
Last edited:
Hi ,

First of all, English is not my native language,
Secondly, I really did not understand the logic.
I was very happy to help you.:)

David
 
Yes, i feel frustrated. The logic is similar like u will want to count manually each time u found product { A; B; or C } and classified to each Product Ledger in in their counting, which have different System criteria. I don't think SUMPRODUCT could applied in this scenario, because i need to process journal in detail and transform into ledger, and sometime system HIGH have to take the MAX between ( last result or additional days from recent date ).

i have edited my Book1_Improvement.xlsx in more generic way, hope anyone could understand much better.

Thanks
Tiong
 

Attachments

  • Book1_Improvement.xlsx
    11.4 KB · Views: 8
Try........

J5, array (CSE) formula copy across and down :

=IFERROR(IF((INDEX($D$5:$D$2219,SMALL(IF($C$5:$C$2219=$H5,ROW($C$5:$C$2219)-ROW($C$4)),J$4))="High")+(J$4=1),MAX(I5,INDEX(($B$5:$B$2219)+($E$5:$E$2219),SMALL(IF($C$5:$C$2219=$H5,ROW($C$5:$C$2219)-ROW($C$4)),J$4))),I5+INDEX($E$5:$E$2219,SMALL(IF($C$5:$C$2219=$H5,ROW($C$5:$C$2219)-ROW($C$4)),J$4))),"")

p.s. Please check with J5, I think 29/Oct/16 is correct (not 29/Nov/16 as proposed in J15)

Regards
Bosco
 

Attachments

  • Book1_Improvement.xlsx
    12.1 KB · Views: 6
Last edited:
Well done Bosco, I'm speechless by now. I know it must be something the trick i never aware. I always amazing with some trick not included with function standalone.

Will u tell me what is the logic u add If statement + (J$4=1) which return true, and if true+true=2, how it solved it?

And yess, i mis typo indeed.

I feel so tiny by now LOL :oops::oops:
 
Well done Bosco, I'm speechless by now. I know it must be something the trick i never aware. I always amazing with some trick not included with function standalone.

Will u tell me what is the logic u add If statement + (J$4=1) which return true, and if true+true=2, how it solved it?

And yess, i mis typo indeed.

I feel so tiny by now LOL :oops::oops:

The formula is briefly explain as in :

=IF((Testing of A)+(J$4=1),"Formula A","Formula B")

is equal to

=IF(OR((Testing of A),(J$4=1)),"Formula A","Formula B")

And,

The OR logical_test: (Testing of X)+(J$4=1) --> return :

TRUE+TRUE=TRUE

FALSE+TRUE=TRUE

TRUE+FALSE=TRUE

FALSE+FALSE=FALSE

Since

(J$4=1) give TRUE only in the 1st result cell

So,

The 1st result cell, logical_test always give TRUE, then formula select "Formula A"

The 2nd result and onwards logical_test give TRUE/FALSE, then formula select "Formula A" or "Formula B".

Regards
Bosco
 
Try........

J5, array (CSE) formula copy across and down :

=IFERROR(IF((INDEX($D$5:$D$2219,SMALL(IF($C$5:$C$2219=$H5,ROW($C$5:$C$2219)-ROW($C$4)),J$4))="High")+(J$4=1),MAX(I5,INDEX(($B$5:$B$2219)+($E$5:$E$2219),SMALL(IF($C$5:$C$2219=$H5,ROW($C$5:$C$2219)-ROW($C$4)),J$4))),I5+INDEX($E$5:$E$2219,SMALL(IF($C$5:$C$2219=$H5,ROW($C$5:$C$2219)-ROW($C$4)),J$4))),"")

p.s. Please check with J5, I think 29/Oct/16 is correct (not 29/Nov/16 as proposed in J15)

Regards
Bosco
Hi, Sorry i just realize some error in your formula and my miss typo result proposed of J7 from post 5 above, which might confused u.
Product Ledger -- C
J7= 09/Nop/16 >> (I7+E11) >> "AKUM"

I just aware that formula constructed statically only able to identify HIGH system for cell 1 of Product Ledger.
For Better understanding, i attached workbook to let u know what i mean, in case i'm not able to tell what i mean, as english is not my native.
I kindly waiting for your review.

Regards,
Tiong

Edited : Don't bother it, i found the error myself. It my bad i proposed the result miss typo before.
Formula to enter just deleted ' +(J$4=1) '; it will worked as my need.

Thanks for your kindness.

Regards,
Tiong
 

Attachments

  • Book1_HighMinorError.xlsx
    12 KB · Views: 6
Last edited:
Back
Top