Excel - Calculation Based On Dynamic Criteria

Closed
Report
Posts
41
Registration date
Thursday June 16, 2011
Status
Member
Last seen
May 7, 2014
-
Posts
41
Registration date
Thursday June 16, 2011
Status
Member
Last seen
May 7, 2014
-
Good day,
I have this question as followed:-

I have a table as attached file where I already created a macro to repeat the lines based on the (rounded up) value under column "No. of Carton". Now, I will need a macro to help to calculate the quantity with the last line of item get the balance quantity. The result should be as seen under column "Carton Qty".

For example, 1600pcs of oranges with 330pcs per carton means that there will be 5 cartons. 4 cartons will be 330pcs each with the last carton consists the leftover of 280pcs.

Here is my file for reference: http://speedy.sh/92zS7/Book1.xlsx

Appreciate the help and guidance from everyone.

Thank you.

Best regards,
wliang

2 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
donwload my file from
http://speedy.sh/p7MeX/wilang-131008.xlsx

columns A,B,C are your data.
see formlas i;n other columns in row no. 2 which are copied down. (your calcualtion must be automatic
1
Posts
41
Registration date
Thursday June 16, 2011
Status
Member
Last seen
May 7, 2014

Dear venkat1926,

Thanks for your help. I tried the formula you have provided and it seems fine. However, I would to check with you if it is possible to get the "amt in last column" to be printed at the last row of each category of fruit instead of the first line in the category?

Thanking you in advance for your help.

Best regards,
wliang
0