Formulas and Functions Tips 11 - 15

then the next uh important tip here is going to be the average if and it'll be very similar to sum if so i'll say equals average if open parenthesis the range is still column f that's where we're looking i'll type in a comma the criteria is still the word breakfast right there in cell k2 and then another comma the average range is the numbers i want to average for the breakfast items which is column e and i'll close the parentheses so it says equals average if open parenthesis f colon f comma k 2 comma e colon e close parenthesis and i get 462. i'm going to copy that down with the black cross so those are pretty useful countif sum if and average f now the next couple important formulas i want you to know are going to be the more complicated variations of those now i have more than one criteria so i'm going to come over here in column p i want to get the sum of the ones that are france and breakfast at the same time so i'm just going to come over here and i'll type an average and that'll be an average f all right good so i'm going to go to cell p2 and we can type this one as well i'll type in equals average if i'm sorry sum if with an s at the end some ifs when we put an s at the end of these then you can do multiple criteria so we'll do an open parenthesis it's looking for the sum range first this time so it's looking for the numbers i'm going to hide that column go back to column e i'll type in a comma the first criteria range is still column f i'm going to have a column f and type in a comma and the first actual criteria is the the word breakfast in in k2 the whole point of the sumifs is to do multiple criteria so i'll type in a comma the second criteria range is way back in column b with the countries i'm going to have a column b and then i'll type in another comma and the second actual criteria is the word france over here in cell 02. and i'll close the parentheses so the whole thing says equals sum ifs open parentheses e colon e comma f colon f comma k2 comma b colon b comma o2 close parentheses so we get 24 23. all of the breakfast items were 73.85 out of those 24 23 were from france at the same time so when we use sum ifs or countifs or average ifs then we could do multiple criteria i'm going to copy it down good so then the next uh important tip or important formula here is going to be the countifs and we'll type that one in so i'll say equals countifs open parentheses the first criteria range is still column f over here and i'll type in a comma and this first criteria is the word breakfast right there and so k2 i'll type in a comma now the second criteria range is way back in column b with the countries and i'll type in a comma and the second actual criteria will be the word france in o2 and i'll close the parentheses so it says equals countifs open parentheses f colon f comma k2 comma b column b comma o2 close parenthesis good so all of the breakfast items were 16 and out of those four are from france at the same time and i just copied it down and then let's do the average fs which would be the next important formula so we'll do the equals average f's with an s at the end open parenthesis and you're going to see it's very similar to the sum f so the average range is going to be the numbers in column e i'll type in a comma the first criteria range is column f and another comma and the first actual criteria is the word breakfast right there in cell k2 we'll type in another comma the second criteria range is way back in column b with the countries and i'll type in a comma and the next criteria is the word france in cell o2 i'm going to close the parentheses and you can see the formula it says equals average ifs open parentheses e colon e comma f colon f comma k 2 comma b colon b comma o 2 close parentheses so then in this case i'm going to copy it down now some of them say division by zero because in this case uh is trying to divide by zero in that row there wasn't any that were from france that were also gifts at the same time uh so in that case i'm going to go ahead and format them as currency format now let me show you another important formula uh so we'll make this the next important one if i wanted to have a different message there we'll use one that you're going to use all the time it's called if error so with the if error i can replace an error message with maybe something better so watch what i'll do now you can use the if error with any kind of formula so i'm going to go to cell r2 and i'll type in if error after the equal sign and that would find any kind of error in the formula so then i have the formula there i'll type in a comma if that formula gave me an error then i'll just type in the zero there that so i can type in something different if it is an error and i'll close the parentheses so i have equals if error open parentheses average f's open parentheses e colon e comma f colon f comma k 2 comma b column b comma o 2 close parentheses comma 0 close parentheses so what this means is if this formula gives me an error of any kind then put the number 0 there otherwise use the value of the formula so i'm going to hit the enter k and i'm going to copy it down watch these ones and say division by zero those should just change to zero now i'm going to copy it down and that's exactly what happened so that's another important formula that you're going to use it's called if error all right and you can use that with any other formula or any other function to give you a better result when you do have an error let's go back into our slides now and if you look at the slide deck slide 17 at least has the count if the sum if and the average f and if you bought the full course then you also have a copy of the workbook with all the finished formulas so that's another place you can find them now let me go back a couple slides and we'll see some of the next must know formulas starting on page starting on slide 10. thank you for watching this video if you liked it please like share comment and subscribe to my channel if you really liked it please click on the thanks icon that looks like this below this video to support the channel please check out some more of my free videos you can also subscribe to my mailing list and find out about my other services by going to www.pc webinars.com thank you again you