Excel - Calculation Based On Dynamic Criteria

Closed
wliang Posts 41 Registration date Thursday June 16, 2011 Status Member Last seen May 7, 2014 - Oct 8, 2013 at 05:00 AM
wliang Posts 41 Registration date Thursday June 16, 2011 Status Member Last seen May 7, 2014 - Oct 9, 2013 at 01:29 AM
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

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Oct 8, 2013 at 08:17 AM
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
wliang Posts 41 Registration date Thursday June 16, 2011 Status Member Last seen May 7, 2014
Oct 9, 2013 at 01:29 AM
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