Using array if dates match

[deduwa]

Hi,

I think this can be done using an array formula but I just don't know how.

I have the below table;

Month	Old weight	New Weight
Jan-19	30%	?
Jan-19	10%	?
Feb-19	30%	?
Feb-19	40%	?
Feb-19	25%	?
Mar-19	30%	?
Mar-19	15%	?
Apr-19	11%	?
Apr-19	22%	?

In the 'New Weight' column, can I use an array formula which will re-balance the 'Old weight' to get the total of each month to total 100%?

For example for Jan, if 30% is the old weight then the new weight is 30%/(30%+10%) = 75%. Likewise for Jan, if 10% is the old weight, then the new weight is 10%/(30%+10%) = 25%

Following the same logic in Feb, if 30%, 40%, 25% are the old weights the new weights would be 32%, 42%, 26% respectively.

Hope this makes sense. The same concept will apply for all other months. (Note that the number of rows corresponding to each month is always different)

Thanks in advance.

 

[Eric W]

You just need a regular formula:

Book1
	A	B	C
1	Month	Old weight	New Weight
2	19-Jan	30%	75%
3	19-Jan	10%	25%
4	19-Feb	30%	32%
5	19-Feb	40%	42%
6	19-Feb	25%	26%
7	19-Mar	30%	67%
8	19-Mar	15%	33%
9	19-Apr	11%	33%
10	19-Apr	22%	67%
Sheet4

Cell Formulas
Range	Formula
C2:C10	C2	=B2/(SUMIF(A:A,A2,B:B))

 

[DanteAmor]

Try this

varios 13jul2020.xlsm
	A	B	C
1	Month	Old weight	New Weight
2	19/01/2020	30%	75%
3	19/01/2020	10%	25%
4	19/02/2020	30%	32%
5	19/02/2020	40%	42%
6	19/02/2020	25%	26%
7	19/03/2020	30%	67%
8	19/03/2020	15%	33%
9	19/04/2020	11%	33%
10	19/04/2020	22%	67%
Hoja20

Cell Formulas
Range	Formula
C2:C10	C2	=B2/SUMIF($A$2:$A$10,A2,$B$2:$B$10)

 

[deduwa]

That's great, thank you both. You proved it was actually easier than I thought.

The next part is I want to then use the new weights to get a return for each month (See below)

Month	Old weight	New Weight	Return
Jan-19	30%	75%	1%
Jan-19	10%	25%	0.5%
Feb-19	30%	32%	3%
Feb-19	40%	42%	2%
Feb-19	25%	26%	4.2%
Mar-19	30%	67%	1%
Mar-19	15%	33%	1%
Apr-19	11%	33%	3.5%
Apr-19	22%	67%	4%

I need to use SUMPRODUCT on the 'New Weight' & 'Return' columns such that I get the Return for each month;

	Return
Jan-19	?
Feb-19	?
Mar-19	?
Apr-19	?

Can this be done using one formula and dragging down?

Thanks again.

 

[DanteAmor]

Is this what you need?

varios 13jul2020.xlsm
	A	B	C	D	E	F	G
1	Month	Old weight	New Weight	Return		Month	Return
2	01/01/2019	30%	75%	1.0%		01/01/2019	0.008750
3	01/01/2019	10%	25%	0.5%		01/02/2019	0.028947
4	01/02/2019	30%	32%	3.0%		01/03/2019	0.010000
5	01/02/2019	40%	42%	2.0%		01/04/2019	0.038333
6	01/02/2019	25%	26%	4.2%
7	01/03/2019	30%	67%	1.0%
8	01/03/2019	15%	33%	1.0%
9	01/04/2019	11%	33%	3.5%
10	01/04/2019	22%	67%	4.0%
Hoja20

Cell Formulas
Range	Formula
G2:G5	G2	=SUMPRODUCT(($A$2:$A$10=F2)*($C$2:$C$10)*($D$2:$D$10))
C2:C10	C2	=B2/SUMIF($A$2:$A$10,A2,$B$2:$B$10)

 

[deduwa]

Yes that's it. Thanks again

 

[DanteAmor]

Glad we could help & thanks for the feedback