Help with IF AND formula again

[BrutalLogiC]

I'm trying to write a formula to work out future potential monthly revenue based on contract start dates and their associated payment terms and value... hopefully someone can assist

The input cells would be:
Column V from cell V12 is the contract monthly value
Column W from cell W12 is the contract start date which is always start of month
Column X is the end date of the contract which is always end of month
Column AA is the first month the contract will be invoiced... typically either "1" or "3"
Column AB is the number of months which will be invoiced every time an invoice is raised (the billing frequency)... typically either "1" or "3" or "12"
Column AC is the month the first invoice will actually be paid... typically either "2" or "3"
Row 11 is the forecast months... first month is "May-19" in cell AE11

example 1:
V12 = $1,000 (the monthly contract value)
W12 = 01 May 2019 (the contract start date)
AA12 = 1 (i.e. the first invoice will be raised in the first month of the contract which would be May in this example)
AB12 = 3 (i.e. each invoice raised will be for 3 months' value, AB12*V12)
AC12 = 3 (i.e. each invoice raised will be paid in month 3... with month 1 being the month the invoice is raised.. so in this example it would be July19)

So in the forecast I would expect to see
May = 0 (cell AE12)
June = 0 (cell AF12)
July = $3000
Aug = 0
Sept = 0
Oct = $3000
Nov = 0
Dec = 0
Jan = $3000

example 2:
V12 = $1,000 (the monthly contract value)
W12 = May 2019 (the contract start date)
AA12 = 1 (i.e. the first invoice will be raised in the first month of the contract which would be May in this example)
AB12 = 1 (i.e. each invoice raised will be for 1 month's value, AB12*V12)
AC12 = 2 (i.e. the first invoice will be paid in month 2 and then every month thereafter)

So in the forecast I would expect to see
May = 0
June = $1000
July = $1000
Aug = $1000
Sept = $1000
Oct = $1000

example 3:
V12 = $1,000 (the monthly contract value)
W12 = 01 May 2019 (the contract start date)
AA12 = 3 (i.e. the first invoice will be raised in the third month of the contract which would be July)
AB12 = 3 (i.e. each invoice raised will be for 3 months' value, AB12*V12)
AC12 = 4 (i.e. the first invoice will be paid in month 4.. then 7...10...etc.)

So in the forecast I would expect to see
May = 0
June = 0
July = 0
Aug = $3000
Sept = 0
Oct = 0
Nov = 0
Dec = $3000
Jan = 0

I haven't factored the end date into any of these examples but if the current month was post the contract end date then there would be no further invoices raised or income received.

 

[BrutalLogiC]

Thank you!

 

[AlanY]

getting too excited with the IF & AND!
this simplified version should work as well

=IF(AND(AE$11>=EDATE($W12,$AC12-1),AE$11<=$X12,MOD(MONTH($W12)+$AC12-1,$AB12)
=MOD(MONTH(AE$11),$AB12)),IF(AND($X12>=AE$11,$X12<=EOMONTH(AE$11,0)),
(DATEDIF($W12,$X12,"m")+1)*$V12-SUM(AD12:$AE12),$V12*$AB12),"")

 

[BrutalLogiC]

getting too excited with the IF & AND!
this simplified version should work as well

=IF(AND(AE$11>=EDATE($W12,$AC12-1),AE$11<=$X12,MOD(MONTH($W12)+$AC12-1,$AB12)
=MOD(MONTH(AE$11),$AB12)),IF(AND($X12>=AE$11,$X12<=EOMONTH(AE$11,0)),
(DATEDIF($W12,$X12,"m")+1)*$V12-SUM(AD12:$AE12),$V12*$AB12),"")

excellent that's much shorter cheers and sorry another query please

first payment month is month 3

start date 01-06-19
end date 30-09-19
it returns 3,000 in June and 1,000 under September (which is perfect as it's 4 months)

start date 01-06-19
end date 31-10-19
it returns 3,000 in June and 3,000 under September (should be 2,000 in Sept as it's 5 months x 1,000) - any idea how to fix this please

 

[AlanY]

need a bit more clarification

excellent that's much shorter cheers and sorry another query please

first payment month is month 3

start date 01-06-19
end date 30-09-19
it returns 3,000 in June and 1,000 under September (which is perfect as it's 4 months)

for the above, shouldn't it be first payment is August (3000), and last payment in Sept (1000)?

start date 01-06-19
end date 31-10-19
it returns 3,000 in June and 3,000 under September (should be 2,000 in Sept as it's 5 months x 1,000) - any idea how to fix this please

again, shouldn't the first payment in Aug (3000) and Oct (2000)?

 

[BrutalLogiC]

need a bit more clarification



for the above, shouldn't it be first payment is August (3000), and last payment in Sept (1000)?



again, shouldn't the first payment in Aug (3000) and Oct (2000)?

Yes sorry exactly this as you suggested

 

[AlanY]

ok, try this

=IF(AND(AE$11>=EDATE($W12,$AC12-1),AE$11<=$X12),IF(AND($X12>=AE$11,
$X12<=EOMONTH(AE$11,0)),(DATEDIF($W12,$X12,"m")+1)*$V12-SUM(AD12:$AE12),
IF(AND(MOD(MONTH($W12)+$AC12-1,$AB12)=MOD(MONTH(AE$11),$AB12)),$V12*$AB12,"")),"")

 

[BrutalLogiC]

Amazing!! thanks so much, what does the "m" and MOD part do?

Also I have a formula in a different sheet same workbook =SUMPRODUCT((Revenue!$B$12:$B$82=$B15)*(Revenue!$AE$11:$CJ$11=D$14)*Revenue!$AE$12:$CJ$82) which is looking at these cells with your formula in it (AE11 to CJ82) but it is returning #VALUE ! but if I delete your formula and just type any number in all the cells it calculates that okay, it also works okay with the first formula you suggested.

 

[AlanY]

Amazing!! thanks so much, what does the "m" and MOD part do?

Also I have a formula in a different sheet same workbook =SUMPRODUCT((Revenue!$B$12:$B$82=$B15)*(Revenue!$AE$11:$CJ$11=D$14)*Revenue!$AE$12:$CJ$82) which is looking at these cells with your formula in it (AE11 to CJ82) but it is returning [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=value]#value [/URL] ! but if I delete your formula and just type any number in all the cells it calculates that okay, it also works okay with the first formula you suggested.

the "m" in the datedif() is the different in months between the 2 dates

#value means that the formula expected a number but found a non-numerical, you need to trace back where it is

 

[BrutalLogiC]

Hi Alan, if the first payment month is say August in AH12 then May/June/July display no value (which is correct) but the sumproduct formula can't seem to cope with the blank cells in May/June/July (and any others throughout the range) because if I delete your formula from the blank cells then the sumproduct works okay

 

[AlanY]

i made them blank cells to look tidier, can put them back to 0 like this

=IF(AND(AE$11>=EDATE($W12,$AC12-1),AE$11<=$X12),IF(AND($X12>=AE$11,$X12<=EOMONTH(AE$11,0)),(DATEDIF($W12,$X12,"m")+1)*$V12-SUM(AD12:$AE12),IF(AND(MOD(MONTH($W12)+$AC12-1,$AB12)=MOD(MONTH(AE$11),$AB12)),$V12*$AB12,[COLOR="#FF0000"]0[/COLOR])),[COLOR="#FF0000"]0[/COLOR])