You might learn from some sources that when you enter a formula into a cell in Excel, you have to start your entry always with the equal sign. This is not true. You can, but DON'T NEED to start with the '=' sign.
Each formula can be started also with either '+', or '-' (if you mean negative number/expression) signs. When you press the ENTER key, Excel will voluntarily add the equal sign for you.
Here are couple of examples to illustrate what happens:
Elementary: what is the Excel cell?
It is much more than "rectangular-shaped box on a worksheet", where you can enter any combination of numbers or words...
First of all, it can be rectangularly- as well as squarely-shaped. Secondly, you can enter into a cell (within specified limits) not only any kind of text and any kind of graphics, but also pictures and drawings, and enter them in many layers, displayed or hidden.
Here is an example of content fitted in just ONE cell, in multiple displays.
Normally you use the VLOOKUP function to find things in a table or a range by row. E.g., look up a price of a mower part by the part number The function works fine for a specific table in a given worksheet.
But, let's say, you have a workbook with related several tables located in different worksheets. You might need to check all worksheets to find a match for your lookup value. In such a case you would need to use the VLOOKUP repeatedly in each of the worksheets. Much more efficient would be using this UDF (User Defined Function) I've created to make the task easier:
Function VLOOKUPinWBK(lookup_value As Variant, table_array As Range, _
col_index_num As Integer, Optional range_lookup As Boolean)
'This UDF looks in ALL worksheets in your workbook and finds the
' first match for lookup_value.
Dim wSh As Worksheet
Dim wks As String
Dim vLooked
On Error Resume Next
For Each wSh In ActiveWorkbook.Worksheets
With wSh
wks = wSh.Name
Set table_array = .Range(table_array.Address)
vLooked = WorksheetFunction.VLookup(lookup_value, table_array, _
col_index_num, range_lookup)
End With
If Not IsEmpty(vLooked) Then vLooked = vLooked: Exit For
Next wSh
Set table_array = Nothing
VLOOKUPinWBK = vLooked & " (found in '" & wks & "')"
End Function
Do you know that you can easily set the Column width in your worksheet to fit the contents of your specific cell in the column?
You can do that with Excel shortcut. Just select your target cell with some content, then press and hold ALT key, pressing consecutively O,C,A. You've just entered the shortcut:
Let's say you you know the desired result of some formula, but you need to find possible input value(s) to achieve that result. You may even know the approximate values of those parameters. However, it's probably nothing more than guessing, and you need exact values.
In such cases it's better to use Excel's Goal Seek tool which helps to arrive at exact solutions very efficiently.
I'm providing here an example of using the Goal Seek. The example shows how to setup your worksheet for solving a cubic equation, such as Ac*x^3+Bc*x^2+Cc*x+Dc=0, where I renamed the coefficients a,b,c and d to Ac,Bc,Cc and Dc.
In this case we want to find the roots for this specific equation:
2*x^3-15*x^2-195*x+990=0
In most cases - unless you need a time stamp which displays seconds - you can use Excel shortcuts for both date and time:
CTRL+SHIFT+: (colon) shortcut for your time stamp,
and CTRL+; (semicolon) shortcut for the date stamp.
These two shortcuts provide STATIC (non-refreshable) stamps; they are not updated whenever anything changes in your worksheet. The NOW() function is OK, but it inserts DYNAMIC (refreshable) stamp, so it requires conversion from function to a value format to become static.
|
This is about dating with the Excel DATE... function, and with its versatile formats and forms of uses. It's syntax is simple but the results of its application can be sometimes a bit confusing or unexpected.
Confusing - because the DATE function with its syntax DATE(year,month,day) doesn't object some strange parameters and accepts numbers such as:
So, e.g. entering accidentally month number 14 would set the date to February of the following year. Entering month number 0 would set the date to December of the previous year. Entering year number 1888 would set the date to the year 3788.
Normally, i.e. in General format, DATE function returns a serial number starting from January 01 in the year 1900, represented by number 1, which was Sunday, and increasing by 1 for every next calendar day. September 05, 2021 is the 44444 special day in this sequence, and it's Sunday as well.
If you need to deal with dates before the year 1900 you can access relevant information here:
MrExcel |
