• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. K

    Filter with three criteria

    Hi What version of excel are you using, if you have 365 or 2019, take advantage of DA's =FILTER(B17:P30,(H17:H30=B11)*(F17:F30=B13)*(I17:I30=B15),"") format "General;;" or for 4 columns =FILTER(CHOOSE({1,2,3,4},C17:C30,F17:F30,H17:H30,I17:I30),(H17:H30=B11)*(F17:F30=B13)*(I17:I30=B15))
  2. K

    How to generate numbers 0,1,2,3 in adjacent cells

    Hi If you have office 365. For the horizontal numbers: =SEQUENCE(,8,0,1) For the vertical numbers: =SEQUENCE(11,,0,1)
  3. K

    IF condition is not met, then check on next row

    Hi If you do away with columns C & D, you can use the following. =FILTER(A3:A10&"-"&B3:B10,A3:A10="Apples")
  4. K

    24 hour format time, calculate total hours

    Hi Try the following in F14 =E14-D14+(E14<D14)
  5. K

    Convert into figure

    Use Text to Columns on the Data Tab
  6. K

    Array function

    Maybe a bit faster and non CSE! =IFERROR(INDEX($A$2:$A$199,AGGREGATE(15,6,ROW($A$2:$A$199)-ROW($A$1)/($B$2:$B$199=E$1),ROWS(E$2:E2))),"")
  7. K

    Round a date to previous Saturday

    Or another solution is using the FLOOR function. =FLOOR(A1+7,7)-7
  8. K

    Multiple Value Look up

    Hi nishad See the attached file or look into pivot tables.
  9. K

    Work days formula

    Perhaps you need to post the version of excel you are using, if you are using Excel 2010>. The you can use the NETWORKDAYS.INTL function. =NETWORKDAYS.INTL(B3,B23,"0000001")
  10. K

    Flip names [SOLVED]

    Hi Try the following, assuming the data is in A1. =RIGHT(A1,LEN(A1)-FIND(" ",A1))&", "&LEFT(A1,FIND(",",A1)-1)
  11. K

    Need help extracting numbers from a string that has mix of numbers and letters.

    Hi Try the following. In B1:=LOOKUP(20^20,1*MID(A1,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1))))) and copy down. Kevin
  12. K

    Array for counting total number of items in list, less duplicates, by month

    Hi Absolution Assuming your data is in A1:B10 including the column headers. Then to retrieve the unique months, in say D2: =IFERROR(INDEX($B$2:$B$10,MATCH(0,INDEX(COUNTIF($D$1:D1,$B$2:$B$10),0,0),0)),"") And copy down. Then in E2 to retrieve the number of the unique values per month...
  13. K

    HOW TO SEPARATE TEXT AND NUMBERS ?

    @ bobhc Perhaps something like this, in A1: BLR5658DTDC6785DDSWL =SUM(MID(0&A1,LARGE(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)/1)*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A1)))-1)) CTRL + SHIFT + ENTER
  14. K

    HOW TO SEPARATE TEXT AND NUMBERS ?

    Hi Suri M Assuming your data is in A1:A2. In B1 and copy down this non array formula to extract the numbers. =LOOKUP(20^20,1*MID(A1,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1))))) Then if you require the text values, in C1 & copy down...
  15. K

    How to run Excel 2010 and 2013 on the SAME computer.

    Never saw that option! "From memory as you install 2013 it asks do you want to install a Replacement to an existing version or in Addition to the existing version."
  16. K

    Formula in Excel 2007

    Hi Ramesh_vm Can you not divide the Training Requirement (Hours) by the the number of months (For this case 1 year) =32/12
  17. K

    VLOOKUP formula doesn&#39;t return data in cells when it should

    Hi Andrew Tertes The values in F36:F81 are text, copy a blank cell, select F36:F81, Paste Special, Add. that will convert the values to numbers. F2 & F3 are not in the list! Kevin
  18. K

    Formula for changing date to custom d-mmm-yy

    Hi UNAB Nothing fancy required for that and no need for a formula! Select the data A7:A350 &#62; Data Tab &#62; Text to Columns &#62; Delimited &#62; Next &#62; Next &#62; Tick the Date: & select MDY &#62; Finish &#62; Format as required. Or if you really want a formula, in say B7 and copy...
  19. K

    Sumproduct - counting dates in a datetimelist

    HaHa typo, put the month in the incorrect position. At least you have the savvy to change it.
  20. K

    Sumproduct - counting dates in a datetimelist

    Hi mj2112 Try adding the month function to your range $S:$S =SUMPRODUCT(--(List!MONTH($S:$S)=MONTH($A$1)))
  21. K

    Postcode to postcode distance

    Hi thesilkster Try: http://www.excelforum.com/excel-tips/845249-uk-postcode-distance-calculator.html
  22. K

    Your favorite Excel feature...?

    @ NARAYANK991 The Evaluate Formula, would be better if you could enlarge the damn thing! I have hunted high and low to a solution and to no avail, not even through VBA.!
  23. K

    How to get weekdays and dates from number?

    Hi bhasoriya Try the OFFSET function. Select cells F4:F10. Enter the formula: =OFFSET(A1,MATCH(E4,C:C,0)-1,0,7,1) This is a array formula, to commit; CTRL + SHIFT + ENTER The in G4: =F4 copy down and format as "dddd". Kevin
  24. K

    Grading system

    Hi jigneshkumar.hingu One option is the LOOKUP function, assuming the score is in A1: =LOOKUP(A1,{0,70,80,90},{4,3,2,1}) You might want to look at the VLOOKUP function, where you will have a table in your sheet. Easy to maintain as well.
Back
Top