Лекции.Орг


Поиск:




Категории:

Астрономия
Биология
География
Другие языки
Интернет
Информатика
История
Культура
Литература
Логика
Математика
Медицина
Механика
Охрана труда
Педагогика
Политика
Право
Психология
Религия
Риторика
Социология
Спорт
Строительство
Технология
Транспорт
Физика
Философия
Финансы
Химия
Экология
Экономика
Электроника

 

 

 

 


Select one from a range of options




 

Tools: functions VLOOKUP, HLOOKUP

Level: intermediate

Problem

The property tax rates established by Buttercreek City Council are 0% for all real estate in zip code 88880, 0.1% for all properties in zip code 88881, 0.15% for all properties in zip code 88882 and 0.2% for all properties in zip code 88883. How does one determine the appropriate tax rate for a given property location?

Solution

Typically, an Excel user will think of a straightforward decision tree built as a nested IF statement looking like the following. Cell A1 contains a property location variable.

 

=IF(A1=88880,0,(IF(A1=88881,0.001,(IF(A1=88882,0.0015,0.002)))))

 

This formula is not the best solution, as it has several problems –

 

  • It is complicated to write, enter or edit, especially easy to miss the brackets.
  • If a condition is changed, or a new condition is added, all instances of the formula must be changed. Manually. You cannot use a range with tax figures and thresholds.
  • You can only nest a formula a limited number of levels (for Excel 2003 the limit is 7, for Excel 2007 and 2010 it is 64).
  • It is UGLY.

 

As a matter of fact, the decision tree problem quickly boils down to choosing from a list.

 

Excel contains a special CHOOSE function for choosing a value from a list. Using it will result in something like this. Cell A1 contains the number of position in a list.

 

=CHOOSE(A1,0,0.001,0.0015,0.002)

 

It is not much better, though:

  • Every argument must be entered separately and manually.
  • CHOOSE second and subsequent arguments cannot refer to a range.
  • The value is chosen by its position in the list, no criteria are involved.
  • In Excel 2003, CHOOSE can take only 30 arguments.

 

Is there a way to just type a list of zip codes and tax rates and pull out a rate?

Use VLOOKUP function with a list. VLOOKUP stand for "Vertical Lookup"

 

  1. Create a new service sheet and enter a list
  2. (OPTIONAL) Name range A2:B6 – e.g. property_tax.
  3. (OPTIONAL) Name range A1:A1 (the cell where the zip variable is located) – e.g. zip.
  4. Enter a formula: =VLOOKUP(zip,property_tax,2,0).

 

  A B
    =VLOOKUP(A1,property_tax,2,0)
  ZIP TAX
    0.00%
    0.10%
    0.15%
    0.20%

 

What the VLOOKUP formula does: it compares the value in zip (argument "zip") with values in first column of property_tax (argument "property_tax") and then returns the value in the same row and nth column of the range – in our case, it is a second column (argument "2"). The last "0" argument is optional and forces VLOOKUP to find an exact match in Column 1.

 

If for some reason your data is a horizontal, rather than a vertical range, HLOOKUP formula must be used instead. The formula syntax is exactly the same, except that, as name suggests, HLOOKUP is "horizontal" and so finds a match in first row and then brings the value from the nth row below.

 

  A B C D E
    =HLOOKUP(A1,property_tax,2,0)
  ZIP        
  TAX 0.00% 0.10% 0.15% 0.20%

 

Things to keep in mind:

 

If your list contains non-unique criteria values, VLOOKUP and HLOOKUP find the FIRST match and stop. When you set up a decision tree, make sure you criteria do not overlap and use the optional range_lookup switch wisely.

VLOOKUP searches only to the right and HLOOKUP – down. Their criteria range is always the first column and the first row, respectively. If you need to use for your criteria a row or column in the middle, use another solution. We will provide some later.

In my experience (and experience of most Excel masters), vertical ranges are much easier to use and handle, and keep in mind you have much more room in your spreadsheet down than right, too. Excel 2010 gives you only 65,536 rows by 256 columns, and earlier versions have even less.

Formula syntax

Code Variable Comment
=VLOOKUP(    
zip Lookup_value Value that you are looking for in the table_array
property_tax Table_array Table where the Lookup_value and the values that correspond to it are located
  Col_index_num Number of the column in the Table_array where the value that you want the formula to return is located
  Range_lookup Optional: 1 (default) is an approximate match, 0 is an exact match
)   Formula complete

 

Similar tasks

Task 1. You have a list of monthly average rainfall levels for Sydney.

 

  A B C
  Month mm inches
  Jan 103.4 4.1
  Feb 111.0 4.4
  Mar 131.3 5.2
  Apr 129.7 5.1
  May 123.0 4.8
  Jun 129.2 5.1
  Jul 102.8 4.0
  Aug 80.3 3.2
  Sep 69.3 2.7
  Oct 82.6 3.3
  Nov 81.4 3.2
  Dec 78.2 3.1
  Year 1222.7 48.1

 

Source: GHCN, based on 1790 months between 1840 and 1989

 

If you know the month, how do you get the average rainfall in inches from this table?

 

Solution

 

=VLOOKUP(Aug,A1:C14,3)

 

Task 2. You have a list of public debt of European Union member states (in billions of euro).

 

  A B
  EU member country  
  Germany 2,088,472
  Italy 1,897,179
  France 1,717,256
  United Kingdom 1,547,506
  Spain 734,961
  Netherlands 392,506
  Belgium 361,735
  Greece 355,617
  Austria 217,399
  Poland 192,672
  Portugal 184,291
  Ireland 169,264
  Sweden 150,484
  Denmark 111,766
  Norway 101,533
  Finland 93,030
  Hungary 72,134
  Czech Republic 60,798
  Romania 44,607
  Slovakia 29,911
  Slovenia 16,954
  Cyprus 12,720
  Lithuania 11,827
  Iceland 10,140
  Latvia 8,618
  Luxembourg 7,786
  Bulgaria 6,286
  Malta 4,600
  Estonia  

 

Source: Eurostat, 2012

 

How do you retrieve from this table the debt of Italy?

 

Solution

 

=VLOOKUP(Italy,A1:B30,2)

 





Поделиться с друзьями:


Дата добавления: 2016-11-23; Мы поможем в написании ваших работ!; просмотров: 378 | Нарушение авторских прав


Поиск на сайте:

Лучшие изречения:

Своим успехом я обязана тому, что никогда не оправдывалась и не принимала оправданий от других. © Флоренс Найтингейл
==> читать все изречения...

2378 - | 2186 -


© 2015-2024 lektsii.org - Контакты - Последнее добавление

Ген: 0.009 с.