This is a guest post written by Paramdeep from Pristine. Chandoo.org runs Financial Modeling School program in partnership with Pristine Careers. Visit Financial Modeling School to join our training program.
Greetings!
It’s been long time since we interacted on Chandoo.org. Actually I was very busy teaching the 105 awesome students for financial modeling in Excel. We all worked together to create some easy and some complex financial models. I found the journey to be quite exciting and enriching (From the feedback that I got, my students too didn’t find it bad either 😉 )
During the interaction, I found that a lot of students were looking for financial modeling around the project financing as well. So we thought why not introduce financial modeling for project finance.
In this post I will speak about some of the key aspects of a project finance model and why it can be different from modeling a normal company.
What is so Special About Project Finance?
An organization is an on-going entity – the basic assumption being that it would continue business for time immemorial. Project finance is different. It exists for a limited duration (though that duration is usually long – 20 to 30 Years!) and the project is structured in a special purpose vehicle (SPV). The SPV exists for a specific period of time and after the purpose of the project is solved, the SPV is dissolved. That means that the horizon of analysis is NOT time immemorial but a known time frame for which the SPV is formed.
The other typical difference is that these are long gestation projects, with almost no cash flow in the beginning and a lot of parties involved. This basic difference in the structure creates a lot of issues, Principal – Agency problem because of large number of parties, everybody trying to maximize their returns in a short time, propensity to wait decreases significantly and the initial project gestation period is critical to success of project.
Summarizing, Project Finance is the financing of
- Often long-term, industrial projects
- Increasingly those which provide public services or infrastructure
- Based upon complex financial and contractual structures commonly involving many legal entities
Two main types of Project Financing
- Greenfield – a fresh start
- Brownfield – expansion of an existing project
Key Aspects of Project Finance
I will highlight some of the other key aspects of Project finance.
- Separate Entity (Parent) and SPV Status – Risk of the transaction is generally measured by the creditworthiness of the project itself rather than that of its owners (Sponsors).
- Project Finance debt is often termed as “non-recourse”– That means the financial institutions cannot go to the parent level to get their money back. The money has to be generated at the SPV level. Typically these loans are secured by the project assets and the core project contracts.
- Timing of Cash Flow: The cash flows from the project comes only after the project is fully complete (takes more than a single financial year for completion) and are usually the sole means of repayment of the borrowed funds
- Multiple parties involved
- Sponsors
- Contractors
- Suppliers
- Governments
- Global financiers
- Long Gestation: From inception of an idea to Financial Close, a Project Finance deal can take years to negotiate
- Identifying Risks: The success of the project depends a lot on identifying risks, allocating them appropriately and ensuring that the responsible parties are adequately incentivized to manage their risks efficiently
- Construction time, costs & specification
- Operational cost, reliability
- Supply reliability, quality, cost
- Off-take volume, price
- Political environment, war, local hostility, currency in-convertibility
- Socio-environmental responsibilities
Modeling a Key Risk – Delay in Project Implementation
Because of the structure of the project one of the key risks in the project is the risk of delay. Delay in project can significantly reduce the IRR (what is it?) of the project and completely take it off track! There are multiple ways in which projects can be delayed and each would have its own repercussions on the return. These can be (The list is not exhaustive):
- There is an implementation risk (Delay because you could not execute the project in time)
- Delay in start of project (Could be because of regulatory approvals – Regulatory Risk)
- Delay in project because no funds were available (or delay in arranging the funds)
- Delay in collection of revenues (You built at the right time, but could not sell – Selling risk)
Typically each of these delays would have different affect on the cash flows. For example, if you are implementing a real estate residential project and you are not able to sell the homes – you have incurred all your cash outflows and your inflows are delayed. This would spell doom for the returns. On the other hand, if there is a delay in staring the implementation because of non acquisition of land, typically your cash outflow is not there so the affect would not be that large.
Why are delays more relevant for certain kind of investments?
Typically for long gestation projects – Roads, Real Estate, Power, Telecom, etc. there is a huge upfront investment. Even if there is a small delay in getting back the cash flows, it makes your project financials look very bad.
The Case of ABC Housing Co.
ABC housing company is planning to start the project on 1 Jan 2002 and is expecting to complete the construction over a period of 3 years. The construction cost is expected to be USD 1000 Mn. It is expecting to hold the property for a period of 5 years, in which it would get a lease rental of USD 100 Mn each year. After 5 years, it would sell the property at a value of USD 2000 Mn.
Modeling delays in Excel – Playing around with the date function
If you are planning to incorporate delays in your model, date function comes in very handy. For example, if you are expecting the construction period of 1 year, you can just put this as a parameter in your date (for years) and change his to see, if the construction period increases, what would be the effect on the project IRR.
The simple concept I have used here is to make the counter as a counter from the start date. The EOMONTH function calculates the end of month date of the particular month for which the date is given.
Now, I want to see if this is a construction year of not, I can just compare the number of years!
Some assumptions to start with
I am assuming that once the project starts, the cost incurred during the construction period would be uniform. So if the construction is completed over a period of 3 years, each year 33% of the cost is incurred and if it is completed over 5 years, then 20% of the cost is incurred each year.
To model this in excel, I compare the current year with the time period when the construction started and allocate the cost.
Similarly for Leasing I assume that it gets leased out one year after the construction period and after the leasing is complete, ABC is able to sell the property at the desired price
Calculating the Cash
There are two ways in which you generate cash – by lease and sale of property. The cash is consumed by your Capital expenditure. Assuming there are no taxes, cash generated is just a combination of all these.
Calculating the Return
As discussed last time, Internal Rate of Return (IRR) is an important aspect that all investors look at. Once we have the cash, we can simply ask excel to calculate the IRR for us. But in this case, since we have the exact year of cash with us, we will not use simple IRR function, but the XIRR function in Excel. This would take into account that all cash flows might not be at the year end.
Scenario Analysis
Now that we have the model with us, we can change the construction period and see its effect on the IRR. If we want to do a complete scenario analysis, we can used Data Tables in Excel to generate a complete scenario analaysis.
Related Tip: Learn how to work with Data Tables & Monte-carlo Simulations in Excel
To create the data table, the structure of the table should look like:
Where 1, 2, 3 .. etc are the years of construction and the cell is linked to the IRR that we had calculated earlier.
Then select the data tables option within the data tab > What if Analaysis
Select the changing cell as the years of construction (In this case the change is in a column)
You can see that there is a significant change in the IRR of the project with a change in construction period.
So if you construction period is delayed from 3 years to 5 years, your IRR would change from 15.6% to 13.1% (and hence the investor interest would simply die out!!).
Templates to download
I have created a template for you, where the subheadings are given and you have to link the model to get the cash numbers!
You can download the same from here [mirror].
You can go through the case and fill in the yellow boxes. I also recommend that you try to create this structure on your own (so that you get a hang of what information is to be recorded).
Also you can download this filled template [mirror] and check, if the information you recorded, matches mine or not! 🙂
I am just doing that for the single sheet model and recommend that you do the same for multi-sheet model as a homework problem. If you face any issue, post your excel with the exact problem and we can discuss the way to move forward.
Next Steps
This series gives you a flavor of how financial modeling is done and an idea about specific nuances in modeling for long gestation projects. In the next part of this, we will learn how the interest payments of a project should be modeled.
Join our Financial Modeling & Project Finance Classes
We are glad to inform that our new financial modeling & project finance modeling online class is ready for your consideration.
Please click here to learn more about the program & sign-up.
For any queries regarding the cash impact or financial modeling, feel free to put the comments in the blog or write an email to paramdeep@edupristine.com
132 Responses to “Beyond If and Sum, 15 really useful excel formulas for everyone”
Great post. I had never heard of the CHOOSE function. That one is going in my toolbox immediately. I don't think there's anything more difficult than nesting IF statements in the Excel formula bar. At least with 2007 they made the bar drop down so you could usually see the whole thing if it was a big one.
Love your blog, been reading for a few months but this is first comment. Keep up the good work.
timetaking but worth reading, liked it very much........
thanks for the help
pls send me link
Another useful one for processing cell contents:
If you concatenate text and cells containing dates, the date is usually passed to the cell as a numerical value. Use the TEXT function to pass the date as a text string, using the date format of your choice...
="Date is: "&TEXT(A1, "dd/mm/yyyy")
1. TRIM also converts excess spaces within a string to a single space, which is very handy with imported data:
TRIM(" abc def ") becomes "abc def"
2. Some functions require the Analysis ToolPak, including RANDBETWEEN, CONVERT, and WEEKNUM.
[...] Beyond If and Sum, 15 really useful excel formulas for everyone (tags: excel totw) [...]
@Ben.. thanks, welcome to commenting, I think this is the best place as more good ideas come out of this than the posts often... 🙂
@Sam.. that is a good tip, may be I will include it in the next issue of 15 functions
@Jon, I didnt know that about trim, thanks for pointing.
Yeah, analysis tool pack is required for few of those functions. I have had it on forever, so didnt realize that. 🙂
chando dont reply so much it waste our time
we are in era
Re: Tip #5 (date &/or time)
You all may already know this, but an easy way
to enter the current date in a cell is Ctrl+; then enter, and to enter the current time in a cell type Ctrl+Shift+; then enter.
It is very useful.. thanks..
[...] on names and text formulas: Find word count using excel formulas, 15 excel formulas for everyone, Generate tag clouds using VBA. Categories : Excel Tips | ideas Tagged with: Excel Tips | [...]
[...] 15 Excel Formulas you may not know [...]
Tip #7 for Zip Codes:
=TEXT(A1,"00000")
@Richard... thanks for that. 🙂
@Jon .. This is sweet, thanks very much. Often I use the Rept() way of doing this.. I am sure TEXT() is easier to use when you know the format up front.
[...] SMALL() excel spreadsheet formula is used to sort a list of numbers and fetch nth smallest number in a given [...]
[...] public links >> sorting Beyond If and Sum, 15 really useful excel formulas for everyone First saved by viimmy | 1 days ago sorting out christmas lights First saved by phipsi180 | 23 [...]
[...] Beyond If and Sum: 15 very useful microsoft excel formulas for everyone | Pointy Haired Dilbert - Ch... - [...]
i'm making up a form for a friends business and on a second copy of that same page i using a formula to copy data in particular cells. the problem i have is that i'm getting a bunch of "0" ont he copy if there isn't data on the first form. how can i get rid of that "0"?
@Bill.. Welcome to PHD. Thanks for asking the question.
When you use references you can wrap it in an if clause.
For eg. instead of saying =Sheet1!A2
you can write: =if(Sheet1!A2="","",Sheet1!A2)
that way, when ever the reference is empty you will force excel to show empty space instead of ZERO.
how can we work in ofice on excel plzzz next month i will join my job help me
[...] 1. Become a Conditional Formatting Rockstar with these 5 tips 2. Excel can be Exciting - 15 fun things you can do in MS Excel 3. 25 Free excel downloadable templates and workbooks 4. 73 high quality excel chart templates, download and make awesome charts 5. 15 Excel formulas you must learn [...]
@Bill / Chandoo :
You may untick the zero values from menu==>tool==>option==>view
[...] suck, convert your Pinyin to unicode, good tips for web developers, charts for Javascript users, good functions for Excel users, categories for computer science (this is [...]
Chandoo:
I think NA is for "Not available". (Tip 15).
[...] 28. To get nth largest number in a range, use =large(range,n)… Get Full Tip 29. To get nth smallest number in a range, use = small(range,n)… Get Full [...]
Nice Chandoo 😀
Chandoo 🙁 need your help 🙁
am having an excel sheet it is actually a request sheet that provide an ID.. this ID I have to formlize it each day i want to ask for a request... the problem I just hate this way I need to find a way to make this ID automatically appear with a new serial number each time I add new sheet!!!!
note the ID number has to be formlized by this way: ddmmyy/###
how could I do it Chandoo?? 🙁 need your help :'(
@Loula.. thanks and welcome to PHD. Let me see if we can help you.
automatically incrementing the number whenever you use the formula is possible through circular references. Even automatically getting the current date while keeping the old date values intact uses circular references in formulas. These are slightly complicated formulas and hence I don't recommend them for day to day uses.
A better solution could be to use macros, write once and run whenever you need a new ID to be generated and pasted in the current cell. Let me know if you are interested, I can either help you on this as a consulting engagement or provide you some general guidelines through comments.
I am sorry, but I dont know anything else that is better, may be one of our readers do...
Chandoo, I need your help too. I downloaded the gauge sample and do not know how to replicate it. How did you make the pointer? I am pretty sure I can figure out the arch, but the pointer I am lost. Awesome site!!
Yes Chandoo it seems to be better than the way that I do 🙁
thanks alot Chandoo and wait your guidlines 🙂
@Bobby: I suggest you to read this article : http://chandoo.org/wp/2008/09/09/excel-speedometer-chart-download/
and see the sample download provided there. If you still have any doubts, feel free to ask me.
@Loula: Are you looking for some general info on circular references? if so, try this: http://chandoo.org/wp/2009/01/08/timestamps-excel-formula-help/
If you need a more specific solution, drop another comment, I am sure one of our readers or me can respond with a good answer.
Yes I got some example,, but till now I could not find a way to write my forumla 🙁
I could do the date format but how could I put the serial number that comes automatically whenever I add new sheet??!!
@Loula: hmm.. there are few ways you could do that. (1) obviously using VBA to autogenerate a sequence number and place it cell, say B3 whenever a new sheet is added.
(2) using CELL() function in each sheet, CELL("filename") would tell you the entire file path along with sheet name. Then extract the sheet name using a formula like, =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))). Now use this name to derive the sequence number. If you are using default naming structure, then your sheet names would be sheet 1, sheet 2, sheet 3, ... , sheet 99 etc.
Let me know if you need further help 🙂
I'm kinda new to Excel and I need some help with a formula.
I need to calculate Drive time and actual work time in units if 1 unit=5 minutes.
@Paul: Welcome to Excel and PHD... 🙂
You can do that by using a formula like: =A3/5, assuming A3 has drive time in minutes, wherever you enter this formula it will give you the units
use the same logic for calculating work time. You may want to check our excel formula help section: http://chandoo.org/wp/excel-formula-helper-index-cards/ to learn more about formulas in plain English.
Hi
i want to learn MS Excel please help me
Chandooooo :'(
I tried many ways to do it but really couldn't do it 🙁
please if you do not mind give me step by step
sorry for bothering you 🙁
@Loula.. Sure, give me sometime... (I am sorry, but I am in between few important things and cant really take out time... I do appreciate your patience.. If any of our commenters can help Loula, I will be very thankful to you..)
Hi loula,
I am replying to this because I had a similar problem and couldn't find the answer. So i looked on the help pages in excel. I feel a bit daft not getting it, but who knows anything until you know it? We all have a time of not knowing...
What I didn't get was that you had to put the formula in separate cells from the data you are changing. So if your data is in cell A2, you could put this formula in cell B2: =PROPER(A2) to change "I am not daft" to "I Am Not Daft"
I thought this very simple but important step was worth sharing.
In Cell A1 I Typed :
=PROPER("thank you chandoo very much")
and It displayed
Thank You Chandoo Very Much 🙂
@Al... thank you so much.. that is soo sweet.. 🙂
Ok wait you to finish 🙂 thank you
@Loula: I feel very unfortunate for not being able to help you on time. I have moved to Sweden from a Sunny place in India and found myself with very limited internet access and extremely busy work schedule. I am not sure when I will be able to get back to you... but if you find a solution, share it with us.
Eternal glory awaits you...
Hi - complex issue I think: I have an Xcel doc with multiple worksheets for weeks of the month. It is a timesheet. I want to summarize total time in the month (pulling from multiple worksheets) based on a project number - which is variable, but always in the same column on each worksheet. The project number is in column D, which is totaled into column M for each weekday entry. The biggest stumbling block I see is the project number will not always appear on the same row. So I want a consolidated sum from column M where column D equals a specific text/numerical string from multiple worksheets. Does this make sense? Is it possible? Thanks!
Hi Sharon,
you may download RDB addin to. it help you out.
Hy guys. I am new to excel too.
Lets say there is a column which has 1 or more names, separated by commas. My requirement is that in the next column, a count of number of names is displayed.i.e. one can count the number of commas. I am unable to implement this.Please help.If you have any other suggestion to count,then also please share it.
@Sharon: Welcome to PHD and thanks for asking a question. I have taken an extended easter break to catch up on few things at home and now back online 🙂
coming to your question, yes, you can use excel to solve your problem. You need to use 3d references and sumif(). a 3d reference refers to same range across several sheets. See this: http://chandoo.org/wp/2009/02/04/satisfaction-surveys-excel/ and http://chandoo.org/wp/2008/11/12/using-countif-sumif-excel-help/ for more help.
I am not giving step by step instructions as it is a peculiar problem. But I am sure you can put the pieces together and solve this. Let me know if you hit any road block.
@Varun: Welcome to excel.
You can count the number of commas (or any other symbol) in a cell using this formula: =len("a,b,c,d")-len(substitute("a,b,c,d",",",""))
so, if you want the number of names in the cell, just add 1 to the above formula.
Let me know if you have some problems implementing this.
@Chandoo.
Thanks for the help mate. With a little modification , it worked.
=IF(J423"",LEN(J423)-LEN(SUBSTITUTE(J423,",",""))+1,0)
However, as my motive is to count the no. of words, it'll give an extra count,if a comma is inserted at the end.
Thanks again.Will be bugging in the future as n when new issues will pop.
[...] 15 very Useful Excel Formulas for Everyone [...]
Chandoo,
Just found this excellent blog, and have already incorporated a couple ideas into my work.
One small note on the example used in #7 - I believe it should be =REPT("0",5-LEN(zipcode)) & zipcode and not
=zipcode & REPT("0",5-LEN(zipcode)) as you want to pad the start of the string and not the end.
thanks again.
Hi
I am looking for an easy formula to replace the If function. I need to find the highest number in a series then add the data in the column to the right. The IF function works for this, btu I can only add 7 points for it to check. I have around 20 data points. Can you help
Thanks
Nichola
@Nichola... Welcome to PHD.
You can use MAX formula to find the largest value in a range of cells. You can mix this with Vlookup to find the value in adjacent cell like this.
Assuming the values are in the range A1:B20, with column having values and column B having related data, write the formula,
=vlookup(max(A1:A20),A1:B20,2,false)
Learn more about vlookup here: http://chandoo.org/wp/2008/11/19/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/
Thanks Chandoo. The thing is, the values are in one row (B3:B25). I have 200 rows of data that I need to seperately look at (find the highest value and it's corresponding data). The highest value is not always going to be in the same column.
Thanks
Nichola
Hey... brilliant. I didn't know about the CHOOSE function at all - I usually end up going with hidden lists combined with vlookup functions, or sumifs instead. Nice. I'll find a way to use this at work.
First time commenter, btw.. thank you thank you, pat on my back. I've been going through all your older posts just to find gems like these that I might've missed. My search has not been in vain so far. Thanks for all the great work!
@Nichola... if you have values in a row instead of column, you can use HLOOKUP function in the same way
@Arti... That is sweet. Thank you 🙂
The Weeknum function is not correct if your country uses the ISO8601: 2000 weeknumbersystem, like we do in Europe.
Today is week 4 for us. Excel's weeknum returns week 5.
You can correct this, by using the following Formula:
=INT((B4-DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3)+WEEKDAY(DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3))+5)/7)
The date is in cell b4.
Found this on:
http://www.rondebruin.nl/weeknumber.htm
[...] 15 really useful Excel formulas for everyone — kleine Helferlein abseits des von =SUMME … [...]
Hi Chandoo, you have been able to help me in the past - and now that something has been bugging me for nearly a week I know there was only one place to go - you!!
I have created a table based on months of a year (in columns) bringing back a value of trade which commences in that particular month - what I need to be able to do it to populate the continuing months (row entries)in the Table with that entry if it is found? I was trying to do a kind of IF(CELL=FALSE,"FALSE","The cell value") this just produces all False values or if I try to do it all together with creating another "table" to create the If statement in, then it is just circular......
HELP!!!!!
@Em.. thank you for your appreciation 🙂
I am unable to understand your question.. can you give some more details?
Hi, Yes sorry it is rather confusing - My table has months in columns then customer names in rows, My aim is to sum up the total value of expected trade for the year by Customer by month - so far I am able to return the monthly value of trade (via a look up)in the cell relevant to the month that the customer has said they will start to trade but this value actually should be also be entered in to all the subsequent month columns also? That is what I am struggling with - any ideas? Thanks as always Em.
@Em.. you mean if the monthly value for Jan is 20, Feb, Mar etc. should have this 20 added to whatever they already have?
In that case, you can use a formula like
=vlookup(monthly value...) for Jan
=vlookup(monthly value...) + previous month's value for Feb onwards
Can anyone tel me, how can i convert a list of call duration stated in time format(i.e 05:36:45) into number of units. 60 second or part is one unit. given example should return with 337 units ,
@Ashok
Try : =HOUR(M1)*60+MINUTE(M1)
Make sure you format the cell as Decimal after you enter it ie: Click the , button
Change M1 to suit
Hui... I tried your formula above and only got 336 and I am by no means even close to an expert.... not even close, and this may be too much extra, but I used your formula and changed it a bit to get the 337....
see if this works: =ROUNDUP(HOUR(M1)*60+MINUTE(M1)+(INT(SECOND(M1))/60),0)
dear friends,
can any one tell me how to find consoled number if there are so many cells with number suppose IN cell A1 11, A2 10, A3 20, A4 50, A5, 60 AND A6 11 and i have to find which two cell number sum together and make 22 than how to solve through formula please send me on email
@Manoj
have a read of http://www.dailydoseofexcel.com/archives/2005/10/27/which-numbers-sum-to-target/
Dear Friend ,
I used one column in fraction and other column in value (Eg:Rs.1500.50 is entried one cell in 1500 and another cell 50 ),So I Summed the Fraction its above 100. So I want two neumeric in fractioned cell and if Greater than and equal to 100 I want to add Rs.1 to another Cell.
Please help me , I Want a formula in Excel.......Pls.........
Dear Friend
Whats the formula used to count the Even or Odd no.
I have a 7 column, each colum contain evev or odd number, I want to count In colum no 8 or K(for Even) and 9 or L (For Odd) how many number are Even or Odd in one row A5:J5
Example 3 18 34 41 45 46 37 = even Count 3 and odd count 4
Thanks
@Malik
Try: =SUMPRODUCT(1*(A5:J5=ODD(A5:J5))) for Odds
and =SUMPRODUCT(1*(A5:J5=EVEN(A5:J5))) for Evens
.
even though there is only enough data to go to G2?
Sir,
I want to highlight Odd Number and Even Number by Conditional formatting
Please help on this
@Atish
Select the range
Goto Conditional Formatting
Apply a New CF
Use a Formula
=isodd(A1)
select the format
Apply
Repeat using =iseven(A1)
Thanks
Another function that I also find useful is the "VALUE" function which turns numbers stored as text into numbers. I find this useful when I'm exporting figures from our accounting system which automatically exports as "### " numbers (with a space(s) as the last character).
If "### " is in A1 then I just type in =VALUE(A1) and it returns "###" with the default number formatting. Then I use this data to manipulate and do whatever I need with it.
Hope this makes sense and is useful for other people, too.
chandoo i want the explanations of sumif,countifand vlookup ,hlookup with examplesi can do and put formulas but i dont understand the logic in easy way. i want u to pls send me logic in easy way. especially with spreadsheet example.
It was very useful , thanks.
[...] 28. To get nth largest number in a range, use =large(range,n)… Get Full Tip 29. To get nth smallest number in a range, use = small(range,n)… Get Full [...]
[...] 28. To get nth largest number in a range, use =large(range,n)… Get Full Tip 29. To get nth smallest number in a range, use = small(range,n)… Get Full [...]
So very glad that I found this website. I had been trying to do a pmt function, but every time I tried, it returned the result as a negative amount when I needed it positive. I am in college and it was part of one of my assignments. After I found your website I was finally able to complete my assignment. Thanks for your help!
[...] To get nth largest number in a range, use =large(range,n)… Get Full Tip [...]
Thanks for the great information! I have been searching for a solution to an excel problem, and yet I still cannot seem to figure this problem out:
I have 2 columns: One column displays a Data Validation pull-down table. The second column is where the user can enter a dollar amount. I am trying to figure out a way that the forces the entered dollar amount to become a negative number, based on the conditions from the pull-down menu. Is this possible? I have tried formulas, using conditional formatting, but nothing seems to work...
Here is an example of it
COL A COL B
WITHDRAW $200
I want the "$200" to become "-$200", when the user chooses "WIHDRAW" from column A...thanks in advance for any advice on how to solve this, without learning VBA
Excellent guide, thank you!
hello
can you help me
i want to extract record from other sheet between two dates and a criteria in excel 2007 with formula functions
thanks a lot for your advice
Noviar Rizal
can U pls tell me a short cut key for format painter ?
I need formula that can count the days.
Example: 02-02-2011 & Current date is 29-07-2012.
How many days has been done.
Please advise
Thanks
Ahmed
If
A1: 2/2/2011
A2: 29/7/2012
Simply use =A2-A1
If you want to exclude weekends then use
=NETWORKDAYS.INTL(A1,A2,1)
[...] 3. To get nth largest number in a range, use =large(range,n)… Get Full Tip 4. To get nth smallest number in a range, use = small(range,n)… Get Full [...]
Thank you for this great post!
Hi everyone. Please help with this. I have been tried to find this entirely on Net. Bt i did not fine that.
Actually i have a spreadsheet attched with other sheet.. and i enter value in 1st sheet and its bring the result in 2nd sheet. bt something results are negative number. in cell let say in Cell a1 has some formula and some time result will be change in negative numbers. Once the results automatic change. i want pop-up message instantly. please help me wtih that. Mr. chandoo. please..
Thank you
A great cheat sheet - I use excel loads and always finding new things, thanks
[...] 28. To get nth largest number in a range, use =large(range,n)… Get Full Tip 29. To get nth smallest number in a range, use = small(range,n)… Get Full [...]
[...] via Beyond If and Sum: 15 very useful microsoft excel formulas for everyone | Chandoo.org – Learn .... [...]
I have Excel 2008 for Mac, and somehow I have lost my Excel "Help" file... How can I find whether a text string contains a specified smaller string?
Example: for the larger string "ABCDEFG," the required function would return TRUE for "DE" but FALSE for "DF."
hi!
May I ask if what is the formula to keep the countdown running each day?
I have a workbook with a summary page and a sheet for each day of the month. Each day's sheet has data with units of product sold and sales values. My Summary sheet collects totals for each unit of product sold into a stock control sheet. My Problem. Not everyday is a sales day, so I need to collect the last date that sales were made ie the first sales were made on the fourth of the month and the second sales were made on the 7th.
I need my stock summary report to show that the latest sale was made on the 7th.
Any ideas will be appreciated
Plz. help me about the following problem-
A,B,C,D are 4 employee get salary as 20000, 22000, 40000, 35000 tk.
Income tax rate 0% when salary below 20000; rate 5% for 20000 to 35000 and 10% on above 35000.
How can I calculate it in excel.
@Fayez
Assuming your salary is in B2 I'd use:
=IF(B2<20000,0%,IF(B2<35000,5%,10%))
how to extract name and number
Reliance A/c 100000117503457
use FIND to find the position of each blank space, then LEFT and RIGHT using those values.
[…] how to effectively survive spreadsheets, then sharpening your technical aptitude by learning 15 Really Useful Excel Formulas and an extensive list of excel keyboard shortcuts will help with speed and […]
Hi Chandoo. This is a great site. I had an issue. Im using sumifs to calculate some things but i basically am doing a sum of all the sumifs as one variable in the sumifs calculation always pulls from the same column. Is there a formula i can write which i would be easier?? Thanks for any help you can provide.
@Oz
Can you post the question in the Forums and include a sample file:
http://chandoo.org/forum/
? I have my sheets named day 1, day 2, day 3, I have formulas on day 3 that go back to day 2 to bring it to day 3, but all my formulas but when I copied day2 to make day3. I have to go into formulas and manually change the dates expl. ='DAY 2'!E5:F5 I have to go and change the 2 to a 3. Help
@Michael
The simplest is to keep an index number on every page say in a1 which identifies what day the sheet is
You can add them manually or using a formula like
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))-4)
Then in your cell you can use the Indirect Function to build the previous worksheet
say you are on Day 2 and need the formula to refer to Day 1
In cell A1 you will either have the number 2 or the above formula which will return the Number 2
Then elsewhere you can use eg:
=SUM(INDIRECT("'Day "&A1-1&"'!E5:F5"))
Hi Chandoo, thanks for your great work! I'm looking for an effective method to calculate the maximum numbers of outages in any day of the year. The data set is by year and contains unique work orders with a start date and an end date. I need to know the count of the day that had most outages. Appreciate any help!
@Andreas
Can you ask the question at the Forums and supply a sample file of say 50 or 100 records
http://chandoo.org/forum/
sorry again, I read too fast and didn't pick up on "start date and end date." Hope to post a new method shortly.
This works, but probably there's a much more elegant way:
Put year in Column A, month&day in Column B. Sort on B. Put a blank row before the first row. In C2(or whatever is the first row of your data), type "=IF(B2=B1,C1+1,1)" and copy that down. Select Column C; do a COPY and then PASTE (in same place), using "Values Only." Sort everything on C, descending. The day(s) with the most outages will be in the first row(s).
Dear Sir,
Greetings. I wish to be registered for free Excel Formula and guide if any, i.e., without subscription charges.
Thanks and Regards,
sir formula ISERROR(1234*0=43)is being shown as FALSE???
@Nisha
That is correct
The functional part of the formula 1234*0=43 returns a value of False as the left side of the = sign doesn't equal 43
But the value False isn't an error, it is just a Boolean value of False
So the Iserror() function now is the same as Iserror(False)
As False isn't an error, the function returns False saying that the contents aren't in error.
The function =Iserror(30/0) returns True because the internal part evaluates to an #DIV/0 error
and so the Iserror() function sees an error and returns True
HI,
I have a data total working days in each quarter & one of the date consist 2 quarters working days. (Ex- Q2 & Q3 includes 40 working days, in that I need to need to know Q2 working days & Q3 working days) is there any formula to separate the working days on each quarters. if there please guide me.
Being a designer It was always a quest for me to make excel reporting now its much easier thanks for the tips and tricks sir !
It is nice to learn thanks
This is really awesome. Thanks so much for sharing.
Great Help. Amazing tips. 🙂
Hi Chandoo..how to save the long IF formulas to avoid repetitive typing?
I have number of sheets as part of of my total project estimate which consists of many project and those I summarize to a page by putting the numbers on the summary sheet but for path directrory when i put it on one sheet ( i,e,=+cell('filename"), the detail pops up on all sheet as the first project at present i populate the project by thsis formula and then copy and paste value so that it becomes a text .
I do not know whayt i should do so that I can give the directory path to individual estimates clubbed as different excel sheet in the book
Can anyone figure this out:
This formula works perfectly:
=IF(A1+B1+C1<1,0,((2.6)+((A1+B1+C1)-(1))*0.25))
I want to change it and use A1:C1 (instead of A1+B1+C1) so I don't have to manually revise the formula every time I insert another column between A and C.
However, it no longer works if I simply substitute A1:C1, as follows:
=IF(A1:C1<1,0,((2.6)+((A1:C1)-(1))*0.25))
What am I doing wrong? I've tried SUMIF and SUMIFS, but still can't get it to work.
TEST RESULT:
If the value for A1, B1, and C1 are each 1, the result should be 3.10
If the values are 0 for each, the result should be 0
Use SUM(A1:C1) instead of A1:C1
It worked! Thanks so much.
hi all,
i have a spreadsheet where i capture data regarding my water, electricity and sanitation usage per month. everything works fine and my data is quite accurate but, now i want to add a feature where i can estimate an amount for each category e.g. if my water usage for the month was 23KL of water the cost will be R XXX,XX. This seems quite easy if you have the price per unit but, my problem comes in when i realized that my SP charges me a different price for different categories of water e.g.
for units 0-6KL of water i am charged 6KL @ R X,XX per KL
for units 7-12KL of water i am charged 6KL @ R XX,XX per KL
for units 13-18KL of water i am charged 6KL @ R XX,XX per KL
for units 19-24KL of water i am charged 5KL @ R XX,XX per KL (ALTHOUGH I HAVE NOT USED THE FULL 6KL OF WATER BUT ONLY 5)
THESE TOTALS ARE CALCULATED TOGETHER TO GIVE ME A TOTAL COST FOR THE WATER USAGE.
my question is: I there a way to breakup a total of 23KL usage into four different total: 1st 6, 2nd 6, 3rd six and the rest (5) so that I can add the price per unit for each and calculate the estimated cost for the total if it was calculated i the manner above.
Hello Brandt;
I prepared an Excel worksheet to solve your problem. Please download the Excel workbook file from the following URL:
http://s000.tinyupload.com/?file_id=49965535413976665648
I created a Lookup Table (as you'll see inside the Excel file) where the leftmost column begins with the value zero and continues with the lower ends of the ranges of usages (in your numerical example above, these would be 0, 7, 13, 19, 25, etc.) I assumed that there are 10 price categories with the last category applicable for a volume of water usage of 55 KL or above.
The second column in the table contains the applicable price per KL. The third column (titled 'Base Total Price' in my worksheet) shows the base price payable. Its formula is: =IF(ISNUMBER($C5), $B5*($A6-1)+$C5,0). For example, for a water usage of 23 KL, you lookup 23 in the table with approximate match, locate the row starting with 19 KL, and retrieve from that row the base total price of $138. Why $138? Because (6 KL * $5 per KL + 6 KL * $8 per KL + 6 KL * $10 per KL) = $138. I made up all the unit water usage prices in each range.
Finally: The amount billed is calculated with the formula =IF(Usage=0,0,VLOOKUP(Usage,LookupTable,3)+VLOOKUP(Usage,LookupTable,2)*(Usage-VLOOKUP(Usage,LookupTable,1)+1)).
The formula may seem a little bit too long, thus intimidating. However, it is nothing but simple algebra.
For your example water usage amount of 23 KL, it first locates the 4th row of the LookupTable starting with the amount of usage "19" KL; retrieves the base price $138 from that row; adds on top of that the difference between the actual amount of usage (which is 23 KL) and the upper end of the previous price range (namely: 23 - 19 + 1 = 5 KL) times the applicable unit price (namely $12). Hence, the amount billed is $138 + $12*5 = $198.
You can modify the lower ends of the water usage ranges with different unit prices. The only assumption in my worksheet is that the water usage got to be an integer number and not a fractional amount.
Hope this covers your need. Best regards & happy Excelling! - Deniz
Hi Deniz,
Thank you very for the help. It is appreciated very much. Although I haven't had time to actually look at the spreadsheet, it does make sense if I read you reply. I will test you spreadsheet and if it work integrate it with my tables. I will let you know if I struggle but I usually win with the help that I get on Chandoo.org
Regards
Thank you Deniz Hocam =)
Have you thought about incorporating some social bookmarking buttons to these blogs. At least for twitter.
Dear Sir,
We have one problems please help on this
We Have one rage like
100
150
200
250
100
300
350
100
and than we using this =SMALL(C1:C8,3) result is showing 100
Sir Please help on this
The SMALL function is working properly, since there are three instances of 100 in your range: If you sort the values in your range, the third value from the "small" end is definitely 100.
Thanks @workingonit
Hi
These days i want learn MS access so pls tell me all access web site in Hindi and English.
Dear All,
I have one problems pls help on this
Convert the ff hire Date in Date Format using Formula
Of hire Date Desired Output in Date Format Solution (Using Formula)
Feb 15 2016 15-Feb-16
Oct 17 2016 17-Oct-16
Jul 15 2016 15-Jul-16
Jun 16 2016 16-Jun-16
Apr 30 2017 30-Apr-17
Mar 19 2017 19-Mar-17
Jan 10 2017 10-Jan-17
Thanks a lot iam a excel trainer in active excel microsoft excel certifications in india please contact us
Hello chando,
I need your support,like I am from logistics in my company I need to go through excel data day to day in my life, I want to learn to make my work easy in maintaining data free work.
I don't know how to start from were to start.whether power BI or VBA or excel crawlers like which all this make me easy in my day to day work. I am ready to join has a student to learn.
Please suggest me and I will be waiting for your help.