IF AND formula

BrutalLogiC

Board Regular
Joined
Feb 26, 2006
Messages
247
Office Version
  1. 2016
Platform
  1. Windows
Hi I've been struggling to write a formula for my cash flow forecast for a week:(

I have:
1. list of potential staff
2. their potential employment start date, highly subject to change
3. their cost of full time employment over a 36 month period...based on them all starting on the same date which is the problem here as there will be many different dates
4. their cost of recruitment/mobilisation which will be applicable 14 days before they start work

For each person their start date is column F62 and is in format dd-mm-yy.

Columns AV62 to CE62 (36 columns) is the cost of employment for each full month... assuming they work a full month..

Column AU62 is the mobilisation cost for each staff member which has to be paid 14 days before they start.

The cash flow forecast needs to run from April 2019 as first employees expected to start early May.... i.e. if they start first half May then their mobilisation cost will go into April 2019.

So I'm trying to write a formula which I can drag out over 37 columns (April 19 + a further 36 months) which will
1. look at the employee start date F62, F63, F64 etc.
2. compares it to the current month of the forecast (which starts in April 19), I've currently got April-19 just typed into cell CH60 then June 19 CI60 etc
3. then look at column AU62 to CE62 with the associated costs
4. enter these costs into the correct month based on the start date

rules/examples
1. mobilisation cost AU62 needs to go in the month 14 days prior to start date which of course might still be the same month
2. if a person starts on 10th May then their mobilisation cost (cell AU62) will go in April and their "month 1" May cost (cell AV62) will be on pro-rata basis for the days employed in that month.. e.g. 31 less 9.
3. if a person starts on 14th June then their mobilisation cost will go in May and their "month 1" June cost will be 30 less 13.
4. if a person starts on 20th November then their mobilisation cost will be in November and their "month 1" November cost will be pro-rata for the days employed in November which would be 11 (20th Nov to 30th Nov).

