Task 1 (P2) .com



Unit 42 Task 1 (P2) Implementation of Multiple Worksheets (with links)- I used multiple worksheets for keeping the record of my comics’ sales. I created 3 tables; Sales, Income (revenue) and Expenses + Profit. Then, I created two more tables for their graphs like Sales graph and Income graph table, the reason why I put the graphs in a separate worksheet is because it makes things easier for you to understand and also, this way you don’t have too much data on one worksheet. And I also created some links between them by putting formulas in the cells of those worksheets. Also, I created a table for the Main menu where I put all the hyperlinks for navigating the other tables. To create a main menu, I clicked on the hyperlink button in the Insert Tab and it opened up a window (the one showed above Insert Hyperlink). Then, you click on the Place in this Document and the worksheet you need to make a hyperlink plex formulae (at least two-step process)- I used complex formulae in my worksheet Income to work out the prices of comics according to the cover price. To get the income price according to the cover price, I linked the sales value from the sales table and multiplied it by the drop down box so, if you change the price in the drop down box you will get the new value according to that cover price you chose.(To create a drop down box, I created a list of prices that I wanted on the same worksheet then, I clicked on the data validation and chose the option list to create a drop down box / list and then, I clicked on the source and highlighted the list of prices in the same worksheet and pressed enter and this is how I created a dropdown box for the cover prices).Large data sets I used in my spreadsheet-I used large data sets in my worksheet to work out the profit and loss of comics and in overall business according to month and year and for that I imported the data from my assignment document. I also created the total yearly of all comics, max, min, mode and monthly sales for all the comics and also, monthly sales for each individual comic. Cells Linkage-I linked cells in my table to get the values for my incomes worksheet. To link the cells, I summed them and to do that I put equals sign and then, clicked on the sales table and then multiplied it by the value in the list cover price B1. So, when you click on one of those prices all the value linked to it will change.Drop down boxes-I created the drop down box with list of cover prices for April to February and separate for mar 14 because the value in March is 70% more.To create a drop down box, I created a list of prices that I wanted on the same worksheet then, I clicked on the data validation and chose the option list to create a drop down box / list and then, I clicked on the source and highlighted the list of prices in the same worksheet and pressed enter and this is how I created a dropdown box for the cover prices.Data Validation- I used Data Validation in my worksheet so, I don’t put numbers too big or invalid value for example: in picture above which is from my sales worksheet, I put data validation for my comics so the values are 0 to 25000 no bigger than that and I also put stop in error settings so no matter what it would not let the user put that value and I put “this value is not valid” for the message!. To test that, you can see in the second picture that when I tried to enter bigger number it didn’t let me and gave me error saying that the value is invalid and it was stop so I could not proceed. If I put the warning then, it would let me proceed giving me an option yes or no.Task 2 (P3)What SUMIF, AND, OR and NOT statements-I put what IF statements in my sales table and income table to work out if the sales were below target or on target and to do that I put the formulae in like =IF then, the cell and >= the value and what it would be like good, bad, on target or below target etc. I also put the conditional formatting like green for the on target sales and red for the below target. To set the conditional formatting, you click on the conditional formatting and then, on the text if you are choosing text like I did on mine or you choose the greater than or less than for the values as you can see in the picture above.Relative reference (where you don’t use $ to lock the cells)-By default, the cell reference is relative reference (a reference in which you do not use $ in the formula to lock the cell and to use this reference you just write the formula like SUM something in the first two cells and then drag them down to copy). So, when you use relative reference it will change the cell reference each time copied and pasted for example: if you used relative reference in and you formula in D3 cell which was (B3-C3) and then, you drag it to copy it will change like in D4 it would be (B4-C4) instead of (B3-C4).Absolute reference (where you do use $ to lock the cells)-In excel the absolute reference is not set by default which means you have to put the formula to set it and to do that you use a dollar sign $ and you use absolute reference to lock the cells so, the reference for that cell doesn’t change and stays the same in each cell. For example: if you put (C1: D$1$) in cell C12 and copy it the value be the same like (C2: D$1$) you can also see the example above in the picture from my table expenses where I used it to lock the cells.Named Ranges-I put the named ranges in my tables to so, I know what the following rows or cells are for like you can see in the picture above for cell B6 to B15 I named them Author Translator or you can name yours like where it says totals they maybe for January so, you can name them january totals and to put the name range you just highlight certain cells and then, click in the small box in the right corner above row A and put the name but there are some rules like the name can only contain numbers or letters and you can’t put space but a underscore or backslash and when you put the name that’s it you press enter and to see if it works you just highlight it and see if it tells you the right name. You can do this to your values so you know what that certain values are for like for mar you can name it “Sales_Mar” something like this, it helps you remembering what certain values are for.Correct Operators- I used correct operators to perform different functions. There are four operators in excel included Arithmetic (Addition 3+1, Subtraction 3-1, Negation -1, Multiplication 3*1, Division 3/1, Percent 3% and Exponentiation 3 ^1), text concatenation (Equal sign =, Greater than sign >, Less than sign <, Greater than or equal to >=, Less than or equal to <= and not equal to <>) and comparison operator (&) and Reference operators (colon: Range operator; colon usually means between two cells, comma, Union operator; comma combines the two references and Space Intersection operator). Add-ins-Add-ins in excel are all the things you are provided with and the things that make excel easier for you to use and gives you more functions so, you can do more and different things. Add-ins are placed on the top of your excel in the ribbon, and from add-ins you use different tools according to your needs and in my worksheet I used quite a lot like sum, average, max, min, data validation and conditional formatting etc. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download