Intersections of ranges IT Service

Ir a contenido Ir a Estudios, Gobernanza y organización
Logo UA
Realizar búsqueda
Menú
Services
Logo IT Service   IT Service
IT Service

Intersections of ranges

Curso avanzado EXCEL'00

 1.- Introduction

Excel uses three operators for working with ranges: colon, space and a semicolon. To see how each of them, use the following example:


A B C
1 EMPLOYEE SALARY INCEN DIET
2 María  100000 25000 15000
3 Blanca  150000 30000 13000
4 Juan  175000 10000 12000

Two items: specifies a range that goes from the first cell to the second following a rectangle. In our example, the range C2: D4 includes cells C2, C3, C4, D2, D3 and D4. For example, the result of applying the formula =SUMA(C2:D4) will 105000.


A B C
1 EMPLOYEE SALARY INCEN DIET
2 María  100000 25000 15000
3 Blanca  150000 30000 13000
4 Juan  175000 10000 12000

Area: Gets the value of the intersection. Example: If in cell A6 enter = C2: C4 B3: D3 will result in 30,000, which is the amount that intersects the range C2: C4 with the range B3: D3.


A B C
1 EMPLOYEE SALARY INCEN DIET
2 María 100000 25000 15000
3 Blanca 150000 30000 13000
4 Juan 175000 10000 12000

semicolon: Specify the union of two ranges. If you write to A6 = SUM (C2: C4, B3: D3), which is actually adding C2, C3, C4, B3, C3 (for the second time) and D3. The result will be 258,000.

2. With Names

The real value of the intersection of ranges is shown when using names. For example, if we select the table and then choose from the menu Insert, Name, Create, clicking OK in the Create Names dialog box, Excel creates the following names to automatically:

SALARY: =Hoja1!$B$2:$B$4 María  =Hoja1!$B$2:$D$2
INCEN: =Hoja1!$C$2:$C$4 Blanca:  =Hoja1!$B$3:$D$3
DIET:  =Hoja1!$D$2:$D$4 Juan:  =Hoja1!$B$4:$D$4

With these names you can create formulas easily readable. Some examples:

Space: =Incen White obtained a semicolon 30,000: =Sum(Incen;White) 258,000 is obtained.

3. Exercises
A7 Alicante Valencia Castellón Tarragona Barcelona Gerona
Toll 0 1500 3510 3587 2525 1254
Food 0 875 1875 2540 2451 1789
Sleep 0 7500 0 7500 0 7500

We have the data in the table above, which are the costs of travel from Alicante to Gerona from a prestigious professor.

Requests:
1. Determining how much was spent in Valencia, Tarragona and Girona.
2. Determining how much was spent on food. 
3. Determining how much was spent on food and Tarragona (care to repeating the error amounts to add).
4. Automatically create names for all ranks involved in the above formulas.
5. Automatically replaced by their names ranks.