• 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.

Quantity distribution

Thomas Kuriakose

Active Member
Respected Sirs,

I need your guidance and help, I am struggling to distribute a three year quantity usage of items proportionally into three year quarters. I am not sure how to use thsi input data to arrive at the total usage

The data available is as follows -
1. List of Items - A3:A26 - input
1. Total usage of last three years - B3:B26 - input
2. Usage distribution - I3:T26 - to be worked out

Kindly find attached the file for your kind reference.

Thank you very much for your support always.

Much appreciated,

with regards,
thomas
 

Attachments

  • Quantity distribution.xlsx
    13.8 KB · Views: 2
Hi ,

It is not clear what the output should be like.

The sum total of the past 3 years is 240 ; but was it 80 , 80 and 80 in each of these 3 years ?

What is the purpose of columns C through H in your table ?

Narayan
 
Respected Sir,

Thank you very much for your guidance and support on this question.

For the first three cases, I manually updated to arrive at the distribution to match the total.

The columns C to H was added to check whether the monthly usage was a round number, but this was not a part of the input.

The required output should be whole numbers in quarters and if not based on the quantity used the maximum usage to be distributed in the last year 2019 or the best case which will proportion the quantity.

Thank you very much once again,

with regards,
thomas
 
Hi ,

Wouldn't this work then ?

=Table1[@Quantity]/COUNT($I$1:$T$1)

Enter this in cell I3 , copy across and down.

Narayan
 
Respected Sir,

Thanks once again, I entered the formula, but it gives varying results.

Kindly find attached the file updated for your kind perusal.

Thanks you very much once again,

with regards,
thomas
 

Attachments

  • Quantity distribution.xlsx
    15.7 KB · Views: 1
Respected Sir,

I missed to give a absolute reference in the table. Sorry for this.

Here are the updated results,

How can we get whole numbers instead of decimal values in such a case, is there a way to add the balance portion to the year 2019 quarters.

Thank you very much for your support,

with regards,
thomas
 

Attachments

  • Quantity distribution.xlsx
    14.6 KB · Views: 2
Respected Sirs,

Thank you so much for the two solutions provided.

These are awesome solutions, this works perfectly,

Very much appreciated always.

with regards,
thomas
 
Back
Top