The macro is run by different users - some using Excel 2010 for Windows and some using Excel 2011 for Mac. The macro works fine on Excel 2010. On Excel for Mac, although the array of sheet names is created correctly only the last sheet in the array is saved in the selected folder. Excel Formula Training Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. The tutorial shows how to write an IF OR statement in Excel to check for various 'this OR that' conditions. IF is one of the most popular Excel functions and very useful on its own. Combined with the logical functions such as AND, OR, and NOT, the IF function has even more value because it allows.

Excel functions, or formulas, lie at the heart of the application’s deep well of capabilities. Today we’ll tackle IF statements, a string of commands that determine whether a condition is met or not. Just like a yes-no question, if the specified condition is true, Excel returns one user-determined value and, if false, it returns another.

The IF statement is also known as a logical formula: IF, then, else. If something is true, then do this, else/otherwise do that. For example, if it’s raining, then close the windows, else/otherwise leave the windows open.

The syntax (or sentence structure; that is, the way the commands are organized in the formula) of an Excel IF statement is: =IF(logic_test, value_if true, value_if_false). IF statements are used in all programming languages and, although the syntax may vary slightly, the function provides the same results.

Remember: Learning Excel functions/formulas and how they work are the first steps toward using Visual Basic, Microsoft’s event-driven programming language. We'll start with three easy IF statements, then move on to nested IF statements and multi-function IF statements. At the end we'll get fancy with COUNTIF, SUMIF, AVERAGEIF, and IF with wildcards.

Simple IF statements

1. Past-due notices

In this spreadsheet, the customer’s payment due date is listed in column A, the payment status is shown in column B, and the customer’s company name is in column C. The company accountant enters the date that each payment arrives, which generates this Excel spreadsheet. The bookkeeper enters a formula in column B that calculates which customers are more than 30 days past due, then sends late notices accordingly.

A. Enter the formula: =TODAY() in cell A1, which displays as the current date.

B. Enter the formula: =IF(A4-TODAY()>30, “Past Due”, “OK”) in cell B4.

In English, this formula means: If the date in cell A4 minus today’s date is greater than 30 days, then enter the words ‘Past Due’ in cell B4, else/otherwise enter the word ‘OK.’ Copy this formula from B4 to B5 through B13.

2. Pass/Fail lifeguard test

The Oregon Lifeguard Certification is a Pass/Fail test that requires participants to meet a minimum number of qualifications to pass. Scores of less than 70 percent fail, and those scores greater than that, pass. Column A lists the participants’ names; column B shows their scores; and column C displays whether they passed or failed the course. The information in column C is attained by using an IF statement.

Once the formulas are entered, you can continue to reuse this spreadsheet forever. Just change the names at the beginning of each quarter, enter the new grades at the end of each quarter, and Excel calculates the results.

A. Enter this formula in cell C4: =IF(B4<70,”FAIL”,”PASS”). This means if the score in B4 is less than 70, then enter the word FAIL in cell B4, else/otherwise enter the word PASS. Copy this formula from C4 to C5 through C13.

3. Sales & bonus commissions

Wilcox Industries pays its sales staff a 10-percent commission for all sales greater than $10,000. Sales below this amount do not receive a bonus. The names of the sales staff are listed in column A. Enter each person’s total monthly sales in column B. Column C multiples the total sales by 10 percent, and column D displays the commission amount or the words ‘No Bonus.’ Now you can add the eligible commissions in column D and find out how much money was paid out in bonuses for the month of August.

A. Enter this formula in cell C4: =SUM(B4*10%), then copy from C4 to C5 through C13. This formula calculates 10 percent of each person’s sales.

B. Enter this formula in cell D4: =IF(B4>10000, C4, “No bonus”), then copy from D4 to D5 through D13. This formula copies the percentage from column C for sales greater than $10,000 or the words ‘No Bonus’ for sales less than $10,000 into column D.

C. Enter this formula in cell D15: =SUM(D4:D13). This formula sums the total bonus dollars for the current month.

