Hi,
Need help on a formula please.
Sample book is attached.
All data in the sample book is fake and doesn't mean anything.
I have columns Jan - Dec and a total column. I've added "First Month Spend" and "Amount" as new columns.
First Month Spend:
- This column should find the amount of money that will be spent in the first month of that line i.e. if there is no monitary value from Jan - Aug but there is monitary values from Sep - Dec, that means I begin paying monthly for that line in September and therefore that is the first month spend
Amount:
- This column should show the amount of money that is spent in the already-calculated first month spend. i.e. the formula found that the first monitary value was in Sep, therefore that's the first month spend, but this cell will show the value of Sep for that value. I.e. £50,000 is forecasted to spend in Sept, therefore £50,000 will be displayed in the "Amount" column.
I used this formula to find the amount, which works partially:
Copy to clipboard
It basically looks in January, if there is a value in January it will stop the formula and input January's value, if there is nothing in January it looks in Feburary and repeats the process until December - if there's nothing in December it will input "No Spend" into the cell as there is no forecasted spend.Please Login or Register to view this content.
This formula works, but doesn't take into consideration the current month. If the first spend is in February, I am not interested because February has been and gone... I want it to only give me values after today's month, but automatically (i.e. i don't want to have to remmeber to go in and change the formula each month).
Any help on this would be appreciated.
I've tried to include as much info as possible but apologies if I have missed some stuff out - please just ask for more info.
Many thanks
Bookmarks