Prorate the amount based on other Cells

[imran1059]

Hello Everone,

My Column "A" contains the amount that I want to prorate based on if cells in column "B", "C" and "D" in same row are empty or not . Some of them have numerical values in them. I want a formula/macro that will check how many columns in a specific row are not empty and then divide amount that is available in column "A" with the number of columns that are not empty. My example is shown below. My output should be displayed in next columns. The total of 3 columns of my desired output must be the same of Amount in Column "A" for each row.

Amount	Value 1	Value 2	Value 3	Desired Output Column 1	Desired Output Column 2	Desired Output Column 3
6,543.00	10589	10387		3,271.50	3,271.50	0.00
89,650.00	11583			11,583.00	0.00	0.00
8,540.00	15863	13335	14896	2,846.67	2,846.67	2,846.67
40,786.00	20587			40,786.00
164,580.00	25899	19054	18647	54,860.00	54,860.00	54,860.00

<colgroup><col><col span="3"><col span="3"></colgroup><tbody>
</tbody>

Thanks,
Imran.

 

[arthurbr]

Supposing your first row is row 2 and Output 1 in col F
In F2 enter =A2/COUNT(B2:D2)
In G2 =(A2/count((B2:D2))*(count(b2:d2)>=2)
In H2 =(A2/count((B2:D2))*(count(b2:d2)=3)

Then pull down as required

 

[AliGW]

You only need one formula for this. In E2 copied across and down:

=IF(B2="",0,$A2/COUNT($B2:$D2))

 

[imran1059]

Thanks AliGW

 

[imran1059]

Thanks arthurbr.

 

[AliGW]

You're welcome!