Recently, Bluetaurean asked in the Chandoo.org Forums about ways to allocate work durations for various product lines across 24 hour days to create a daily schedule.
Both formula-based and VBA-based solutions were offered.
Today at formula Forensics we will take a look at the formula-based approach.
As always at Formula Forensics you can follow along, Download Here – Excel 2007-2013.
Set the Scene
Since one might encounter a similar need in a variety of contexts (manufacturing, engineering, project planning, etc.), we will look at a more general problem of allocating a set of tasks and corresponding durations to one or more days, as shown in the following diagram.
We will create two output views:
- One that is a flat list that can then be manipulated further using Excel’s Pivot table feature, and
- Another view that mimics a pivot-table (and is similar to a typical project Gantt view, but with actual values listed instead of a bar chart).
You can follow along using the attached Excel document. Download here Excel 2007+
Problem Specifics
- We have a list of tasks and their durations.
- We need to distribute the tasks to different days, without exceeding the maximum available duration in a given day.
- When the hours in a day are “used up”, we need to allocate the remaining task duration to the next day, and so on.
- On the other hand, if a given task does not use up all of the hours in a given day, we will need to assign more than one task for that day, provided the combined durations do not exceed the available hours for that day.
- In other words, we will need to split a task across one or more days, or combine one or more tasks into a single day, as needed, to maximize the work performed in a given day.
Developing the Approach
Before we tackle this problem in Excel, let us review how we might do this manually. Like most things, we might use the following three step process:
- Take the first task and assign its duration to Day 1. If the task’s duration exceeds the maximum hours available in a day, allocate the portion of the duration that does not fit into Day 1 into Day 2.
- Take the second task, and see whether it can fit into an existing day, or whether it needs to be distributed to multiple days
- Etc. (OK… so that three-step process was a stretch!)
Statistics show that most people think in terms of IF-THEN-ELSE statements. So here it is…
For a given Day, and for a given Task, If [Hours Not Allocated For that Task] > [Hours Available for that Day] Then Set Duration for that Day as [Hours Available for that Day] Else Set Duration for that Day as [Hours Not Allocated for that Task] EndContinue the above evaluation until all tasks have been allocated to days.
Of course, the above IF() logic can be condensed as follows:
MIN( [Hours Not Allocated For that Task] , [Hours Available for that Day] )
Putting it All Together: Output Option 1: Gantt-like View
Let us employ the above approach to create the Gantt-like view.
To make our approach more generic, we will use an Excel Name called “MaxHrsPerDay” to indicate the maximum available hours in a given day. (In the sample worksheet, it has been set to 24 hours.)
Our source data is setup as shown in the diagram below:
- Tasks are in the range A2:A5
- Durations are in the range B2:B5
We will create the output in a separate worksheet, in the range A1:E5 as shown below:
Put the following formula into cell A2 and copy down to A5:
=SourceData!$A2
(This formula is merely referencing the values from the SourceData sheet. The sample workbook also includes an approach to make this reference more location independent.)
Put the following formula in cell B2, and copy it down and right:
=MIN((SourceData!$B2-SUM($A2:A2)), (MaxHrsPerDay-SUM(B$1:B1)))
Setup the header row (B1:E1) as desired. (I have used text values for the header. You could also calculate the header text using formulas. Since that is straightforward, I will leave that as an exercise for the reader.)
Now let us look at what the formula in cell B2 is doing:
- SUM($A2:A2) is calculating the sum of the allocated durations for TaskA. (Please note the use of absolute and relative references. The formula is anchored on column A, but the starting row, ending row and ending column are free to expand.) SUM($A2:A2) returns zero since SUM() ignores text values.
– If you look at cell C2, the reference changes to SUM($A2:B2).
– In cell B3, the reference changes to SUM($A3:A3). You get the idea
- (SourceData!$B2-SUM($A2:A2)) calculates the difference between the duration for TaskA (40 in the example) and the hours allocated as of that point (0), to return 40-0=40.
- SUM(B$1:B1) is calculating the sum of the allocated hours for Day1. (Again, we are using a combination of absolute and relative references to keep the calculation anchored on column B.) In this case, the value is zero, since this is the first allocation for Day1.
- (MaxHrsPerDay-SUM(B$1:B1)) calculates the hours remaining (i.e. available) for Day1. Since this is for cell B2, the calculation returns 24 – 0 = 24.
That is it!
We put those absolute and relative references to good use!
This approach was easy because all we had to do was calculate the duration for a given task for a given day.
On the other hand, if we had to figure out what the Task was, or which Day it was, the calculation gets a little more involved. Since this is “formula forensics”, we would not have it any other way! 🙂
Putting it All Together: Output Option 2: A Sequential List of Tasks and Durations for Each Day (i.e. a Flat List)
As before, we will use the Excel Name “MaxHrsPerDay” to refer to the maximum hours in a Day.
As shown in the following diagram, we will turn the source data into a flat list of Days, Tasks and Durations:
Unlike with VBA, since a formula cannot choose which row and column to write its output, we have to set the formula in every cell where we suspect there might be a value.
In the above sample diagram, we copy the formulas from row 2 to row 9. However, row 9 shows “…” indicating that the list was completed by row 8.
Let us look at how to determine the value for Day, Task and Allocated Duration.
For ease of description, I have created the following Excel Names:
WorkList: =A2:A5 in the source data.
WorkDuration: =B2:B5 in the source data
While creating the Gantt-like view earlier, we were able to take advantage of the static “Day” and “Task” values to determine the Remaining Duration, Available Duration, etc. Since we now have to determine all three values (Day, Task, Allocated Duration), we will need some “helper” data.
We will add a column alongside the source data that shows the cumulative duration (for reasons that will become clear shortly), as shown in the following diagram:
Cumulative Duration is calculated as the sum of all durations up to a given row.
- For example, in cell C2, the Cumulative Duration is 40.
- In cell C3, the Cumulative Duration is 40+20=60
- And so on.
For ease of referencing, we will use an Excel Name called CumulativeDuration =C2:C5.
Let us look at why we need the “CumulativeDuration” helper column:
The circular logic problem
In order to determine the durations already allocated for a given day, we will need to know which Day it is.
We also need to know which Task we are trying to calculate the duration for.
So… do we calculate the Day or the Task or the Duration first?!! As you can imagine, that will soon land us in some circular logic.
Some helpful observations about the output:
- In column C of the output (on worksheet FlatList), the sum of allocated durations adds up to the total duration for all tasks. (No surprise here!)
- If every task had duration equal to the MaxHrsPerDay, you would have the same duration value for all days. (Not surprising, but interesting!)
- In other words, you could think of the Allocated Duration column as the total duration for all tasks, allocated MaxHrsPerDay at a time.
- Now we need a way to iterate through the duration values one at a time and account for the durations already processed. In other words, each value needs to contain all of the previous values. Welcome to an array of the cumulative durations!
- For example, in the cumulative array “{40;60;65;80}”, the value 60 already includes the previous value 40 in it. This allows us to subtract all durations allocated up to a given row, to get the duration value that is remaining to be allocated.
- Since Excel is good with numbers, we will base the calculation for AllocatedDuration and Tasks on the Duration values.
- By calculating the two values separately, we avoid the circular logic.
Let’s now look at the formulas for Day, WorkItem and AllocatedDuration.
It would be easier if we looked at the formulas in reverse order, starting with AllocatedDuration, then WorkItem, and finally Day.
Formula for “AllocatedDuration”
Enter the following formula into cell C2, ending with Ctrl+Shift+Enter, as shown in the following diagram:
=IF(SUM(C$1:C1)>=SUMPRODUCT(WorkDuration), “…”,MIN(INDEX(WorkDuration, MATCH(TRUE, CumulativeDuration-SUM(C$1:C1) > 0, 0)) – SUMIFS(C$1:C1, B$1:B1,B2), MaxHrsPerDay-SUMPRODUCT((A$1:A1=A2)* IF(ISNUMBER(C$1:C1), C$1:C1, 0)))) Ctrl+Shift+Enter
Let us look at the formula closely (using the formula in row 2):
- SUMPRODUCT((A$1:A1=A2)* IF(ISNUMBER(C$1:C1), C$1:C1, 0)) -> This calculates the sum of all allocated durations up to the previous row, where the Day = current row’s day. Please note the use of absolute and relative references. They allow us to expand the range as we go down the rows, while remaining anchored to the first row.
– Since this is the first data row, C$1:C1 returns “Allocated Duration” and the ISNUMBER() function returns FALSE, and consequently, the IF() function returns 0.
– A$1:A1 returns “Day”, and the test A$1:A1=A2 returns FALSE. Please note that in this case, it does not matter whether A2 has a value in it, whether it has the value 1, etc.
– SUMPRODUCT() provides the result of FALSE * 0 = 0
- MaxHrsPerDay – SUMPRODUCT((A$1:A1=A2)* IF(ISNUMBER(C$1:C1), C$1:C1, 0)) -> This calculates the difference between maximum duration available for a day and the sum of durations allocated for the current day. In other words, it calculates the available duration for the current row’s day.
– In this example, the calculation results in MaxHrsPerDay (24 in our example) – 0 = 24
- SUMIFS(C$1:C1, B$1:B1,B2) -> This calculates the sum of all allocated durations for the current row’s task. Since B$1:B1 is the text value “Work Item”, the SUMIFS() returns 0. Again, it does not matter if B2 is blank or has a value like “TaskA”, since Excel correctly evaluates the condition whether B$1:B1 equals B2.
- SUM(C$1:C1) -> This calculates the sum of all allocated durations up to the previous row.
- CumulativeDuration — SUM(C$1:C1) -> CumulativeDuration evaluates to {40;60;65;80}. SUM(C$1:C1) evaluates to zero. As such, the expression evaluates to {40;60;65;80} – 0, or {40;60;65;80}.
– If we look at the calculation for this expression in cell C3 (the expression would be “CumulativeDuration—SUM(C$1:C2)”), we would get the result of {40;60;65;80} – (0+24) = {16;36;41;56}. (As you know, subtracting a scalar value from an array results in an array with each value reduced by the scalar value.)
– If we look at the calculation for this expression in cell C4 (the expression would be “CumulativeDuration—SUM(C$1:C3)”) , we would get the result of {40;60;65;80} – (0+24+16) = {0;20;25;40}
– As you can see, each successive calculation reduces the CumulativeDuration array by the amount of hours already allocated. By reducing the CumulativeDuration array in this fashion, we ensure that we do not “double count” a duration.
– If a value in the array evaluates to zero, it means the corresponding duration has been fully allocated. (In cell C3, the first value in the array is zero, indicating that the original 40 hours has been fully allocated.) We will put this knowledge to good use in the next expression.
- MATCH(TRUE, CumulativeDuration—SUM(C$1:C1) > 0, 0) -> The expression CumulativeDuration—SUM(C$1:C1) > 0 evaluates to ={TRUE;TRUE;TRUE;TRUE} because all values are greater than zero. By performing a MATCH() for TRUE, we are able to find the first location in the array that has a non-zero value.
– If we look at the result of this expression in cell C3, we get {16;36;41;56} > 0 = {TRUE;TRUE;TRUE;TRUE}
– If we look at the result of this expression in cell C4, we get {0;20;25;40} > 0 = {FALSE;TRUE;TRUE;TRUE}
– As you recall, the zero values (or FALSE) correspond to the durations that have been fully allocated, whereas, the non-zero values (or TRUE) correspond to the durations that have NOT been fully allocated.
– It is helpful to note that MATCH() returns the LOCATION of what it finds. As such, the returned location is that of the first duration value that has not been fully allocated! Since the CumulativeDuration array is the same size as the WorkDuration array, we will be able to put this returned location value to good use in the next expression.
- INDEX(WorkDuration, MATCH(TRUE, CumulativeDuration — SUM(C$1:C1) > 0, 0)) -> By using the location value (of the first duration value that has not been fully allocated), we find the corresponding original duration value from the WorkDuration array.
– As we saw earlier, the expression “CumulativeDiration – SUM(C$1:C1)” reduces the CumulativeDuration by the duration values allocated to that point. However, the resulting array could have partial duration values as well. By referencing the corresponding duration value from the WorkDuration array, we ensure that we retrieve the original (full) duration value that was to be allocated.
- MIN(…) -> This expression calculates the value of MIN([Hours Not Allocated For that Task], [Hours Available for that Day])
– [Hours Not Allocated For that Task] is returned by INDEX(WorkDuration, MATCH(TRUE, CumulativeDuration—SUM(C$1:C1) > 0, 0)) – SUMIFS(C$1:C1, B$1:B1,B2)
– [Hours Available for that Day] is returned by second half of the MIN() expression: MaxHrsPerDay—SUMPRODUCT((A$1:A1=A2)* IF(ISNUMBER(C$1:C1), C$1:C1, 0)).
– So, we essentially got back to the logic we started from, which is the same logic we used for creating the Gantt-like view as well.
- The remaining portion of the formula (the IF() check) determines if all of the hours have been allocated. If all hours have been allocated, it returns “…”.
– SUMPRODUCT(WorkDuration) -> This expression calculates the total of all work duration values. In cell C2, it evaluates to SUMPRODUCT({40;20;5;15}) = 80
– SUM(C$1:C1)>=SUMPRODUCT(WorkDuration) -> Determines if the sum of durations allocated up to that point is greater than the total for all durations. (Since this is part of an array formula, you could also use the SUM function in place of SUMPRODUCT. But I am partial to the SUMPRODUCT function!! So, unless you are in a competition where the winner is determined by the shortest formula, feel free to use either one!
Formula for “WorkItem”
Enter the following formula into cell B2, ending with Ctrl+Shift+Enter, as shown in the following diagram.
=IF(SUM(C$1:C1)>=SUMPRODUCT(WorkDuration), “…”,INDEX(WorkList, MATCH(TRUE, (CumulativeDuration-SUM(C$1:C1)) > 0, 0))) Ctrl+Shift+Enter
You are already familiar with most of the formula components since you saw them in the formula for AllocatedDuration. The only difference is that in this formula, we are returning a value from WorkList. (i.e. we locate the position of the first non-zero duration in CumulativeDuration array, and since that array is the same size as the WorkList array, we are able to find the first Task that has not been fully allocated.)
Formula for “Day”
Enter the following formula into cell A2, ending with Ctrl+Shift+Enter, as shown in the following diagram:
=IF(SUM(C$1:C1)>=SUMPRODUCT(WorkDuration), “…”, MAX( N(A1) + (SUMIFS(C$1:C1, A$1:A1, A1)>=MaxHrsPerDay), 1)) Ctrl+Shift+Enter
Let us look at the formula in detail (using the formula in row 2):
- SUMIFS(C$1:C1, A$1:A1, A1) -> This expression calculates the sum of all durations (in column C) where the Days (in column A) equal the previous day.
– In cell A2, this expression evaluates to “SUMIFS(“Allocated Duration”, “Day”, “Day”)” = 0. (Excel smartly ignores any non-numeric values in the first argument.)
– In cell A3, this expression evaluates to “SUMIFS({“Allocated Duration”;24}, {“Day”;1}, 1)” = 24.
- SUMIFS(C$1:C1, A$1:A1, A1)>=MaxHrsPerDay -> This expression checks if the sum of all durations where the Days equal the previous day is greater than or equal to MaxHrsPerDay.
– In cell A2, this expression evaluates to FALSE
– In cell A3, this expression evaluates to TRUE
- N(A1) -> This expression returns the numeric value for its argument. Since N() returns zero for any non-numeric arguments, we use this function to return zero for the heading (“Day”) in A1. (Any numeric values are returned as is.)
- MAX( N(A1) + (SUMIFS(C$1:C1, A$1:A1, A1)>=MaxHrsPerDay), 1) -> The first argument of the MAX function “N(A1) + (SUMIFS(C$1:C1, A$1:A1, A1)>=MaxHrsPerDay)”returns the next increment for day, if the previous day has been fully allocated. Otherwise, it returns the same value as the previous day.
– In cell A2, this expression evaluates to MAX( N(“Day”) + (SUMIFS(“Allocated Duration”, “Day”, “Day”)>=24), 1), which evaluates to MAX( N(“Day”) + (0>=24), 1), which evaluates to MAX( 0 + (FALSE), 1), which finally evaluates to 1.
– In cell A3, this expression evaluates to MAX( N(1) + (SUMIFS({“Allocated Duration”;24}, {“Day”;1}, 1)>=24), which evaluates to MAX( N(1) + (24>=24), 1), which evaluates to MAX( 1+ (TRUE), 1), which finally evaluates to 2 since 1 + TRUE = 2.
Download
You can download a copy of the above file and follow along, Download Here – Excel 2007-2013.
Final Thoughts
While we used the same basic logic for both output options in this article, there are probably many other ways to tackle the age-old problem of production scheduling.
I would love to hear about some of your ideas, as well as ways to extend the concepts described here.
In the meantime, I wish you continued EXCELlence!
Sajan.
Other Chandoo.org Posts related to Scheduling
Here at Chandoo.org you can find the following related posts:
http://www.chandoo.org/wp/2010/11/18/scheduling-variable-sources/
http://chandoo.org/wp/2009/06/16/gantt-charts-project-management/
http://chandoo.org/wp/project-management-templates/gantt-charts/
Thank You
This was Sajan’s second post at Chandoo.org and so a special thank you to Sajan for putting pen to paper to describe the technique here.
You may want to read Sajan’s first post here or thank him in the comments below:
Formula Forensics “The Series”
This is the 31st post in the Formula Forensics series.
You can learn more about how to pull Excel Formulas apart in the following posts: Formula Forensic Series
Formula Forensics Needs Your Help
I need more ideas for future Formula Forensics posts and so I need your help.
If you have a neat formula that you would like to share like above, try putting pen to paper and draft up a Post like Sajan has done above or;
If you have a formula that you would like explained, but don’t want to write a post, send it to Hui or Chandoo.
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.