Nested IF statements

4. Convert scores to grades with nested IF statements

This example uses a “nested” IF statement to convert the numerical Math scores to letter grades. The syntax for a nested IF statement is this: IF data is true, then do this; IF data is true, then do this; IF data is true, then do this; IF data is true, then do this; else/otherwise do that. You can nest up to seven IF functions.

The student’s names are listed in column A; numerical scores in Column B; and the letter grades in column C, which are calculated by a nested IF statement.

A. Enter this formula in cell C4: =IF(B4>89,”A”,IF(B4>79,”B”,IF(B4>69,”C”,IF(B4>59,”D”,”F”)))) , then copy from C4 to C5 through C13.

Note: Every open, left parenthesis in a formula must have a matching closed, right parenthesis. If your formula returns an error, count your parentheses.

5. Determine sliding scale sales commissions with nested IF statements

This example uses another nested IF statement to calculate multiple commission percentages based on a sliding scale, then totals the commissions for the month. The syntax for a nested IF statement is this: IF data is true, then do this; IF data is true, then do this; IF data is true, then do this; else/otherwise do that. The names of the sales staff are listed in column A; each person’s total monthly sales are in column B; and the commissions are in column C, which are calculated by a nested IF statement, then totaled at the bottom of that column (in cell C15).

A. Enter this formula in cell C4: =IF(B4<5000,B4*7%,IF(B4<8000,B4*10%,IF(B4<10000,B4*12.5%,B4*15%))) , then copy from C4 to C5 through C13.

B. Enter this formula in cell C15: =SUM(C4:C13). This formula sums the total commission dollars for the current month.

6. Calculate product price based on quantity

Many retailers and most wholesalers provide customers a price break based on quantity. For example, a wholesaler may sell 1 to 10 T-shirts for $10.50 each, and 11 to 25 T-shirts for $8.75 each. Use the following IF statement formula to determine how much money you can save by purchasing wholesale products for your company in bulk.

A. Enter the header Wholesale Prices in cell A3. Enter the headers Qty and Cost in cells B3 and C3.

B. Enter the quantity breaks in cells B4 through B9.

C. Enter the price breaks in cells C4 through C9.

This is the quantity/price matrix for your wholesale business.

D. Next, enter the headers Qty Ordered and Qty Cost in cells B11 and C11.

E. Enter the header Wholesale Products in cell A11, then enter as many products as you like below.

F. Enter the quantity ordered for each product in column B.

G. And last, enter the following formula in the adjacent cells of column C:

=B12*IF(B12>=101,3.95,IF(B12>=76,5.25,IF(B12>=51,6.3,IF(B12>=26,7.25, IF(B12>=11,8,IF(B12>=1,8.5,””)))))) Showbox for mac 2018.

Note there are no spaces in this formula. Copy down the formula as far as needed in column C to apply to all the wholesale products and quantities in columns A and B.

H. Once the order is complete, create a total line: In column A (in this case, A20), type TOTAL Invoice Amount. In column C (in this case, C20), use the SUM function to total the column.

Now, that all the formulas are in place, all you have to do each week (or month) is enter the new quantities in column B, and Excel does the rest. If the prices change, just modify the matrix at the top of the spreadsheet, and all the other formulas and totals will automatically adjust.

Multi-function IF statements

7. Using other functions inside the IF statement

In this example, we’ll nest two IF statements and insert the SUM function all into the same formula. The purpose is to determine whether the individual’s goals are less than, equal to, or greater than the company’s monthly cap.

A. Enter a monthly cap amount in B1, which can be changed every month to reflect the company’s expectations.

B. Enter the following headers in cells A3 through G3:Goals, 1st Qtr, 2nd Qtr,3d Qtr, 4th Qtr, Goals or Cap? and Bonus.

C. Enter the top five marketing persons' goal amounts in cells A4 through A8.

D. Enter the same individuals' quarterly totals in cells B4 through E8.

