listi.net

Formulas vs. Values vs. Format and Dates in Excel

One of the most important and powerful features of Excel are Formulas. A formula allows you to change the value in one cell and affect the calculation of one or more cells.

Date calculations are an important feature of Excel. It will be briefly described here.

Also, to reduce some confusion we'll discuss the difference between formulas, values, and formats.

If you used Excel before, values are the most familiar. you type into a cell and a value appears. What you're seeing is actually the formatted value. This is the value that's supposed to be visually appealing.

Formulas 101

Enter the following into a cell:

=1+1

and press Enter.

The value 2 will be displayed.

2

You've created your first formula.

The = (equal sign)1 is telling Excel that a formula follows. Note that the + key also works and Excel will change the + to an =

Enter 1 in cell A1 and 1 in cell B1. In cell C1 enter =A1+B1

You'll see the result of 2

A	B	C
1	Value	Comment
2	Formula	Value	Comment
3	=1+1	2	Addition
4	=B2+1	3	Addition
5	=B2+B3	5	Addition
6	=B4-B3	2	Subtraction
7	=B5*2	4	Multiplication
8	=B6*3	12	Multiplication
9	=B7/B6	3	Division
10	=2^5	32	Exponent
11	=22222	32	Multiplication

This formula is adding the contents of A1 to B1 and stores the result in C1

Open a new spreadsheet and type the data in columns A and B from the below table. This lesson will also teach you about converting from various types of dates

Weeks to days

Days to months

280 days from a date

Here is the explanation

Look up the number of weeks to be pregnant and you should get 40
Enter into cell A2: Pregnancy Weeks and B2: 40
A3: Days per week and in B3: 7 as there are 7 days per week
A4: Days per pregnancy and B4: =B1*B2
- the = (equal sign) tells Excel that the following is a formula
- B1 refers to the value of the cell in Column B row 1 (40)
- * is the multiplication symbol in Excel and all programming languages
- B2 refers to the value in the cell of 7
- Excel multiplies 40x7 and displays the value of 280
- Most conversions use a similar process
Continue entering the description in column A
B5: enter the days per year 365 (note: we are ignoring leap years)
B6: enter 12 for the number of months per year
B7: you can enter 30 for the days per month but we're calculating it by dividing the number of days in the year (365) by the number of months in the year =B5/B6 The / (slash) is used to denote a division. The result you'll see is 30.41..
B8: is the months pregnant which is the number of days pregnant (280) in cell B4 divided by the number of days per month, cell B7 which returns 9.2 months
B9: Enter the day for mother's Day which is the 2nd Sunday in May, but for simplicity, I'll use May 12th.
B9: You enter dates in Excel by entering the Month/day/year, separating the month with a "/" which is also use as a division symbol.
B10: =B9+B4 .In Excel, you can add a number to a date and get the new date for example Jan.1 + 1 = Jan. 2. So if we add the number of days pregnant (280) to Mother's Day we get a date of approximately when the child will be born, which is Feb. 16th in this example
B11: enter Valentine's day of February 14th as 2/14/2021
B12: =B11+B4 is adding 280 days to Valentine's Day giving you a date of around November 21st

So formulas start with the = (equal sign) in the first position of a cell. Values are number, characters (like column A) and date you type in. A formula can also start with a + (plus) @ (at sign) or / (slash) for lotus notes compatibility.

But Excel can also "format" a value to look different than the value.

In cell B7 we have
- formula =B5/B6
- Value 30.41666667
- formatted 30.4

Exercise

Calculate your approximate conception date (believe it or not your parents really had sex and you're proof) based on your birthday
Convert your height (feet/inches) to inches and meters
Find the date 30 days from today and 30 days prior to today
Get the last day of each month

Solutions