Excel Function

Statistical Math Date&Time Lookup Information Logical Text data Data1 TRIM Dog Dog With space Without Space After trim B4 VS C4 C4 VS D4 CLEAN This is the test Before After LEN This is the test LEN(B12) Sample REPT Title Quatity Etc Description LEFT(B26,5) LEFT RIGHT RIGHT(B32,5) MID MID(B36,3,4) =B16&REPT(".",20-LEN(B16)) =REPT("g",B23) description =UPPER(B44) =UPPER(LEFT(B44,1))&RIGHT(B44,LEN(B44)-1) FIND/SEARCH Expression =FIND("e",B51,1) =SEARCH("e",B51,1) Jeeradate Kaowkanchana =LEFT(B55,FIND(" ",B55,1)) =RIGHT(B55,LEN(B55)-FIND(" ",C55)) REPLACE =REPLACE(B60,FIND("2006",B60,1),4,"2008") Happy New year 2006 at Beer Factory SUBSTITUTE Happy New year from 2006 to 2006 at Beer Factory =SUBSTITUTE(B65,"2006","2008",2) This is the test. =SUBSTITUTE(B67,CHAR(10),"") UPPER/LOWER/PROPER this is the test. =PROPER(B48) AND =AND(B3,C3) OR =OR(B9,C9) NOT =NOT(B15) IF Mine Your =IF(B20>C20,"OK", "Not OK") =B22/C22 =IF(C22=0,"",B22/C22) =IF(B25>=1000,IF(B25>=10000,"Realy Big!","Big"),"Not Big") CELL Cell #1 Cell #2 Cell #3 a cell Function =CELL("address",C4) =CELL("col",C4) =CELL("contents",C4) =CELL("format",C4) =CELL("prefix",C4) =CELL("row",C4) =CELL("type",C4) =CELL("width",C4) ERROR.TYPE() =ERROR.TYPE(B17) Error_val Value ERROR.TYPE() Returns #NA #N/A! All others INFO =INFO("Directory") =INFO("Memavail") =INFO("memused") =INFO("numfile") =INFO("origin") =INFO("osversion") =INFO("recalc") =INFO("release") =INFO("system") =INFO("totmem") IS =ISBLANK(B47) =ISERR(B48) =ISLOGICAL(B49) =ISNONTEXT(B51) =ISNUMBER(B52) =ISTEXT(B53) CHOOSE Data Surface Mail Air Mail Courier =CHOOSE(B4,C4,D4,E4) =CHOOSE(C7,"Sun","Mon","Tue","Wed","Thu","Fri","Sat") =NOW() =WEEKDAY(B7) VLOOKUP Monday Tuesday Wednesday Thursday Friday Saturday Sunday =VLOOKUP(F12,$B$12:$C$18,2,FALSE) =HLOOKUP(C32,$C$26:$I$27,2,FALSE) HLOOKUP Serial Number Entering Date & Time m/d/yyyy d-mmm-yy d-mmm mmm-yy h:mm:ss h:mm:ss AM m/d/y h:mm DATE(year,month,day) =DATE(2008,1,30) TODAY() =TODAY() =DATE(A24,A23,A22) =DATEVALUE("August 23, 2008") DateValue YEAR =YEAR(B32) MONTH =MONTH(B32) DAY =DAY(B32) WEEKDAY =WEEKDAY(B28) Example =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()) + 25) Next 25 Days Last Day of month =DATE(YEAR(TODAY()),2+1,0) =B59-B58 Number of days between 2 dates HOUR =HOUR(B66) SECOND MINUTE =MINUTE(B66) =SECOND(B66) TIME =TIME(10,B72,B76) TIMEVALUE =TIMEVALUE("10:00:00") Current time =NOW()-TODAY() =NOW()+6/24 Next 6 Hours Next 6 Minutes =NOW()+6/24/60 NOW ROUND =ROUND(B4,2) ROUNDDOWN =ROUNDDOWN(B4,2) ROUNDUP =ROUNDUP(B4,2) =ROUNDUP(B12,2) INT =INT(B17) TRUNC =TRUNC(B22,-2) =TRUNC(B22,0) =TRUNC(B22,2) SUM =SUM(B28:B30) =SUM((B33:B36>0)*B33:B36) =SUM((B33:B36<0)*B33:B36) =SUM(IF(B33:B36>0,B33:B36,0)) =SUM(IF(B33:B36<0,B33:B36,0)) MOD =MOD(B43,B44) Leap Year calculation =(MOD(B46,4)=0)-(MOD(B46,100)=0)+(MOD(B46,400) = 0)+28 RAND =RAND() =RAND()*30 =INT(RAND()*6)+1 AVERAGE =AVERAGE(Data1) MEDIAN =MEDIAN(Data1) MODE =MODE(Data1) MAX MIN =MAX(Data1) =MIN(Data1) Mean Standard Diviation Cumulative sigma =STDEV(Data1) Lower Upper n*STD =COUNT(Data1) COUNT Desciption test In this case, year is a four-digit year number. This formula works for the years 1901 to 2099, which should take care of most people’s needs. The formula doesn’t work for 1900 and 2100 because, despite being divisible by 4, these years aren’t leap years. The general rule is that a year is a leap year if it’s divisible by 4 and it’s not divisible by 100, unless it’s also divisible by 400. Therefore, because 1900 and 2100 are divisible by 100 and not by 400, they aren’t leap years. The year 2000, however, is a leap year. If you want a formula that takes the full rule into account, use the following formula: =(MOD(year, 4) = 0) - (MOD(year, 100) = 0) + (MOD(year, 400) = 0) INDIRECT D32 =INDIRECT(C38) 0.00 0.00 0.00 0.00 0.00 00.00 00.00 00.00 00.00 00.00 00.00 00.00 00.00 00.00 00.00 00.00 00.00 00.00 00.00 00.00 0.00 00.00 0.00 00.00 00.00 0.00 0.00 0.00 0.00 0.00 00.00 0.00 0.00 00.00 -0.00 00.00 0.00 0.00 00.00 -00.00 00.00 000.00 000.00 0.00 000.00 0.00 000.00 000.00 0.00 000.00 0.00 000.00 000.00 000.00 000.00 -0.00 000.00 0.00 000.00 0.00 0.00 0.00 0.00 0.00 0.00 -0.00 0.00 -0.00 0.00 0.00 -0.00 0.00 0.00 0.00 0.00 -0.00 0.00 0.00 00.00 00.00 0.00 0000.00 00.00 0.00 0.00 000.00 0.00 000.00 0.00 00.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0000.00 0.00 0.00 000.00 12/31/1900 000.00 12/31/1900 5:31:12 AM 000.00 12/31/1900 12:14:24 AM 00.00 1/30/2008 0000.00 0.00 00.00 0.00 1/30/2008 0000.00 1/30/2008 00000.00 8/23/2008 August 23, 2008 0000.00 0.00 00.00 0.00 2/24/2008 00.00 2/29/2008 0.00 0.00 0.00 1/30/2008 3/26/2008 00.00 1/30/2008 1:17:10 PM 00.00 00.00 00.00 10:17:10 AM 00.00 0.00 10:00:00 0.00 1:17:10 PM 1/30/2008 7:17:10 PM 0.00 00.00 1/30/2008 1:23:10 PM 0.00 00.00 00.00 0.00 Surface Mail 1/30/2008 1:17:10 PM 0.00 Sun Mon Tue Wed Thu Fri Sat Wed 0.00 0.00 0.00 Tuesday 0.00 0.00 Wednesday 0.00 0.00 Saturday 0.00 0.00 Wednesday 0.00 0.00 Tuesday 0.00 0.00 0.00 0.00 Friday 0.00 Saturday 0.00 Tuesday 0.00 Wednesday 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 00.00 0.00 0.00 0.00 0.00 0.00 0.00 Tuesday Thursday Saturday Wednesday Tuesday Tuesday 00.00 00.00 00000.00 address $C$4 address $D$4 address $E$4 0.00 col 0.00 col 0.00 col 00.00 contents contents a cell 00000.00 contents format G format G format ,2 prefix prefix ^ prefix 0.00 row 0.00 row 0.00 row type v type l type v 00.00 width 00.00 width 00.00 width 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Directory D:\takaj\Personal Data\ 0000000.00 Memavail 000000.00 memused 00.00 numfile origin $A:$A$1 osversion Windows (32-bit) NT 5.01 recalc Automatic release 10.0 system pcdos 0000000.00 totmem 0.00 0.00 000.00 -0.00 0.00E-00 0.00 0000.00 000.00 OK Not OK OK 0.00 0.00 0.00 0.00 0.00 000.00 0000.00 00000.00 Realy Big! Big Not Big Not Big Dog This is the test 00.00 . 00.00 Title............... . 00.00 Desciption.......... . 00.00 Quatity............. . 00.00 Etc................. 00.00 g gggggggggggg 00.00 g gggggggggggggg 00.00 g gggggggggggggggg 0.00 Descr 0.00 ption 0.00 0.00 scri DESCRIPTION 0.00 0.00 Description This Is The Test. 0.00 e 0.00 0.00 e 0.00 0.00 Jeeradate Kaowkanchana 2006 0.00 0.00 2008 Happy New year 2008 at Beer Factory 2006 2008 0.00 Happy New year from 2006 to 2008 at Beer Factory 00.00 This is the test.