VLOOKUP PROJECT

 

PPROJECT BY MRS. BLESSING, SUCCESS AND ABRAHAM

VLOOKUP is a powerful function in Excel that searches for a value in the first column of a range (table or array) and returns a corresponding value in the same row from a specified column. The "V" in VLOOKUP stands for "Vertical," indicating that it searches vertically (downward) in a table.

VLOOKUP: Searches for a value in the first Colum of a table and returns a value in the same row from another Colum. to use it, type = Vlookup (“followed by the value to search for, the range of the table, the Colum number to return the value from, and the FALSE for an exact match. The “V” in vlookup stands for ‘’vertical’’, 

Indicating that it searches vertically (downward) in a table.

                                 How to write a VLOOKUP formula            

To write the VLOOKUP formula in cell F2, follow these steps:

1.     Type =VLOOKUP(

2.     Use cell E2 as the lookup value

3.     Select the range of cells B5:F17 which defines the table where the data is stored (the table array argument)

4.     Insert 5 as the col_index_number argument as we are looking to retrieve data from the 5th column from our table

5.     Choose Exact match for the match_type parameter. Remember, this corresponds to FALSE in our formula.

6.     Type ) and then press Enter to complete the VLOOKUP formula.

THE USE OF VLOOKUP FUNCTION SYNTAX & ARGUMENTS

There are four possible parts of this function:

=VLOOKUP (search value, lookup table, column number, [approximate match] )

• Search value is the value you're searching for. It must be in the first column of lookup table.
• Lookup table is the range you're searching within. This includes search value.
• Column number is the number that represents how many columns into lookup table, from the left, should be the column that VLOOKUP returns the value from.
• Approximate match is optional and can be either TRUE or FALSE. It determines whether to find an exact match or an approximate match. When omitted, the default is TRUE, meaning it will find an approximate match.

 How to use VLOOKUP function in Excel

The first questions I hear from people are “how does VLOOKUP work?” and “how to do VLOOKUP?” The function retrieves a lookup value from a table array by matching the criteria in the first column. The lookup columns (the columns from where we want to retrieve data) must be placed to the right.

How to use Vlookup Formula

          I know that we have given you a formula before, but VLOOKUP have different formula so, this is the second one:

1)    Select a cell  ( F3 )

2)    Type (=VLOOKUP)

3)    Double Click the vlookup command

4)    Select the cell where search value will be entered ( B3 )

5)    Types(   ,   )

6)    Mark table range ( B2:D10 )

7)    Types(   ,   )

8)    Type the number of the column, counted from the left (  2  )

9)    Type true (  1  )

10)   Hit enter.

Let have a look at an example use the Vlookup. Function to find the Pokémon names based on their Id#:

STEP 1

STEP 2

STEP 3

STEP 4

STEP 5

T

he VLOOKUP function also has an optional fourth argument: range lookup. This can be either TRUE or FALSE. If the range lookup argument is FALSE, VLOOKUP will find only exact matches. If the range lookup argument is TRUE, or if a range lookup argument is not entered, VLOOKUP can find approximate matches. In this case, the lookup table must be sorted in ascending order by the first column in it; otherwise VLOOKUP may not return the correct value.

If the range lookup argument is TRUE or omitted and the lookup value does not appear in the first column of the lookup table, but falls between two values in it, Excel will use the lower of the two values. If the lookup value is smaller than any value in the first column of the lookup table, Excel returns an error message.

        The lookup value is the value for which you want to find matching data and must appear in the first column of the lookup table, it can be a value, a text string or a call reference.

           They use of vlookup when you need to find anything in a table or a range by row. For example vlookup a price of an automotive part by the part number, or find an employee name based on their employee ID  

 THANK YOU FOR READING.s

PROJECT ON SUMIF FUNCTION

The sumif function, one of the math and trig functions, adds all of its arguments that meet multiple criteria.

CREATED BY PECULIA AND FARIDAH

For Example: you would use sumifs to sum the number  of retailers in the country  who (1) reside in a single zip code and (2) whose profit exceed a specific dollar value

        The sumif function is a premade function in excel, which calculates the sum of values in a range  based on a true or false condition.

FORMULAR OF SUMIF IN EXCEL

