So You Think You Can Excel ®

Facebook Twitter Gplus LinkedIn
formats

Convert text to time in Excel ®

by in Formulas

In Excel®, time is just a number between 0 = midnight and 0.999999 = 11:59:59 pm.

We can use TIME Function to returns the decimal number for a particular time.

The decimal number returned by TIME is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).

TIME(hour, minute, second)

The TIME function syntax has the following arguments:

  • Hour  Required. A number from 0 (zero) to 32767 representing the hour. Any value greater than 23 will be divided by 24 and the remainder will be treated as the hour value. For example, TIME(27,0,0) = TIME(3,0,0) = .125 or 3:00 AM.
  • Minute  Required. A number from 0 to 32767 representing the minute. Any value greater than 59 will be converted to hours and minutes. For example, TIME(0,750,0) = TIME(12,30,0) = .520833 or 12:30 PM.
  • Second  Required. A number from 0 to 32767 representing the second. Any value greater than 59 will be converted to hours, minutes, and seconds. For example, TIME(0,0,2000) = TIME(0,33,22) = .023148 or 12:33:20 AM
 
formats

How to Remove all the Custom Styles in Excel by VBA?

by in Formatting, VBA

Sub RemoveStyle()
Dim st As Style
Dim x As Integer
x = 0
For Each st In ActiveWorkbook.Styles
If st.BuiltIn = False Then
st.Locked = False
st.Delete
x = x + 1
End If
Next
MsgBox (“Removed ” & x & ” styles”)
End Sub

 
Tags: ,
formats

Wait Excel calculations to finish

by in Formulas, VBA

The Excel object model provides the Application.CalculationInterruptKey property to control how if calculation can be interrupted.

Application.CalculationInterruptKey = xlAnyKey >>> The calcuation interrupt key is set to any key

Application.CalculationInterruptKey = xlEscKey >>> The calcuation interrupt key is set to ‘Escape’

Application.CalculationInterruptKey = xlNoKey >>> The calcuation interrupt key is set to no key (Must wait for calculations finish)

 
Tags: ,
formats

Text Formatting within a Cell

by in Formatting

From  chandoo.org  Posted on March 6th, 2012 in Excel Howtos , Huis , Posts by Hui

1. Apply any text effect to a single character or group of text characters within a cell

Select the cell

Enter Edit Mode by pressing F2

Move to the characters you want to edit

Hint 1: Use Ctrl and the Left/Right Arrows to jump words left and right

Hint 2: Use Ctrl Home/End to Jump to the Start /End of the cells text

Select the text with Shift and Left/Right Arrows

Hint 3: Use Ctrl Shift and the Left/Right Arrows to Jump over and select words left and right

Apply the format, see below for a list of available formats

You can then move to other characters and edit them as appropriate.

2. Apply any text effect to text as you enter it

As you are typing some text try the following

Enter some text, as you are typing apply Bold (Ctrl B), Italic (Ctrl I), Underline (Ctrl U) to toggle the effects on,

Hint 4: Use the keyboard shortcuts,  Bold (Ctrl B), Italic (Ctrl I), Underline (Ctrl U) again to toggle the effect on/off as required

You can also apply color /font by using the appropriate menu drop down or Ctrl 1, Format Cells

Once again apply a format continue to type the text, apply/change formats as you go

 

What Formats are available

There are many Text formats that are available:

Bold > Ctrl B

Underline > Ctrl U

Double Underline > Ctrl + 1

Italic > Ctrl I or Ctrl + 1

Font Color > Color Picker or Ctrl + 1

Font Size > Font Size Picker or Ctrl + 1

Super Script > Ctrl + 1

Sub Script > Ctrl + 1

Strikethrough > Ctrl + 1

Alt Enter > Add a second Line of text

Uses

There are many times where the use of in-cell text formatting is required

  • Highlighting Individual Characters or Words
  • Adding Footnote and other references
  • Writing Chemical Formulas
  • Writing Mathematical Formulas

Limitations

