# Prorate the amount based on other Cells

#### imran1059

##### Board Regular
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.

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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

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

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

Thanks AliGW

Thanks arthurbr.

You're welcome!

Replies
3
Views
488
Replies
5
Views
2K
Replies
5
Views
395
Replies
5
Views
387

1,196,408
Messages
6,015,102
Members
441,870
Latest member
kojack

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back