Nested Formulas and automated cells updates

Mari81

New Member
Joined
Aug 20, 2019
Messages
1
Hi All,

I am new to Excel so apologies if my question will miss pieces of information or is not clear.

We work within the Construction Industry and make monthly projections of our turnover based on prospect.

BACKGROUND

We assume based on the size of the fee - which is related to the size of the project - that the invoicing will roll for either 12, 18, 24, or 30 months, and assign to each of it a weight:

12 2 3 5 10 15 20 20 10 5 5 3 2
18 2 3 5 8 10 13 13 10 8 5 5 3 3 2 2 2 2 1
24 1 1 1.5 1.5 2.5 2.5 5 5 7.5 7.5 10 10 10 10 5 5 2.5 2.5 2.5 2.5 1.5 1.5 1 1
30 1 1 1.5 1.5 2.5 2.5 5 5 8 9 10 10 10 5 5 5 2 2 2 1.5 1.5 1 1 1 1 1 1 1 1 1


So that for a fee of £45K lasting 12 months we would invoice on the first month: £45*2/100, on the second month £45K*3/100

We have then assumed a starting date for each project and based on the TODAY date if today is the 19/08/19 and the starting date is July-19 we obtain

Aug-19 Sep-19 Oct-19 Nov-19 Dec-19 Jan-20 Feb-20 Mar-20

0 900 1350 2250 4500 6750 9000 9000



the formula used being

=IF(AND(SEP-19+1>START DATE, SEP-19>TODAY DATE), £45k*2/100,0)

we assume that Sept must be > than the starting date and > than today date because if the project doesn't start when we assume and it is still a prospect we need Sept to turn 0 and the other months to keep rolling.


PROBLEM


With this formula what I obtain at the end of August is that Sept turns 0 and October still shows 1350. But what I really need is that October reflects this change by assuming that the projection starts back from the beginning of the weighted period, so that when Sept turn 0 Oct turns 900 and Nov 1350.

Is there any way I can add a piece of formula to obtain this result and automatically have my projections up to date? The spreadsheet is attached below.

Thanks all in advance
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
You cannot attach a file so detail of what you have/want is not entirely clear to me, nor likely to others, judging by the lack of response.

Does something like the below approach offer any possibilities?

Example is for the 12 month roll out.
Vary the fictitious start date to see how it computes for dates where Today is pre-project, in-project and post-project.



Excel 2010
ABCDE
1FeeStart Date12 Stage Fee this month
24500019/10/20190
Sheet3
Cell Formulas
RangeFormula
E2=IFERROR((CHOOSE(DATEDIF(B2,TODAY(),"m"),2,3,5,10,15,20,20,10,5,5,3,2))*A2/100,0)
Excel 2010
ABCDE
1FeeStart Date12 Stage Fee this month
24500019/06/20191350
Sheet3
<br /
Excel 2010
ABCDE
1FeeStart Date12 Stage Fee this month
24500019/07/20180
Sheet3
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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