If 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 readingI didn’t know this before today when an user at MrExcel wondered why he was not getting consistent answers with Excel and VBA. The key is negative numbers. If you happen to have negative numbers (one of them) and you want to calculate MOD, you should use this code in VBA yourmodval=n-d*INT(n/d) Where n is […]
Continue readingIf you want to change references of a formula, you could toggle F4 key inside the formula to change the references from Absolute, Column Absolute Row Absolute or all relative. Although, if you have to change references from a range, you can use these two options1. Download ASAP utilities, because it’s worth it, they have […]
Continue reading