For example the formular =sumif  CB2-B5,”john”(2.C5) sum only the values in the  range (2.C5, where the corresponding cells in the range B2:B5 equal (“john “ it is typed =sumifs. =sumifs (sum-range –criteria-range1, criteria [criteria –range 2,criteria 2 ]…)

                                 

FUNCTION OF SUMIFS ON MIRCOFSOFT EXCEL

For example =sumif(A2:Aa,B2:Ba”A*,C2:C9” “T0 ”)will add instanas with the name that begin with “TO and “ends with a last letter that could vary understand the difference between sumif and sumifs. The order of arguments differ between sumif and sumifs.

HOW DO YOU USE SUMIF FUNTIONS IN A TABLE

1.   Prepare a table with multiple column

2.   Determine the values you want to add up

3.   Select  Your First Criteria and range

4.   Add other Criteria and range

 

 

The sumifs function is premade function in Excel, which calculates the sum of a range based on one or more true or false condition. It is typed= sumif =sumif(sum-range ,criteria-range1, criteria1,[criteria-range2, criteria2 ]…)

TABLE ON SUMIF FUNCTION IN MICROSOFT EXCEL

ORDER	DATE ITEM	STATE	TOTAL
1001	1-JAN SHORT	CA	$32,00
1001	1-JAN HOODIE	CA	$34,00
1002	2-JAN T-SHIRT	CA	$18.00
1003	3-JANT-SHIRT	CA	$18.00
1004	3-JAN SHORT	OR	$32.00
1004	3-JAN HOODIE	OR	$34.00
1004	3-JAN HAT	OR	$16.00
1005	4-JAN SHORT	ID	$32.00
1005	4-JAN HOODIE	ID	$34.00
1006	5-JAN T-SHIRT	CA	$18.00
1006	5-JAN T-SHIRT	CA	$18.00
1007	7-JAN T-SHIRT	CA	$18.00

 

ITEM	TOTAL
SHORTS	$288.00
HOODIE	$850.00
T-SHITE	$342.00
HAT	$288.00
SANDALS	$336.00

 

Sumif function can work in excel like

If  you want, you can apply the criteria to one range and sum the corresponding values in a different range.

FOR EXAMPLE, the formular

=sumif (B2:B5,”JOHN”,C2:C5) SUM only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal “JOHN”

The sumif function most useful to calculate total sales of a specific product, sum expenses for a particular category, or determine the total quantity of item sold within a certain region

The sumif function is a common widely used function in excel and can be used to sum cells based  on dates, text values, and numbers sumif supports logical operators such as (>,<,=)and also wild card(*,?)     

THIS IS THE END OF SUMIF PRESENTATION

THANKYOU!

CREATED BY:

PECULIAR AND FARIDAH

DASH BOARD CREATION (EXCEL) BY AJAYI GBEMISOLA

  

AJAYI GBEMISOLA  

  

 WHAT  IS A DASH BOARD

A dashboard is a visual display of key information, usually in the form of graphs, charts, and widgets, design to provide a quick overview of data relevant to a particular goal or business process. It’s like a control panel for monitoring and managing various aspects of a system or organizations.

 

STEPS NEEDED IN CREATING A DASHBOARD?

1.     Define objectives: Determine the purpose of the dashboard and what key metrics or information it should display

2.     Gather data: Collect the necessary data from various source such as data base, spreadsheet, e.t.c

3.     Clean and process data: Prepare the  data for visualization by cleaning it, transforming it, and aggregating it as needed.

4.     Choose visualization tools: Select the appropriate tools and software for creating the dashboard such as excel, table, powerBI, or custom coding.

5.     Design layout: Plan the layout of dashboard, deciding which charts, graphs, tables, and other element will be included and how they will be arranged.

6.     Create visualization: Develop the individual visualizations based on the chosen layout or data, ensuring they effectively communicate the intended information.

7.     Add interactivity: Enhance the dashboard with interactive features like filters, drill-downs, to allow users t explore the data more deeply. Test and iterate: Review the dashboard for accuracy, usability, and effectiveness, making any necessary adjustments based on feedback or testing

8.     Deploy: Publish or deploy the dashboard to the intended audience, whether it’s through a web portal, a standalone application or integrated within existing systems.

9.      Maintain and update: Regularly maintain and update the dashboard to ensure it remains relevant and continues to provide value over time. This may involve adding new data sources, adjusting and visualizations or incorporating feedback from users.

 

HOW WE CAN CREATE A DASHBOARD (STEP BY STEP GUIDE)

When creating a dashboard, we need three (3) sheets on excel and that is:

1.     DATASET

2.     PIVOT TABLE

3.     DASHBOARD

NOTE: This is the data set we will be working with.

Step2: Turn the data set into table

NOTE: Highlight your data set, go to insert, click on table to turn your data set to a tabular form.

This is how our data looks like when we turned it to table

STEP3: Go to insert and click under pivot table and click on new work sheet

This is what you should be seeing if you are doing it right.

It will automatically take you to this pivot sheet and you can rename your sheet as “PIVOT”

STEP 4 : After we open our pivot sheet, we select or choose field to add to report, then it gives us the pivot table and we copy and paste under because we are still going to be using it. Highlight, go to option under pivot tools and click on pivot chart. Select any chart you want to use and click on OK. It will automatically bring out your chart

This is the first chart, to determine the next chart, we highlighted our pivot table again, paste it under as shown on the table above then we move to change my” field to add to add to report” and we selected STATE AND TOTAL NUMBER IN STATE so as to help us present it in a chart then again we highlight, under PIVOT TOOL we go to option then select pivot chart. Then again you select any chart you want. Below is the representation of what we are talking about.

After this, it is assumed we have a self explanatory chart. The next thing is to copy our chart and  move it to a new sheet and we can now rename it “DASHBOARD”. After copying our chart to our dashboard, we can now go ahead and format our chart

 

This is how our dashboard looks like, presentation of our two charts under the same sheet. If you notice, you can tell that the heading of the first chart is different from the second, the reason is because we have edited the first one and to edit the second, we click on the” TOTAL” and edit it to whatsoever heading we want

CONCLUSION: There are few things I want us to observe from our dashboard, if you are not conversant with the table, you might not understand the chart and that’s why we can as well label our axis for better understanding

As clearly shown with the aid of the arrow, we can see that our chart as been properly labelled and it can now be easily understood by anyone even without seeing the table. To insert the axis, you go to layout under your pivot tools, click on axis title then for vertical, you select primary vertical axis title then you get options of how you want your vertical title to be presented. You can go ahead and repeat for the horizontal axis just that under your axis title, you select primary horizontal axis to label your horizontal data.

NOTE: The census dashboard provides a comprehensive overview of the country’s population, name of citizens, age of each citizen and total number of each citizen in the state.