Information Technology SBA-Spring Tech Sports Club Financial Statement

Added: 2026-05-25

[00:00:02]All right, guys. So, I've gone ahead to prepopulate my spreadsheet as I'm here making a video for your SBA.

[00:00:12]All right. So, if you look carefully, I've gone ahead to populate the sections for me first name, last name, sport name, and membership type because these they don't really take a they don't use a function. So I think you can do that on your own.

[00:00:30]All right. So let me go to the question.

[00:00:33]It says create a worksheet that includes the names of the members, their sport name, membership type, membership fee, registration cost, and feed.

[00:00:46]So I've gone ahead to populate the label headings and also insert information for one, two, three, four columns. cuz I don't want to use the entire video to take that up. Now I'm at the first bullet. It says the heading spring tech financial report must be centered across all columns used in the spreadsheet using the font style Broadway font 16.

[00:01:17]All right. So here I'm going to head to type spring financial report.

[00:01:24]What I'm going to do now, sorry about the background, noise. What I'm going to do now is to highlight the column headings.

[00:01:36]So, I'm highlighting row one straight across to G because if you look carefully, that's where the label stop.

[00:01:44]So, now I'm going to change the font type. It says Broadway, right? So, that's Broadway and it says size 60.

[00:01:55]change it to 16. And I'm not sure what version of Microsoft Excel you're using or if you're using Google Sheets. If you're using Google Sheets, you can simply type in merge and center.

[00:02:08]However, on Excel, it will be there.

[00:02:12]So, there you go. Mer center.

[00:02:17]All right. Now ensure that all edings are properly aligned using the wrap text feature. I've gone ahead to do that already. I'm just going to undo it so that you can see firsthand how it's done.

[00:02:32]All right. So these are the headings. If you look carefully, some of them appear as if the some letters are missing or some words are missing. So to correct that I go ahead and highlight the neighbors.

[00:02:47]After highlighting them, I'm going to use the feature for wrap text.

[00:02:55]So this it says wrap extra long text into multiple lines. So you can see all of it. So I'm just going to go ahead and apply.

[00:03:05]Boom. There you have it. And it says that they should be center.

[00:03:09]All right. All right.

[00:03:13]And you should know how to change your your background to the the color of your cell background. So, we use this one for the fill color and we use this one for the font color. I'm going to change this one so you can see exactly how it works. So, I'm going to apply I'm going to choose purple.

[00:03:39]Mhm. And then I'm going to choose yellow right there. There you go.

[00:03:45]So that's how it works. This might be a bit bright, but you can change yours according to colors that you love, but ensure that your labels stand out.

[00:03:56]All right. Next, it's now asking us to ensure that uh registration cost is 2,500 per person.

[00:04:06]So I'm going to go to registration cost.

[00:04:09]I'm going to insert 2500 because that's the amount, right? It's now in the accounting format. If you don't know how to format your numbers or your values, you go to the number section in the formatting bar and you choose the specific option you want. I'm going to use currency since we're dealing with money.

[00:04:34]It should be the same 2500 for each person. So, what you can do is to double tap at the autofill handle. And this is what we call the fill handle where you see the small square right here. Once the cursor goes to a plus sign and it's black, you can either left click and drag down the mouse or you double tap.

[00:04:54]I'm going to double tap.

[00:04:58]All right. So, look going down for everyone. It's now showing $2,500.

[00:05:09]All right. Next, we're going to move on.

[00:05:13]It's now saying that the membership type should be either weekly or monthly. I preid that as well. So, you can see each person has either weekly or monthly for the membership type.

[00:05:28]Go to the next. All right. We're now at number two. It says on another sheet create the following table which outlines your sports name and the membership cost per month.

[00:05:41]Now to get a new sheet what you have to do is to press the plus sign. You get a new sheet just like that.

[00:05:50]Right. However, I've g ahead to create my table beforehand. So my table is right here. So I have the sport names along with the membership cost per month.

[00:06:03]All right. So there you have it. And I have since aligned the labels as well and they're in currency format.

[00:06:13]All right.

[00:06:15]So we have that. It now says use an appropriate function to link the membership cost per month to the membership cost column on the spring tech members sheet.

[00:06:32]the spring tech member sheet.

[00:06:43]That's a spring tech sheet. I'm going to delete this sheet because I don't want it. Right.

[00:06:50]So, there we have the two sheets. So, this is the one that we're going to link to this one around here. All right. Now, because it wants us to return a value and link two sheets, the function that is fit for this scenario would be the VLOOKUP function.

[00:07:13]I'm going to go ahead. Remember that you begin each function with the equal sign.

[00:07:18]It's going to be equal followed by the name of the function which is VLOOKUP.

