recruitment forecasting

[naf123]

I’m trying to do a column (in column D) that forecasts actual recruitment based on weekly recruitment rate
=IF(A13>TODAY(),$B$1+$C$1,NA())

Where
Column A contains week in date format
e.g
A4 13/03/17
A5 20/03/17
A6 27/03/17

B1 = actual number of people recruited
C1 = Current weekly recruitment rate

Going back to the formula written above,
If A13 is yesterday, it returns NA() which is good because I don’t need to forecast something that is in the past.
If A13 is tomorrow, it calculates the forecast by adding the number recruited (B1) with the weekly recruitment rate (C1).
This works for forecasting the very next week, however I can’t seem to figure out how to forecast 2 weeks’ time when its basically twice the weekly rate + actual recruited, and 3 weeks’ time which is three times the weekly rate + actual recruited…

I am hoping to drag down the formula in the column D….. and build a chart on forecasted recruitment based on forecasted recruitment weekly!

Can anyone help?

Many thanks

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[naf123]

Ah thank you - attached!

The cells box highlighted in pale orange are the problematic ones - they are not accumulating...

Many thanks!

[JohnTopley]

Try

in E3 and copy down

=IF(A3>TODAY(),(ROWS($1:1)-MATCH(TODAY(),$A$3:$A$20,1))*$J$3+$L$3,NA())

[Glenn Kennedy]

I deleted the zeros from the future recruited numbers. I then used this in E3, copied down:

=IF(C3<>"","",$L$2+$J$2*(COUNTIF($E$2:E2,">0")+1))

I amde a few other changes (highlighted) to streamline things a bit.

[naf123]

Hi


Both very good solutions, thank you so much!

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[naf123]

To make it a bit more challenging, is there a way to preserve historical forecasted rates?

e.g cell E13 is 10.5 today.

Is there a way to keep that fixed after today has passed? I could keep this data in a new column? Just not sure how to preserve that data

Many thanks!

.

[Glenn Kennedy]

Check if this is mathematically accurate. I think it's OK... but.

[naf123]

thanks this is brilliant! Really appreciate it!