There are a few limitations to what and when in-cell formatting can be applied

  • The limitations of these techniques is that it cannot be applied to characters of a cell where the cell is a formula
  • The formats can’t be applied selectively by Conditional Formatting
  • The Cells background color applies to the whole cell and cannot be changed for part of the cell or on a character by Character basis
  • The Copy Cell Format tool does not copy in-cell text formats
 
Tags:
formats

Display the sheet name in a cell by Excel Functions and VBA

by in Formulas, VBA

VBA Function:

Function ShName(N As Range)
ShName = N.parent.name
End Function

Excel Functions:

=MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,31)

=MID(CELL(“filename”,A1),SEARCH(“]”,CELL(“filename”,A1))+1,31)

Please note that you have to save your workbook first, otherwise it will return #VALUE error.

The maximum length of a tab name is 31.

Functions used above explained from Office Online Help:

CELL(info_type, [reference])

The CELL function syntax has the following arguments:

  • info_type  Required. A text value that specifies what type of cell information you want to return. The following list shows the possible values of the info_typeargument and the corresponding results.
    info_type Returns
    “address” Reference of the first cell in reference, as text.
    “col” Column number of the cell in reference.
    “color” The value 1 if the cell is formatted in color for negative values; otherwise returns 0 (zero).
    “contents” Value of the upper-left cell in reference; not a formula.
    “filename” Filename (including full path) of the file that contains reference, as text. Returns empty text (“”) if the worksheet that contains reference has not yet been saved.
    “format” Text value corresponding to the number format of the cell. The text values for the various formats are shown in the following table. Returns “-” at the end of the text value if the cell is formatted in color for negative values. Returns “()” at the end of the text value if the cell is formatted with parentheses for positive or all values.
    “parentheses” The value 1 if the cell is formatted with parentheses for positive or all values; otherwise returns 0.
    “prefix” Text value corresponding to the “label prefix” of the cell. Returns single quotation mark (‘) if the cell contains left-aligned text, double quotation mark (“) if the cell contains right-aligned text, caret (^) if the cell contains centered text, backslash (\) if the cell contains fill-aligned text, and empty text (“”) if the cell contains anything else.
    “protect” The value 0 if the cell is not locked; otherwise returns 1 if the cell is locked.
    “row” Row number of the cell in reference.
    “type” Text value corresponding to the type of data in the cell. Returns “b” for blank if the cell is empty, “l” for label if the cell contains a text constant, and “v” for value if the cell contains anything else.
    “width” Column width of the cell, rounded off to an integer. Each unit of column width is equal to the width of one character in the default font size.
  • reference  Optional. The cell that you want information about. If omitted, the information specified in the info_type argument is returned for the last cell that was changed. If the reference argument is a range of cells, the CELL function returns the information for only the upper left cell of the range.

CELL format codes

The following list describes the text values that the CELL function returns when the info_type argument is “format” and the reference argument is a cell that is formatted with a built-in number format.

If the Excel format is The CELL function returns
General “G”
0 “F0″
#,##0 “,0″
0.00 “F2″
#,##0.00 “,2″
$#,##0_);($#,##0) “C0″
$#,##0_);[Red]($#,##0) “C0-”
$#,##0.00_);($#,##0.00) “C2″
$#,##0.00_);[Red]($#,##0.00) “C2-”
0% “P0″
0.00% “P2″
0.00E+00 “S2″
# ?/? or # ??/?? “G”
m/d/yy or m/d/yy h:mm or mm/dd/yy “D4″
d-mmm-yy or dd-mmm-yy “D1″
d-mmm or dd-mmm “D2″
mmm-yy “D3″
mm/dd “D5″
h:mm AM/PM “D7″
h:mm:ss AM/PM “D6″
h:mm “D9″
h:mm:ss “D8″

 Note    If the info_type argument in the CELL function is “format” and you later apply a different format to the referenced cell, you must recalculate the worksheet to update the results of the CELL function.

MID(text, start_num, num_chars)

MIDB(text, start_num, num_bytes)

The MID and MIDB function syntax has the following arguments:

  • Text  Required. The text string containing the characters you want to extract.
  • Start_num  Required. The position of the first character you want to extract in text. The first character in text has start_num 1, and so on.
  • Num_chars  Required. Specifies the number of characters you want MID to return from text.
  • Num_bytes  Required. Specifies the number of characters you want MIDB to return from text, in bytes.

