Quantity distribution

[Thomas Kuriakose]

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

 

[NARAYANK991]

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

 

[Thomas Kuriakose]

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

 

[NARAYANK991]

Hi ,

Wouldn't this work then ?

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

Enter this in cell I3 , copy across and down.

Narayan

 

[Thomas Kuriakose]

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

 

[Thomas Kuriakose]

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

 

[NARAYANK991]

Hi ,

When I do it , I am not getting the results you get.

Narayan

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[Peter Bartholomew]

The attached distributes the balance evenly over the requisite number of quarters.

 

[Thomas Kuriakose]

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