Consecutive Figures Matching Timeline

[CraigACE]

Hey Guys,

i'm looking for a solution to the below.
I have a cash-flow calculator which I cannot get to allocate the labour rates in the right weeks.
for example, on a 6 week project, if materials take 2 weeks to deliver (Orange Cell) and labour 4 weeks then I need the labour cost to go against the final 4 weeks (Sheet 2) (weeks 2-6) (Column K)
The cost is a simple formula of labour cost divided by number of labour active weeks.


Sheet 1:

Sheet 2:

could anyone assist with a formula / solution?


thanks,

 

[JimM]

You don't show all the cell refs but in K2 try something like

=IF(and(H2>Sheet1!B10,H2<=Sheet1!B16),Sheet1!B20/(Sheet1!B16-Sheet1!B10),0) and copy down

where B10 is material receipt week ie2 and B16 is the expected length of project ie 6 and B20 is the labour cost ie£4670 (obviously change these references to the actual cells on your sheet)

 

[CraigACE]

You don't show all the cell refs but in K2 try something like

=IF(and(H2>Sheet1!B10,H2<=Sheet1!B16),Sheet1!B20/(Sheet1!B16-Sheet1!B10),0) and copy down

where B10 is material receipt week ie2 and B16 is the expected length of project ie 6 and B20 is the labour cost ie£4670 (obviously change these references to the actual cells on your sheet)

Hi Jim,

thanks for the reply, i cant seem to get this to work, it keeps returning 0's. Im maybe getting the cells mixed up. Ive added a screenshot incl. the references.


Thanks,

 

[JimM]

My bad, I forgot to lock the cell refs! Try this

=IF(AND(H2>Sheet1!$B$14,H2<=Sheet1!$B$20),Sheet1!$B$24/(Sheet1!$B$20-Sheet1!$B$14),0)

 

[CraigACE]

My bad, I forgot to lock the cell refs! Try this

=IF(AND(H2>Sheet1!$B$14,H2<=Sheet1!$B$20),Sheet1!$B$24/(Sheet1!$B$20-Sheet1!$B$14),0)

Brilliant. Thank you! works a charm.