For employees who achieved their goals, the bonus is 10% of the goal amount. For employees who exceeded the monthly cap, the bonus is 25% of their actual sales. Use these formulas to determine the bonus amounts.

E. Enter this formula in cells F4 through F8: =IF(A4<=B1,A4,IF(A4>B1,SUM(B4:E4,0)))

F. Enter this formula in cells G4 through G8: =IF(F4<=A4,F4*10%,IF(F4>=B1,F4*25%,0))

8. Use IF to avoid dividing by zero

In Excel, if you try to divide any number by zero, the error message that Excel displays is this: #DIV/0!

So, what to do if you have a large database that requires a calculation based on division, and some of the cells contain zeros? Use the following IF statement formula to address this problem.

A. To track the money collected per month by the staff at Friar Tuck’s Fundraisers, open a spreadsheet and create the following headers:Dollars in A1, Dollars per Worker in B1, Dollars Left Over in C1.

B. Enter some dollar amounts in column A and the number of staff working per day in column B. Column B will include some zeroes because, some days, nobody works (but donations still come in through the mail).

C. Enter this formula in C2 through C11: =IF(B2<>0,A2/B2,0). By using an IF statement to prevent Excel from dividing any of the funds collected by zero, only the valid numbers are calculated.

D. You could use another IF statement to locate the zeroes in column B, then total the corresponding dollars in column A, which would then be divided equally among all the staff. Copy this formula in D2 through D11: =IF(B4=0,A4,0).

E. Next, move your cursor to B12 and enter this formula =MAX(B2:B11) to locate the largest number of workers in column B, which is eight.

F. Move your cursor to D12 and enter this formula: =SUM(D2:D11)/B12. The answer is $392.50 for each of the eight staff members working at Friar Tuck’s Fundraisers.

COUNTIF, SUMIF, AVERAGEIF, & IF with wildcards

9. Use COUNTIF to satisfy specific criteria

COUNTIF is an IF statement that counts the contents of a range of cells based on explicit criteria (that you specify) that's contained within that range of cells. The syntax is simple: formula, range, criteria: =COUNTIF (range, criteria).

In this example, we'll use COUNTIF to count the cells in a specific range that are greater than > a chosen number, less than < a chosen number, equal to a chosen number, or a combination of two or more logical operators used together.

Greater Than >

A. Enter some numbers in cells A4 through A16.

B. In cell E4, enter this formula: =COUNTIF($A$4:$A$16,'>1610')

C. Then enter the 'greater than' number in cell F4, so you can verify that the formula works.

D. Copy E4 down from E5 to E11, then change the greater than values to a different number.

Less Than <

A. Enter 13 numbers in column B (B4:B16).

B. In cell G4, enter this formula: =COUNTIF($B$4:$B$16,'<200')

C. Then enter the 'less than' number in cell H4, so you can verify that the formula works.

D. Copy G4 down from G5 through G11, then change the less than values to a different number.

Equal To =

A. Enter 13 numbers in column C (C4:C16).

B. In cell I4, enter this formula: =COUNTIF($C$4:$C$16,'<2010')

C. Then enter the 'equal to' number in cell J4, so you can verify that the formula works.

D. Copy I4 down from I5 through I11, then change the less than values to a different number.

NOTE: You can also combine these logical operators to broaden your results. For example, Greater than or equal to: =COUNTIF($A$4:$A$16,'>=1610'); Less than or equal to: =COUNTIF($A$4:$A$16,'<=1655')

In this example, we'll use COUNTIF to count all the cells in a specified range that match the selected dates, dollars, or text.

E. Enter some dates in column A4:A12; enter some text such as flowers in column B4:B12, and enter some dollars in column C4:C12.

F. Enter the following formulas in column D 'Single Dates,' D4, D5, D6, D7, and D8:

