Search results

@balaji3081 I am not sure if we can write a simple SUMIF or other formulas to solve this problem. But you can use a pivot table to get similar result. See attached.

Good idea Luke.. I will schedule a post on this.

Hi Ian, Thanks for your message and interest in this course. Yes, you get full downloads (videos, workbooks, course notes) with the $297 price. Please visit http://chandoo.org/wp/financial-modeling/ to join us.

Hi @stanley thomas Welcome to Chandoo.org forum and thanks for posting your question. You can use MEDIAN(IF(..)) pattern to solve this problem. Assuming your data is in columns B & C (B3:C43), below formula gives the answer. =MEDIAN(IF(($B$3:$B$43>=120)*($B$3:$B$43<=140)=1,$C$3:$C$43)) You...

@Stephen de Cadenet Welcome to chandoo.org forums and thanks for posting your question. You may use a couple of helper columns to solve this problems. For the sake of simplicity, I have named the range of data as data. This optimizes the formulas. Set up 2 columns - start & end and write...

@Ecel Dumbo... Interesting question. To be fair, your model doesn't look versatile. May be it is work in progress. Assuming you have the information about how many each person should receive in the cells I2:K2, You can use below formula in cell I5. Copy paste it in the range I2:K11 to get rest...

Hi Matt, Welcome to Chandoo.org and thanks for your question. See these pages for help on how to solve this problem. http://chandoo.org/wp/2010/06/30/range-lookup-excel/ http://chandoo.org/wp/2015/12/01/pricing-tier-lookup-formula/

Good formulas @Deepak Here is one more that is generic. Assuming your substrings are in E3:E6 and the cell to match is B3, =INDEX($E$3:$E$6,SUM(COUNTIFS(B3,"*"&$E$3:$E$6&"*")*ROW($A$1:$A$4))) Array formula, so press CTRL+Shift+Enter after typing. Here is the sample workbook. A while ago...

@sherifab: As we have already issued your refund, can you please visit http://chandoo.org/pmt/pmt-index-1.html and purchase the project management templates. Sorry for the inconvenience.

@sherifab Thanks for purchasing our templates. We have received only email from your since your purchase. This was on December 2nd. May be the other emails were sent to someother address? Anyways, I have processed your refund as you wanted some other product. All the best.

Good one @Asheesh Here is another try. First define two names start =MATCH($G$2,$A$2:$A$33,0) end =MATCH(1,INDEX($B$2:$B$33,start+1):$B$33,0)+start Now select a bunch of cells starting I4 and type, =INDEX($C$2:$C$33,start):INDEX($C$2:$C$33,end) and press CTRL+Shift+Enter You should see the...

As per your example and your original formula, you are only looking at LOA data in first 2 columns. So I assumed there will be only one LOA per month. Also, since you have 3 sets of LOA columns, I assumed each column refers to one month. If multiple LOAs can be entered, your input data...

@Leigh Digons Welcome to Chandoo.org forum and thanks for posting your question. At the outset, your formulas for calculating Days OOO (out of office?) and Days in Office seem very long and complicated. I may be wrong in interpreting their full power and intentions. Here is a simpler formula...

@Frank Bacchus I am not sure why you would use a complex formula like, =IFERROR(IF(ISBLANK(A2),"NO",LOOKUP(2^15,SEARCH($H$1:$H$366,B2),$I$1:$I$366)),"YES") When the obvious one is better - =VLOOKUP(B2,$I$1:$J$366,2,FALSE) That said, the reason for your error is below: SEARCH formula...

@sachar Try this formula =VALUE(LEFT(E2,FIND("/",E2)-1))

@Mange08 I think you can solve this with a helper column next to original data table. You may be able to solve this with single formula, but I can't figure out a simple solution yet. Please find the attached workbook.

Try something like this: Calculate the total of each sport scores Divide this with the number of people selecting for that sport (ie average) Now, divide this average with total of all averages of all sports. You get % representation. Same as Nebu's solution.

You can calculate the range of data (max minus min) using MAX(E14:E253) - MIN(E14:E253). Once you have the range, calculate 4 chunks. Using these chunks, you can then find which chunk (or bucket, as these are usually called) an organization belongs to. If you wish, you can calculate relative...

@Asheesh brilliant. I was trying to use SUM(OFFSET(...)) structure and couldn't get it. :awesome: idea to use SUBTOTAL... Here is one more: =LOOKUP(H2, SUBTOTAL(9,OFFSET(A2,,,,COLUMN($A$1:$G$1))))

@Emmanuel Guido Interesting variation of original question. Here is one formula that seems to work ok. Assuming your data is in range A2:B11, you can use below formula. =IF(B2<>"",B2,SUM(B2:INDEX(B2:$B$11, MATCH(A2,A3:$A$11,0)))) We essentially get the value in column B if one is present...

:awesome: stuff @pedrowave

@KIM Chanthan Thank you. You can use similar logic to find most frequent item in the list for a given dept. Below array formula works fine, assuming E1 contains the branch code. =INDEX($B$2:$B$13, MATCH(MAX(COUNTIFS($B$2:$B$13,$B$2:$B$13,$A$2:$A$13,$E1))...

Congratulations to all the new ninjas.. It is a pleasure and honor to have you here and learn from you. Thank you :)

@KIM Chanthan Interesting question. You can use MODE formula to find the most frequent item in a list, but it works only for numbers. So we use a pair of INDEX & MATCH to get what we want. For your data, this formula works: =INDEX($A$2:$A$13,MODE.SNGL(MATCH($A$2:$A$13,$A$2:$A$13,0))) You may...

Also check out Excel Sparklines - an introduction.