[00:07:24]There you have it. And it says the VLOOKUP looks for a value in the left most column of a table and then returns a value in the same row from a column you specify. By default, the table must be sorted in ascending order.

[00:07:41]Now looking at my table, it's not sorted, but I can tell you it's going to work. All right, so let me go again. So equal VLOOKUP open bracket. Now the first thing that we're going to add to our argument remember what comes between the brackets we simply call them the argument. So it first ask us for the lookup value.

[00:08:04]According to previous lessons that I have taught you should already know that the lookup value is actually the value that you know and want to return a value to match it. Right? So the lookup value I'm going to use would be this port name.

[00:08:22]So therefore I have two options. I can simply type in the cell address for the sport name for the first person which is C3 or I can simply just click on it.

[00:08:35]All right. It now ask us to put a comma now asking for the table array. The table array in this situation would be the table that has the that actually holds the value you want to return. So table that has a value would be the sports category 2 table or sheet. So I'm going to go ahead click on that section and these are the sport names or the sport names and the values. So I'm going to just highlight that section only.

[00:09:09]So after the table array, it asks for a column index number. Before I go back to the spring members sheet, I'm just going to point that out. This is the same table that we're referencing, right? And we're going to put the column index number as the column that has the value we want to actually return. So remember that each column is represented by a number in this situation. So a would be one and b would be >> so we're going to enter two because two would actually be the column index number. Let me go back to this sheet.

[00:09:49]And if you look carefully, >> mommy, >> we would realize that the two is right here, >> right? It's now asking us for the range lookup and according to V4 because we have V1, V2, V3, and V4. So V4 would actually be the range lookup and the range lookup you're referring to is actually a true value.

[00:10:18]A true which returns the approximate match and false which returns the exact match. In this situation, we're going to use false because we want every member to be charged according to the the membership cost. So we don't want some persons to be paying 10,000, some paying 9,000, some paying whatever. So we want everybody to be on the same page and we have the same cost. So we use false.

[00:10:47]After closing the table, after closing the function, we're going to press equal. So we're going to just double check a bit to see if this worked because it's saying that the membership cost for basketball is 10,000.

[00:11:04]And it did work. So we're going to check football as well. So let us just fill this function.

[00:11:13]All right. Now if you look carefully, 16 persons weren't given a value for their membership cost.

[00:11:25]Reason being the function that we entered is a it has relative cell addresses and if we're going to apply the same charge for each person we're going to en we're going to have to ensure that we fix the function so it doesn't change and in doing so we apply what is known as the absolute cell referencing and um what doesn't work is the actual values so where I we're going to have to look back at the function to see exactly where we need to to anchor or have it not change. So, it's in the table array, which is right here.

[00:12:08]So, what we're going to have to do is to anchor the table array so it doesn't change. So, we're going to use the dollar sign. And remember, the dollar sign is what we use for absolute cell referencing. So, we're going to put the dollar sign before the column letter and also the row number.

[00:12:24]So, we have dollar sign A, dollar sign two, and we're going to have dollar sign B and dollar sign five, right? And remember, your functions can be edited after you type them. All you have to do is to go to the function bar at the top right here. So, let me see if it works. So, I'm going to just simply autofill just to double check. Tada.

[00:12:52]There we go.

[00:12:54]the function actually work.

[00:12:57]All right, what's next? Now, it's asking us to populate the fees paid column.

[00:13:06]In doing so, it says this is dependent on the membership type. If it is weekly, the membership cost must be divided by four. So, let's reason this out a bit.

[00:13:18]If the person is of the membership type weekly then the membership cost should be divided by four otherwise the person would pay the regular fee. So if it's monthly and yes guys if if you're joining a sport club for just one week or you want to pay it in part pay payments and you're paying weekly it would be fair enough if the amount is 10,000 you divide it by four right so let's go ahead and apply.

[00:13:50]So in the face page it says equal and the function that would be most appropriate for this scenario would be the if function right equal if and we use our open bracket to begin inserting the the different sections of the function. So first we need the logical test.

[00:14:14]Now let us backtrack a bit. It says it is dependent on membership type if it is weekly. So there and then we know that we're going to have to test to see if the person is a weekly me as a weekly membership or monthly membership. If it is weekly then we're going to divide by four.

[00:14:37]So equal if membership type remember this is the membership type column hence we're working with column D. So if D3 because that's the first membership type we have. So if D3 is equal to weekly and we can't just type the weekly. We're going to have to ensure that we enclose it within quotation marks because we're using a word not a value. And it's a bit tricky because we're we're going to have to put the weekly exactly how it appears under the column for it to work properly. So, we're going to say weekly.

[00:15:16]All right. Close our quotation mark.

[00:15:20]Now, it's asking us for a comma.

[00:15:24]So, in bold, now we have value if true.

