I have always been the one to write diaries, where a notebook and pen was not too far away. However, I have been finding my filofax obsession to be far too expensive and inconvenient for traveling around. I hate saying this, but I’ve falling more in love with technology and am quickly adapting to the…
Microsoft Excel pt 2: Utilising my spreadsheet
Although my spreadsheet began as an Access database it lacks the powerful features that Excel is known for. So in this blog post I want to display the most popular features that I have used in a work environment .
1. SUMIFS feature
What is the total cost of benefit blusher?
Far easier than going through the filters
2. VLOOKUP Formula
This is a useful function for finding a specific line of information within the dataset. The following formula is the basic of it:
=VLOOKUP( this value, your data table, column number, optional is your table sorted?)
In my spreadsheet I want to find the cost of the viva glam V lipstick within my collection so:
=VLOOKUP(“Viva Glam V”,B2:I95,7,FALSE) – which returned the value 16…a quick format of the call thus then displayed $16 perfect!
The most annoying feature is that is only is able to look up in the left most column! Boo! But useful nevertheless.
3. Index+Match formulas
This method is able to include the ID numbers which are in the spreadsheet itself. In this formula I want to find the ID of Headliner by ELF. The following formula I will put into the spreadsheet.
This gives me the answer of 24….which is the ID number of course.
4. If formula
Making a decision? You can with this function
I want to find out the sum of the cost for alternate rows.
Row 1 -> =SUMIF(J2:J95,1,H2:H95) = £360.97
Row 2 -> =SUMIF(J2:J95,0,H2:H95) = £375.98
In the below image it shows that I have created a separate table that displays the product name and if it is discontinued. I know this is correct as it uses the same formula and is from the same row.
5. Nesting formulas
Nesting formulas are used to display complex questions. So i will try to do this with my limited dataset.
– Give 5% hike to Rimmel products
-Give 10% hike to Benefit products
– For the rest 2% hike
=IF(Rimmel >0, “5% hike”, IF(benefit >0, “10% hike”, “2% hike”))
6. Basic Arithmetic Expressions
The table below gives the basic operators and expressions used in Excel formulas.
7. Text formulas
As Chandoo says:
By learning few very useful TEXT formulas, you can save a ton of time when cleaning data or extracting portions from mountains of text.
– LEFT, RIGHT & MID – to extract portions of text from left, right & middle.
– TRIM – to remove un-necessary spaces from beginning, middle & end of a text.
– SUBSTITUTE – to replace portions of text with something else.
– LEN – to calculate the length of a text
– TEXT – to convert a value to TEXT formatting
– FIND – to find whether something is present in a text, if so at what position
In my spreadsheet I used =LEFT(C26,5) where the original call C26 displayed ”concealer”, but in the cell where I entered this text formula it just displayed Conce…perfect.
8. NETWORKDAYS & WORKDAY formulas
Diary management skills are important to any workplace. Throughout my degree I utilised the NETWORKDAYS function which allowed me to quantitatively view the working days I had to plan for my project. I’d say this is the most important diary management function to kick start a project alongside using the calendar feature in Outlook. The result from this meant that I ensured work was complete in plenty of time.
9. SMALL and LARGE formulas
- SMALL: Used to find nth smallest value from a list. Use it like =SMALL(range of values, n).
- LARGE: Used to find nth largest value from a list.
- MIN: Gives the minimum value of a list.
- MAX: Gives the maximum value of a list.
- RANK: Finds the rank of a value in a list. Use it like =RANK(value, in this list, order).
10. IFERROR Formula
The IFERROR formula to handle errors in your formulas.
Microsoft Excel: What is it? Brief introduction
Microsoft Excel I don’t think is taught enough during School, College and University. It is often viewed as a database, which it is not as Access is far more powerful at this; but it is used in millions of offices worldwide to hold and store and display information. Wiki provides the following definition:
Microsoft Excel is a spreadsheetapplication developed by Microsoft for Microsoft Windows, Mac OS X, and iOS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. It has been a very widely applied spreadsheet for these platforms, especially since version 5 in 1993, and it has replaced Lotus 1-2-3 as the industry standard for spreadsheets. Excel forms part of Microsoft Office.
The ability to perform calculations and create tabular with graphical data is probably why it is used over Access. My first blog posts for microsoft office were based around Access as this is the tool I found most uncomfortable to use, but I now want to use my make-up database to display tabular and graphical information of my obsession. As this is just a brief introduction and blog post I will just post around exporting my database into Access.
Exporting an Access database into an Excel workbook
It was quite easy to export to an Excel workbook without formatting. It now needs to be tidied up, but it is this file that I will use to create tabular and graphical datasets for the next tutorial.
Part two Microsoft Access: Analysing information from the ”make-up database”.
What is the point in having a database full of information if it isn’t used for analyses. Using the database that I have created I want to find out a few things.
- What is the most popular brand in my collection? How many units?
- Within that collection, what is the most popular product?
- What is the combined cost of everything?
- What is the total cost of my benefit products?
- Most popular product
Four simple questions!
Designing the query
By designing the query I need to segregate three fields out of the eight columns that I have. Four the 5 questions that I want to answer the majority of them can be answered by designing a query around company, amount and cost. The idea behind this is that the information run will display the total sum of the product units and the cost, thus answering 1, 3 and 4.
Although I already knew the answer that benefit was my most popular brand, it was nice to know that I have 24 products in my collection (Q1) with the total cost of these products :O £286(Q4). That is 38% of the overall total cost of my make up collection (£736.95……Q3)
To answer the remaining questions I need information from the product category. Going back to the design query tab I add in this option into the query and hit run
Now to refine what I wanted from the query to match on question without searching through masses of numbers I used the search function to only find benefit entries. Right clicking on the sum field allowed me to sort the field by ascending order showing that I have lipgloss as the most products within that collection which totals £25.
To answer the last question I don’t actually require the company field as the question is not specific to this. Going back onto the design query screen I removed this entry and hit run again
The following fields are displayed by again…sorting the sum field as largest first. This shows that blush is my most popular product. making up 43% of my total make-up collection Yea…I don’t think I need anymore blushes
- What is the most popular brand in my collection? How many units? Benefit with 24 products
- Within that collection, what is the most popular product? Lipgloss
- What is the combined cost of everything? £736.95 (Yes I need to change the dollar sign!)
- What is the total cost of my benefit products? £286
- Most popular product? Blush
Although my phone is ancient, I have decided to try out the wordpress app and blog when out. Right now I am at the quad cinema in Derby and have just watched a fantastic movie about a resistance fighter called georg Elber. The film is called 13 minutes and for those who are interested in…
Used in thousand of offices around the world to receive e-mail, hold contacts, input important dates and outline important tasks over all these attributes. Although I have used thunderbird in the past, outlook is the one I keep coming back to. Brief summary by Wikipedia… Microsoft Outlook is a personal information manager from Microsoft, available as a part of the…
New month and a new way of planning Re: Sticky notes, Clear Stamps and Hobbycraft ink!
When I was first drawn to the planner community I am honest when I say that I admired the care and craft people put into decorating their week (Purpley life springs to mind). Part of me wanted this , but I also realise how time consuming this can be and how the U.K. lacks all the pretty stationary supplies that the U.S.A. has (clear stamps, page flags..ect). Although, I have slowly collected clear stamps and decided to put 20 minutes aside today to actually use the crafting things I have actually purchased the last few months.
Just a quick note on sticky notes…I love them, but I feel post-it ones are over priced. I am currently using Tiger sticky notes …3 packs for a £1…bargain
So decorating pages is a bit new to me, so I don’t really have a theme for this week. The whole point of this page is to write certain goals for the upcoming week. Used a new clear stamp some washi tape (which I generally here). Now, of course I am not going to detail the most private stuff on here, but I am using the pen and paper movement to improve my hand writing. Also I make my sticky notes seem ‘cuter’ I am rounding them off with this corner cutter. The size is 10mm and works week me thinks….
Now onto the clear stamps!
Although I have been collecting clear stamps for the past few months I have never really bothered to actually use them. Main reason for this is because a diary system such as this requires on-going writing, rather than just planning. Totally get that this seems contradictory as ..well hello….I am decorating this, but when I stamped today I felt a sense of relaxation and feel my notes/appointments are actually quite clear. . . .
These stamps include a mixture of studio l2e/Dovecraft and hobby craft stamps with basic black ink from hobby craft. As I am quite ‘new’ to this, I found the whole process a bit messy. Using a clear block and a flat surface, I mounted the stamp into the block and allowed (what I thought) was just enough ink to print with, but maybe I was heavy handed with it, or the ink was shit as I don’t think the image is clear as it should have been? The below image is from the black and pink ink which was not as bad as the metallic ones (further image below).
So I tried to stamp with the bronze….yea not that great It seems like it is just printing with glitter. The gold ink was more disappointing…thinking of actually taking this back. . . .
Overall I enjoyed taking the time out, but as I journal more throughout the week I can’t promise myself that I will have the time to make it ”look pretty” as well….these things are there to help in life and not kinder.
On a positive note…I now know how to use the camera on my phone for clearer images.
It is about the time of year where I require a version of next year’s diary as there are some dates which I do plan well in advance. So an afternoon of spending and I ended up with 100x white refill sheets and the pink 2016 diary . It is fair to say that the typical diary layout was focussed on those working a mon-fri 9-5 pattern which included little space for writing activities in the week….
Much to my delight I can see that filofax have addressed the old yuppie style layout with a more equally spaced diary which includes a brief section where odd bits and bobs can be written down for the week plus….equally space day sections! Hallelujah!
Happy to see a more workable version considering how expensive each refill pack is (the pink version cost me £7.50). Crazy to see how our lives are so busy that we consider each day equally important instead of factoring in rest days. I guess that is just part of our society now though.
Gone are the days where one could waltz into a place of work and hand over a C.V. and cover letter at the end of one week and expect employment in the following. As a recent graduate I was made aware of how time consuming it is to get interviews, let alone the promise of a job….and a full time one at that! Therefore I wanted a system that tracked what jobs I researched to apply for (let’s call this processing) with the positions I have actually applied for (called applied….) which is then ”synced” with a job hunting folder on my computer. So let’s go through the steps :
Step one – research potential positions (processing)
When I first researched for vacancies the whole objective was to apply for as many positions as possible. Looking back, this was a huge mistake as it is quality not quantity. Whilst my CVs differed slightly with each job, my cover letters were quite generic and this showed in the lack of interviews I received. Added to this was that I had no idea the next day what company I applied for, nor the position. Therefore I researched a maximum of 5 positions noting the following down:
- Company name (could also be recruitment agency)
- Position applied for
- Method (e-mail, application, post..ect)
- Closing date
- Notes (misc information such as ”asked for NI number?”)
This meant that I was able to prioritise what I was applying for and focus on the specifics of the job so my cover letter could be the best it could be! Using a yellow highlighter I highlight what I was actually doing and then highlighted over with green once complete. Each cover letter and CV was then filed in a folder with the same company name along with a screen cap of the job posting. This is put in bold because if you get a call asking for an interview, then 9 times out of 10 the job positing isn’t available online meaning you would have to e-mail HR for this information.
Step two – applied list
This list pretty much reflects the processing page, but life is hectic and not all jobs can be applied for so this list reflects what applications have been sent off and the date that it was officially applied for. The following categories are in mine:
- Application method
- Date applied
- Closing date
I use a slightly different highlighting system here for various outcomes:
- Blue – Unsuccessful/no interview stage (not all companies let you know though)
- Pink – interview/special requirements (such as in-tray tests/online tests)
- Orange – Second interview.
Since using such a system I have been far more successful with gaining interviews and eventually got that job. It was also a lifesaver when being called out of the blue by companies about a position as I was able to quickly find what I applied for as I could quickly flick through the pages to find the information I needed asap. The same system could be used in Excel or Access format too.
I really love Geology! I did used to hate it in my first year at University because the mass amount of information was overwhelming, but my goodness it fascinates me when out and I feel like I have been given x-ray vision for it! I live in the East Midlands which is not too far away…