Remarks

  • If start_num is greater than the length of text, MID returns “” (empty text).
  • If start_num is less than the length of text, but start_num plus num_chars exceeds the length of text, MID returns the characters up to the end of text.
  • If start_num is less than 1, MID returns the #VALUE! error value.
  • If num_chars is negative, MID returns the #VALUE! error value.
  • If num_bytes is negative, MIDB returns the #VALUE! error value.

FIND(find_text, within_text, [start_num])

FINDB(find_text, within_text, [start_num])

FIND is intended for use with languages that use the single-byte character set (SBCS), whereas FINDB is intended for use with languages that use the double-byte character set (DBCS). The default language setting on your computer affects the return value in the following way:

  • FIND always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.
  • FINDB counts each double-byte character as 2 when you have enabled the editing of a language that supports DBCS and then set it as the default language. Otherwise, FINDB counts each character as 1.

The languages that support DBCS include Japanese, Chinese (Simplified), Chinese (Traditional), and Korean.

The FIND and FINDB function syntax has the following arguments:

  • Find_text  Required. The text you want to find.
  • Within_text  Required. The text containing the text you want to find.
  • Start_num  Optional. Specifies the character at which to start the search. The first character in within_text is character number 1. If you omit start_num, it is assumed to be 1.

Remarks

  • FIND and FINDB are case sensitive and don’t allow wildcard characters. If you don’t want to do a case sensitive search or use wildcard characters, you can use SEARCH and SEARCHB.
  • If find_text is “” (empty text), FIND matches the first character in the search string (that is, the character numbered start_num or 1).
  • Find_text cannot contain any wildcard characters.
  • If find_text does not appear in within_text, FIND and FINDB return the #VALUE! error value.
  • If start_num is not greater than zero, FIND and FINDB return the #VALUE! error value.
  • If start_num is greater than the length of within_text, FIND and FINDB return the #VALUE! error value.
  • Use start_num to skip a specified number of characters. Using FIND as an example, suppose you are working with the text string “AYF0093.YoungMensApparel”. To find the number of the first “Y” in the descriptive part of the text string, set start_num equal to 8 so that the serial-number portion of the text is not searched. FIND begins with character 8, finds find_text at the next character, and returns the number 9. FIND always returns the number of characters from the start of within_text, counting the characters you skip if start_num is greater than 1.

SEARCH(find_text,within_text,[start_num])

SEARCHB(find_text,within_text,[start_num])

The SEARCH and SEARCHB functions locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string. For example, to find the position of the letter “n” in the word “printer”, you can use the following function:

=SEARCH(“n”,”printer”)

This function returns 4 because “n” is the fourth character in the word “printer.”

You can also search for words within other words. For example, the function

=SEARCH(“base”,”database”)

The SEARCH and SEARCHB functions have the following arguments:

  • find_text  Required. The text that you want to find.
  • within_text  Required. The text in which you want to search for the value of the find_text argument.
  • start_num  Optional. The character number in the within_text argument at which you want to start searching.

Remark

  • The SEARCH and SEARCHB functions are not case sensitive. If you want to do a case sensitive search, you can use FIND and FINDB.
  • You can use the wildcard characters — the question mark (?) and asterisk (*) — in the find_text argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
  • If the value of find_text is not found, the #VALUE! error value is returned.
  • If the start_num argument is omitted, it is assumed to be 1.
  • If start_num is not greater than 0 (zero) or is greater than the length of the within_text argument, the #VALUE! error value is returned.
  • Use start_num to skip a specified number of characters. Using the SEARCH function as an example, suppose you are working with the text string “AYF0093.YoungMensApparel”. To find the position of the first “Y” in the descriptive part of the text string, set start_num equal to 8 so that the serial number portion of the text (in this case, “AYF0093″) is not searched. The SEARCH function starts the search operation at the eighth character position, finds the character that is specified in the find_text argument at the next position, and returns the number 9. The SEARCH function always returns the number of characters from the start of the within_text argument, counting the characters you skip if the start_num argument is greater than 1.
 
Tags: ,
formats