[00:15:27]And if we recall carefully, it says if membership cost is weekly, we should then divide by four. So, ends we're going to say divide by four. What are we dividing by for? It says we should divide the membership cost, right? So membership cost for this person is located in column E. Hence, we're going to put the cell address for the membership cost for that person and the cell address is E3.

[00:16:03]So we can simply type it or we can go ahead and click on it. So E3.

[00:16:08]So if it is that we're going to say E3 / 4 good and that's it for the value if true and we're going to enter the next parameter. So we're going to put our comma value if false. If it is false we're going to give the person the the original charge. Right? So we're going to put the same E3 because we're comparing.

[00:16:34]We can blindly say we don't know the membership type for the person. So, we're going to have to put both value if true or false depending on the person's membership type. So, let's go ahead and hit the enter button to see if the function actually worked. All right, there you go. So, 10,000 divided by 4 would give us 2500.

[00:17:01]Now if you pay keen attention to this function you will realize that we use all relative cell referencing meaning we did not anchor the cell address because we want it to flow and change according to um the person's membership type and the membership cost as well. So we're not going to make it fixed because we want it to change accordingly when it's copied. So, we're going to go ahead and copy it for the other person's.

[00:17:31]All right.

[00:17:32]Now, one important thing when you're doing your functions, always check to see if they work properly. So, we're going to do a bit of comparison.

[00:17:41]So, membership cost, this one is 15,000.

[00:17:45]But what's the membership type? It's showing monthly. Therefore, this person's membership cost wouldn't be affected. They would pay in full. So, we're seeing the 15 monthly again we're seeing the 20. This one is weekly and the cost is 8,000. Therefore, 8,000 divided by 2 would give us 2,000.

[00:18:07]All right. So, we're seeing now that our function it actually worked.

[00:18:12]All right. Good.

[00:18:15]Right. Let's see what's happening further. It says save this sheet as sports categories too.

[00:18:26]Oh, good. So, we now have sports categories.

[00:18:33]Hold on.

[00:18:36]Sports categories, too. And that's the sheet that they're referring to the with the table right there.

[00:18:45]All right.

[00:18:47]sprinted members sheet and sports categories too. So I have it the other way around. I'm going to have to change this.

[00:18:55]So sports categories two should be this one and the other one should be spring tech members.

[00:19:03]It's a spring.

[00:19:11]So it's N spring members.

[00:19:18]It's going to give me a height because the other one has the same name.

[00:19:23]All right. So that should be spring tech membership.

[00:19:44]sports.

[00:19:52]So the one with the sports name should be sports categories too and the other one should be spring tech members. So there you have it.

[00:20:01]All right. Right. So as you go along as you save you go to file and or you can simply use the save icon. All right. Let me continue.

[00:20:11]Oh that's it for that sheet.

[00:20:15]All right let's go to task B.

[00:20:19]Let's take it one step at a time.

[00:20:22]It says make a copy of spring tech members one worksheet.

[00:20:27]Save it as spring tech payments 2. So this should be spring tech members one should be funny and we're going to save it as sprinted payments too. So what we're going to do is to make a copy of this sheet. In order for you to do that you can simply right click on your mouse and you move or copy. Some of your applications might say duplicate. So I'm going to just click on the sheet I want to copy and then I click create a copy.

[00:21:07]All right. Now the copy normally when you copy a document it has a one or two in bracket. So this is showing that this is the other one and it should be called spring tech payments tool. So let me just double tap to change it. screen tech payments auto.

[00:21:31]All right, there we go.

[00:21:35]All right, it says now create a column called total amount.

[00:21:42]All right. So to create a new column, since we don't have all the columns for the data, we can simply just add a new add a label for the column and it should be called total amount.

[00:21:59]All right.

[00:22:02]Right. So there we have it. And automatically it's it's um the same as the others because Excel is that smart.

[00:22:12]create a column called total amount which is the sum of registration cost and fees paid. All right. Now it says it's the sum of registration cost and the fees paid. I am not going to use a function for this guys because you're also marked for formula. So I'm going to insert a simple formula. So we begin a formula with the same equal sign. So, it's going to be equal registration cost plus fees paid. So, I'm going to click on the registration cost for the first person, put the plus sign, and then click on the fees paid for the first person. And look carefully. Once you enter the cell address, it highlights it. So, registration cost, fees paid. Once you see both cells highlighted, it's telling you that you're on the correct truck. So if it is incorrect, you would know because it would be showing other highlighted cells.

[00:23:10]All right. Now I hope you remember why we have the number signs right here.

[00:23:17]It's simply telling us that we need to widen the width of the column for the value to show. Right? There we have it.

[00:23:25]Since it's a simple formula using relative cell referencing, we can simplify and fill. Don't panic.

[00:23:35]Simply I like the widen the width of the column again.

[00:23:41]All right, moving on.

