Avoid manual inputting of decimal differences

[ExcelPupper]

Hello. I have this data wherein I need to allocate the total amount of the invoice equally based on the number of class it contains.
Allocation should be rounded up to 2 decimal places and sometimes it results to decimal differences. Could you suggest a vba code for this?

 

[Trevor_S]

Firstly - a couple of suggestions with your existing formula:

• You don't need the IF statement. If there is only one row for the invoice number, the second part of the formula would divide the value of column D by 1 - i.e. leave it the same.
• By using a range of $B$2:$B$12, you risk problems should any invoice have more than 11 classes. Provided that the invoice number isn't repeated in column B below the range you need, it would be better to use range $B:$B - as this will look at the entire column.

On this basis, your existing formula would become:
=ROUND(D2/COUNTIF($B:$B,B2),2)

To deal with the differences, you don't need VBA. You just need to add something to the formula which identifies the first row relating to the invoice, and adds any difference to it. Provided that you are able to use the B:B range instead of B2:B12, would suggest this formula:
=ROUND(D2/COUNTIF($B:$B,B2),2)+IF(MATCH(B2,$B:$B,0)=ROW(B2),D2-(ROUND(D2/COUNTIF($B:$B,B2),2)*COUNTIF($B:$B,B2)),0)

I've colour coded the various elements of the formula to help show what each part is doing. The first bit works out the basic allocation without adjusting for differences. Then there's an IF statement, which calculates the adjustment needed to the basic allocation. The MATCH will return the position number in column B where the invoice number first appears - this is compared with the current row number. So for the first invoice row the IF statement will be true, but for the second and third it will be false. Where it is true, the adjustment is calculated as the difference between the total amount and the unadjusted allocation multiplied by the number of times the invoice number appears. Where it is false, the adjustment is zero.

N.B. : If you do need to use the range $B$2:$B$12 instead of $B:$B, you'll need to change all four occurrences in my formula. You'll also need to change the match element to MATCH(B2,$B$2:$B$12,0)+1=ROW(B2) because row 2 would then be the first position in the search range rather than the second.

Hope this helps!

 

[Peter_SSs]

Could you use something like this, copied down?

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. It will generally result in faster & more accurate suggestions.

Nelson78 2020-08-17 1.xlsm
	A	B	C	D	E	F	G
1		Invoice		Amount			Allocation
2		1100		100			33.34
3		1100		100			33.33
4		1100		100			33.33
Sheet1

Cell Formulas
Range	Formula
G2:G4	G2	=IF(B2=B1,ROUND(D2/COUNTIF(B$2:B$12,B2),2),D2-SUMIF(B3:B$12,B2,G3:G$12))

 

[pjmorris]

a similar approach to Peter's which might cater for more disorganised data:

=ROUND(D3/COUNTIF($B$2:$B$12,B3)+IF(COUNTIF($B$1:B3,B3)=1,D3-COUNTIF($B$2:$B$12,B3)*ROUND(D3/COUNTIF($B$2:$B$12,B3),2),0),2)

 

[ExcelPupper]

Firstly - a couple of suggestions with your existing formula:

• You don't need the IF statement. If there is only one row for the invoice number, the second part of the formula would divide the value of column D by 1 - i.e. leave it the same.
• By using a range of $B$2:$B$12, you risk problems should any invoice have more than 11 classes. Provided that the invoice number isn't repeated in column B below the range you need, it would be better to use range $B:$B - as this will look at the entire column.

On this basis, your existing formula would become:
=ROUND(D2/COUNTIF($B:$B,B2),2)

To deal with the differences, you don't need VBA. You just need to add something to the formula which identifies the first row relating to the invoice, and adds any difference to it. Provided that you are able to use the B:B range instead of B2:B12, would suggest this formula:
=ROUND(D2/COUNTIF($B:$B,B2),2)+IF(MATCH(B2,$B:$B,0)=ROW(B2),D2-(ROUND(D2/COUNTIF($B:$B,B2),2)*COUNTIF($B:$B,B2)),0)

I've colour coded the various elements of the formula to help show what each part is doing. The first bit works out the basic allocation without adjusting for differences. Then there's an IF statement, which calculates the adjustment needed to the basic allocation. The MATCH will return the position number in column B where the invoice number first appears - this is compared with the current row number. So for the first invoice row the IF statement will be true, but for the second and third it will be false. Where it is true, the adjustment is calculated as the difference between the total amount and the unadjusted allocation multiplied by the number of times the invoice number appears. Where it is false, the adjustment is zero.

N.B. : If you do need to use the range $B$2:$B$12 instead of $B:$B, you'll need to change all four occurrences in my formula. You'll also need to change the match element to MATCH(B2,$B$2:$B$12,0)+1=ROW(B2) because row 2 would then be the first position in the search range rather than the second.

Hope this helps!

Thanks for this. Great help!

 

[Peter_SSs]

Glad you got something that suited. Thanks for letting us know..