MROUND function: Rounding to nearest multiple of 5 solved!

by in Formulas

=MROUND(A1,5)

or

=ROUND(A1/5,0)*5

In fact, MROUND  can return a number rounded to the desired multiple!

MROUND(number, multiple)

The MROUND function syntax has the following arguments:

  • Number Required. The value to round.
  • Multiple Required. The multiple to which you want to round number.

Remark

MROUND rounds up, away from zero, if the remainder of dividing number by multiple is greater than or equal to half the value of multiple.

Table from Offce Online Help:

Formula Description (Result)
=MROUND(10, 3) Rounds 10 to a nearest multiple of 3 (9)
=MROUND(-10, -3) Rounds -10 to a nearest multiple of -3 (-9)
=MROUND(1.3, 0.2) Rounds 1.3 to a nearest multiple of 0.2 (1.4)
=MROUND(5, -2) Returns an error, because -2 and 5 have different signs (#NUM!)
 
Tags:
formats

CalculateFullRebuild, CalculateFull & Calculate Methods in Excel

by in VBA

1. CalculateFullRebuild:

For all open workbooks, forces a full calculation of the data and rebuilds the dependencies.

Dependencies are the formulas that depend on other cells. For example, the formula “=A1″ depends on cell A1. The CalculateFullRebuild method is similar to re-entering all formulas.

Application.CalculateFullRebuild

2. CalculateFull:

Forces a full calculation of the data in all open workbooks.

Application.CalculateFull

3. Calculate:

Calculates all open workbooks, a specific worksheet in a workbook, or a specified range of cells on a worksheet, as shown in the following table.

To calculate Follow this example
All open workbooks Application.Calculate (or just Calculate)
A specific worksheet Worksheets(1).Calculate
A specified range Worksheets(1).Rows(2).Calculate
 
Tags:
formats

WMI service error in Excel (80041002)

by in System

1. Rename the C:\WINDOWS\system32\wbem\Repository to Repository.old

2. Restart the Windows

This will force WMI to rebuild as well as make the SMS client reinstall :)

 
Tags:
formats

IS Functions Summary

by in Formulas

This section describes the nine worksheet functions used for testing the type of a value or reference.

Each of these functions, referred to collectively as the IS functions, checks the type of value and returns TRUE or FALSE depending on the outcome. For example, the ISBLANK function returns the logical value TRUE if value is a reference to an empty cell; otherwise it returns FALSE.

Syntax

ISBLANK(value)
ISERR(value)
ISERROR(value)
ISLOGICAL(value)
ISNA(value)
ISNONTEXT(value)
ISNUMBER(value)
ISREF(value)
ISTEXT(value)

Value is the value you want tested. Value can be a blank (empty cell), error, logical, text, number, or reference value, or a name referring to any of these, that you want to test.

FUNCTION RETURNS TRUE IF
ISBLANK Value refers to an empty cell.
ISERR Value refers to any error value except #N/A.
ISERROR Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).
ISLOGICAL Value refers to a logical value.
ISNA Value refers to the #N/A (value not available) error value.
ISNONTEXT Value refers to any item that is not text. (Note that this function returns TRUE if value refers to a blank cell.)
ISNUMBER Value refers to a number.
ISREF Value refers to a reference.
ISTEXT Value refers to text.

 

Remarks

  • The value arguments of the IS functions are not converted. For example, in most other functions where a number is required, the text value “19″ is converted to the number 19. However, in the formula ISNUMBER(“19″), “19″ is not converted from a text value, and the ISNUMBER function returns FALSE.
  • The IS functions are useful in formulas for testing the outcome of a calculation. When combined with the IF function, they provide a method for locating errors in formulas (see the following examples).

ISEVEN

Returns TRUE if number is even, or FALSE if number is odd.

Syntax

ISEVEN(number)

Number is the value to test. If number is not an integer, it is truncated.

Remark

If number is nonnumeric, ISEVEN returns the #VALUE! error value.

Note: If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.

ISODD

Returns TRUE if number is odd, or FALSE if number is even.

Syntax

ISEVEN(number)

Number is the value to test. If number is not an integer, it is truncated.

Remark

