Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

excel cashflow formula

Posted on 2011-09-11
6
Medium Priority
?
299 Views
Last Modified: 2012-05-12
For a project, I need to estimate the expenditure for certain tasks over the next 10 years.  The tasks have various start dates (in terms of year its first undertaken), and various frequencies (in terms of how many years until its next repeated)
The columns that I would enter data are A - Task Name, B - Cost  C - Start Date, D - Frequency (years)
I then would have columns E to N for Expenditure for Years 2012....2022.  Could someone advise what formula I would use in Columns E to N to show the expenditure in each of those years.

Thanks in advance
0
Comment
Question by:gh_user
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1000 total points
ID: 36518611
Hello,

with headers in row 1 and E1 to N1 being numbers, try in E2

=IF(MOD((E$1-YEAR($C2))/$D2,1)=0,$B2,"")

copy down and across.

see attached.

cheers, teylyn
Book4.xlsx
0
 
LVL 4

Assisted Solution

by:AgeOfEmpires
AgeOfEmpires earned 1000 total points
ID: 36519115
I believe this formula needs to be adjusted slightly to account for when the start date (col C) is after the year contained in E1-N1.

Using teylyn's spreadsheet, try this formula in E2 and then copy down and to the right

=IF(AND(E$1>=YEAR($C2),(MOD((E$1-YEAR($C2))/$D2,1)=0)),$B2,"")

0
 
LVL 81

Expert Comment

by:byundt
ID: 36519141
teylyn's formula can be simplified to:
=IF(MOD(E$1-YEAR($C2),$D2),"",$B2)

This formula takes the difference in years between E1 and C2 and divides by the frequency D2. If the remainder is greater than 0 (interpreted by the formula as TRUE), then E1 is not an anniversary year for this expenditure and an empty string (looks like a blank) is returned. If the remainder is 0 (interpreted as FALSE), then the year is an anniversary and the cost in B2 is returned.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:AgeOfEmpires
ID: 36519177
With this formula simplification (something has always bothered me about treating a boolean value as an integer value, although I've done it many times and it's standard practice - let's just hope MS never decides not to permit this), I still contend that you need to do a check similar to my response above to make sure the start year doesn't occur after the year for the column in which you are performing the calculation.

I believe this formula still returns an expenditure for 2012 in row 2 when the start date is changed to 2014.
0
 
LVL 50
ID: 36519839
Brad, I thought about using it this way round, too, but I didn't for two reasons:

- I prefer it when the TRUE part of an IF statement delivers what I actually want to see as the positive result of the formula
- not everybody understands the relationship between TRUE/FALSE and 1/0, so for ease of maintenance by others, I prefer to spell out the comparison

AgeOfEmpires, you are absolutely right. I had not factored that in.

cheers, teylyn
0
 

Author Closing Comment

by:gh_user
ID: 36520186
Thanks for quick response
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question