[00:23:43]It says create Mr. Jones will issue a discount of 15% for members who have a monthly membership.

[00:23:52]Oh, create a field called discount allow. So, we're going to create another field and it should be called discount allow.

[00:24:07]All right, there we have it.

[00:24:10]Wait a bit. So, discount allowed and let's read again. It says Mr. Jones will issue a discount of 15% for members who have a monthly. So if you look carefully or recall uh when we were using the membership type we use something similar to this except that we're not dividing by four.

[00:24:32]We're simply going to insert yes or no if the person has a monthly membership.

[00:24:40]We're not yet applying any discount.

[00:24:44]We're just gonna state whether the person has monthly membership. We return a yes. If not, we're gonna put no. So, let's go.

[00:24:55]Again, we have the equal sign name of the function. So, equal if open bracket.

[00:25:02]So, the logical test. What are we testing? We're testing if the person has a membership type that is monthly, right? But we're testing for the first person. Hence, we're going to put the cell address for the first person's membership type, which is D3.

[00:25:22]And we're going to perform a logical test to find out if it is monthly.

[00:25:28]Therefore, when we're going to use the quotation mark and we're going to type the word monthly just how just as how it appears right here. So, we're going to put monthly.

[00:25:39]All right. There you have it.

[00:25:44]close quotation mark. It's now asking us for a comma. So value is true and according to the question it says if it is monthly we're going to return a yes otherwise no. So we're going to put it exactly how it appears. So quotation marks and be yes close quotation marks comma value if false will be no close quotation mark close bracket.

[00:26:14]There you have it. So we're going to test to see if our function actually worked. So if the person has monthly it's going to be yes. If it is weekly it should have no. So going across right here, we realize that no discount will be applied for Josephine because that's a weekly membership type. Now let's autofill to see if it work for others.

[00:26:39]So here we have monthly. But did it work? No, it simply mean I made a simple simple mistake. So it's M O N T H L Y.

[00:26:49]M O N T H L Y. So yes, no.

[00:26:57]So this should be D4, D3, D4.

[00:27:04]So there's an issue with the function.

[00:27:06]Things like this do happen, guys.

[00:27:10]D3.

[00:27:17]And if we remove the quotation marks, it's not going to work.

[00:27:21]I'm not sure why this is giving us error cuz it won't work. But I'm going to just go ahead and start it over. So equal if open bracket D3 monthly.

[00:27:45]Yes.

[00:27:53]I should probably this work is a problem right here.

[00:28:23]There has to be a problem.

[00:28:41]I'm not seeing where there's a problem with the function.

[00:28:57]There's nothing wrong with the function, guys. Not sure what's happening. I'm going to just copy all the weekly and put them as weekly because if it worked for one function, it should have worked for the other.

[00:29:11]Let me just copy this monthly as well.

[00:29:18]Just place this right here. So, see, it's worked. M O N T H. But I spelled it the same way.

[00:29:27]See guys, this thing is so case sensitive.

[00:29:30]So, I'm going to have to replace all the monthly with monthly.

[00:29:36]And if you look carefully straight across, you realize that they're changing. And I'm not sure as well.

[00:29:42]And then I have to manually do this for the function to have two here, one here, one here.

[00:29:54]All right. So monthly should now show.

[00:29:56]Yes. All right.

[00:30:00]All right, guys. There you have it.

[00:30:03]And you have to have confidence because if I wasn't confident enough, I would say something is wrong with my spreadsheet, but it seem as if there was a slight difference with the word. Probably one had a space and one didn't because I'm just going to simply put a space here and show you guys. It may not work. See, put a space and it became no. When I remove that space, come off of it turns yes. So it's very case sensitive and it doesn't allow for mistakes. It doesn't work.

[00:30:36]All right. Next question.

[00:30:40]It says create a vehicle discount amount. Use a appropriate function to perform this calculation.

[00:30:48]So what we should be doing now is to apply a 15% discount. So, we're going to call this this one um discount amount.

[00:31:02]So, discount amount, right? A bit. So, we're going to use a if function again. So if how I'm going to set it now is to return apply a discount to the discount to the the um discount amount. Sorry. So we're going to have a discount amount and we're going to perform the discount on the total amount.

[00:31:44]Right? So we're going to say equal if open bracket. So if the person has a yes, they will get the discount. If it's a no, they're not going to get it because they're giving discount only to person.

[00:32:00]So if I3 equals yes, then we're going to say total amount times because it's a a discount and we're trying to find the amount matching against the total amount. So, it's going to be times the 15% and we can't just put the 15% as is. We're going to have to put it to a decimal value. So, it's going to be 0.15.

[00:32:36]So, that's if it is true. If not, we're just going to say zero because we're not giving that person a discount. All right. So, the first person has a no, which simply means that a discount is not applied for a weekly membership.

