Ex­cel’s top 12 most pop­u­lar for­mu­las with ex­am­ples

Make sure these date and time func­tions, math tricks, and data manipulation tools are all at your dis­posal.

PCWorld (USA) - - Here’s How - BY JD SARTAIN

Ex­cel has over 475 for­mu­las in its Func­tions Li­brary, from sim­ple math­e­mat­ics to very com­plex sta­tis­ti­cal, log­i­cal, and en­gi­neer­ing tasks such as IF state­ments ( go.pcworld.com/lgfm; one of our peren­nial fa­vorite sto­ries); AND, OR, NOT func­tions; and COUNT, AV­ER­AGE, and MIN/MAX.

The ba­sic func­tions cov­ered be­low are the top 12 most pop­u­lar for­mu­las in Ex­cel. To help you learn, we’ve also pro­vided a spread­sheet with all the for­mula ex­am­ples we cover here. It can be down­loaded from go.pcworld.com/exex.

1. TO­DAY/NOW

There are 24 Date And Time func­tions listed on the drop-down menu un­der For­mu­las >

Date & Time: 11 Date for­mats, 10 Time for­mats, and as many user-de­fined cus­tom for­mats as you can cre­ate. The TO­DAY func­tion re­veals the cur­rent month, day, and year; while the NOW func­tion re­veals the cur­rent month, day, year, and time of day. This is a handy func­tion if you al­ways for­get to date your work.

1: En­ter the fol­low­ing for­mula in cell A1: =TO­DAY() and press En­ter. 2: Next, type over that func­tion in A1 with =NOW().

IM­POR­TANT NOTE: Why type over? In or­der for these two for­mu­las to work prop­erly, they must be en­tered in the Home cell, that is, A1, oth­er­wise, they won’t up­date au­to­mat­i­cally when the spread­sheet re­cal­cu­lates. Press Shift-f9 to cal­cu­late/ re­cal­cu­late the ac­tive spread­sheet only, or press F9 for the en­tire work­book.

Af­ter you en­ter one of these func­tions in A1, you can then re­for­mat the Date and Time or use the sys­tem de­fault. The de­fault for­mat for the TO­DAY func­tion is 8/29/18, and the de­fault for NOW is 8/29/18 21:57. If these don’t work for you, change them.

3: Po­si­tion your cur­sor on the Date or Time you want changed and choose Home > For­mat > For­mat Cells.

4: In the For­mat Cells di­a­log win­dow, choose Date (or Time) from the Cat­e­gory panel un­der the Num­ber tab.

5: Scroll through the list of Date/ Time for­mats in the Type di­a­log pane and se­lect the for­mat that best fits your project.

2. SUM FUNC­TIONS

Prob­a­bly the most fre­quently used func­tion in Ex­cel (or any other spread­sheet pro­gram), =SUM does just that: It sums a col­umn, row, or range of num­bers—but it doesn’t just sum. It also sub­tracts, mul­ti­plies, di­vides, and uses any of the com­par­i­son op­er­a­tors to re­turn a re­sult of 1 (true) or 0 (false).

You can also get the same re­sults just us­ing the plus (+) sign in place of the func­tion SUM. For ex­am­ple, both of these for­mu­las pro­duce the same an­swer: =SUM( J7*9) and =+( J7*9). In the spread­sheet graphic at right, no­tice that cells E3 through E8 use the SUM func­tion, while cells E9 through E14 use the plus (+) sign and the re­sults of the func­tion are the same.

You can en­ter the SUM func­tion (or + sign) man­u­ally or se­lect it from the Rib­bon menu un­der For­mu­las > Math & Trig

(but­ton), then choose from the drop-down list; or choose (from the Rib­bon menu) For­mu­las > In­sert Func­tion, then scroll down the list and se­lect it from there.

If you just want to add a sin­gle col­umn of num­bers, po­si­tion your cur­sor in the cell at the bot­tom of that col­umn, click the Au­to­sum but­ton and then SUM, and press En­ter. Ex­cel frames the col­umn of num­bers in green borders and dis­plays the for­mula in the cur­rent cell.

