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
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
800
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!