[00:32:49]Let's see if it works for the monthly.

[00:32:52]There we go. So, if the person has a monthly membership, it's going to apply the 15% discount value. And remember, it's discount amount. It's not actually the amount after the discount is applied. So, the amount that will be taken away would be the 15% which is this. And if you have a calculator, you can simply type in the the value that you have. -7,500.

[00:33:20]Right? Let me just find the calculator.

[00:33:26]So it's 15,500 times 15%.

[00:33:41]Equal So, it's 2.

[00:33:45]Let me see if I put in the values correctly.

[00:33:48]Oh, it's not 15. 17,5. Oh, Jesus. So, let me go again. 17,500 times 15%.

[00:34:02]Equal.

[00:34:04]Uh guys, I don't know what's happening with this calculator cuz right now it says 1,500.

[00:34:15]Let me plug it in properly. One.

[00:34:19]Come on. 17 5 0 0 times 15= 2625. So there you have it.

[00:34:38]All right. So that's that. My battery is running low.

[00:34:43]So we're now at uh we did that. We're now at number seven, which says calculate the overall payment, which is the difference of the discount amount and the total amount paid. So overall payment is going to be the name of our new field.

[00:35:01]Overall payment All right, there we go. Adjust accordingly.

[00:35:08]So, it's going to be the difference for discount amount and the total amount paid.

[00:35:18]All right? So, equal and difference guys in math difference is known as subtraction. So it's going to be equal and we take the smaller figure from the bigger one because we know that we can't take this this one from this one. So it's going to be total amount minus the discount amount which is this amount right here minus this amount. And we yes we're using a formula. We're not using a function.

[00:35:47]We're using a formula. say it's equal the sale address for the 5,000 minus the cell address for the zero. All right. So this person didn't get a discount.

[00:35:59]Therefore the overall payment the person should make remains as the 5,000 and we auto for everybody else. All right. There we go. Let me just adjust this. All right.

[00:36:18]So, we're now at number eight.

[00:36:26]Yes, my battery is low.

[00:36:35]Trying to find charger.

[00:36:41]There we go.

[00:36:56]Here we go.

[00:37:14]All right. So, we're now at number eight. It says, "Enter function to count the number of members who are giving a monthly discount."

[00:37:24]So, enter function to count the number of members who are given a monthly discount. Now, who remembers which function we use to count sales that has a value?

[00:37:41]So, we're going to use the count function and anything that you're doing on your SBA, ensure that it is labeled. So, in order for us to count the members, what we can simply do is to insert a function to count them. But I'm just going to simply put my label at the bottom here to see total members with uh let's go again with a monthly discount. Oh, monthly discount.

[00:38:17]So no, we're not going to use the count function. We're going to have to use the count if because we're counting according to we're counting according to a particular criteria or condition.

[00:38:39]Just adjust this.

[00:38:44]All right. I want the discount and I'm going to put it right here cuz I want it to be shown according to the discount allow me across all right here. Good. So, we're going to say equal count if and we're counting if this right here, this range, we're counting if the entire range has yes. So, the range is there. Now, we're going to put the criteria, and the criteria is yes.

[00:39:27]And we're going to put it in quotation, guys, because it's a label or a word.

[00:39:31]So, it's telling us I have, let me just backtrack a bit. Says count if I3 to I22 because we began at three. Remember the first person is in row three. The last person is in row 22.

[00:39:49]So therefore it's I3 to I22.

[00:39:54]And we're counting only the yeses. So we put the word yes. After that we close enter. So we have the total amount of persons being 11. Want us to do that manually. Okay.

[00:40:10]So we have one, two, three, four, five, 6, 7, 8, 9, 11.

[00:40:19]All right. So that's that.

[00:40:23]We are now at number nine.

[00:40:27]Three rows below the spreadsheet. Insert the following table and perform the necessary calculations.

[00:40:35]So they're asking us to put this table three rows below the spreadsheet.

[00:40:42]Three rows. So one, two, three. And what's the label? Overall payment made.

[00:40:52]O V E R U L made. And if you look carefully guys, it spans across two columns. Therefore, we're going to have to apply the merge and center feature again for it to span across our tool. We're going to apply our feature or formatting again because we want our labels to stand out or the different sections to stand out.

[00:41:22]And we have total basketball payment and we're going to populate this section. So let me just go ahead by typing in the first total basketball payment right and you have to type it just as how you have you have to type the sports name just as how they appear in the top part.

[00:42:00]So I did not separate my basketball.

[00:42:04]So I can't separate it right here. So the way you begin, you have to just continue that way.

[00:42:11]So all of this now should be in one column. I'm going to have to expand the column.

[00:42:22]Right. I'm just going to copy this because I know it's going to be for all four sports. So, I'm going to duplicate it. I'm going to copy it four times. So, I have basketball.

