Project 3 – Week 5 (Day 1, non-school day) – What I Did Today (sort of) – Finishing the tables

Today was a non-school day, but technically, I was supposed to have a lesson today, so I’m still going to make a post. I spent quite a lot of time trying to fix up the tables in the bookings sheet, and a bit of time on the finance. Here’s what I came up with:

Finance (updated)

Like I showed in the previous post, I used SUMS, PRODUCT, and COUNTIFS (and COUNT). I also added a total revenue area. I basically copy pasted the formula of the booking status, and then added in a SUM function. You might have noticed a small area being covered up over there. I didn’t really do much there, and its kind of in a mess. It’s supposed to be the Total costs area, but I haven’t finished it up yet. I’ll work on it tomorrow. Another thing I did was this:

Profit

It couldn’t fit in my last screen shot, so I had to make a separate one. I just copy-pasted the formulas of total costs (I already made a SUM formula in the total costs cell, but it’s part of the area being covered up) and total revenue, but then I used a difference function to show the total profit.

What I spent majority of my time doing was the tables for the booking sheet.  I spent a lot more time than I thought I would on this. The status and tutor parts of the table were especially difficult to make.

So first of all, I used the COUNTIF functions to count the adult/student/senior bookings, and then I used SUM to add them up. Nothing new there, but then it gets slightly harder. At first I was stumped on how to go about this. I was planning to do it similar to the sample works of previous people, but I had no idea how to go about it. (*note: I did not look at his formulas and copy-paste anything. I figured out how to do it my own way. I only used his layout. Only when I checked his, did I find out that that what I did was identical to his.)

Another person's work

(*note: I did not look at his formulas and copy-paste anything. I figured out how to do it my own way. I only used his layout. Only when I checked his, did I find out that that what I did was identical to his.)

I eventually worked it out into this:

my info table

What I eventually thought of was to add an if statement, so that if a certain cell was a certain number, it would show ‘not enough students.’ After a bit of trial and error, I ended up with this formula: IF(cell name)>=12,”Good To Go”,”Not Enough Students”   Let me explain. >= means greater than or equal to. The formula itself means: if (cell name) is greater than or equal to 12, ‘Good to go’ will appear. If not, ‘Not enough students’ will appear. So basically the cell with the total number of bookings would be examined, and whether or not the number in it was over 12 would determine what words would appear. I put in 12 because in the specifications, it says the class will only run if it is %60 full or more. 12 is %60 of 20, so I just added in the number 12. I didn’t use percentages because I didn’t want to complicate things. This was the simplest way.

How I Did It

I did it like that.

Then, I used conditional formatting to make it so that if it said not enough students, it would turn light red, and if it was ‘good to go’, it would turn light green. It looks more professional that way.

Conditional Formatting

I selected the rule, and then made it so that it would turn a certain color if a certain set of words appeared (which would be managed my the IF statement), as you can see above.

After that, I did the tutor thing. The reason I had to think a lot about it was because I wanted to merge two cells together and only have one sign appear when a tutor was required, not like that other person”s two cell tutor table. It was tricky because  I had to make it so that if either of the two cells had the number 12 or higher in them, it would say Tutor Required.Originally, I just highlighted the two total booking cells and then added an IF statement to make it so that I if it was either one of the cells showed 12 or above, it would show “Tutor Required.” Sadly, when I tried that, I found a bug. If the bottom Total bookings cell was 12 or higher, nothing registered and the Tutor cell would not change. I then tried using a nested if. I tried making 2 IF statements, like this:

=IF(AD8=12,”Tutor Required”,”Not Required “, IF(AD9>=12, “Tutor Required”,”Not Required”))

Of course, it didn’t work. Excel told me I entered too many arguments for the function. I spent a long time trying to figure it out. Eventually, someone helped me out a bit and showed me what was wrong with my formula. I then modified it into this:

=IF(AD8>=12,”Tutor Required”,IF(AD9>=12,”Tutor Required”,”Not Required”))

Apparently, a nested if is adding another IF statement to the value_false, or something like that. You can’t add two IF statements to one cell. I don’t quite complete understand why it’s like that though. I just know that it has to be like that. When I input the formula, it said Excel encountered an error, but it works exactly the way I want it to. Huh, strange.

Well, thats about it for today. I did quite a lot. Recently, my posts have been getting shorter, but this was quite a long post, so I hope this makes up for it. I have to start working on VBA and the welcome page in the coming days.

Advertisements

About jaoj2

Sha Tin College 10S
This entry was posted in Project 3 - Rockschool Excel Project, Projects, What I Did Today. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s