=and I need some logical help

[dannyok90]

Evening all!

Im trying to create a new spread sheet and well, iv come accross my brain block..

you can see in the spreadsheet attatched. http://s000.tinyupload.com/?file_id=84408271285290770767

Im trying to write a formula that will populate the 'F' (forcast) Column with the Weekly Spend over the weeks relative to the start and end date

i know you can do this sort of thing with =weekday and i think im onto the right sort of thing but because dates are incrementing in weeks i cant think of a way for excel to know that it needs to put the weekly spend in the columns in between the star and end date if you see what i mean, sorry i know thats pretty rubbish way of explaining!!

any help would be hugely appreciated!

Thanks Guys

 

[dannyok90]

Maybe something like this but its returning an error??

=IF(H3,G3,""), AND(H$1>=$D3,H$1<=E$3))

 

[dannyok90]

Cracked it

=IFERROR((H$1>=$D3,H$1<=E$3),G3)

 

[FDibbins]

Cracked it

=IFERROR((H$1>=$D3,H$1<=E$3),G3)

I don't thing that is really doing what you think it is.
IFERROR will test your formula, if it returns an error, you then specify what to do. If there is no error, it returns your formula.
In your case, this ((H$1>=$D3,H$1<=E$3) will return either TRUE or FALSE, it will not return any value. If the arguments in ((H$1>=$D3,H$1<=E$3) are value, the answer will be TRUE, if not, it will return the contents of G3

If you need to test if a date is between 2 dates (you were almost there)...
=IF(AND(H$1>=$D3,H$1<=E$3),G3,0)

 

[dannyok90]

I don't thing that is really doing what you think it is.
IFERROR will test your formula, if it returns an error, you then specify what to do. If there is no error, it returns your formula.
In your case, this ((H$1>=$D3,H$1<=E$3) will return either TRUE or FALSE, it will not return any value. If the arguments in ((H$1>=$D3,H$1<=E$3) are value, the answer will be TRUE, if not, it will return the contents of G3

If you need to test if a date is between 2 dates (you were almost there)...
=IF(AND(H$1>=$D3,H$1<=E$3),G3,0)

Hi! yes my last post was a little over anticipated, i soon realised that it wasnt returning the correct values, but i did some jigging and i come up with the correct formula which (low and behold) is the same as yours haha. thanks for the guidance though!

What im finding now (i dont know if you've looked at the spreadsheet) is that the forcasted figure is populating under two dates, which technically only represents one week. if the task is 2 weeks its populating under 3 dates, if you see what im saying.. i think its possibly something to do with the >=<= but not sure. heres a link to the current xls http://s000.tinyupload.com/?file_id=02891617996003745039

 

[FDibbins]

I am not able to go to file hosting sites Can you show some sample data here please?

 

[dannyok90]

Sure Stuff

 

[FDibbins]

Sorry to be a pain, but I cannot view images either
Can you copy/paste some data here

 

[dannyok90]

haha, yeah sure no problem, ive done two tables so you can see what im trying to achieve, like i said the first task duration is only 1 week but in the dates to the right its apearing over two dates when them dates only really mean one week.

another thing aswell you maybe able to shed light on, how could i be sure that the dates on the left are always on a weekending friday?

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Subtask

Area​

Budget

Start Date

Finish Date

Weeks

Forcast Wk Spend

03/02/17

04/02/17

05/02/17

06/02/17

07/02/17

08/02/17

2

F

F

F

F

F

F

3

001

Bidding Stage

£33,000

03/02/2017

10/02/2017

1

£33,000

£33,000

£33,000

4

002

Prelims

£10,000

03/02/2017

17/02/2017

2

£5,000

£5,000

£5,000

£5,000

5

6

<tbody>
</tbody>

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Subtask

Area

Budget

Start Date

Finish Date

Weeks

Forcast Wk Spend

=D3

=H1+7

=I1+7

=J1+7

=K1+7

=L1+7

2

F

F

F

F

F

F

3

001

Bidding Stage

£33,000

03/02/2017

=D3+7*F3

1

=IFERROR(C3/F3,"")

=IF(AND(H$1>=$D3,H$1<=$E3),$G3,"")

=IF(AND(I$1>=$D3,I$1<=$E3),$G3,"")

=IF(AND(J$1>=$D3,J$1<=$E3),$G3,"")

=IF(AND(K$1>=$D3,K$1<=$E3),$G3,"")

=IF(AND(L$1>=$D3,L$1<=$E3),$G3,"")

=IF(AND(M$1>=$D3,M$1<=$E3),$G3,"")

4

002

Prelims

£10,000

03/02/2017

=D4+7*F4

2

=IFERROR(C4/F4,"")

=IF(AND(H$1>=$D4,H$1<=$E4),$G4,"")

=IF(AND(I$1>=$D4,I$1<=$E4),$G4,"")

=IF(AND(J$1>=$D4,J$1<=$E4),$G4,"")

=IF(AND(K$1>=$D4,K$1<=$E4),$G4,"")

=IF(AND(L$1>=$D4,L$1<=$E4),$G4,"")

=IF(AND(M$1>=$D4,M$1<=$E4),$G4,"")

5

6

<tbody>
</tbody>

 

[C with no eyes]

Found out why. Take your formula in F3 =IF(AND(H$1>=$D3,H$1<=$E3),$G3,"")

Your AND conditions states if it's more/less than or equal to the column date then put it in.

If you change it to =IF(AND(H$1>=$D3,H$1<$E3),$G3,"") and drag

This will then show it in the first column H (same as the start date)

If you change it to =IF(AND(H$1>$D3,H$1<=$E3),$G3,"") and drag

This will then show it in the second column I (same as the end date)

Also worth pointing out that the dates 03/02/17 to 10/02/17 spans 8 days i.e. Friday to Friday inclusive.

If you keep the formula as it is and just add -1 to your formula in the finish date i.e. in cell E3 put =D3+7*F3-1