Calculate Revenue Based on Date Periods

[Shred Dude]

I"m having difficulty nailing this one down. Some fresh eyes would be much appreciated.

Attached is a workbook with some sample data, and my attempt that is not working, along with example of expected results. I was trying to do the calculation in one cell with SumProduct formula on an existing data layout without resorting to any helper columns.

Basically it's trying to calculate projected revenue for next 12 months based on Start Dates and End Dates of various Items. Small twist is data presents Rate per 28 days. So, I was approaching it by calculating a daily rate and then extrapolating to the # of days active in the next 12 months.

Problem seems to be in the part where I'm trying to come up with the #of Active Days.

eg:


{=SUMPRODUCT(--(INDEX(Items,,1)=Choice)*--(INDEX(Items,,3)<TODAY()+365)*--(INDEX(Items,,4)>TODAY())*(INDEX(Items,,2)/28)*(DATEDIF(MAX(INDEX(Items,,3),TODAY()),MIN(TODAY()+365,INDEX(Items,,4)),"d")))}

Please see attached. Any ides on how to achieve 12 month projection and historical 12 month accounting, in one cell formula much appreciated!

Thanks in advance!!

[Bob Phillips]

=SUMPRODUCT((INDEX(Items,,1)=Choice)
*(INDEX(Items,,3)<TODAY()+365)
*(INDEX(Items,,4)>TODAY())
*(INDEX(Items,,2)/28)
*(DATEDIF(MIN(TODAY()+365,INDEX(Items,,4)),MAX(INDEX(Items,,3),TODAY()),"d")))

[Shred Dude]

XLD:

Thanks for the reply. Gave it a try but didn't get the results I would expect.

That formula returned $4,250 for Item 1. I would expect the answer to be $10.781.25. I've detailed an example of how I would think the answer should be arrived at in the revised attachment. This block can be used to validate every choice. It also includes the formula above in action.

Item 1 has a 28 day rate of $875, thus a daily rate of $31.25. It starts before today and ends before a year from now. So the period to use would be today's date (11 March 2011) as the start date, and its end date of 19 Feb 2012, giving a total of 345 days...at $31.25/day = $10,781.25.

At this point it's an exercise for me. I'm just curious to see if it can be done by one formula in one cell. Maybe SumIfs() could be used?

[Bob Phillips]

Shred,

The problem is the MAX/MIN functions, as they are not getting an array of MAX/MIN of TODAY and each date, it is getting a scalar MAX/MIN of TODAY and ALL row dates. As it happens, because you are using the day type, you don't know DATEDIF, you can subtract 1 from the other, but you have to use IF and ditch the MAX/MIN


=SUMPRODUCT((INDEX(Items,,1)=Choice)
*(INDEX(Items,,3)<TODAY()+365)
*(INDEX(Items,,4)>TODAY())
*(INDEX(Items,,2)/28)
*(IF(INDEX(Items,,4)>=TODAY()+365,TODAY()+365,INDEX(Items,,4))-IF(INDEX(Items,,3)<=TODAY(),TODAY(),INDEX(Items,,4))))

[Bob Phillips]

BTWW, you might want to round it



=ROUND(SUMPRODUCT((INDEX(Items,,1)=Choice)
*(INDEX(Items,,3)<TODAY()+365)
*(INDEX(Items,,4)>TODAY())
*(INDEX(Items,,2)/28)
*(IF(INDEX(Items,,4)>=TODAY()+365,TODAY()+365,INDEX(Items,,4))-IF(INDEX(Items,,3)<=TODAY(),TODAY(),INDEX(Items,,4)))),2)

[Shred Dude]

XLD:

Thank You!

Looking at it now, I get it. I think when I wrote it I was thinking that my previous constraints would've made the Min/Max work on only the relevant date and I could get an appropriate scalar result to multiply with.

With this understanding I can back into the result of the earlier formula that returned $4,250 on the sample data.

So, SUMPRODUCT 101: Make sure each element returns an appropriate array!

Shred