=and I need some logical help

dannyok90

dannyok90

Board Regular
Joined
Aug 30, 2016
Messages
115
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
 
Last edited:

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Maybe something like this but its returning an error??

=IF(H3,G3,""), AND(H$1>=$D3,H$1<=E$3))
 
Upvote 0
dannyok90 said:
Cracked it :)

=IFERROR((H$1>=$D3,H$1<=E$3),G3)
Click to expand...

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)
 
Upvote 0
FDibbins said:
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)
Click to expand...


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
 
Last edited:
Upvote 0
Sure Stuff :)

znva5h.png


2dang9k.png
 
Last edited:
Upvote 0
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? :confused:


[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Subtask[/TD]
[TD]
Area​
[/TD]
[TD="align: center"]Budget[/TD]
[TD="align: center"]Start Date[/TD]
[TD="align: center"]Finish Date[/TD]
[TD="align: center"]Weeks[/TD]
[TD="align: center"]Forcast Wk Spend[/TD]
[TD="align: center"]03/02/17[/TD]
[TD="align: center"]04/02/17[/TD]
[TD="align: center"]05/02/17[/TD]
[TD="align: center"]06/02/17[/TD]
[TD="align: center"]07/02/17[/TD]
[TD="align: center"]08/02/17[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]001[/TD]
[TD]Bidding Stage[/TD]
[TD]£33,000[/TD]
[TD]03/02/2017[/TD]
[TD]10/02/2017[/TD]
[TD]1[/TD]
[TD]£33,000[/TD]
[TD]£33,000[/TD]
[TD]£33,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]002[/TD]
[TD]Prelims[/TD]
[TD]£10,000[/TD]
[TD]03/02/2017[/TD]
[TD]17/02/2017[/TD]
[TD]2[/TD]
[TD]£5,000[/TD]
[TD]£5,000[/TD]
[TD]£5,000[/TD]
[TD]£5,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Subtask[/TD]
[TD]Area[/TD]
[TD]Budget[/TD]
[TD]Start Date[/TD]
[TD]Finish Date[/TD]
[TD]Weeks[/TD]
[TD="align: center"]Forcast Wk Spend[/TD]
[TD="align: center"]=D3[/TD]
[TD="align: center"]=H1+7[/TD]
[TD="align: center"]=I1+7[/TD]
[TD="align: center"]=J1+7[/TD]
[TD="align: center"]=K1+7[/TD]
[TD="align: center"]=L1+7[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001[/TD]
[TD]Bidding Stage[/TD]
[TD]£33,000[/TD]
[TD]03/02/2017[/TD]
[TD]=D3+7*F3[/TD]
[TD]1[/TD]
[TD]=IFERROR(C3/F3,"")[/TD]
[TD]=IF(AND(H$1>=$D3,H$1<=$E3),$G3,"")[/TD]
[TD]=IF(AND(I$1>=$D3,I$1<=$E3),$G3,"")[/TD]
[TD]=IF(AND(J$1>=$D3,J$1<=$E3),$G3,"")[/TD]
[TD]=IF(AND(K$1>=$D3,K$1<=$E3),$G3,"")[/TD]
[TD]=IF(AND(L$1>=$D3,L$1<=$E3),$G3,"")[/TD]
[TD]=IF(AND(M$1>=$D3,M$1<=$E3),$G3,"")[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]002[/TD]
[TD]Prelims[/TD]
[TD]£10,000[/TD]
[TD]03/02/2017[/TD]
[TD]=D4+7*F4[/TD]
[TD]2[/TD]
[TD]=IFERROR(C4/F4,"")[/TD]
[TD]=IF(AND(H$1>=$D4,H$1<=$E4),$G4,"")[/TD]
[TD]=IF(AND(I$1>=$D4,I$1<=$E4),$G4,"")[/TD]
[TD]=IF(AND(J$1>=$D4,J$1<=$E4),$G4,"")[/TD]
[TD]=IF(AND(K$1>=$D4,K$1<=$E4),$G4,"")[/TD]
[TD]=IF(AND(L$1>=$D4,L$1<=$E4),$G4,"")[/TD]
[TD]=IF(AND(M$1>=$D4,M$1<=$E4),$G4,"")[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
365
STARL0RD
S
N
  • Question Question
Need help with organizing processes to proceed. Project / Challenge.
Replies
3
Views
466
noveske
N
D
  • Question Question
I need some help with a sorting procedure
Replies
1
Views
627
Col Delane
C
H
  • Question Question
Calendar userform-text selection
Replies
1
Views
7K
hissonrr
H
O
  • Question Question
Helpful Tool: Reserve Spending and Budget Tracker
Replies
0
Views
479
oddzac
O

Forum statistics

Threads
1,223,907
Messages
6,175,300
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top