How to assign values from intervals
The following table presents a list of customers. They are collecting the points.
Membership depends on the number of points. The task is to assign correct membership from points intervals.
Tags: VLOOKUP
Joining tables with the corresponding values
There are two tables. The first contains list of products with Product ID. The second is the actual quantity of products in the stock. Our task is to assign correct Product Name to Product ID in the second table.
Continue reading “Joining tables with the corresponding values” »
Tags: VLOOKUP
How to rotate and transpose a table
The table in this example should be rotated in such a way that we change rows per columns. We will use Transpose from the Excel menu to solve it.
Quick copy values or formulas in large table
There is an easy way how to do copy faster. Let’s have large table like on the following picture. We want copy formula for TOTAL PRICE to all items in the column E. As you see table contains nearly 17000 rows.
Continue reading “Quick copy values or formulas in large table” »
How to quickly lock (protect) all of the cells containing formula
There are many values and formulas on the sheet. We want to protect all formulas against overwriting. Other cells must remain unlocked for entry. We don’t need to manually go through cell by cell.
This is a table with some formulas:
Let’s see how to do that: Continue reading “How to quickly lock (protect) all of the cells containing formula” »
Running total for multiple items solved by array formula
There are some stuff dealers. In the following table is list of their sales. We need to know, how many sales they made from the beginning of the year. But there is a problem, we need to know sum to the date in each record for the dealer. For example: Jack Smith sold 1 piece on 2011/01/07 and then 3 pieces on 2011/01/19. So total to 2011/01/07 is 1, but total to 2011/01/19 is 4 (1+3).
Question is how to create running total for each dealer in the table. Continue reading “Running total for multiple items solved by array formula” »
Tags: Array formula