If number is nonnumeric, ISEVEN returns the #VALUE! error value.

Note: If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.

 
Tags:
formats

VBA to hide/unhide Excel 2010 ribbon, ribbon tabs, quick access toolbar, formula bar and workbook tabs

by in VBA

Sub HideRibbon()
Application.DisplayFormulaBar = False
ActiveWindow.DisplayWorkbookTabs = False
Application.ExecuteExcel4Macro “SHOW.TOOLBAR(“”Ribbon”",False)”
End Sub

Sub ShowRibbon()
Application.DisplayFormulaBar = True
ActiveWindow.DisplayWorkbookTabs = True
Application.ExecuteExcel4Macro “SHOW.TOOLBAR(“”Ribbon”",true)”
End Sub

 
Tags:
formats

Switch/Change Userform from Modal to Modeless at run time

by in VBA

Code in standard module for loading of UserForm1:

Sub Start()
UserForm1.Show 0 ‘vbModeless = 0
End Sub

Code in UserForm1 with two command buttons:

‘ ZVI:2009-08-09 http://www.mrexcel.com/forum/showthread.php?t=408356
‘ Switching form to Modal / Modeless at runtime
‘ Code in userform module
Private Declare Function EnableWindow Lib “user32.dll” _
(ByVal hWnd As Long, ByVal fEnable As Long) As Long
Const Modal = 0, Modeless = 1

Private Sub CommandButton1_Click()
‘ Modeless
EnableWindow Application.hWnd, Modeless
End Sub

Private Sub CommandButton2_Click()
‘ Modal
EnableWindow Application.hWnd, Modal
End Sub

Private Sub UserForm_Activate()
‘ Activate as Modal,EnableWindow API doesn’t solve the problem
‘ if the userform is initially loaded modal.
‘ But you can initially load userform as modeless and
‘ in activation event code set it to the modal.
‘ In this case you can use ShowWindow API to switch modal / modeless
‘ without keyboard blocking and other limitations.
EnableWindow Application.hWnd, Modal
End Sub

There is another way (not as good as above since it changes form’s position and incurs blinking):

Private Sub CommandButton1_Click()
‘ Modeless
Me.Hide
Me.Show 0
End Sub

Private Sub CommandButton2_Click()
‘ Modal
Me.Hide
Me.Show 1
End Sub

 
Tags:
formats

Excel/VBA Golden Rules – These Should NOT Be Optional

by in General

Cell Master Dave Hawley (founder of ozgrid.com)  published the following article years ago:

  1. Never Use Manual Calculation Mode, It’s a False Reading Waiting to Happen! If You are Forced to use Manual Calculation, you HAVE a Bad Spreadsheet Design Which Should be Fixed, not Catered to.
  2. 1 Worksheet For ALL Related RAW Data.
  3. Classic Table Format For Related Data. That is, Row 1 For Headings and Corresponding Data Underneath.
  4. Don’t Mix Raw Data With Final Results, Reports or Data.
  5. No Blank Cells in a Table.
  6. No Merged Cells (Use Center Across Selection Instead).
  7. Avoid Empty Text (“”) For Formula Results, Use Zero Instead. Tools>Options – View – Zero Values to Hide Globally. Or, Custom Format Like: 0;-0; To Hide Cell-By-Cell.
  8. Avoid Array Formulae and Multiple Criteria SUMPRODUCT. Make Use Of PivotTables and/or Database Functions.
  9. Don’t Nest 2 Lookups To Avoid #N/A! Allow it to Occur and Reference Like: =IF(ISNA(A1),0,A1) and Hide the Lookup Column
  10. Avoid Deleting Rows and Make Use of Auto Filter (AutoFilter), AdvancedFilter or Sort.
  11. Avoid Volatile Formulae (especially for current date and/or time. NOW, TODAY etc). E.g If Current Date is Needed in Multiple Formulae, Add =TODAY() Into a Cell You Have NamedToday. Then, in Your Formulae use Today in Place of TODAY().
  12. Avoid Formatting Cells as Text. Very Rarely is a Text Format Needed.
  13. Never Store Numbers as Text.
  14. Avoid Changing the Default Horizontal Alignment of Cells. Numbers, by Default are Right Aligned, While Text, by Default, is Left Aligned.
  15. Always use True Dates & Times That Excel Will Recognize as Such. Even Headings for Things Like Month & Day Names. Use True Dates and Custom Format as MMMM or DDDD.

For VBA:

  1. Turn on Option Explicit. Tools>Options – Editor – Require variable declaration in the VBE. Then Educate Yourself on Their Proper Use and Data Types.
  2. Split Procedures Into Logical Seperate Procedures and use Call or Run When Needed.
  3. Make Good use Of Functions so They Can be Called When Needed and With Variable Elements Passed.
  4. Try & Keep all Related Procedures in the Same Module. Excel Compiles Each Module as a Procedure in the Module in Run.
  5. You Rarely Need to Select or Activate any Objects to Change Their Properties or Access Their Methods. If you MUST Select a Range Object, Use GoTo.
  6. Use a Sheet CodeName Over The Tab Name or Index Number.
  7. Avoid Loops. Make Good use of Much Faster Alternatives Like Find (Find Method), AutoFilter, AdvancedFilter, SpecialCells etc.
  8. Loops Through Object Collections are the Fastest, Compared to Other Loop Types.
  9. Don’t Assume Code is Needed. Often a Worksheet Function is FAR better and More Efficient.
  10. Avoid Using Custom Functions When a Built-in Worksheet Function can be Used. Even Deeply Nested Worksheet Function are Often a Lot More Effiecient Than Custom Function Written in VBA.
  11. Avoid Macros That Delete Rows/Column/Cells. Make Use of AutoFilter, AdvancedFilter or Sort.
  12. Turn Off Calculations via Code for Slow Macros. See Macro Code via Manual Calculation. Assume Your Macro Will Fail, so Turn Back on In any Error Trapping.
  13. Use VbNullString Over “”
  14. Turn off Sheet/Workbook Events if They are Not Needed While Macro is Running. That is, Application.EnableEvents = False and Don’t Forget to Turn Back on and Assume Your Macro Will Fail, so Turn Back on In any Error Trapping.
  15. Make Good use of With Statements When Working With Objects.
  16. Select Case is Often Better Than Mutiple If Else Statements.
  17. IIf is Slower Than If Else.
  18. Use Boolean Logic Over If Statements. E.g bYesNo = Range(“MyValue”) = 5.
  19. Use Named Ranges Over Cell Addresses.
  20. Use Meaningful Variable Names and Precede Them With Their Data Type. E.g lngRowCount NOT Meaningless Names Like x, y, z etc.
  21. Capitalise at Least 1 Character in Variable Names and When Using in a Procedure use ALL Lower Case. Excel Will Convert as To the Case Used When Dimensioned.
 
Tags:
formats

Errors! – #NULL!, #DIV/0!, #VALUE!,#REF!,#NAME?, #NUM! and #N/A

by in Formulas

ERROR.TYPE(error_val)

IF ERROR_VAL IS ERROR.TYPE RETURNS
#NULL! 1
#DIV/0! 2
#VALUE! 3
#REF! 4
#NAME? 5
#NUM! 6
#N/A 7
Anything else #N/A

Below is a very good summary from http://www.ozgrid.com/Excel/formula-errors.htm:

As soon as you have discovered how to use formulas in Excel, you will likely need to lean how to correct or hide formula errors. The first thing you should know is what each error type means. Once you understand what each error value means, correcting the formula becomes a LOT easier. Also note that a Formula can return an error IF a range it references contains an error cell.

To mask errors and return an alternate value in its place, it is best to return zero rather than empty text (“”). This is because zeros are generally more downstream formula friendly than text.

Hide Zeros

To hide zeros on the Workbook level go to Tools>Options>View – Zero Values.

Custom Formats

Excel sees a cells format as having four Sections. These are, from left to right:

Positives;Negatives;Zeros;Text.

To hide zeros cell-by-cell use a Custom Number Format like 0.00;-0.00; where 0.00 is desired the format for non zeros. Note the use of -0.00 for negatives.

Error.Type Function

For specifying error types. #NULL! = 1 #DIV/0! = 2 #VALUE! = 3 #REF! = 4 #NAME? = 5 #NUM! = 6 #N/A = 7

#NULL!

Often occurs when you specify a intersecting range which in fact does NOT intersect. The space is the Intersect Operator and should be used correctly like;

=A1:F1 B1:B10
OR with named ranges
=Range1 Range2
In both cases Excel will return the cell value that intersects A1:F1 and B1:B10. In this case, B2.

However, if we used =A1:F1 B2:B10 Excel would display the #NULL! error as it is NOT possible for a row 1 range to intersect a column range that starts at row 2.

#DIV/0!

Simply means you cannot divide zero into a number. For example

=A1/A2

would result #DIV/0! IF A2 contains nothing or zero. To correct this one could use one of 2 methods.

=IF(A2=0,0,=A1/A2)

OR

=IF(ERROR.TYPE(A1/A2)=2,0,A1/A2)

Note the use of the ERROR.TYPE Function. It is important to identify the error type so you are NOT masking another error type you SHOULD know about.

That is, we could use;
=IF(ISERROR(A1/A2),0,A1/A2)

OR

=IF(ISERR(A1/A2),0,A1/A2)

BUT, it is NOT good practice as you will end up masking most error values when you SHOULD be masking only the #DIV/0! error.

#VALUE!

Possibly the most frequent error type. Occurs when the wrong type of argument or operand (operand: Items on either side of an operator in a formula. In Excel, operands can be values, cell references, names, labels, and functions.) is used. For example, you may have;

=A1*A2
and IF either cell had text and NOT numbers, the #VALUE! error would be displayed. This is why one should NOT change the default horizontal alignment of data cells. That is, text is always left aligned while numbers are right aligned by default. If you allow this and then widen a Column, you can tell at a glance what Excel is seeing as text and numbers.

#REF!

This means a non-valid reference in your formula. Often occurs as the result of deleting rows, columns, cells or Worksheets. This is why deleting rows, columns, cells or Worksheets is bad practice. Also check named ranges if used.

You DO NOT want to mask this error as you SHOULD be aware of it.

#NAME?

This error means a Function used is not being recognised by Excel. Check for typos and always type Excel Functions in lower case. This way, when you enter the formula Excel will automatically convert it to upper case, if it is recognised.

Another common reason is if you are using a custom function without the code being present in he same Workbook. Or, you are using a function that requires a specific Excel add-in being installed. E.g the Analysis Toolpak

On the Tools menu, click Add-Ins.  In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.

If necessary, follow the instructions in the setup program.

As with the #REF! error, you don’t want to mask this error.

#NUM!

This error occurs if you supply a non valid number to a function argument. E.g, using a negative number when a positive is needed. Or, using a $, % symbol with the number.

This error can be masked so long as you are aware of the reason why.

#N/A

The most common reason for this error is any of the Lookup functions. It means Excel cannot find a match for the value it’s being told to find. There are many ways to correct or mask this error out there, BUT most are wrong in their approach and force a LOT of unneeded over-heads.

Consider placing the Lookup functions on the same Worksheet as the Table (if not already), then create a simply reference (e.g. =IV1) to the cell(s) to get the result into the needed Worksheet. Doing this also opens up another opportunity in that we could now use;

IF(ISNA(IV1),”",IV1)

 
Tags:
formats

Disabling/Enabling Excel & COM Add-ins via VBA

by in VBA

Disabling/Enabling Excel Add-ins:

AddIns(“Exact name of the Excel Add-ins”).Installed = False/True

Disabling/Enabling COM Add-ins:

Dim oCOMAddin As COMAddIn
For Each oCOMAddin In Application.COMAddIns
If oCOMAddin.progID = “Exact progID (not the name) of the COM Add-ins” Then
If oCOMAddin.Connect = True/False Then
oCOMAddin.Connect = False/True
End if
End if
Next oCOMAddin

 
Tags:
formats

Date Formatting for PivotChart Axis Label

by in Charts

From Excel 2007, you cannot DIRECTLY format the Date on a PivotChart.

The correct way is to format the underling PivotTable.

Right click the date field you want to format > click Field Settings… > Number Format (bottom left)

 
Tags: