Lookup Function for Excel 2007

[Cornelius]

We were given the "hint" to use the "lookup" function possibly to make this
excel chart work. If there's a better way without using the lookup function
that would work great too.

So, we have 5 different tasks that have to be completed, each with a start
day, end day, and cost per day. Then we have to bill every certain number of
days and find the cost of that time period.

The problem is that certain tasks overlap in time period. If task A lasts
0-10 days, task B lasts 5-25 days, and we are billing every 15 days, how
would I add 10 days of the task A and only 10 days of task B to the first
bill? It's easy to do this in my head, but I just can't figure out how to
write it in Excel code.

I've tried using IF functions with not much luck and I can't really figure
out how "lookup" functions would apply.

All start, end, costs, and billing periods have to be variable.

Thanks for any help!

 

[Shane Devenshire]

Hi,

Why don't you show us a sample of your data and the result you want.

 

[Cornelius]

Task Start End Cost/Day
A 0 35 1714.29
B 15 40 2720.00
C 30 50 2250.00
D 45 75 3833.33
E 50 85 2428.57

Bill Date Period Payment
0 0
15 25,714
30 66,514
45 69,521
60 93,036

This is just some sample data and how it should turn out.

I have to write the code for the period payment column. As you can see,
some of the periods overlap, so each "period payment" cell should check the
periods of all of the "tasks" and then multiply from there.