I think I need to explain this more in depthly.
1. I am creating a form to report stock levels and distribution form remote areas in developing countries. There are going to be a lot of people adding info to this sheet who have very limited knowledge of Excel.
2. All warehouses don't have the same requirements for which medicines they need to have on hand. The warehouse has to indicate with a 1 or 0 for needed meds vs. non needed.
3. The above mentioned formula is trying to add the stock in each warehouse into an aggregated column.
4. The adjacent cell has a similar formula for the distribution.
5. The next cell divides stock by distribution to find months of stock on hand.
6. Columns will likely get added and subtracted, because the users don't know how to use excel well. The need to add is important, because not all warehouses will report each month.
7. The below table is basically how I want it to look.
Medicine
| Obligatory 1=yes, 0=no
| quantity avail
| distribution
| months of stock
|
A
| 1
| 5000
| 500
| 10
|
b
| 1
| 500
| 500
| 1
|
c
| | | | |
d
| | | | |
<tbody>
</tbody>
The formula referenced at the beginning is the formula used to get the quantity available for medicine A. If I used Neil's formula also for the adjacent cell, it works fine for a and b. For c the months of stock returns #div, but I would like to return a blank. Otherwise the form is too cluttered with non pertinent info.
Finally, the months of stock need to be counted as available or not available in a dichotomous fashion to come up with some indicators. I am currently using a 0, >0 designation to determine the availability. "" causes an error, because it counts it as being available, even though it is not.
If you can help with the top part first, I can move on to the second part later in the thread.