The prob­lem comes when the range of num­bers you need to cal­cu­late gets com­pli­cated with mul­ti­ple cal­cu­la­tion op­er­a­tors over mul­ti­ple cells. For ex­am­ple: =SUM(H1+I1*J1-M1*J1. Re­mem­ber your high school math? If the num­bers in­side the for­mula are not grouped prop­erly, the an­swer will be wrong. No­tice the

screen­shot above.

En­ter the fol­low­ing col­umn head­ers in H2 through P2 (use Alt+en­ter to stack head­ers in a sin­gle cell): Daily Earn­ings, Plus Bonuses, Times Days Worked, Gross Pay, (for­mula), Mi­nus Meals at $9.00 per day, To­tal Monthly Earn­ings, (for­mula), and Com­ment.

NOTE: The for­mula columns are FYI only and pro­vide no in­trin­sic value to the spread­sheet. They just “dis­play” the for­mula for your ben­e­fit (so you can see the syn­tax of each for­mula used).

For this ex­er­cise, you can en­ter the same val­ues in H3:11, I3:11, and J3:11, with or with­out the blank rows in be­tween (again, added for eas­ier view­ing). Com­plete as fol­lows: $86.00, $20.00, 22.0 work­days, and the rest are for­mu­las. Note that as we build each for­mula, we are com­bin­ing the steps, even­tu­ally, into a sin­gle for­mula.

1: We start out with three sep­a­rate for­mu­las. The first is to add the Daily earn­ings, plus Bonuses, mul­ti­plied by the num­ber of days worked in a month, which equals Gross

Pay: =SUM(H3+I3*J3) in cell K3. No­tice that the an­swer is $526.00. That just doesn’t look right.

2: Use your cal­cu­la­tor to check the for­mu­las to en­sure they’re cor­rect BE­FORE you copy them to the rest of the cells in the col­umn.

3: The for­mula in K3 is wrong. It re­quires group­ing the num­bers ac­cord­ing to the or­der of cal­cu­la­tion us­ing com­mas or paren­the­ses.

4: Note the cor­rected for­mula in cell K4: =SUM(H4+I4)*J4. Check your num­bers again (with your cal­cu­la­tor) and note that this for­mula is cor­rect. The cor­rect an­swer is $2,332.00.

5: The sec­ond for­mula (in M4) is

=SUM( J4*9) mul­ti­plies the work­days (22) times $9.00, the cost of meals per day. The an­swer is $198.00.

6: The third for­mula (in N4) cal­cu­lates the monthly earn­ings mi­nus the meals: =SUM(K4–M4); an­swer is $2,134.00.

7: In the next group (H6:N8), the for­mu­las in M6:M8 re­main the same: =SUM( J7*9), etc.—again that’s the num­ber of work­days times the cost of meals. But the for­mu­las in col­umn K are elim­i­nated and then com­bined with the for­mu­las in col­umn M: =SUM(H7+I7)*J7–M7. Note that the syn­tax (the struc­ture of the for­mula) is cor­rect in cells N7 and N8, but in­cor­rect in N6.

8: The next group (H10:H11) com­bines the for­mu­las in col­umn M with the for­mu­las in col­umn N: =SUM(H11+I11)*J11– (M11*j11)—note that the for­mula in N10 is in­cor­rect. By com­bin­ing these for­mu­las into one, you can elim­i­nate columns K and L.

9: Also, in­stead of “hard­cod­ing” the price of the meals (as shown in M3:M4 and M6:M8), you can now change the price of the meals in col­umn M (M10:M11) when in­fla­tion dic­tates an in­crease in­stead of chang­ing the for­mula.

3. RAND FUNC­TION

The RAND func­tion is re­ally sim­ple and tra­di­tion­ally used for sta­tis­ti­cal anal­y­sis, cryp­tog­ra­phy, gam­ing, gam­bling, and prob­a­bil­ity the­ory, among dozens of other things. In Ex­cel, the RAND func­tion gen­er­ates a ran­dom num­ber be­tween 0 and 1.

Note; how­ever, that ev­ery time you en­ter new data and press the En­ter key, the list of ran­dom num­bers you just cre­ated changes. If you need to main­tain your ran­dom num­bers lists, you must for­mat the cells as val­ues.

1: En­ter the func­tion =RAND() in columns A3 through A14. Se­lect that col­umn and press Ctrl+c (for copy) or click the Copy but­ton un­der the Home tab and choose Copy from the drop-down menu. Move your cur­sor to cell B3 and se­lect Home > Paste > Paste Spe­cial. Click the Val­ues but­ton from the Paste Spe­cial di­a­log win­dow, then click OK.

2: Now the list con­tains val­ues in­stead of func­tions, so it will not change. No­tice (in the for­mula bar) that the ran­dom num­bers have 15 dig­its af­ter the dec­i­mal (Ex­cel de­faults to 9), which you can change, if nec­es­sary (as dis­played in cell F3). Just click the In­crease Dec­i­mal but­ton in the Num­ber group un­der the Home tab.

3: If you pre­fer to work with whole num­bers, en­ter this for­mula in cell F3: =INT(RAND()*999) and you get a 3-digit ran­dom num­ber. Copy the for­mula down through F12, then add an­other 9 to the string to add an­other digit to your ran­dom num­ber—e.g., four nines equal four dig­its, five nines equal five dig­its. Again, you must copy the list and Paste as Val­ues to main­tain a static list.

4. COUNT FUNC­TIONS

Use the COUNT func­tion to count the num­ber of nu­meric val­ues in a range of cells. For

ex­am­ple, C4:C15 con­tains the quan­tity of gar­den tools Mr. Mcgre­gor needs to or­der for his shop. Note that the an­swer is 10 (out of 12), be­cause the COUNT func­tion doesn’t in­clude blank cells. How­ever, if you en­ter a zero, a nu­meric code, or a date, Ex­cel counts it as an “oc­cu­pied” cell and in­cludes it in its an­swer.

En­ter 10 num­bers into col­umn C (Quan­tity). Re­place one num­ber with a space (or a tap on the space­bar), then re­place an­other num­ber with a semi­colon, and then en­ter a date into cell C7.

En­ter this for­mula at the bot­tom of the num­ber list (C16): =COUNT(C4:C15). The an­swer is 10 (out of 12) be­cause Ex­cel counted all the num­bers and the date, but ig­nored the blank cell (con­tain­ing the space) and the punc­tu­a­tion in cell C8.

Use the COUNTA func­tion if you want to in­clude nu­meric val­ues, log­i­cal or er­ror val­ues, text, a space (from the space­bar), punc­tu­a­tion, sym­bols, or any other char­ac­ter on your key­board.

1: En­ter 12 dol­lar amounts into col­umn D (Price). Re­place one cell with a ques­tion mark, an­other cell with a sym­bol, and an­other cell with some text.

2: En­ter this for­mula in D16: =COUNTA(D4:D15). The an­swer is 12 (out of 12) be­cause Ex­cel in­cluded all the “non-nu­meric” val­ues and char­ac­ters. 3: No­tice that row 18 (C and D) dis­plays the ac­tual for­mu­las that are in C and D 16.

5. AV­ER­AGE FUNC­TION

Most ev­ery­one knows that an av­er­age is de­ter­mined by adding all the val­ues in a list, then di­vid­ing by the num­ber of val­ues listed; e.g., 4+5+3=12/3=4, which is the av­er­age. You can use the SUM func­tion and add the di­vi­sion all in one for­mula, or you can just use the AV­ER­AGE func­tion. The syn­tax is: =AV­ER­AGE(RANGE).

1: En­ter some num­bers in col­umn A. En­ter the AV­ER­AGE func­tion at the bot­tom of the list: =AV­ER­AGE(A4:A13) and note the an­swer (in our case) is 53. You can ver­ify your an­swer with the SUM func­tion; that is: =SUM(A4:A13/10) = 53.

2: Next en­ter some more num­bers in col­umn C but, this time, add some text to one cell, punc­tu­a­tion to an­other, and a space to an­other. En­ter the same for­mula: =AV­ER­AGE(A4:A15), and note the an­swer is 78. To ver­ify, en­ter the SUM for­mula omit­ting the cells that con­tain non-nu­meric char­ac­ters:

Cells that con­tain text, log­i­cal val­ues, punc­tu­a­tion, or empty cells are dis­re­garded; but cells with the ze­ros (as a num­ber, but not as text) are in­cluded. A text zero would have an apos­tro­phe in front of the zero, which you can­not see in the cell, but is vis­i­ble in the For­mula Bar.

IM­POR­TANT NOTE: If you’re im­port­ing huge data­bases from a main­frame or an out­side, ex­ter­nal source, some­times the num­bers ex­port as text. How can you know if a num­ber is re­ally text? Gen­er­ally, text is left-jus­ti­fied and num­bers are right-jus­ti­fied but, be­cause ev­ery­one for­mats their spread­sheets for aes­thet­ics now, that method is un­re­li­able. An­other op­tion is to scroll quickly through a long list of im­ported num­bers and watch the For­mula Bar. If you see apos­tro­phes be­fore any of the num­bers, those en­tries are text. Last, look for the green tri­an­gle in the top left cor­ner of the cell. Un­less the pre­vi­ous owner of the spread­sheet in­structed Ex­cel to ig­nore this er­ror, then the con­tents of the cell are text.

If the val­ues are text, you must con­vert them to num­bers im­me­di­ately. To do this, move down to the first num­ber in the list that’s ac­tu­ally text. High­light the range of text that’s im­per­son­at­ing num­bers. Rightclick the yel­low warn­ing sign that’s left of the first text cell in the range. Click Con­vert To Num­ber from the pop-up list, and it’s done.

6. MIN/MAX FUNC­TIONS

Use the MIN func­tion to find the small­est num­ber in a range of val­ues, and the MAX func­tion to find the high­est. The syn­tax for these func­tions are: =Min(range); =Max(range) where range equals the list of

num­bers you’re cal­cu­lat­ing.

Com­mon uses of this func­tion are; for ex­am­ple, find the high­est/low­est grade in a class­room; the high­est/ low­est sales dol­lars in a store; the high­est/low­est bat­ting av­er­ages of your fa­vorite base­ball team; and so on.

Some would ask, why not just sort the data? You could, but ev­ery time the num­bers changed, you’d have to re-sort. And, if you’re sort­ing mul­ti­ple columns/fields with a lot of records/rows, the sort op­tion could get cum­ber­some.

The MIN/MAX func­tions re­main the same re­gard­less of the changes in the data, even if you add more rows (as long as you add the rows us­ing the In­sert > Row fea­ture within the ex­ist­ing range—that is, above the cell that con­tains the for­mula).

1: En­ter some num­bers in col­umn A4:A11, then en­ter this for­mula in

A13: =MIN(A4:A11) and this for­mula in A14: =MAX(A4:A13).

NOTE: The MIN/MAX func­tions dis­re­gard empty cells, TRUE/FALSE an­swers, text, text im­per­son­at­ing num­bers, sym­bols, and punc­tu­a­tion.

7. CONCAT/CONCATENATE

The func­tions CONCAT and CONCATENATE do the same thing: They both com­bine mul­ti­ple cells, ranges, or strings of data into one cell. The most com­mon use of this func­tion is to com­bine first and last name into one cell or join the city, state, and ZIP code into one cell.

NOTE: CONCAT re­placed CONCATENATE in Ex­cel 2016, but both func­tions are still avail­able. Note that CONCAT ap­pears only un­der For­mu­las > Text and For­mu­las > In­sert

Func­tion > Cat­e­gory > Text, but both CONCAT and CONCATENATE ap­pear un­der For­mu­las > In­sert Func­tion > Cat­e­gory > All.

1: En­ter some first names in col­umn A and last names in col­umn B. En­ter the fol­low­ing for­mula in col­umn C: =CONCATENATE(A4,” “,B4) or =CONCAT(B4,” “,C4), then copy the for­mula down. What are the dou­ble quotes for? See Note be­low #2.

2: En­ter a few cities (or ski re­sorts) in col­umn F, states in col­umn G, and ZIP codes in col­umn H. En­ter the fol­low­ing for­mula in col­umn I:

=CONCATENATE(F4, “,”, ““, G4,” “,H4)

NOTE: If you want a space be­tween the first and last name, you must en­ter that space in­side quo­ta­tion marks in your for­mula. The same thing is true for punc­tu­a­tion, such as a comma be­tween city and state. In the fol­low­ing for­mula the “,” (quote comma quote—in red) tells Ex­cel to in­sert a comma be­tween the data in F15 (city) and the data in G15 (state). The ““(quote space quote—in pur­ple) adds a space af­ter the comma be­tween F15 (city) and G15 (state) and an­other space be­tween G15 (state) and H15 (zip code).

=CONCATENATE(F15, ”,”, ” “, G15, ” “,H15)

8. TRIM

This func­tion re­moves ex­tra (or padded) spa­ces that in­fect your data as a re­sult of user er­ror, down­load­ing data from an ex­ter­nal source such as the in­ter­net, or im­port­ing data from an­other com­puter sys­tem. And you don’t have to “tell Ex­cel” where the spa­ces are lo­cated in the string of text in each cell; it rec­og­nizes the ex­tra spa­ces and re­moves them. Note; how­ever, that it will not re­move a space in the mid­dle of a word. The syn­tax is sim­ple: =Trim(cell ad­dress).

1: En­ter some data in col­umn A. Add some spa­ces be­fore, af­ter, and in the mid­dle of mul­ti­ple words, then en­ter the fol­low­ing for­mula in cell A4: =TRIM(A4).

2: Copy the for­mula down. It’s that easy!

NOTE: There is one case where this func­tion does not work, and that’s with a non-break­ing space char­ac­ter used in web­pages. The dec­i­mal value is 160, and the HTML code is &nbsp. You can re­move this char­ac­ter us­ing a com­bi­na­tion of TRIM, CLEAN, and SUB­STI­TUTE.

9. UP­PER/LOWER/PROPER

An­other easy group, these func­tions con­vert text in a cell or range of cells to up­per­case, low­er­case, or proper case. Proper case is first let­ter in caps and re­main­ing let­ters in low­er­case. The syn­tax is easy: func­tion, cell ad­dress.

En­ter some mixed-case data in col­umn A; e.g., cal­i­for­nia, NEW YORK, span­ish. En­ter the fol­low­ing for­mula in col­umn B: =UP­PER(A4), in col­umn C: =LOWER(A4), and col­umn D: =PROPER(A4).

No­tice that Ex­cel cor­rects all the mis­placed case er­rors and con­verts the data cor­rectly. Copy the for­mu­las down, and that’s it for this sim­ple one.

NOTE: In Word, you can use Shift-f3 to cy­cle through up­per­case, low­er­case, and proper case, but this short­cut is not avail­able in Ex­cel. Note that the Ex­cel func­tion =PROPER is called Sen­tence Case in Word.

10. REPT

When Lo­tus 1-2-3 was the only game in town, you could en­ter a back­slash fol­lowed by any char­ac­ter and Lo­tus would re­peat that char­ac­ter through­out a cell. If the cell width grew larger or smaller, so did the char­ac­ter. In Ex­cel, this fea­ture is han­dled by the func­tion REPT. It’s not quite as ef­fi­cient be­cause you must add the char­ac­ter to the for­mula, then spec­ify how many times you want that char­ac­ter re­peated. This means if the cell width is

in­creased, the re­peated char­ac­ter is not, and if the cell width is de­creased, the re­peated char­ac­ter bleeds over into the ad­ja­cent cell.

The syn­tax for this func­tion: =REPT(“*”,5); =REPT(“—“,10), =REPT(“+”,12). You can re­peat any char­ac­ter on the key­board, plus sym­bols.

11. IF STATE­MENT

The IF func­tion (also more com­monly called IF state­ments) works like this: IF, then, else. Ba­si­cally, that means if a con­di­tion is true, then do one thing, else/oth­er­wise do some­thing else. For ex­am­ple, if the puppy is a Labrador, then buy a blue col­lar, oth­er­wise/else, buy a red col­lar.

The syn­tax (the way the com­mands are or­ga­nized in the for­mula) of the IF state­ment is: =If(log­ic_test, val­ue_if true, val­ue_if_­false). IF state­ments are used in all pro­gram­ming lan­guages and, although the syn­tax may vary slightly, this func­tion pro­vides the same re­sults.

1: En­ter the fol­low­ing col­umn head­ers: Cookie Boxes Sold; 3rd Prize =More than 500 Sold, Less than 1000; 2nd Prize =More than 1000 Sold, Less than 1500; 1st Prize =More than 1500 Sold, Less than 2000; Grand Prize =More than 2000 Sold

2: En­ter some num­bers into col­umn A4:A13. Mix it up so you get data in all of the Sold columns.

3: En­ter this for­mula in B4: =IF($A4>500, $A4, 0).

NOTE the $ sign be­fore the col­umn let­ter ‘A’ in the above for­mula. Place your cur­sor on the first ‘A’ in the for­mula, then use the func­tion key F4 to cy­cle through

the Ab­so­lute and Rel­a­tive Ref­er­ences. Stop when the $ sign pre­cedes the ‘A’ (for each A in the for­mula). This tells Ex­cel NOT to change the col­umn let­ter, but only change the row num­bers when this for­mula is copied. If you put a dol­lar sign be­fore both the col­umn let­ter and the row num­ber, nei­ther would change.

4: Copy the for­mula in B4 to C4, D4, and E4, then edit as fol­lows: C4

=IF($A4>1000, $A4, 0); D4

=IF($A4>1500, $A4, 0); and E4 =IF($A4>2000, $A4, 0). Then copy down.

5: The for­mula works, but you have to re­view each col­umn to see who won the prizes, be­cause each col­umn shows ALL the val­ues greater than the amount in the for­mula. That’s ok for a small spread­sheet, but not for any­thing larger than a sin­gle screen.

6: We need a Nested IF state­ment for this one. Re­peat num­bers 1, 2, and 3 above be­gin­ning on row 20; but in­stead of the for­mula in 3 above, en­ter this for­mula in B20: =IF(AND($A 20>500,$A20<1000),$A20,0).

Re­peat num­ber 4 above, but edit the for­mu­las like this: C20 = =IF(AND($A20 >1000,$A20<1500),$A20,0); D20 =

=IF(AND($A20>1500,$A20<2000) ,$A20,0); and E20 = =IF($A20>2000, $A20, 0). Yes, this last one is dif­fer­ent be­cause there is no “less than” amount. Then copy down. Now you can look at each col­umn and de­ter­mine who the win­ner is for that cat­e­gory.

12. AND/OR

AND and OR are com­mon func­tions in the pro­gram­mers’ en­vi­ron­ment, also known as Bool­ean op­er­a­tors (along with NOT). AND means that all con­di­tions in the query must be true; OR means that at least one con­di­tion must be true.

For ex­am­ple, look­ing for an ap­pli­cant with MS Word AND MS Ex­cel ex­pe­ri­ence means the ap­pli­cant must have both skills to qual­ify for the job. This con­di­tion would pro­vide a TRUE re­sult. Look­ing for an ap­pli­cant with MS Word OR Ex­cel means the ap­pli­cant must have one OR the other, but not nec­es­sar­ily both. Also a TRUE re­sult. Hav­ing nei­ther skills would, ob­vi­ously, pro­vide a FALSE re­sult.

1: Copy the num­bers from the spread­sheet above, or down­load the full work­book ( go. pcworld.com/exex).

2: En­ter the fol­low­ing AND for­mula in cell

D4: =AND($B4>=501,$C4<=500). Again, note the $ signs. Then copy down to cell D13.

3: En­ter this for­mula in cell F4: =OR($B4>= 501,$C4<=500), then copy down. No­tice the re­sults in the rows with borders; that is, 5, 8, and 13. The AND re­sults are all FALSE be­cause both con­di­tions were false (or not true); while the OR re­sults were all TRUE be­cause one of the con­di­tions was true, while the other was false.

If this seems con­fus­ing, study the num­bers in columns B and C. Then read the for­mu­las that cal­cu­late for the AND func­tion, then the OR func­tion, and it will make more sense.

Date and Time func­tions and for­mats.

Sum func­tions us­ing mul­ti­ple cal­cu­la­tion op­er­a­tors.

Com­plex SUM func­tions us­ing mul­ti­ple ar­gu­ments.

Use the RAND func­tion to cre­ate lists of ran­dom num­bers.

COUNT &amp; COUNTA count the num­ber of cells that con­tain some­thing.

The MIN &amp; MAX func­tion.

Use CONCAT &amp; CONCATENATE to com­bine data from mul­ti­ple cells into one cell.

Use TRIM to re­move ex­tra or padded spa­ces from your data.

Use UP­PER &amp; LOWER &amp; PROPER case to al­ter case of char­ac­ters.

Use the REPT func­tion.

IF state­ments and nested IF state­ments.

AND plus OR func­tions.

Newspapers in English

Newspapers from USA

© PressReader. All rights reserved.