[00:42:35]The next one is going to be badinton.

[00:42:46]Badminton. I think it's swimming next.

[00:42:52]and then football.

[00:43:00]All right, there you have it.

[00:43:03]And it says that we should populate each section.

[00:43:08]And then there's another one, average payment mode.

[00:43:21]All right. Now, in order for us to get the total payment for each sport, we're going to have to find out what sport the person plays and the total amount that is paid and the overall payment made by each person.

[00:43:42]Yeah, overall payment we need to figure out how much each sport speed. Now, based on the scenario, we're going to have to sum for each sport. However, we can sum according to a criteria as well. And we use a function that is known as sum if.

[00:44:11]First time hearing it. It says the sum if function adds the cells specified by a given condition or criteria. So equals sum if open bracket. It's now asking us for a range.

[00:44:28]Now the range guys is the range for what you know. Again we already know the different sports. So, we're going to highlight the range for the sports followed by the comma. It's now asking us for a criteria. The criteria is basically going to be the sport that you're trying to find the sum for. So, the first sport is basketball.

[00:44:58]You're going to have to type it exactly how it's seen. And then the sum range is going to be the range for the overall payment. So my overall payment is right here.

[00:45:12]All right.

[00:45:14]And that's it. We close our bracket.

[00:45:19]Now it's telling us that we don't have a sum for basketball.

[00:45:25]We're going to have to double check that.

[00:45:30]Let me just copy the word basketball from here or check to see if there's a space after the name because trust me, if there's a space, it doesn't work.

[00:45:44]So, there isn't a space there.

[00:45:48]I'm just going to copy to ensure that it's the exact thing I'm using while working.

[00:45:56]I'm just going to put it right here.

[00:46:01]Still didn't work.

[00:46:09]And this you're going to have all of these headache throughout the SBA, but it makes you a bit more confident and you learn things better.

[00:46:23]So we have how many basketballs? Four.

[00:46:27]All right.

[00:46:30]It's saying zero for all of them.

[00:46:36]So let me go again.

[00:46:39]This should be in quotation marks people. How did I even forget that? So you have to put the words in quotation marks and that's the reason why the function wasn't working. So there we have basketball. Now we need to do it for badminton. But because it's a different sport because it's a different sport, we're going to have to use we don't use the autofill.

[00:47:04]And if you do, you can simply just change the name of the sport. So this is no longer a basketball. It's bad.

[00:47:15]All right. So we have badminton.

[00:47:18]Make this swimming. All right. So, that's swimming.

[00:47:26]Swimming didn't work because we need a quotation mark.

[00:47:32]Here you have it.

[00:47:34]This is the football.

[00:47:41]>> All right. There we go.

[00:47:44]We have the total for each. Now it's asking for the average payment made. So to get that we just going to simply average the overall payment for each sport. So it's going to be equal average bracket and our range is going to be from B27 to B30.

[00:48:06]All right. There we go. Now let's format this because it's going to ask us to do that. See, it says center all column headings and format all monetary values for currency with two decimal places.

[00:48:22]So, we're going to have to center all column headings and then format to currency with two decimal places. So, all of these that we've been centering thus far, that's what they're referring to. So, all of these have to be centered.

[00:48:37]All right.

[00:48:41]And we should change this to currency two decimal places. So this is currency.

[00:48:48]And behind the decimal point you're seeing two values. It simp means it's two. If you're not seeing that on your end, you can simply go to the number formatting section. We use the backward arrow to increase. And you can see the zero is getting more as I increase. And use the other one for decrease. So we need just two. And that's that.

[00:49:11]All right, there we have it.

[00:49:17]Now, always remember that it says that we should merge and center the heading across all columns used in the spreadsheet.

[00:49:24]And we're going to have to edit this so it spans across all the column headings.

[00:49:31]Just like that. Let me just minimize this so you can see my spreadsheet a bit more.

[00:49:37]Right.

[00:49:39]There we go.

[00:49:43]Right. I'm going to move on.

[00:49:47]Now we are t C.

[00:49:50]It says make a copy of the spring tech payment to worksheet and save as spring tech sports block three. So spring tech payment tool which is this one. So we're going to have to make a copy.

[00:50:04]And you should know by now know how to make a copy. So we create a copy.

[00:50:11]All right. And remember the copy normally have a number in bracket. So this is the original right here. And this will be the copy.

[00:50:19]And that's what the one that we're going to read be. It should be called spring sports club 3.

[00:50:26]So, spring tech sports club 03.

[00:50:42]All right, let's see what they have for us to go.

[00:50:47]It says, "Sort the worksheet in descending order. First by sport name, then by last name." So, we're going to sort by sport name first. So, we're going to highlight all the sports names in the sheet and we should sort in descending order. So, we go to the sort and section, sort and filter section and choose seat.

[00:51:15]All right. And we always expand because we want the values to move with the person's information. Right.

[00:51:23]Now, it's saying to do this, all the mercers need to be the same size.

[00:51:29]So, what I'm going to have to do is let me see if I insert a new row if it's going to remove that error.

[00:51:43]All right, it did not. So, I'm going to have to just I'm going to have to unmerge this first.

[00:51:53]And I'm going to have to unmerge here as well.

[00:52:01]And then I'm going to sort it doesn't allow you to merge to sort.

[00:52:11]All right, let's see. All right, here we go. So, it's sorted. So sort by sport name and then by members last name.

[00:52:23]So we go to members last name and we're going to also sort in descending order.

[00:52:30]So sort right there we have it. So let me just go ahead and merge back these cells.

[00:52:41]Merge and center.

[00:52:45]And guys, always remember to undo.

[00:52:54]There's an error right here.

[00:52:58]All right.

[00:53:04]Let's see what's next.

[00:53:06]It says, "Use the advanced filter with a criteria to find all members who have signed up for weekly membership. The results should be clearly shown. Three rows below all existing data. So, we're going to go three rows below everything on the spreadsheet. And we're going to put the label all members who signed up for weekly membership or we can simply say members with weekly membership. Doesn't matter.

[00:53:35]So, three rows below which is one, two, three, we're going to say members.

[00:53:42]Put it in all caps.

[00:53:46]with weekly membership.

[00:53:53]And I'm going to, if you realize, I love to put my labels big and bold so they can actually see them.

[00:54:01]Which column did we stop? Oh, it's K.

[00:54:06]We want this all the way over to K.

[00:54:11]It's right here. We're going to apply the color and we're going to merge and center. Let me just make it bold as well.

[00:54:25]Change this time 12 times 12 as well.

[00:54:37]All right. So, let's begin. It says that we should use a criteria along with advanced filter. So what's going to be the criteria, guys?

[00:54:48]Based on the question, it says weekly membership. So we're going to have to make that our criteria. And we are going to label the area for criteria, of course, because we love to show exactly what we're doing. So, our criteria is going to be membership type.

[00:55:13]We're going to copy that. We don't type it. We're going to copy and we're going to use weekly.

[00:55:25]So, we're going to use weekly.

[00:55:28]I'm not going to type that either.

[00:55:38]We're going to we going to just copy it.

[00:55:42]Membership should be weaker.

[00:55:44]All right. These are edings.

[00:55:47]So, we're going to center them.

[00:55:52]Right there we have it.

[00:55:57]All right.

[00:55:58]Now to apply the advanced filter we have to go to the tab data. So once you go to data we use advanced.

[00:56:15]All right. So we're going to have to set our parameters. And please remember you use copy to another location. Do not filter the list in place because if you do so, everybody with a monthly membership will be deleted and we don't want that. So, we copy to another location.

[00:56:34]Our list range is going to be the table with everyone's detail at the top starting with the labels. So, we go across, we go down. So, our range is from A2 to K22.

[00:56:50]Now, what's our criteria? It's the information that we just copied right here. So, it's going to be the label.

[00:56:58]Don't do don't do don't do don't do don't do don't do don't do don't do don't do don't do don't do don't do don't do don't do don't do don't do don't do don't do don't do don't do don't not include the label that you typed criteria. You're going to select the label membership type and weekly because that is the actual criteria and you're going to put the information.

[00:57:12]Let's say we skip a line. We're going to put the copy to location as just go back here.

[00:57:20]Change.

[00:57:22]All right. So, the copy to location, let's put it at 39.

[00:57:27]Press okay. All right. Let's see if it worked because what we should have here is only persons who have weekly.

[00:57:43]Why do we have a yes here?

[00:57:48]We shouldn't have a Who is that person?

[00:57:58]So G should have a yes.

[00:58:02]Let's see G is G is weekly.

[00:58:10]Why is there a discount here?

[00:58:14]Okay, good.

[00:58:15]>> So, let's see if it was changed.

[00:58:19]This should be changed.

[00:58:23]I'm going to have to go over.

[00:58:26]There was an error right here. I did not realize.

[00:58:32]All right, let's go again.

[00:58:38]All right. So, we go to data advanced. I always copy to another location. The list range is the same K2 to K22. The criteria is going to remain the same.

[00:58:52]A36 and A37.

[00:58:55]And our copy to location is going to be 39, of course. All right. So, let's see if it worked. So, it's still showing gifts, but it's now showing a note.

[00:59:04]Excellent. All right. So, that's that.

[00:59:07]So, nobody with weekly should receive a discount. And that's shown.

[00:59:13]So that's excellent.

