In April 2011, Mdsuhair asked a question at the Chandoo.org Forums
This is a candidate for Solver.
This post will walk us through the solution to the problem using Solver. It uses Solver and screen shots from Excel 2007.
All versions of Excel have solver available. Users should note that the screen shots may not match your version although the functionality will, some of the functions appear in different locations in different versions of Solver.
What and Where Is Solver
What is Solver
Solver is an Excel add-in that can solve problems by enabling a Target cell to achieve some goal.
This goal may be to minimse, maximise, or achieve some target value.
It solves the problem by adjusting a number of input cells according to a set of criteria or constraints which are defined by the user.
Where is Solver
Solver is an Excel add-in supplied with Excel, but not enabled by default.
To enable solver
File, Excel Options, Add-ins, Manage Excel Add-ins, Select the Solver Add-in checkbox
Solver will now appear as a New Tab on the Data, Analysis Tab
Solver Example
This post is based around a worked example Solver Example File, the file is compatible with all versions of Excel.
Defining the Problem
Mdsuhair had a series of 8 Items each which had a value:
He wants to know which items should be combined so that the sum of the values of the items is nearly equal.
To do this we need to assign each item into a Bucket. Namely Bucket A and Bucket B.
We will put values of 1 into a Bucket for each Item to show that it is assigned to that Bucket and a value of 0 to show that the bucket is empty (In/Out) Value.
We can start by adding a Total Column, which counts items in Buckets A + Bucket B for each Item, It then totals the totals.
We also know that we need to work out the Value of each Item in each Bucket
We do this by multiplying the Items Value by the Buckets (In/Out) value
Finally we need to add up the values in each Bucket and work out the difference between them
Mdsuhair’s original problem was to minimise the difference between the sum of the values in the 2 buckets.
Now we can place values of 1 in the buckets manually and Excel will show us the value of each Bucket and the Difference between them in Cell G11.
I have applied some conditional formatting to show when a cell has a value > 0.
The problem is that there are 28 or 256 combinations of answers, and to test them all manually at 1 every 5 seconds would take 21.3 minutes, assuming we can keep up that pace and remember which was the best combination.
This is where solver comes to the fore.
Applying Solver
To apply solver we need to define a series of requirements, rules and constraints.
These requirements, rules and constraints guide solver and set limits which allow solver to quickly narrow in on the answer.
What are our rules
Our main requirement is to minimise the difference between the value of the 2 buckets.
The difference between the 2 buckets in our example is cell G11, the sum of Bucket 2 values minus the Sum of Bucket 1 values.
We want to have G11 as low as possible but greater than or equal to 0.
We also know that an item can only be in Bucket A or Bucket B, it can’t be in both and can’t be Broken apart.
That is 2 Constraints for each Item
Firstly The Total column must be equal to 1
Secondly the Buckets Values must be Integers
We also know that the Total Number of Items is 8, this is another constraint.
We will discuss how these constraints are used in the next section
The Solver Window
This section will explain the solver window and its use in defining the problem within solver.
A Blank Solver Window
A Filled Solver Window
Set Target Cell:
This is the Target cell which is the cell which you are trying to solve the problem for.
Our Target cell is G11, The difference between the 2 Buckets values
Equal To:
The Equal To: section defines what we want to do with our Target Cell.
We want to achieve the same value in each Bucket and so the difference between the Buckets will be 0.
It might sound strange but we don’t want to minimise that difference. A minimal value will be achieved when all the Items are placed in Bucket A, as our equation for G11 will then have 0 – Total which is –Total, which is more minimal than 0.
Another way to constrain this is to Change G11 to =Abs(G10-H10)
This allows us to use Min as an Equal To: Value
But for now we can just leave G11 as =G10-H10 and we will set the Equal To: section as 0.
By Changing Cells:
Changing Cells: refers to the cells which will be modified by Solver to try and solve the problem.
We want to let Solver change the number of items in each bucket, this is the range: $C$2:$D$9
Hint: You can try the Guess button next to the Range Reference and Solver will take a Guess at what cells the problem is dependent on.
Always check this if you use it, especially in complex models.
Subject to the Constraints:
Constraints are the rules which define the limits of the possible solutions to the problem
We will add several constraints for our rules:
1. The Total column must be equal to 1 for each Item
2. The Bucket Value must be an Integer
3. The total contents of the 2 buckets must be 8 items
4. You could add a further constraint that each Bucket should hold the same number of items
Hint: As a general Rule, Under Constrain rather than over constrain! You can always add more constraints later.
To do this we will use the Add Constraint Button
1. We need to add a constraint for each cell in the Range E2:E9 that it is only allowed to be = 1
This constraint must be applied for each cell in the range E2:E9
2. We need to add a constraint for each cell in the Range C2:D9 that it is only allowed to be an integer
This constraint must be applied for each cell in the range C2:D9
3. We need to add a constraint for the Total of the 2 Buckets, E10=8
You can Change or Delete Constraints if you make a mistake by selecting the appropriate constraint and using the Change or Delete Buttons
Save and Load Solves Parameters
Selecting the Options Button there is the Option to Save Model and Load Models.
Hint: The Save/Load Models has been shifted onto the main Solver dialog in Excel 2010.
Using the Save Model and Load Model options you can Save and the Load the Solver Parameters for your model. The Save Model saves the parameters in a Range of cells as shown below.
This allows an easy way to actually setup and/or change the solver parameters.
Hint: Setup one constraint using solver then Save the model. Edit the model on the worksheet and re-load the model as required.
Note: That the parameters although when saved show as True/False or Numbers are all Excel Equations, see above.
This means you can edit them to change the Constraints and Parameters as required and re-load them into solver.
I have included 3 sets of Parameters for our model.
These are:
- Base Case – Forces bucket values to be equal, Allows uneven bucket counts
- Equal Sized Buckets – Forces each bucket to contain the same number of items
- Force an error – Which forces an error in the solver model
Load each model and try them at your leisure.
Running the Solver Model
Warning: Solver is a computationally complex add-in, so once your model is setup, Save your Workbook.
Prior to running the model there are a few parameters we should look at to ensure the model solves correctly.
On the main Solver window select the Options button. (Some of these parameters are on the Main Solver window in Solver 2010)
Generally you can accept the defaults but in this case we will change the following
Assume Linear Model – Select
Assume Non-Negative – Select
Note: Solver in Excel 2010 will return a better answer without these 2 parameters enabled by default
The other 2 parameters which you may need to change from time to time is
Precision: Precision is a number from 0 to 1 and higher means more precise
Tolerance: Tolerance shows how far away from a Number, an Integer constraint is allowed to be
The use of the Estimates, Derivatives and Search parameters are beyond the scope of this post. I direct you to the Excel Help on these subjects, by selecting the Help button.
Run the Model
To Run the Model, select the Solve Button from the main Solver menu.
The main status bar in Excel will flash up a number of statistics about the internal workings of the Solver add-in. Generally these flash by and are too fast to read. If a model is too complex it may stall and you won’t see any movement for a while. Solver generally recovers from these problems itself.
Once the Solver model finishes it will display a dialog of the results and allow you to do several things
First thing to note is that “Solver Found a Solution“.
If it has found a solution, the worksheet cells will be changed to show the solution
You now have 4 options:
- Run a Report
- Save a Scenario
- Return to the model
- Check Your Results
Run a Report
Run a report by clicking the report you want.
A new sheet will be added to your workbook depending on the report but will be called:
- Answer Report 1,
- Sensitivity Report 1
- Limits Reports 1
etc.
Note, that not all reports are relevant at all times, depending on the Constraints you have applied.
Save a Scenario
Selecting the Save a Scenario button takes you to the Save Scenario dialog.
Type in a Name and the Scenario of your model is saved as a Scenario.
Scenarios as available for use in the Scenario Manager, which is accessed from the Data, What-If-Analysis Tab
Return to the model
You can return to your model and either:
- Keep Solver Solution
- Restore Original Values
Check the Results
Solver is probably the most Black Box’ish of systems within Excel. As such any results it puts out must be manually checked for suitableness before further use.
These checks for realness, should as a start confirm that the results meet all the criteria supplied.
Are the results roughly what were expected?
Are any Minimums or Maximums violated?
What If Solver Doesn’t Find a Solution?
From time to time Solver will return with an error that a “Solver could not find a feasible solution.”
When this happens it is indicating one of several possibilities:
- Your model is over or under constrained
- Your model constraints are impossible to meet
- Your model constraints have an error
Start by checking the current constraints for errors and ambiguities
Eg: in our case we have 8 items so requiring the Count of the two buckets to be 20 is impossible to meet
My Solver Answer has Strange Numbers?
In solver up to and including Excel 2007, solver would commonly return numbers like 3.5E-18.
This is 0.0000000000000000035, which is effectively 0
If your model returns these, feel free to go through the model and change them to 0, in our model we should also check as the corresponding 1, may in fact be 0.9999999999999999965.
It should be noted that this problem in Solver in Excel 2010 does not occur as often but will still occur.
What and How have you used solver in the past?
What and How have you used solver in the past?
Let us know in the comments below:
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.