Search results

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))

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

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

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

Use Text to Columns on the Data Tab

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))),"")

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

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

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")

Perhaps

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

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

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...

@ 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

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...

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."

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

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

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...

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

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

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

@ 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.!

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

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.