Formula Help

[Guest]

Here is what I need to do.

My client has a budget of $2,000,000 dollars for a software development
project.

I need to create a spreadsheet that lists the tasks with a level of effort
for each one (1 to 3) 3 would be the most difficult. The formula needs to
take the level of effort into consideration and the total budget amount,
whats left and then return the amount to charge for each task.

Example: Task 1, Level of effort = 3, formula = $????

Any help would be greatly appreciated.

 

[Guest]

Let's put the tasks in column A and the difficulties in column B:

task1 1
task2 1
task3 2
task4 3
task5 2
task6 1
task7 1
task8 1
task9 2
task10 1
task11 3
task12 3
task13 1
task14 2
task15 1
task16 2
task17 1
task18 1
task19 1
task20 1

We will assume that a level2 task is twice as difficult as level1 and a
level3 is three times as difficult as level1. At the bottom of column B in
cell B21 we enter:

=SUM(B1:B20)
which in this case displays 31

Then in C1 we enter:
=2000000*B1/$B$21 and copy down thru C20. We see:


task1 1 64,516.13
task2 1 64,516.13
task3 2 129,032.26
task4 3 193,548.39
task5 2 129,032.26
task6 1 64,516.13
task7 1 64,516.13
task8 1 64,516.13
task9 2 129,032.26
task10 1 64,516.13
task11 3 193,548.39
task12 3 193,548.39
task13 1 64,516.13
task14 2 129,032.26
task15 1 64,516.13
task16 2 129,032.26
task17 1 64,516.13
task18 1 64,516.13
task19 1 64,516.13
task20 1 64,516.13
31


This technique can be adjusted for any number of tasks. It gives a
reasonable way of allocating the budget.

 

[Guest]

Thank you so much! This works great...

Let's put the tasks in column A and the difficulties in column B:

task1 1
task2 1
task3 2
task4 3
task5 2
task6 1
task7 1
task8 1
task9 2
task10 1
task11 3
task12 3
task13 1
task14 2
task15 1
task16 2
task17 1
task18 1
task19 1
task20 1

We will assume that a level2 task is twice as difficult as level1 and a
level3 is three times as difficult as level1. At the bottom of column B in
cell B21 we enter:

=SUM(B1:B20)
which in this case displays 31

Then in C1 we enter:
=2000000*B1/$B$21 and copy down thru C20. We see:


task1 1 64,516.13
task2 1 64,516.13
task3 2 129,032.26
task4 3 193,548.39
task5 2 129,032.26
task6 1 64,516.13
task7 1 64,516.13
task8 1 64,516.13
task9 2 129,032.26
task10 1 64,516.13
task11 3 193,548.39
task12 3 193,548.39
task13 1 64,516.13
task14 2 129,032.26
task15 1 64,516.13
task16 2 129,032.26
task17 1 64,516.13
task18 1 64,516.13
task19 1 64,516.13
task20 1 64,516.13
31


This technique can be adjusted for any number of tasks. It gives a
reasonable way of allocating the budget.