The MAX function returns the largest numeric value in a range. Similarly, the MIN function returns the smallest numeric value in a range.
To use MAX and MIN functions:
Enter the formula =MAX(range) or =MIN(range) where range is any range. These produce the obvious results: the maximum (or minimum) value in the range.
Try it! Use the MAX and MIN functions to fill in the range B8:C9. For example, you want the values $2300 and $3600 in cells B8 and C9.
There are many times when you need to sum products of values in two (or possibly more than two) similar-sized ranges. Fortunately, there is an Excel function that sums products quickly.
To use the SUMPRODUCT function
Enter the formula =SUMPRODUCT(range1,range2), where range1 and range2 are exactly the same size. For example, they might be two column ranges with 10 cells each, or they might be two ranges with 4 rows and 10 columns each. The formula sums the products of the corresponding values from the two ranges.
There can actually be more than two ranges in the SUMPRODUCT formula, separated by commas, as long as all of them have exactly the same size. This is not as common as having only two ranges, but it is sometimes useful
Try it! Sum the products of the two ranges in the following spreadsheet to find the total shipping cost. Enter the result in cell G1. (Scroll to the right for the answer.) By the way, if you are tempted to write the formula without the SUMPRODUCT function as the sum of 9 products, as many of my students continue to do, imagine how long your formula would be if there were 10 plants and 50 cities!
Dostları ilə paylaş: |
