 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
I'm doing a cashflow.
I have a list of values, and their corresponding start & finish weeks. I want to spread the values between those start and finish weeks as a table like this:
------------------Value----Start-End---wk1---wk2---wk3---wk4---wk5---etc
Foundations----300-------3----9
Frame----------1200-----10----12
ie for row 1 under wk3 to wk9, I want (9-3+1=7 weeks) one seventh of 300
Is there a simple formula for this, or am i into nested conditionals?
=IF(E$1<$B2,0,IF(E$1<=$C2,$A2/$D2,0))
------------------Value----Start-End---NoWks---wk1---wk2---wk3---wk4---wk5---etc
Foundations----300----------3----9-----9-3+1---
Frame----------1200--------10----12----12-10+1-
=IF(E$2<$C3,0,IF(E$2<=$D3,$B3/($D3-$C3+1),0))
what you are asking is the key to building cashflows and so it's worth your while having a crack at it yourself - give a man a meal and he eats for a day, teach him to fish and he can build cashflows forever etc etc 🙂
as thegeneralist has alluded to you need to build a formula that checks the location of itself in a calender against the start and stop dates.
So always build a calender along the top and running from left to right, starting a number of columns along to the right so as to give yourself some room for calculation cells in the columns to the left (say, 10 of them. You can hide surplus ones)
Your calendar could be week numbers, or dates, or year numbers, or month or even all of them at once, so plan it out carefully.
finally the amount you want to enter is simply the nth fraction of your total, where n is the number of periods between end and start - an easy formula.
So the formula in a given cell, along the row you want it entered goes something like:
"Check whether DATE at the top of this column is both: [i]greater than or equal to[/i] the START DATE and [i]less than or equal* to[/i] the END DATE, if the answer is YES, then take AMOUNT and divide by END date minus START date, if the answer is NO then put a zero in here."
or something like in cell J6:
=IF(AND(J$1>=$H6, J$1<$I6)), $E6/($I6-$H6), 0)
where J$1 is the date at the top of my column, $H6 is my START DATE, $I6 is my END DATE and $E6 is my AMOUNT
HTH
*EDIT have added the EQUAL TO, depending on how you're using your start and end weeks and whether the cashflow period is inclusive of them or not.
Stoner - yours is simplest but still uses a conditional expression. I was wondering if Excel had a special formula that would suit this application but perhaps not. I've been doing it this way for over 20 years! (but not frequently enough to invest the time in streamlining it).
Cheers 🙂
whatever you do, it cant help but be "conditional" because you are checking the condition of the date in every cell. Excel has a very special formula for this, called.....IF... 😉
I've been doing it this way for over 20 years!
Doing what for 20 yrs? Typing the number in manually?
No, using a conditional expression.
I was looking for something like an inverse SUMIF that would do it all in one hit. Just to be smart!
ah, I see what you mean.
The problem is that "SUM" is a function, where as an inverse like "DIVIDE BY X" requires an additional argument (the divisor), so cant be a standalone function.
Functions such as MOD and QUOTIENT do similar, but they too require additional argument.s