<\/span><\/h3>\n\n\n\nYou can write like this : B3+B4+B5+B6+B7. Use the mouse to select the cells in range like “=SUM( selected cells )”<\/strong><\/span>MIN<\/span><\/h3>\n\n\n\nPurpose : To find out the minimum value within selected cells. Use the mouse to select the cells in range like “=MIN( selected cells )”<\/strong><\/span>MAX<\/span><\/h3>\n\n\n\nPurpose : To find out the maximum value within selected cells. Use the mouse to select the cells in range like “=MAX( selected cells )”<\/strong><\/span>COUNT<\/span><\/h3>\n\n\n\nPurpose : To count how many cells are there. Use the mouse to select the cells in range like “=COUNT( selected cells )”<\/strong><\/span>COUNTA<\/span><\/h3>\n\n\n\nPurpose : To count how many cells are there ( Only cells with a value that counts ). Use the mouse to select the cells in range like “=COUNTA( selected cells )”<\/strong><\/span>AVERAGE<\/span><\/h3>\n\n\n\nYou can write like this : (B3+B4+B5+B6+B7)\/5. Use the mouse to select the cells in range like “=AVERAGE( selected cells )”<\/strong><\/span>RANK<\/span><\/h3>\n\n\n\nPurpose : To find out what is the ranking of the value in the selected cell. Use the mouse to select the cells in range like “=RANK( the selected cell, reference range, ascending \/ descending sort )”<\/strong><\/span>Advanced Function<\/span><\/h2>\n\n\n\n<\/span>IF<\/span><\/h3>\n\n\n\nPurpose : If it meets the condition, output A result, otherwise output B result. “= IF ( Judgement\/Condition , meet the condition output A result , otherwise output B result )”<\/strong>However, it seems a bit unreasonable to give Bs to all the students who get grades under 90, doesn’t it? We can try to solve this by using multiple layers of IF. For example :
<\/span>IFS<\/span><\/h3>\n\n\n\nNow we’re thinking that there are so many IF. Is there a way to solve this formula and make it easier?
Purpose : If it meets the condition, output A result, otherwise output B result. It’s a clean and easy function for humans to read. “= IF ( Condition A , Output result A ,<\/strong> Condition B , Output result B , Condition C , Output result C , otherwise result D )”<\/strong><\/span>SUMIF<\/span><\/h3>\n\n\n\nPurpose : Sum up the values among selected cells with a designated condition such as the brand’s sales number.“= SUMIF ( Condition Range , Designated Condition , Calculate Range )”<\/strong><\/td> A<\/td> B<\/td> C<\/td><\/tr> 1<\/td> Date<\/td> Brand<\/td> Sales<\/td><\/tr> 2<\/td> 10<\/td> Dell<\/td> 8000<\/td><\/tr> 3<\/td> 11<\/td> HP<\/td> 2600<\/td><\/tr> 4<\/td> 15<\/td> HP<\/td> 2400<\/td><\/tr> 5<\/td> 16<\/td> Lenovo<\/td> 6000<\/td><\/tr> 6<\/td> 22<\/td> ASUS<\/td> 9000<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<\/span>AVERAGEIF<\/span><\/h3>\n\n\n\nPurpose : Calculate the average among selected cells with a designated condition such as the brand’s sales number.“= AVERAGEIF ( Condition Range , Designated Condition , Calculate Range )”<\/strong><\/td> A<\/td> B<\/td> C<\/td><\/tr> 1<\/td> Date<\/td> Brand<\/td> Sales<\/td><\/tr> 2<\/td> 10<\/td> Dell<\/td> 8000<\/td><\/tr> 3<\/td> 11<\/td> HP<\/td> 2600<\/td><\/tr> 4<\/td> 15<\/td> HP<\/td> 2400<\/td><\/tr> 5<\/td> 16<\/td> Lenovo<\/td> 6000<\/td><\/tr> 6<\/td> 22<\/td> ASUS<\/td> 9000<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<\/span>COUNTIF<\/span><\/h3>\n\n\n\nPurpose : Count the number of cells among selected cells with a designated condition such as the brand’s sales number.“= COUNTIF ( Condition Range , Designated Condition )”<\/strong><\/td> A<\/td> B<\/td> C<\/td><\/tr> 1<\/td> Date<\/td> Brand<\/td> Sales<\/td><\/tr> 2<\/td> 10<\/td> Dell<\/td> 8000<\/td><\/tr> 3<\/td> 11<\/td> HP<\/td> 2600<\/td><\/tr> 4<\/td> 15<\/td> HP<\/td> 2400<\/td><\/tr> 5<\/td> 16<\/td> Lenovo<\/td> 6000<\/td><\/tr> 6<\/td> 22<\/td> ASUS<\/td> 9000<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<\/span>IFERROR<\/span><\/h3>\n\n\n\nPurpose : To beautify the error message due to any reason but mostly caused by something went wrong in the formula. “= IFERROR ( Under normal condition showing the correct result , When error messages occur showing the way you wanted )”<\/strong><\/td> A<\/td> B<\/td> C<\/td> D<\/td><\/tr> 1<\/td> Item<\/td> Qty<\/td> Price<\/td> Invoice<\/td><\/tr> 2<\/td> Dell<\/td> 3<\/td> 1200<\/td> 3600<\/td><\/tr> 3<\/td> HP<\/td> 5<\/td> 1000<\/td> 5000<\/td><\/tr> 4<\/td> HP<\/td> 2<\/td> 1500<\/td> 3000<\/td><\/tr> 5<\/td> Lenovo<\/td> 6<\/td> 1300<\/td> 7800<\/td><\/tr> 6<\/td> ASUS<\/td> 7<\/td> <\/td> #DIV\/0!<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<\/span>VLOOKUP<\/span><\/h3>\n\n\n\nPurpose : To find out the specific values we wanted from a large amount of raw data and place them in our arrangement\/will.NOTE : It is very important to know how to switch between Relative and Absolute references by using “F4”.<\/strong> Absolute reference : $ was shown in front of it. Relative reference : There is no $ sign.<\/strong>