Blog of Calc LLC

If function is a logic function. It does two things: Whether the criteria is met Return a value based on whether the criteria is met or not. If the criteria is met, it returns one value; if the criteria is not met, it returns a different value. The reason IF function is confusing for some Excel users is that it does two things: a logical test, AND a return value depending on the logical test result, while the name of "IF" implies it only does a logical test. If this function only returns TRUE or False, it would be much easier to understand. In the following example, there are 5 employees, each having his own budget. Column C records the exact amount of money they spent. If the actual spending is greater than budget, we call it "Over budget", otherwise, we call it "Within budget". The IF function is: =IF(C2>B2,"Over budget","Within budget") The first part, "C2>B2", is a logical test. If this is TRUE, then the IF function ...

Excel has a data validation tool. However, that only works during data entry stage. It cannot validate existing data. Learn how to use Excel data validation tool on data entry Here we tell you a method about how to validate existing data. Below are scores of students in a class. The score should be between 0 and 100. However, the teacher made mistakes with the scores of David and Emily, which is below 0 and above 100 respectively. Of course, if there are only 5 cases, you can check it manually. However, if there are large number of records, manual inspection is inefficient. You can write a simple formula to do the data validation. Write a formula like this: =IF(AND(B2>=0,B2<=100),"correct","wrong") Two functions are used in this formula. The first function is the AND function. It combines the two logical tests: score >=0 and score <=100. A valid score has to meet both criteria to be valid. Then we used the IF function. If the condition is met, it return...