[00:59:16]All right, how far are we? We are now at 13, which is a pivot table. It says, "Use the pivot table feature to show how much money is generated for each sport. Name this pivot table sheet as sports total."

[00:59:33]So we're going to create a pivot table to show how much money is generated for each sport.

[00:59:39]Uh so that information is listed on the same table right here. So this is the total payment for each sport.

[00:59:51]So we're going to uh highlight the information. Now we we're going to use we're not going to include right here. If you want to put a particular heading for each column, what you can do is to simply go ahead and put your heading. So I'm going to put sport type and you can delete this afterwards.

[01:00:13]Just putting this to have the headings for the chart. And I'm going to put total.

[01:00:23]All right. So, I'm going to highlight this information right here. We don't want average either. So, we're going to just use the first four. We're going to go to insert tab.

[01:00:35]And it says that we should use a pivot table.

[01:00:43]Right? So let's go. We go insert and we choose pivot table.

[01:00:49]Now according to the question, it says that we should put the pivot table on a table called sports total five.

[01:00:57]Therefore, we're going to have to put it on a new sheet and name it.

[01:01:02]So we should call this sheet sports total 05.

[01:01:11]All right. Now, after we do that, we need to select the columns or the rows that we want to use. So, we're going to use these two labels.

[01:01:22]All right. And there we have it. We have the different sports and we have the totals.

[01:01:32]So, we're going to put sports.

[01:01:41]Let's get it going. All right, there we have it. And that's the pivot table, guys.

[01:01:48]All right, so let's see the next question.

[01:01:52]Uh, generate a column chart showing the comparison of the overall totals for each sport. So, they're using the same thing again. Include a chart title, data, labels, and legend.

[01:02:04]So, we're not going to insert a pivot table. This time, we're going to insert a chart. So, let's highlight the information again.

[01:02:12]We go insert and we use a column chart.

[01:02:17]Going to choose that one. If you realize it's blocking the data on the sheet. So, we're going to move the chart and uh we're going to move it to the same page with the pivot table because it didn't tell us to put it on a new sheet. So, we're just going to just use the same table for both for both diagrams or both um tables.

[01:02:45]All right. So, let's put it at the top.

[01:02:49]Wend this a bit. Now when your table look like this guys, you can't create a legend successfully though it seeming a bit confusing.

[01:02:58]It's best for you to change the color for each bar.

[01:03:02]So I'm going to go ahead and fill each bar with a different color.

[01:03:08]I'm using the theme purple and yellow thus far.

[01:03:12]So you when you tap on it one time once the bubbles are showing for just one bar or one column it simply means that you're working with that column only. So I'm going to change this one to yellow.

[01:03:25]I'm going to change this one to green.

[01:03:32]And then I'm going to change this one to let's say red.

[01:03:39]Uh All right. Let me see something.

[01:03:45]Black, black, gold, green, and red. That's more uniform.

[01:03:54]All right. So, it says that we should have a title.

[01:03:57]So, just double tap up here to get the chart title. And I'm going to call it total for or overall payment for each sport OB payment for each sport.

[01:04:17]Right now it says we should add labels.

[01:04:20]So I'm going to add the label. So you go to add chart element. I'm going to add the axis. Yes. Going to add that one.

[01:04:29]I'm going to add that one as well. So, these are the totals and these are the sport categories. I'm going to say sport names, sports name.

[01:04:53]And we were also asked to add data labels. So, we're going to add it at the It doesn't matter. You choose where you want to have it. I'm going to add mine at the outside end.

[01:05:07]And just this one a bit. I'm going to also add my legend.

[01:05:13]I'm going to put it to the right. There you have it. So, we have all the different sections labeled.

[01:05:28]Put that there.

[01:05:32]I'm going to put the text to yellow just for it to look uniform.

[01:05:38]Do this as well.

[01:05:42]Change this to yellow.

[01:05:44]And then I'm going to change the title option.

[01:05:51]All right.

[01:05:53]This one as well.

[01:05:55]change purple and the text as yellow.

[01:06:03]All right, guys. So, I have my pivot table here and I have my chart here. And if you look carefully, that's the last thing in spreadsheet. And it says that we should save the workbook as printex sports club financial statement.

[01:06:18]And I already have spring text financial statements. I'm going to have to change this.

[01:06:24]And I'm going to save it as spring tech finan spring tech sports club.

[01:06:40]Sports club financial statement.

[01:06:43]There you have it.

[01:06:45]And I'm going to save it on my desktop.

[01:06:49]Save.

[01:06:50]All right. So I have all my different sheets.

[01:06:57]There you go. So that's it for the spreadsheet section of your display.

[01:07:29]All right, guys. So, all right, guys.

[01:08:08]All right, guys.

[01:09:42]All right, guys.

[01:10:13]All right, guys.