Welcome to the Excel Formulas Generator! This tool helps you generate Excel formulas based on your requirements. Simply enter the name of the formula you need, and click the "Generate Formula" button to see the corresponding Excel formula.
Below are some examples of formula names you can enter:
Explanation of each of the formulas you mentioned and how they work in Excel:
SUM: Calculates the sum of a range of cells.
=SUM(A1:A5)
AVERAGE: Calculates the average of a range of cells.
=AVERAGE(B1:B10)
COUNT: Counts the number of cells in a range that contains numbers.
=COUNT(C1:C8)
MAX: Returns the largest value in a range of cells.
=MAX(D1:D7)
MIN: Returns the smallest value in a range of cells.
=MIN(E1:E6)
IF: Performs a logical test and returns different values based on the result.
=IF(F1 > 10, "Greater than 10", "Less than or equal to 10")
VLOOKUP: Searches for a value in the leftmost column of a table and returns a value in the same row from a specified column.
=VLOOKUP(G1, A1:B10, 2, FALSE)
INDEX-MATCH: Similar to VLOOKUP, it searches for a value in a table based on a given criteria using the INDEX and MATCH functions.
=INDEX(B1:B10, MATCH(G1, A1:A10, 0))
CONCATENATE: Combines text from multiple cells into one cell.
=CONCATENATE(A1, " ", B1)
LEFT: Extracts a specified number of characters from the beginning of a cell.
=LEFT(C1, 3)
RIGHT: Extracts a specified number of characters from the end of a cell.
=RIGHT(D1, 4)
MID: Extracts a specified number of characters from the middle of a cell.
=MID(E1, 3, 5)
TRIM: Removes leading and trailing spaces from text.
=TRIM(F1)
ROUND: Rounds a number to a specified number of decimal places.
=ROUND(G1, 2)
COUNTIF: Counts the number of cells that meet a specific criterion.
=COUNTIF(H1:H10, ">50")
SUMIF: Adds the cells that meet a specific criterion.
=SUMIF(I1:I10, ">10")
AVERAGEIF: Calculates the average of cells that meet a specific criterion.
=AVERAGEIF(J1:J8, "<50")
SUMPRODUCT: Multiplies corresponding components in the given arrays and returns the sum of those products.
=SUMPRODUCT(K1:K5, L1:L5)
DATE: Creates a date by specifying the year, month, and day.
=DATE(2023, 6, 15)
NETWORKDAYS: Calculates the number of working days between two dates, excluding weekends and specified holidays.
=NETWORKDAYS(M1, N1, O1:O5)
These formulas are powerful tools in Excel for performing various calculations, data manipulation, and conditional operations. They can greatly enhance productivity and automate repetitive tasks in spreadsheet applications.
Introduction: Excel is a powerful tool that has revolutionized data analysis and calculation tasks across various industries. At the heart of Excel's capabilities lies its formulas. These formulas enable users to perform complex calculations, manipulate data, and automate repetitive tasks with ease. In this article, we will explore the world of Excel formulas and delve into their incredible potential for boosting productivity.
Section 1: Understanding Excel Formulas Excel formulas are structured expressions that combine functions, operators, and cell references to perform calculations. By understanding their basic structure, users can harness the full potential of Excel's computational capabilities. We will explain the significance of cell references and the importance of using proper syntax and operators within formulas.
Section 2: Essential Excel Formulas for Beginners For those new to Excel formulas, we will cover fundamental formulas that form the building blocks of data analysis. We will explore formulas such as SUM, AVERAGE, COUNT, and more. With clear explanations and practical examples, beginners will grasp the essential concepts and confidently apply these formulas in their daily tasks.
Section 3: Intermediate Excel Formulas for Data Analysis Moving beyond the basics, we will dive into intermediate-level Excel formulas that are crucial for data analysis. VLOOKUP, INDEX-MATCH, and SUMPRODUCT are powerful tools for retrieving and manipulating data efficiently. Through real-world scenarios and examples, users will understand how to leverage these formulas to extract valuable insights from their datasets.
Section 4: Advanced Excel Formulas for Complex Calculations In this section, we will explore advanced Excel formulas that can handle complex calculations and conditional logic. The IF function, nested functions, and array formulas open up a world of possibilities for automating intricate calculations. With step-by-step examples and explanations, users will gain confidence in implementing these advanced formulas.
Section 5: Tips and Tricks for Excel Formulas To further enhance productivity, we will share valuable tips and tricks for working with Excel formulas. Error handling techniques and troubleshooting common formula errors will be discussed, ensuring users can identify and rectify issues efficiently. Additionally, we will introduce time-saving keyboard shortcuts and highlight Excel functions that complement formula usage.
Section 6: Resources for Learning Excel Formulas Learning Excel formulas is an ongoing process, and we will provide readers with a list of reputable online tutorials, courses, and resources for further exploration. We will include links to Excel formula documentation and community forums where users can seek support and guidance. Furthermore, we will recommend books and websites that offer in-depth knowledge on Excel formulas.
Conclusion: Excel formulas are indispensable tools for anyone working with data. By mastering these formulas, users can unlock the full potential of Excel and significantly enhance their productivity. We have explored the importance of Excel formulas, from their basic structure to advanced techniques. By implementing the tips and resources provided, readers can embark on a journey towards becoming proficient Excel formula users. Embrace the power of Excel formulas and witness the transformation in your data analysis capabilities.
VLOOKUP and HLOOKUP are both Excel formulas used to retrieve data from tables. The main difference between the two is the orientation of the lookup table.
VLOOKUP (Vertical Lookup):
Example of VLOOKUP usage: =VLOOKUP(lookup_value, table_range, col_index_number, [range_lookup])
HLOOKUP (Horizontal Lookup):
Example of HLOOKUP usage: =HLOOKUP(lookup_value, table_range, row_index_number, [range_lookup])
Now, which one is best depends on the orientation of your data. Here's a comparison:
VLOOKUP is commonly used when:
HLOOKUP is commonly used when:
In terms of efficiency, VLOOKUP and HLOOKUP are similar. However, it's worth noting that if your table is large or you need to perform multiple lookups, other formulas like INDEX-MATCH or the combination of INDEX and MATCH functions may offer better performance.
To implement VLOOKUP and HLOOKUP in an Excel sheet:
Remember to adjust the column/row index number and table range appropriately based on your specific data arrangement and requirements.
Note: It's recommended to use the INDEX-MATCH combination instead of VLOOKUP or HLOOKUP for more flexibility, better performance, and the ability to handle two-dimensional lookups.
Cell Entry | Result |
---|---|
1 | TRUE |
1-Jan-98 | TRUE |
FALSE | |
#DIV/0! | FALSE |
Hello | FALSE |
This function examines a cell or calculation to determine whether it is a numeric value.
If the cell or calculation is a numeric value, the result TRUE is shown.
If the cell or calculation is not numeric, or is blank, the result FALSE is shown.
=ISNUMBER(CellToTest)
The cell to test can be a cell reference or a calculation.
No special formatting is needed.
The following table was used by a personnel department to lookup the salary of an employee.
The employee can be entered as a Name or as a Numeric value.
The =ISNUMBER() function has been used to identify the type of entry made, and then the =IF() decides which VLOOKUP to perform.
ID No. | Name | Salary |
---|---|---|
1 | Alan | £10,000 |
2 | Eric | £12,000 |
3 | Carol | £8,000 |
4 | Bob | £15,000 |
5 | David | £12,000 |
Type Employee Name or ID: eric
The Salary is: £12,000
=IF(ISNUMBER(E35),VLOOKUP(E35,C29:E33,3,FALSE),VLOOKUP(E35,D29:E33,2,FALSE))
Initial value | % to find | Percentage value |
---|---|---|
120 | 25% | 30 |
A company is about to give its staff a pay rise.
The wages department needs to calculate the increases.
Staff on different grades get different pay rises.
Grade | % Rise |
---|---|
A | 10% |
B | 15% |
C | 20% |
Name | Grade | Old Salary | Increase |
---|---|---|---|
Alan | A | £10,000 | £1,000 |
Bob | B | £20,000 | £3,000 |
Carol | C | £30,000 | £6,000 |
David | B | £25,000 | £3,750 |
Elaine | C | £32,000 | £6,400 |
Frank | A | £12,000 | £1,200 |
Initial value | % increase | Increased value |
---|---|---|
120 | 25% | 150 |
A company is about to give its staff a pay rise.
The wages department needs to calculate the new salary including the % increase.
Staff on different grades get different pay rises.
Grade | % Rise |
---|---|
A | 10% |
B | 15% |
C | 20% |
Name | Grade | Old Salary | Increase |
---|---|---|---|
Alan | A | £10,000 | £11,000 |
Bob | B | £20,000 | £23,000 |
Carol | C | £30,000 | £36,000 |
David | B | £25,000 | £28,750 |
Elaine | C | £32,000 | £38,400 |
Frank | A | £12,000 | £13,200 |
Value A | Value B | A as % of B |
---|---|---|
120 | 60 | 50% |
You will need to format the result as % by using the % button on the toolbar.
A manager has been asked to submit budget requirements for next year.
The manager needs to specify what will be required each quarter.
The manager knows what has been spent by each region in the previous year.
By analyzing the past year's spending, the manager hopes to predict what will need to be spent in the next year.
Region | Q1 | Q2 | Q3 | Q4 |
---|---|---|---|---|
North | £9,000 | £2,000 | £9,000 | £7,000 |
South | £7,000 | £4,000 | £9,000 | £5,000 |
East | £2,000 | £8,000 | £7,000 | £3,000 |
West | £8,000 | £9,000 | £6,000 | £5,000 |
Total | £26,000 | £23,000 | £31,000 | £20,000 |
Last year's Quarters as % of last year's Total
Region | Q1 | Q2 | Q3 | Q4 |
---|---|---|---|---|
North | 9% | 2% | 9% | 7% |
South | 7% | 4% | 9% | 5% |
East | 2% | 8% | 7% | 3% |
West | 8% | 9% | 6% | 5% |
Total | 26% | 23% | 31% | 20% |
Next year's budget: £150,000
Region | Q1 | Q2 | Q3 | Q4 |
---|---|---|---|---|
North | £39,000 | £34,500 | £46,500 | £30,000 |
South | £31,500 | £27,750 | £37,250 | £24,000 |
East | £9,000 | £7,950 | £10,650 | £6,900 |
West | £36,000 | £31,800 | £42,600 | £27,600 |
Total | £115,500 | £101,000 | £137,000 | £88,500 |
Number | Absolute Value | |
---|---|---|
10 | 10 | =ABS(C4) |
-10 | 10 | =ABS(C5) |
1.25 | 1.25 | =ABS(C6) |
-1.25 | 1.25 | =ABS(C7) |
This function calculates the value of a number, irrespective of whether it is positive or negative.
=ABS(CellAddress or Number)
The result will be shown as a number, no special formatting is needed.
The following table was used by a company testing a machine which cuts timber.
The machine needs to cut timber to an exact length.
Three pieces of timber were cut and then measured.
In calculating the difference between the Required Length and the Actual Length it does not matter if the wood was cut too long or short, the measurement needs to be expressed as an absolute value.
Test Cut | Required Length | Actual Length | Difference | Error Percentage |
---|---|---|---|---|
Test 1 | 120 | 120 | 0 | 0% |
Test 2 | 120 | 90 | 30 | 25% |
Test 3 | 120 | 150 | -30 | -25% |
=D36-E36 |
Table 2 shows the same data but using the =ABS() function to correct the calculations.
Test Cut | Required Length | Actual Length | Difference | Error Percentage |
---|---|---|---|---|
Test 1 | 120 | 120 | 0 | 0% |
Test 2 | 120 | 90 | 30 | 25% |
Test 3 | 120 | 150 | 30 | 25% |
=ABS(D45-E45) |
Type a column number: | 2 |
---|---|
Type a row number: | 3 |
Type a sheet name: | Hello |
Formula | Result |
---|---|
$B$3 | =ADDRESS(F4,F3,1,TRUE) |
B$3 | =ADDRESS(F4,F3,2,TRUE) |
$B3 | =ADDRESS(F4,F3,3,TRUE) |
B3 | =ADDRESS(F4,F3,4,TRUE) |
R3C2 | =ADDRESS(F4,F3,1,FALSE) |
R3C[2] | =ADDRESS(F4,F3,2,FALSE) |
R[3]C2 | =ADDRESS(F4,F3,3,FALSE) |
R[3]C[2] | =ADDRESS(F4,F3,4,FALSE) |
Hello!$B$3 | =ADDRESS(F4,F3,1,TRUE,F5) |
Hello!B$3 | =ADDRESS(F4,F3,2,TRUE,F5) |
Hello!$B3 | =ADDRESS(F4,F3,3,TRUE,F5) |
Hello!B3 | =ADDRESS(F4,F3,4,TRUE,F5) |
This function creates a cell reference as a piece of text, based on a row and column numbers given by the user.
This type of function is used in macros rather than on the actual worksheet.
=ADDRESS(RowNumber, ColNumber, Absolute, A1orR1C1, SheetName)
The SheetName does not actually have to exist.
Items To Test | Result | ||
---|---|---|---|
500 | 800 | TRUE | =AND(C4>=100,D4>=100) |
500 | 25 | FALSE | =AND(C5>=100,D5>=100) |
25 | 500 | FALSE | =AND(C6>=100,D6>=100) |
12 | TRUE | =AND(D7>=1,D7<=52) |
What Does It Do?
This function tests two or more conditions to see if they are all true. It can be used to test that a series of numbers meet certain conditions. It can be used to test that a number or a date falls between an upper and lower limit. Normally the AND() function would be used in conjunction with a function such as =IF().
Syntax
=AND(Test1, Test2)
Note that there can be up to 30 possible tests.
Formatting
When used by itself it will show TRUE or FALSE.
Example 1
The following example shows a list of examination results. The teacher wants to find the pupils who scored above average in all three exams. The =AND() function has been used to test that each score is above the average. The result of TRUE is shown for pupils who have scored above average in all three exams.
Name | Maths | English | Physics | Passed |
---|---|---|---|---|
Alan | 80 | 75 | 85 | TRUE |
Bob | 50 | 30 | 40 | FALSE |
Carol | 60 | 70 | 50 | FALSE |
David | 90 | 85 | 95 | TRUE |
Eric | 20 | 30 | Absent | FALSE |
Fred | 40 | 60 | 80 | FALSE |
Gail | 10 | 90 | 80 | FALSE |
Harry | 80 | 70 | 60 | TRUE |
Ian | 30 | 10 | 20 | FALSE |
Janice | 10 | 20 | 30 | FALSE |
=AND(C38>=AVERAGE($C$29:$C$38), D38>=AVERAGE($D$29:$D$38), E38>=AVERAGE($E$29:$E$38)) |
Averages
47, 54, 60
Pink | Name | Age |
Alan | 18 | |
Bob | 17 | |
Carol | 20 |
Green | Name | Age |
David | 20 | |
Eric | 16 | |
Fred | 19 |
What Does It Do?
This function tests a range to determine whether it is a single block of data or whether it is a multiple selection. If it is a single block, the result will be 1. If it is a multiple block, the result will be the number of ranges selected. The function is designed to be used in macros.
Syntax
=AREAS(RangeToTest)
Formatting
The result will be shown as a number.
Example
The example at the top of this page shows two ranges colored pink and green. These ranges have been given the name PeopleLists. The =AREAS(PeopleLists) gives a result of 2, indicating that there are two separate selections that form the PeopleLists range.
Note
To name multiple ranges, the Ctrl key must be used. In the above example, the pink range was selected as normal, then the Ctrl key was held down before selecting the green range. When a Range Name is created, it will consider both Pink and Green as being one range.
Mon | Tue | Wed | Thu | Fri | Sat | Sun | Average | ||
---|---|---|---|---|---|---|---|---|---|
Temp | 30 | 31 | 32 | 29 | 26 | 28 | 27 | 29 | =AVERAGE(D4:J4) |
Rain | 0 | 0 | 0 | 4 | 6 | 3 | 1 | 2 | =AVERAGE(D5:J5) |
Mon | Tue | Wed | Thu | Fri | Sat | Sun | Average | ||
---|---|---|---|---|---|---|---|---|---|
Temp | 30 | 32 | 29 | 26 | 28 | 27 | 28.66666667 | =AVERAGE(D8:J8) | |
Rain | 0 | 0 | 4 | 6 | 3 | 1 | 2.333333333 | =AVERAGE(D9:J9) |
Mon | Tue | Wed | Thu | Fri | Sat | Sun | Average | ||
---|---|---|---|---|---|---|---|---|---|
Temp | 30 | No | 32 | 29 | 26 | 28 | 27 | 28.66666667 | =AVERAGE(D12:J12) |
Rain | 0 | Reading | 0 | 4 | 6 | 3 | 1 | 2.333333333 | =AVERAGE(D13:J13) |
What Does It Do?
This function calculates the average from a list of numbers. If the cell is blank or contains text, the cell will not be used in the average calculation. If the cell contains zero (0), the cell will be included in the average calculation.
Syntax
=AVERAGE(Range1, Range2, Range3... through to Range30)
Formatting
No special formatting is needed.
Note
To calculate the average of cells that contain text or blanks, use =SUM() to get the total and then divide by the count of the entries using =COUNTA().
Mon | Tue | Wed | Thu | Fri | Sat | Sun | Average | ||
---|---|---|---|---|---|---|---|---|---|
Temp | 30 | No | 32 | 29 | 26 | 28 | 27 | 24.57142857 | =SUM(D31:J31)/COUNTA(D31:J31) |
Rain | 0 | Reading | 0 | 4 | 6 | 3 | 1 | 2 | =SUM(D32:J32)/COUNTA(D32:J32) |
Mon | Tue | Wed | Thu | Fri | Sat | Sun | Average | ||
---|---|---|---|---|---|---|---|---|---|
Temp | 30 | 32 | 29 | 26 | 28 | 27 | 28.66666667 | =SUM(D35:J35)/COUNTA(D35:J35) | |
Rain | 0 | 0 | 4 | 6 | 3 | 1 | 2.333333333 | =SUM(D36:J36)/COUNTA(D36:J36) |
Further Usage
Binary Number | Decimal Equivalent |
---|---|
0 | =BIN2DEC(C4) |
1 | =BIN2DEC(C5) |
10 | =BIN2DEC(C6) |
11 | =BIN2DEC(C7) |
111111111 | =BIN2DEC(C8) |
1111111111 | =BIN2DEC(C9) |
1111111110 | =BIN2DEC(C10) |
1111111101 | =BIN2DEC(C11) |
1000000000 | =BIN2DEC(C12) |
11111111111 | #NUM! |
What Does It Do?
This function converts a binary number to decimal. Negative numbers are represented using two's-complement notation.
Syntax
=BIN2DEC(BinaryNumber)
The binary number has a limit of ten characters.
Formatting
No special formatting is needed.
Index Value | Result |
---|---|
1 | =CHOOSE(C4,"Alan","Bob","Carol") |
3 | =CHOOSE(C5,"Alan","Bob","Carol") |
2 | =CHOOSE(C6,"Alan","Bob","Carol") |
3 | =CHOOSE(C7,10%,15%,18%) |
1 | =CHOOSE(C8,10%,15%,18%) |
2 | =CHOOSE(C9,10%,15%,18%) |
The following table was used to calculate the medals for athletes taking part in a race.
The Time for each athlete is entered.
The =RANK()
function calculates the finishing position of each athlete.
The =CHOOSE()
then allocates the correct medal.
The =IF()
has been used to filter out any positions above 3, as this would cause
the error of #VALUE to appear, due to the fact the =CHOOSE()
has only three items in it.
Name | Time | Position | Medal |
---|---|---|---|
Alan | 1:30 | 2 | =IF(D30<=3,CHOOSE(D30,"Gold","Silver","Bronze"),"unplaced") |
Bob | 1:15 | 4 | =IF(D31<=3,CHOOSE(D31,"Gold","Silver","Bronze"),"unplaced") |
Carol | 2:45 | 1 | =IF(D32<=3,CHOOSE(D32,"Gold","Silver","Bronze"),"unplaced") |
David | 1:05 | 5 | =IF(D33<=3,CHOOSE(D33,"Gold","Silver","Bronze"),"unplaced") |
Eric | 1:20 | 3 | =IF(D34<=3,CHOOSE(D34,"Gold","Silver","Bronze"),"unplaced") |
=RANK(C34,C30:C34) |
This example calculates the possible number of pairs of letters available from the four characters ABCD.
Total Characters | Group Size | Combinations |
---|---|---|
4 | 2 | =COMBIN(C4,D4) |
The proof!
The four letters: ABCD
Pair 1 | Pair 2 | Pair 3 | Pair 4 | Pair 5 | Pair 6 |
---|---|---|---|---|---|
AB | AC | AD | BC | BD | CD |
A decorator is asked to design a colour scheme for a new office.
The decorator is given five colours to work with but can only use three in any scheme.
How many colour schemes can be created?
Available Colours | Colours Per Scheme | Total Schemes |
---|---|---|
5 | 3 | =COMBIN(C41,D41) |
The colours:
Scheme 1 | Scheme 2 | Scheme 3 | Scheme 4 | Scheme 5 |
---|---|---|---|---|
Red | Red | Red | Red | Red |
Green | Green | Green | Blue | Blue |
Blue | Yellow | Black | Yellow | Black |
Scheme 6 | Scheme 7 | Scheme 8 | Scheme 9 | Scheme 10 |
Green | Green | Green | Blue | ?????? |
Blue | Blue | Yellow | Yellow | |
Yellow | Black | Black | Black |
Name 1 | Name 2 | Concatenated Text |
---|---|---|
Alan | Jones | =CONCATENATE(C4,D4) |
Bob | Williams | =CONCATENATE(C5,D5) |
Carol | Davies | =CONCATENATE(C6,D6) |
Alan | Jones | =CONCATENATE(C7," ",D7) |
Bob | Williams | =CONCATENATE(D8,", ",C8) |
Carol | Davies | =CONCATENATE(D9,", ",C9) |
Note: You can achieve the same result by using the & operator.
Name 1 | Name 2 | Concatenated Text |
---|---|---|
Alan | Jones | =C25&D25 |
Bob | Williams | =C26&D26 |
Carol | Davies | =C27&D27 |
Alan | Jones | =C28&" "&D28 |
Bob | Williams | =D29&", "&C29 |
Carol | Davies | =D30&", "&C30 |
Amount To Convert | Converting From | Converting To | Converted Amount |
---|---|---|---|
1 | in | cm | =CONVERT(C4,D4,E4) |
1 | ft | m | =CONVERT(C5,D5,E5) |
1 | yd | m | =CONVERT(C6,D6,E6) |
1 | yr | day | =CONVERT(C8,D8,E8) |
1 | day | hr | =CONVERT(C9,D9,E9) |
1.5 | hr | mn | =CONVERT(C10,D10,E10) |
0.5 | mn | sec | =CONVERT(C11,D11,E11) |
Abbreviations:
Category | Abbreviation | Description |
---|---|---|
Weight & Mass | g | Gram |
Weight & Mass | kg | Kilogram |
Weight & Mass | lbm | Pound mass |
Weight & Mass | ozm | Ounce mass |
Distance | m | Meter |
Distance | mi | Statute mile |
Distance | Nmi | Nautical mile |
Distance | in | Inch |
Distance | ft | Foot |
Distance | yd | Yard |
Distance | ang | Angstrom |
Distance | pica | Pica (1/72 in.) |
Time | yr | Year |
Time | day | Day |
Time | hr | Hour |
Time | mn | Minute |
Time | sec | Second |
Pressure | Pa | Pascal |
Pressure | atm | Atmosphere |
Pressure | mmHg | mm of Mercury |
Temperature | C | Degree Celsius |
Temperature | F | Degree Fahrenheit |
Temperature | K | Degree Kelvin |
Liquid | tsp | Teaspoon |
Liquid | tbs | Tablespoon |
Liquid | oz | Fluid ounce |
Liquid | cup | Cup |
Liquid | pt | Pint |
Liquid | qt | Quart |
Liquid | gal | Gallon |
Liquid | l | Liter |
Force | N | Newton |
Force | dyn | Dyne |
Force | lbf | Pound force |
Energy | J | Joule |
Energy | e | Erg |
Energy | c | "Thermodynamic calorie" |
Energy | cal | IT calorie |
Energy | eV | Electron volt |
Energy | HPh | Horsepower-hour |
Energy | Wh | Watt-hour |
Energy | flb | Foot-pound |
Energy | BTU | BTU |
Magnetism | T | Tesla |
Magnetism | ga | Gauss |
Prefixes:
Prefix | Multiplier | Abbreviation | Prefix | Multiplier | Abbreviation |
---|---|---|---|---|---|
exa | 1.00E+18 | E | deci | 1.00E-01 | d |
peta | 1.00E+15 | P | centi | 1.00E-02 | c |
tera | 1.00E+12 | T | milli | 1.00E-03 | m |
giga | 1.00E+09 | G | micro | 1.00E-06 | u |
mega | 1.00E+06 | M | nano | 1.00E-09 | n |
kilo | 1.00E+03 | k | pico | 1.00E-12 | p |
hecto | 1.00E+02 | h | femto | 1.00E-15 | f |
deca | 1.00E+01 | da | atto | 1.00E-18 | a |
Day | Month | Year | Date |
---|---|---|---|
25 | 12 | 99 | 12/25/99 |
25 | 12 | 99 | 25-Dec-99 |
33 | 12 | 99 | January 2, 2000 |
What Does It Do?
This function creates a real date by using three normal numbers typed into separate cells.
Syntax:
=DATE(year, month, day)
Formatting:
The result will normally be displayed in the dd/mm/yy format.
By using the Format - Cells - Number - Date command, the format can be changed.
Number1 | Number2 | Delta |
---|---|---|
10 | 20 | 0 |
50 | 50 | 1 |
17.5 | 17.5 | 1 |
17.5 | 18 | 1 |
17.50% | 0.175 | 1 |
Hello | Hello | #VALUE! |
1 | 1 |
What Does It Do?
This function compares two values and tests whether they are exactly the same.
If the numbers are the same, the result will be 1, otherwise the result is 0.
It only works with numbers; text values produce a result of #VALUE.
The formatting of the number is not significant, so numbers which appear rounded due to the removal of decimal places will still match correctly with non-rounded values.
Syntax:
=DELTA(FirstNumber, SecondNumber)
Formatting:
No special formatting is needed.
Example:
The following table is used to determine how many pairs of similar numbers are in a list.
The =DELTA() function tests each pair, and then the =SUM() function totals them.
Number1 | Number2 | Delta |
---|---|---|
10 | 20 | 0 |
50 | 50 | 1 |
30 | 30 | 1 |
17.5 | 18 | 1 |
12 | 8 | 0 |
100 | 100 | 1 |
150 | 125 | 0 |
Total Pairs | =SUM(E30:E36) |
Example 1:
Jan | Feb | Mar |
---|---|---|
10 | 80 | 97 |
20 | 90 | 69 |
30 | 100 | 45 |
40 | 110 | 51 |
50 | 120 | 77 |
Type a month to look for: Feb
Which row needs to be picked out: 4
The result is: 100
=HLOOKUP(F10,D3:F10,F11,FALSE)
Example 2:
Maker | Spare | Cost |
---|---|---|
Vauxhall | Ignition | £50 |
VW | GearBox | £600 |
Ford | Engine | £1,200 |
VW | Steering | £275 |
Ford | Ignition | £70 |
Ford | CYHead | £290 |
Vauxhall | GearBox | £500 |
Ford | Engine | £1,200 |
=HLOOKUP(B79,G72:I77,MATCH(C79,F73:F77,0)+1,FALSE)
Example 3:
Unit Cost Table | ||
---|---|---|
Brick | Wood | Glass |
£2 | £1 | £3 |
Discount Table | |||
---|---|---|---|
1 | 100 | 300 | |
Brick | 0% | 6% | 8% |
Wood | 0% | 3% | 5% |
Glass | 0% | 12% | 15% |
Orders Table | ||||
---|---|---|---|---|
Item | Units | Unit Cost | Discount | Total |
Brick | 100 | £2 | 6% | £188 |
Wood | 200 | £1 | 3% | £194 |
Glass | 150 | £3 | 12% | £396 |
Brick | 225 | £2 | 6% | £423 |
Wood | 50 | £1 | 0% | £50 |
Glass | 500 | £3 | 15% | £1,275 |
Unit Cost: =HLOOKUP(C127,E111:G112,2,FALSE)
Discount: =HLOOKUP(D127,E115:G118,MATCH(C127,D116:D118,0)+1,TRUE)
What Does It Do?
This function tests a condition.
If the condition is met, it is considered to be TRUE.
If the condition is not met, it is considered as FALSE.
Depending upon the result, one of two actions will be carried out.
Syntax
=IF(Condition, ActionIfTrue, ActionIfFalse)
The Condition is usually a test of two cells, such as A1=A2.
The ActionIfTrue and ActionIfFalse can be numbers, text, or calculations.
Formatting
No special formatting is required.
Example 1
The following table shows the Sales figures and Targets for sales reps.
Each has their own target which they must reach.
The =IF()
function is used to compare the Sales with the Target.
If the Sales are greater than or equal to the Target, the result of "Achieved" is shown.
If the Sales do not reach the target, the result of "Not Achieved" is shown.
Note that the text used in the =IF()
function needs to be placed in double quotes, like "Achieved"
.
Name | Sales | Target | Result |
---|---|---|---|
Alan | 1000 | 5000 | Not Achieved |
Bob | 6000 | 5000 | Achieved |
Carol | 2000 | 4000 | Not Achieved |
Example 2
The following table is similar to Example 1.
This time the Commission to be paid to the sales rep is calculated.
If the Sales are greater than or equal to the Target, the Commission is 10% of Sales.
If the Sales do not reach the Target, the Commission is only 5% of Sales.
Name | Sales | Target | Commission |
---|---|---|---|
Alan | 1000 | 5000 | 50 |
Bob | 6000 | 5000 | 600 |
Carol | 2000 | 4000 | 100 |
Example 3
This example uses the =AND()
function within the =IF()
function.
A builders merchant gives a 10% discount on certain product lines.
The discount is only given on products that are on Special Offer when the Order Value is £1000 or above.
The =AND()
function is used with the =IF()
function to check that the product is on offer and that the value of the order is above £1000.
Special | Product | Offer | Value | Discount | Total |
---|---|---|---|---|---|
Wood | Yes | £2,000 | £200 | £1,800 | |
Glass | No | £2,000 | £- | £2,000 | |
Cement | Yes | £500 | £- | £500 | |
Turf | Yes | £3,000 | £300 | £2,700 |
=IF(AND(C61="Yes", D61>=1000), D61*10%, 0)
Holiday booking price list.
People | Weeks | 1 | 2 | 3 | 4 | |
---|---|---|---|---|---|---|
1 | £500 | 1 | £300 | £250 | £200 | |
2 | £400 | £300 | £250 | |||
3 | £500 | £350 | £300 |
What Does It Do?
This function picks a value from a range of data by looking down a specified number of rows and then across a specified number of columns.
It can be used with a single block of data or non-continuous blocks.
Syntax
Syntax 1
=INDEX(RangeToLookIn, Coordinate)
This is used when the RangeToLookIn is either a single column or row.
The Coordinate indicates how far down or across to look when picking the data from the range.
Example:
Colours Red Green Blue Type either 1, 2 or 3: 2 The colour is: Green =INDEX(D32:D34,D36)
Syntax 2
=INDEX(RangeToLookIn, RowCoordinate, ColumnColumnCordinate)
This syntax is used when the range is made up of rows and columns.
Example:
Country Currency Population Capitol England Sterling 50 M London France Franc 40 M Paris Germany DM 60 M Bonn Spain Peseta 30 M Barcelona Type 1, 2, 3, or 4 for the country: 2 Type 1, 2, or 3 for statistics: 3 The result is: Paris =INDEX(D45:F48,F50,F51)
Syntax 3
=INDEX(NamedRangeToLookIn, RowCoordinate, ColumnColumnCordinate, AreaToPickFrom)
Using this syntax, the range to look in can be made up of multiple areas.
The easiest way to refer to these areas is to select them and give them a single name.
The AreaToPickFrom indicates which of the multiple areas should be used.
Example:
NORTH Qtr1 Qtr2 Qtr3 Qtr4 Bricks £1,000 £2,000 £3,000 £4,000 Wood £5,000 £6,000 £7,000 £8,000 Glass £9,000 £10,000 £11,000 £12,000 SOUTH Qtr1 Qtr2 Qtr3 Qtr4 Bricks £1,500 £2,500 £3,500 £4,500 Wood £5,500 £6,500 £7,500 £8,500 Glass £9,500 £10,500 £11,500 £12,500 Type 1, 2, or 3 for the product: 1 Type 1, 2, 3, or 4 for the Qtr: 3 Type 1 for North or 2 for South: 2 The result is: 3500 =INDEX(NorthAndSouth,F76,F77,F78)
Example:
This is an extended version of the previous example.
It allows the names of products and the quarters to be entered.
The =MATCH()
function is used to find the row and column positions of the names entered.
These positions are then used by the =INDEX()
function to look for the data.
EAST Qtr1 Qtr2 Qtr3 Qtr4 Bricks £1,000 £2,000 £3,000 £4,000 Wood £5,000 £6,000 £7,000 £8,000 Glass £9,000 £10,000 £11,000 £12,000 WEST Qtr1 Qtr2 Qtr3 Qtr4 Bricks £1,500 £2,500 £3,500 £4,500 Wood £5,500 £6,500 £7,500 £8,500 Glass £9,500 £10,500 £11,500 £12,500 Type 1, 2, or 3 for the product: wood Type 1, 2, 3, or 4 for the Qtr: qtr2 Type 1 for North or 2 for South: west The result is: 6500 =INDEX(EastAndWest,MATCH(F100,C91:C93,0),MATCH(F101,D90:G90,0),IF(F102=C90,1,IF(F102=C95,2)))
Numbers | "Least Common Multiple" | ||
---|---|---|---|
6 | 20 | 60 | =LCM(C4,D4) |
12 | 18 | 36 | =LCM(C5,D5) |
34 | 96 | 1632 | =LCM(C6,D6) |
What Does It Do?
This function calculates the Least Common Multiple, which is the smallest number that can be divided by each of the given numbers.
Syntax:
=LCM(Number1, Number2, Number3... through to Number29)
Formatting:
No special formatting is needed.
Name | Jan | Feb | Mar |
---|---|---|---|
Alan | 10 | 80 | 97 |
Bob | 20 | 90 | 69 |
Carol | 30 | 100 | 45 |
David | 40 | 110 | 51 |
Eric | 50 | 120 | 77 |
Francis | 60 | 130 | 28 |
Gail | 70 | 140 | 73 |
Type a Name in this cell: Eric
The March value for this person is: 77 =LOOKUP(F12, D4:G10)
What Does It Do?
This function looks for a piece of information in a list and then picks an item from the last cell in the adjacent row or column. It selects the data from the end of the row or column, making it unsuitable for picking data from partway across a list (use VLOOKUP or HLOOKUP instead).
Syntax:
=LOOKUP(WhatToLookFor, RangeToLookIn)
Formatting:
No special formatting is needed.
Problems:
The list of information to be looked through must be sorted in ascending order; otherwise, errors will occur, resulting in either #N/A or incorrect results.
Table 1 shows the Name column sorted alphabetically, and using =LOOKUP() will yield correct results.
Table 2 shows the same data but not sorted. Sometimes the results will be correct, but other times the result will be an #N/A error or an incorrect figure.
Table 1 | Table 2 | ||||||
---|---|---|---|---|---|---|---|
Name | Jan | Feb | Mar | Name | Jan | Feb | Mar |
Alan | 10 | 80 | 97 | David | 40 | 110 | 51 |
Bob | 20 | 90 | 69 | Eric | 50 | 120 | 77 |
Carol | 30 | 100 | 45 | Alan | 10 | 80 | 97 |
David | 40 | 110 | 51 | Bob | 20 | 90 | 69 |
Eric | 50 | 120 | 77 | Carol | 30 | 100 | 45 |
Francis | 60 | 130 | 28 | Francis | 60 | 130 | 28 |
Gail | 70 | 140 | 73 | Gail | 70 | 140 | 73 |
Name: Eric
Value: 77
=LOOKUP(C88, B80:E86)
=LOOKUP(H88, G80:J86)
Names Values
Bob 250
Alan 600
David 1000
Carol 4000
Type a name to look for: Alan Type a value: 1000
The position of Alan is: 2 Value position: 3
=MATCH(E9, E4:E7, 0) =MATCH(I9, I4:I7, 1)
What Does It Do?
This function looks for an item in a list and shows its position.
It can be used with text and numbers.
It can look for an exact match or an approximate match.
Syntax:
=MATCH(WhatToLookFor, WhereToLook, TypeOfMatch)
The TypeOfMatch can be either 0, 1, or -1.
Using 0
This option looks for an exact match. If no match is found, the #NA error is shown.
Examples:
Ascending | Descending | Wrong Value |
---|---|---|
10 | 40 | 10 |
20 | 30 | 20 |
30 | 20 | 30 |
40 | 10 | 40 |
20 | 20 | 25 |
2 | 3 | #N/A |
=MATCH(G45, G40:G43, 0)
Using 1
This option looks for an exact match or the next lowest number if no exact match exists.
If there is no match or next lowest number, the #NA error is shown.
Examples:
Ascending | Descending | Wrong Value |
---|---|---|
10 | 40 | 10 |
20 | 30 | 20 |
30 | 20 | 30 |
40 | 10 | 40 |
20 | 20 | 25 |
2 | #N/A | 2 |
=MATCH(G62, G57:G60, 1)
Using -1
This option looks for an exact match or the next highest number if no exact match exists.
If there is no exact match or next highest number, the #NA error is shown.
Examples:
Ascending | Descending | Wrong Value |
---|---|---|
10 | 40 | 40 |
20 | 30 | 30 |
30 | 20 | 20 |
40 | 10 | 10 |
20 | 20 | 25 |
#N/A | 3 | 2 |
=MATCH(G79, G74:G77, -1)
Example 4
The tables below were used by a bus company taking bookings for bus tours.
They needed to allocate a bus with enough seats for all the passengers.
The list of bus sizes was entered in a list.
The number of passengers on the tour was then entered.
The =MATCH() function looks down the list to find the bus with enough seats.
If the number of passengers is not an exact match, the next biggest bus is picked.
After the =MATCH() function has found the bus, the =INDEX() function is used to look down the list again and pick out the actual bus size required.
Bus Size
Bus 1 | 54 |
Bus 2 | 50 |
Bus 3 | 22 |
Bus 4 | 15 |
Bus 5 | 6 |
Passengers on the tour: 23
Bus size needed: 50
=INDEX(D95:D99, MATCH(H94, D95:D99, -1), 0)
Example 5
The tables below were used by a school to calculate the exam grades for pupils.
The list of grade breakpoints was entered in a list.
The pupils' scores were entered in another list.
The pupils' scores are compared against the breakpoints.
If an exact match is not found, the next lowest breakpoint is used.
The =INDEX() function then looks down the Grade list to find the grade.
Exam Score | Grade | Pupil Score | Grade | |
---|---|---|---|---|
0 | Fail | Alan | 60 | Pass |
50 | Pass | Bob | 6 | Fail |
90 | Merit | Carol | 97 | Distinction |
95 | Distinction | David | 89 | Pass |
=INDEX(D111:D114, MATCH(G114, C111:C114, 1), 0)