Hopefully there's someone who can understand and suggest a formula for all this
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,192
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
check it carefully

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>F</th><th>AU</th><th>AV</th><th>AW</th><th>CD</th><th>CE</th><th>CF</th><th>CH</th><th>CI</th><th>CJ</th><th>CK</th><th>CQ</th><th>CR</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">60</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Apr-19</td><td style="text-align: right;;">May-19</td><td style="text-align: right;;">Feb-22</td><td style="text-align: right;;">Mar-22</td><td style="text-align: right;;"></td><td style="text-align: right;;">Apr-19</td><td style="text-align: right;;">May-19</td><td style="text-align: right;;">Jun-19</td><td style="text-align: right;;">Jul-19</td><td style="text-align: right;;">Jan-20</td><td style="text-align: right;;">Feb-20</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">61</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">62</td><td style="text-align: right;;">08/05/19</td><td style="text-align: right;;">500</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">250.0</td><td style="text-align: right;;">1024.2</td><td style="text-align: right;;">1000.0</td><td style="text-align: right;;">1000.0</td><td style="text-align: right;;">1000.0</td><td style="text-align: right;;">1000.0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">63</td><td style="text-align: right;;">09/06/19</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">2000</td><td style="text-align: right;;">2000</td><td style="text-align: right;;">2000</td><td style="text-align: right;;">2000</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.0</td><td style="text-align: right;;">428.6</td><td style="text-align: right;;">2038.1</td><td style="text-align: right;;">2000.0</td><td style="text-align: right;;">2000.0</td><td style="text-align: right;;">2000.0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">64</td><td style="text-align: right;;">04/07/19</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">3000</td><td style="text-align: right;;">3000</td><td style="text-align: right;;">3000</td><td style="text-align: right;;">3000</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.0</td><td style="text-align: right;;">0.0</td><td style="text-align: right;;">1178.6</td><td style="text-align: right;;">3031.1</td><td style="text-align: right;;">3000.0</td><td style="text-align: right;;">3000.0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">65</td><td style="text-align: right;;">18/07/19</td><td style="text-align: right;;">2000</td><td style="text-align: right;;">4000</td><td style="text-align: right;;">4000</td><td style="text-align: right;;">4000</td><td style="text-align: right;;">4000</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.0</td><td style="text-align: right;;">0.0</td><td style="text-align: right;;">0.0</td><td style="text-align: right;;">3806.5</td><td style="text-align: right;;">4000.0</td><td style="text-align: right;;">4000.0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">66</td><td style="text-align: right;;">01/08/19</td><td style="text-align: right;;">2500</td><td style="text-align: right;;">5000</td><td style="text-align: right;;">5000</td><td style="text-align: right;;">5000</td><td style="text-align: right;;">5000</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.0</td><td style="text-align: right;;">0.0</td><td style="text-align: right;;">0.0</td><td style="text-align: right;;">2500.0</td><td style="text-align: right;;">5000.0</td><td style="text-align: right;;">5000.0</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet7</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">CH62</th><td style="text-align:left">=$AU62/14*IF(<font color="Blue">IF(<font color="Red">$F62-14>=CH$60,$F62-14<CI$60</font>),MIN(<font color="Red">14,DAY(<font color="Green">EOMONTH(<font color="Purple">CH$60,0</font>)</font>)-DAY(<font color="Green">$F62-14</font>)+1</font>),
IF(<font color="Red">IF(<font color="Green">$F62-14>=CG$60,$F62-14<CH$60</font>),14-(<font color="Green">MIN(<font color="Purple">14,DAY(<font color="Teal">EOMONTH(<font color="#FF00FF">CG$60,0</font>)</font>)-DAY(<font color="Teal">$F62-14</font>)+1</font>)</font>),0</font>)</font>)+
AV62/DAY(<font color="Blue">EOMONTH(<font color="Red">CH$60,0</font>)</font>)*IF(<font color="Blue">AND(<font color="Red">$F62<=EOMONTH(<font color="Green">CH$60,0</font>)</font>),
IF(<font color="Red">AND(<font color="Green">$F62<=EOMONTH(<font color="Purple">CG$60,0</font>)</font>)=FALSE,DAY(<font color="Green">EOMONTH(<font color="Purple">CH$60,0</font>)</font>)-DAY(<font color="Green">$F62</font>)+1,
DAY(<font color="Green">EOMONTH(<font color="Purple">CH$60,0</font>)</font>)</font>),0</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

BrutalLogiC

Board Regular
Joined
Feb 26, 2006
Messages
247
Office Version
  1. 2016
Platform
  1. Windows
Thanks so much AlanY, I am going to try this tomorrow morning
 

BrutalLogiC

Board Regular
Joined
Feb 26, 2006
Messages
247
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi Alan sorry I can't get it to work the way I envisioned

I think better if I label AU60, AV60, AW60, AX60 etc. as Month 0, Month 1, Month 2, Month 3 etc. even though they are not linked to anything

If I enter:
AU62 = 1000 (this would be month 0, the mobilisation cost due 14 days before start date)
AV62 = 100 (this would be month 1 which might be pro-rata for the first month dependent on what date in the month the person starts)
AW62 = 200 (this amount would be payable month 2 regardless of when the person starts in month 1)
AX62 = 300 (month 3)
AY62 = 400 (month 4)
AZ62 =500 (month 5)
BA62 = 600 (month 6)

and use start date 01 June 2018 in F62... this would be the employee's "month 1" regardless of whether they worked 1 day or 31 days

then
CH62 (April 19, cash flow month 0) should be zero... this works
CI62 (May 19, cash flow month 1) should be 1,000.... this works
CJ62 (June 19, cash flow month 2) should be 100 as it's the month the person started work and they worked the full month ... but it's returning 300 as it's looking at AX62 (month 3)

I'm not sure if it's a simple fix or if I haven't explained the problem very well

if I change start date F62 to 21-06-19 then:
CH62 (April19) should = zero... this works
CI62 (May 19) should = zero... this works
CJ (June 19) should = the "mobilisation" cost from AU62 plus 10 days (21st June to 30th June) worth of the "month 1" cost from AV62
CK (July 19) should = the "month 2" cost from AW62
CL (Aug 19) should = the "month 3" cost from AX62

I guess the whole thing would be a lot easier if I made everyone start on the first day of each month and called this the month 1 and the prior month as the full mobilisation cost...
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,192
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
right, this might do it

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>F</th><th>AU</th><th>AV</th><th>AW</th><th>AX</th><th>AY</th><th>AZ</th><th>BA</th><th>CG</th><th>CH</th><th>CI</th><th>CJ</th><th>CK</th><th>CL</th><th>CM</th><th>CN</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">60</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Apr-19</td><td style="text-align: right;;">May-19</td><td style="text-align: right;;">Jun-19</td><td style="text-align: right;;">Jul-19</td><td style="text-align: right;;">Aug-19</td><td style="text-align: right;;">Sep-19</td><td style="text-align: right;;"></td><td style="text-align: right;;">Apr-19</td><td style="text-align: right;;">May-19</td><td style="text-align: right;;">Jun-19</td><td style="text-align: right;;">Jul-19</td><td style="text-align: right;;">Aug-19</td><td style="text-align: right;;">Sep-19</td><td style="text-align: right;;">Oct-19</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">61</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">62</td><td style="text-align: right;;">01/06/19</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">100</td><td style="text-align: right;;">200</td><td style="text-align: right;;">300</td><td style="text-align: right;;">400</td><td style="text-align: right;;">500</td><td style="text-align: right;;">600</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">0.0</td><td style="text-align: right;background-color: #E2EFDA;;">1000.0</td><td style="text-align: right;background-color: #E2EFDA;;">100.0</td><td style="text-align: right;background-color: #E2EFDA;;">200.0</td><td style="text-align: right;background-color: #E2EFDA;;">300.0</td><td style="text-align: right;background-color: #E2EFDA;;">400.0</td><td style="text-align: right;background-color: #E2EFDA;;">500.0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">63</td><td style="text-align: right;;">08/06/19</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">100</td><td style="text-align: right;;">200</td><td style="text-align: right;;">300</td><td style="text-align: right;;">400</td><td style="text-align: right;;">500</td><td style="text-align: right;;">600</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">0.0</td><td style="text-align: right;background-color: #E2EFDA;;">500.0</td><td style="text-align: right;background-color: #E2EFDA;;">576.7</td><td style="text-align: right;background-color: #E2EFDA;;">200.0</td><td style="text-align: right;background-color: #E2EFDA;;">300.0</td><td style="text-align: right;background-color: #E2EFDA;;">400.0</td><td style="text-align: right;background-color: #E2EFDA;;">500.0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">64</td><td style="text-align: right;;">16/06/19</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">100</td><td style="text-align: right;;">200</td><td style="text-align: right;;">300</td><td style="text-align: right;;">400</td><td style="text-align: right;;">500</td><td style="text-align: right;;">600</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">0.0</td><td style="text-align: right;background-color: #E2EFDA;;">0.0</td><td style="text-align: right;background-color: #E2EFDA;;">1050.0</td><td style="text-align: right;background-color: #E2EFDA;;">200.0</td><td style="text-align: right;background-color: #E2EFDA;;">300.0</td><td style="text-align: right;background-color: #E2EFDA;;">400.0</td><td style="text-align: right;background-color: #E2EFDA;;">500.0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">65</td><td style="text-align: right;;">01/07/19</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">100</td><td style="text-align: right;;">200</td><td style="text-align: right;;">300</td><td style="text-align: right;;">400</td><td style="text-align: right;;">500</td><td style="text-align: right;;">600</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">0.0</td><td style="text-align: right;background-color: #E2EFDA;;">0.0</td><td style="text-align: right;background-color: #E2EFDA;;">1000.0</td><td style="text-align: right;background-color: #E2EFDA;;">100.0</td><td style="text-align: right;background-color: #E2EFDA;;">200.0</td><td style="text-align: right;background-color: #E2EFDA;;">300.0</td><td style="text-align: right;background-color: #E2EFDA;;">400.0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">66</td><td style="text-align: right;;">08/07/19</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">100</td><td style="text-align: right;;">200</td><td style="text-align: right;;">300</td><td style="text-align: right;;">400</td><td style="text-align: right;;">500</td><td style="text-align: right;;">600</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">0.0</td><td style="text-align: right;background-color: #E2EFDA;;">0.0</td><td style="text-align: right;background-color: #E2EFDA;;">500.0</td><td style="text-align: right;background-color: #E2EFDA;;">577.4</td><td style="text-align: right;background-color: #E2EFDA;;">200.0</td><td style="text-align: right;background-color: #E2EFDA;;">300.0</td><td style="text-align: right;background-color: #E2EFDA;;">400.0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">67</td><td style="text-align: right;;">16/07/19</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">100</td><td style="text-align: right;;">200</td><td style="text-align: right;;">300</td><td style="text-align: right;;">400</td><td style="text-align: right;;">500</td><td style="text-align: right;;">600</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">0.0</td><td style="text-align: right;background-color: #E2EFDA;;">0.0</td><td style="text-align: right;background-color: #E2EFDA;;">0.0</td><td style="text-align: right;background-color: #E2EFDA;;">1051.6</td><td style="text-align: right;background-color: #E2EFDA;;">200.0</td><td style="text-align: right;background-color: #E2EFDA;;">300.0</td><td style="text-align: right;background-color: #E2EFDA;;">400.0</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet7</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">CH62</th><td style="text-align:left">=$AU62/14*(<font color="Blue">IF(<font color="Red">AND(<font color="Green">$F62-14>=CH$60,$F62-14<=EOMONTH(<font color="Purple">CH$60,0</font>),DAY(<font color="Purple">$F62</font>)<=14</font>),
EOMONTH(<font color="Green">CH$60,0</font>)-(<font color="Green">$F62-14</font>)+1,0</font>)+IF(<font color="Red">AND(<font color="Green">EOMONTH(<font color="Purple">CH$60,0</font>),$F62>=CH$60,
$F62<=EOMONTH(<font color="Purple">CH$60,0</font>)</font>),IF(<font color="Green">DAY(<font color="Purple">$F62</font>)<=14,DAY(<font color="Purple">$F62</font>)-1,14</font>),0</font>)</font>)
+IF(<font color="Blue">$F62<CI$60,OFFSET(<font color="Red">$AU62,,DATEDIF(<font color="Green">$F62,EOMONTH(<font color="Purple">CH$60,1</font>),"M"</font>)</font>)
*MIN(<font color="Red">1,(<font color="Green">EOMONTH(<font color="Purple">CH$60,0</font>)-$F62+1</font>)/DAY(<font color="Green">EOMONTH(<font color="Purple">CH$60,0</font>)</font>)</font>),0</font>)</td></tr></tbody></table></td></tr></table><br />
 

BrutalLogiC

Board Regular
Joined
Feb 26, 2006
Messages
247
Office Version
  1. 2016
Platform
  1. Windows
Superb that appears to do the trick! Only tried a couple of examples but works great! Thanks!
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,192
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,109,522
Messages
5,529,329
Members
409,863
Latest member
stacy09
Top