• Microsoft Excel 2000 Free Download

Contoh Soal Excel - Kali ini phc akan sharing tentang sebuah soal remidi excel dengan hanya menggunakan rumus if dan vlookup saja. Lead shot maker. Ada banyak soal sebenarnya yang ingin saya sharing. Kebetulan karena saya mengajar di sebuah lembaga kursus komputer di jember, jadi ada banyak perbendaharaan soal yang bisa dijadikan pembelajaran buat temen-temen phc yang juga lagi belajar tentang excel. Read this essay on Microsoft Excel Vlookup & Hlookup. Come browse our large digital warehouse of free sample essays. Get the knowledge you need in order.

One piece wanted poster luffy. Apr 12, 2009 - Font for Bounty: Vrinda Type: Regular Size: 25.35. Spacing: 100. For color just choose the one from the poster(after you hide the 'Full texture'.

V & H LOOKUP FUNCTION
These functions provide a means of reading individual data values from lookup tables (also known as arrays or ranges). Excel allows you to perform both vertical and horizontal lookups by using VLOOKUP and HLOOKUP functions.
Vertical lookup
Vertical lookup Searches the leftmost column of a lookup table for a specific “Search Value” and then returns a value contain in the intersecting cell of the relevant row and relevant column of the lookup table.
Note: Function abbreviation of Vertical lookup is “VLOOKUP”
Vlookup Syntax
VLOOKUP (lookup value, table array, column index number, Type)
Lookup Value – is the search value (i.e. the item being search for in the leftmost column of the table). This search value may either be a number, a text entry (which needs to be enclosed in double quotation marks), or a cell or range name.
Table Array – is the range reference or range name for the table containing the information (ie. Lookup Table).
Column Index num –is the column number in the table containing the information that is to be returned.
Type – is an optional argument for exact match or approximate match. • If True – Lookup function searches the left most column of the lookup table for the largest number less than or equal to the search value.
Note that if you do not enter the type, Lookup function takes True type as default type.
• If False - Lookup function searches for an exact match.
Important – Before you can perform the Lookup function it is important to sort the table array according to the values in the left most column in ascending order. Important – Ensure leftmost column in lookup table contains unique values.
Hint: whenever VLookup function uses a text entry (eg. “Monday”) as its search value, its type argument must be set to False, but the left most column of the table does not need to be sorted into ascending order.
Activity 1
How to write a formula to calculate tax rate for an income level
Income Level Tax Rate
Rs.0.00 – Rs.9.999.00 15%
Rs.10,000.00 – Rs.29,999.00 30%
Rs.30,000.00 – Rs.99,999.00 34%
Rs.100,000.00 and over 40%
Activity 2
Given a product ID, how can I lookup the product’s price
Product ID Price Rs.
A134 3.50
B234 4.20
F167 5.60

Microsoft Excel 2000 Free Download

X567 5.90
R356 5.70
S123 4.20
Activity 3
Sales discounts should be calculated based on the sales amount and the selected discount plan. Four discount plans together with relevant sales ranges are given below.
Sales Plan 1 Plan 2 Plan 3 Plan 4
0 0.0% 0.0% 0.0% 0.0%
20,000 2.0% 3.0% 4.0% 4.8%
40,000 2.1% 3.1% 4.2% 4.9%
60,000 2.2% 3.2% 4.3% 5.0%
80,000 2.3% 3.3% 4.4% 5.1%
100,000 2.4% 3.4% 4.5% 5.2%
Develop a model to calculate reverent discount amounts for given sales value and discount plan.
Activity 4
Use Vlookup function for the product repair decision example in the previous lesson.
Horizontal Lookup
HLookup is identical to VLOOKUP function, except it searches across the first row of the lookup table (rather than down its first column) for a given value.
Syntax
HLOOKUP (lookup value, table array, row index number, type)
You will notice that the only deference in the syntax of the HLOOKUP function compared to the VLOOKUP function is that it contains a row index number rather than a column index number. Other things are same as the application of VLOOKUP function.
Activity 5
Suppose that a product’s price changes over time. Following details shows the price charged in different time periods. I know the date the product was sold. How can I write a formula to see the product price charged in that period?
Date sold Price
January – April 2005 Rs.98
May – August 2005 Rs.105
September – December 2005 Rs.112
January -2006 onwards Rs.120
Example 1
The file “Hr data” gives employee ID codes, Salaries and years of experience. Write a formula that takes a given ID code and yields the employee salary. Write another formula that takes a given ID and yield the employee’s years of experience.
Example 2
You are thinking of advertising your products on a sports telecast. As you buy more ads, the price of each ad decreases as shown in the table.
Number of ads Price per ad
1-5 Rs.12,000
6-10 Rs.11,000
11-20 Rs.10,000
More than 20 Rs.9,000
Example 3
Using the same data table now write a formula to calculate the advertising cost as given below.
If you by 8 ads then first 5 you need to pay 12000 per add and next 3 pay 11000 and so on
Example 4
Develop the database of your employees by using necessary information required to develop pay slip.
Develop pay slip for the employees of your company by entering employee’s number and extract the following information’s.
Name of the employee
Grade
Basic salary
Advances
Loans
OT payments
Advance deductions
Loan deductions
EPF deductions of 10%
No pay dedications
Net salary
Company contribution to EPF of 15%
Company contribution of ETE of 3%…..