Excel Allocation Formula Question

Question

I need an allocation formula, but it has to stop allocating at a set amount. We need to allocate time to each project. So for example we have 1000 projects that are classified as small and large. The smalls are allocated .10 (of an hour) and larges are allocated at .25. The total amount of time is 7.8. I have no idea where to start on this one. I found a crummy formula but it doesn't stop allocating at 7.8.

Total Hours: 7.8

A                               B         C

Project Number         Size      Allocated Time

1                             Large       .25

2                             Large       .25

3                             Large       .25

4                             Small       .10

5                              Small       .10

6                              Small       .10

7                              Small       .10

8                              Small       .10

9                              Small       .10

10                            Small       .10

and so on

Answer 1

Let's say you enter the total 7.8 in a cell, say F1.

In C2, enter the formula

=MIN(IF(B2="Large",0.25,IF(B2="Small",0.1,0)),$F$1-SUM(C$1:C1))

and fill down.

Answer 2

Thank you so much! This is perfect.