=COUNTIF($A$4:$A$12, '>=9/1/2018'); =COUNTIF($A$4:$A$12, '>=2/6/2018'); =COUNTIF($A$4:$A$12, '>=12/7/2018'); =COUNTIF($A$4:$A$12, '>=5/14/2018'); and =COUNTIF($A$4:$A$12, '>=8/9/2018').

G. Enter the following formulas in column E 'Single Text String,' E4, E5, E6, E7, and E8:

=COUNTIF($B$4:$B$12, '=Roses'); =COUNTIF($B$4:$B$12, '=Daisies'); =COUNTIF($B$4:$B$12, '=Tulips'); =COUNTIF($B$4:$B$12, '=Lillies'); and =COUNTIF($B$4:$B$12, '=Daffodils')

H. Enter the following formulas in column G 'Multiple Text Strings,' G4, G5, G6, G7, and G8:

The FILTER function allows you to filter a range of data based on criteria you define.

Note: September 24, 2018: The FILTER function is one of several beta features, and currently only available to a portion of Office Insiders at this time. We'll continue to optimize these features over the next several months. When they're ready, we'll release them to all Office Insiders, and Office 365 subscribers.

In the following example we used the formula =FILTER(A5:D20,C5:C20=H2,') to return all records for Apple, as selected in cell H2, and if there are no apples, return an empty string (').

Syntax

The FILTER function filters an array based on a Boolean (True/False) array.

Excel For Mac 2018

=FILTER(array,include,[if_empty])

Excel For Mac 2018 If Then String Theory

Notes:

• An array can be thought of as a row of values, a column of values, or a combination of rows and columns of values. In the example above, the source array for our FILTER formula is range A5:D20.

• The FILTER function will return an array, which will spill if it's the final result of a formula. This means that Excel will dynamically create the appropriate sized array range when you press ENTER. If your supporting data is in an Excel table, then the array will automatically resize as you add or remove data from your array range if you're using structured references. For more details, see this article on spilled array behavior.

• If your dataset has the potential of returning an empty value, then use the 3rd argument ([if_empty]). Otherwise, a #CALC! error will result, as Excel does not currently support empty arrays.

• If any value of the include argument is an error (#N/A, #VALUE, etc.) or cannot be converted to a Boolean, the FILTER function will return an error.

• Excel has limited support for dynamic arrays between workbooks, and this scenario is only supported when both workbooks are open. If you close the source workbook, any linked dynamic array formulas will return a #REF! error when they are refreshed.

Examples

FILTER used to return multiple criteria

In this case, we're using the multiplication operator (*) to return all values in our array range (A5:D20) that have Apples AND are in the East region: =FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),').

FILTER used to return multiple criteria and sort

Excel For Mac 2018 Freeze Panes

In this case, we're using the previous FILTER function with the SORT function to return all values in our array range (A5:D20) that have Apples AND are in the East region, and then sort Units in descending order: =SORT(FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),'),4,-1)

In this case, we're using the FILTER function with the addition operator (+) to return all values in our array range (A5:D20) that have Apples OR are in the East region, and then sort Units in descending order: =SORT(FILTER(A5:D20,(C5:C20=H1)+(A5:A20=H2),'),4,-1).

If you’re looking to turbocharge your internet use for the rest of this year and onto 2018, one of the best ways to speed up your internet without relying on your ISP is by changing your DNS server. By modifying your DNS, or Domain Name System, servers, you can make sure your internet connection is set to be the fastest in the neighborhood. For those that want to take advantage of freebies to optimize their internet experience, here are the best free DNS servers of 2018 to faster the internet. Just like DNS Jumper, DNS Benchmark has 20+ pre-configured DNS Servers to benchmark. If you can’t find your favorite DNS server, simply click on the “Add/Remove” button to add the DNS server. Unfortunately, DNS Benchmark doesn’t offer any way to set the DNS server directly. You have to manually set it on your OS or router. Mac apps for finding the fastest dns servers in 2018 calendar.

Free Excel For Mac 2018

Notice that none of the functions require absolute references, since they only exist in one cell, and spill their results to neighboring cells.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also