You cannot change the default Row (1,2,3,…) and Column (A,B,C,D….) headers, but you can give your spreadsheet the look and feel as if you had custom headings. Solution Enter what you’d like to be your Row Headers in the first column leaving cell A1 blank Enter what you’d like to be your Column Headers in […]
Continue readingIn this short video you can learn how to assign a macro to a button on your toolbar. If this macro is in the personal workbook then you can use it on any workbook.
Continue readingIf you want to count cells in a given range without formulae use this function Function CountNoFormula(rng As Range) As LongDim c As Range On Error GoTo CountNoFormula_Error For Each c In rng If Not c.HasFormula Then CountNoFormula = CountNoFormula + 1 End If Next cCountNoFormula_CleanUpExit: Exit FunctionCountNoFormula_Error: CountNoFormula = CVErr(Err.Number) Resume CountNoFormula_CleanUpExitEnd Function If you want to count cells with formule […]
Continue readingTo calculate the average of n biggest numbers from a list enter this formula with Ctrl + Shift + Enter: =AVERAGE(IF(RANK(yourrange,yourrange)<=n,yourrange,””)) To calculate the average of n smallest numbers from a list enter this formula with Ctrl + Shift + Enter: =AVERAGE(IF(RANK(yourrange,yourrange)>COUNT(yourrange)-n,yourrange,””)) For an example:
Continue readingThis is one of the finest forumlae I’ve ever seen. Check this post at MrExcel. If you want to have a value specific number of digits otherwise add zeros in front of it, this formula could be used: =TEXT(A1,REPT(“0”,n)) where n is the number of digits you want. So 4555 becomes 000004555 with formula =TEXT(4555,REPT(“0”,9))
Continue reading