![]() Each Cell reference must be in them, and each IF statement must also be in parentheses. This means if the year is 2013, it returns a 12, meaning the warranty expired before 2014, so we must pay the monthly out of warranty fee for all 12 months. The "value_if_false" argument for the nested IF statement will be 12 because if it fails the greater-than test and the equal-to test, then the Year($C2) must be less than 2014. So, if the warranty expires in April 2014, then Month($C2) will be four, and the result in Column E would be eight. Using the MONTH function, we subtract MONTH($C2) from 12. If it is, then the warranty expires in 2014, and we have no need to know how many months are not in warranty. The "value_if_false" test is a nested IF statement. If the year is, say, 2015, then the device is in warranty all of 2014, and there will be zero months out of warranty and no cost in 2014. If the year in cell C2 is greater than 2014, then return zero. We also want to find the cost for this device for the years 20, since our support contract is three years. Multiplying the out-of-warranty months (E2) by the monthly non-warranty support cost (D2) returns the total cost for that device in 2014. If we know the number of months it is in warranty, we can subtract that from 12. In this statement, we test if the Year(C2) is equal to 2014 if it is (the value_if_true test) then we return "Equal." If it is not equal, and since we already tested for Greater, then the Year(C2) must be less than 2014, and we return "Less."įor our warranty table in cell E2, we need to know how many months this device will be out of warranty in 2014. If this is false - C2 is not greater than 2014 - then go to the next IF statement in the value_if_false test. This reads: IF Year(C2) is greater than 2014, then return "Greater" (the value_if_true test). For instance, if we want to test whether the year in C2 is greater than 2014, we could nest a second IF statement in the "value_if_false" argument: The result here would be No since Year (C2) is 2011 (see previous example). If the year in cell C2 is >2014, return Yes, otherwise return No.The "value_if_true" is what IF will return if the test is true, and "value_if_false" is what IF will return if the test is false. IF (logical_test,value_if_true,value_if_false) the "logical_test" is a test such as C2>0.Same as Year function but returns the month. For example, if cell C2 had the date 04-27-2011, then Year(C2) would return the year portion of the date in C2, or 2011. You can then manipulate the year, as shown in the example. Not to worry - when a date is provided in a cell, the Year function reads its serial number. This returns the year when the serial number of that year is provided. To make this spreadsheet work, we manipulate the Year and Month functions into an IF statement (see Table 1).
0 Comments
Leave a Reply. |