use offset function

D

danial mansoor

i ve data from jan to dec and i make drop downlist in cell d9 wat i
want if i click drop down and select sep than i want September data
and so on.. i want current expense, average monthly, ytd total
according to months?

kindly use offset function so i can understand it better?waiting for
ur help

words are column and numbers are rows;month = sep is drop downlist of
months.



A B C D E F G H I J K L M N O
8 User Input
9 Month = Sep
10
11 Report
12
13 Current Average YTD
14 Expense Monthly Total
15 Salaries
16 Wages
17 Overtime
18 Phone
19 Supplies
20 Other
21
22 Total 0 0 0
23
24 Data
25 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Total
26 Salaries 100,000 110,000 120,000 130,000 140,000 150,000
160,000 170,000 180,000 190,000 200,000 210,000 1,860,000
27 Wages 57,156 58,222 59,288 60,354 61,420 62,486 63,552
64,618 65,684 66,750 67,816 68,882 756,228
28 Overtime 1,084 2,002 2,920 3,838 4,756 5,674 6,592 7,510
8,428 9,346 10,264 11,182 73,596
29 Phone 4,215 4,404 4,593 4,782 4,971 5,160 5,349 5,538
5,727 5,916 6,105 6,294 63,054
30 Supplies 8,674 8,788 8,902 9,016 9,130 9,244 9,358 9,472
9,586 9,700 9,814 9,928 111,612
31 Other 448 500 552 604 656 708 760 812 864 916 968
1,020 8,808
32
33 Total 171,577 183,916 196,255 208,594 220,933 233,272
245,611 257,950 270,289 282,628 294,967 307,306 2,873,298
 
G

Gord Dibben

See help on the following functions available for manipulation of data.

Pivot Table

Autofilter

Advanced Filter


Gord Dibben MS Excel MVP
 
D

Don Guillett Excel MVP

i ve data from jan to dec and i make drop downlist in cell d9 wat i
want if i click drop down and select sep than i want September data
and so on.. i want current expense, average monthly, ytd total
according to months?

kindly use offset function so i can understand it better?waiting for
ur help

words are column and numbers are rows;month = sep is drop downlist of
months.

        A       B       C       D       E      F       G       H       I       J       K       L       M       N       O
8       User Input
9               Month   =       Sep
10
11      Report
12
13                      Current         Average         YTD
14                      Expense         Monthly         Total
15              Salaries
16              Wages
17              Overtime
18              Phone
19              Supplies
20              Other
21
22              Total   0       0       0
23
24      Data
25                      Jan     Feb     Mar    Apr     May     Jun     Jul     Aug     Sep     Oct     Nov     Dec
Total
26              Salaries        100,000        110,000         120,000         130,000         140,000         150,000
160,000         170,000         180,000         190,000         200,000         210,000         1,860,000
27              Wages   57,156  58,222  59,288  60,354  61,420  62,486  63,552
64,618  65,684  66,750  67,816  68,882  756,228
28              Overtime        1,084   2,002  2,920   3,838   4,756   5,674   6,592   7,510
8,428   9,346   10,264  11,182  73,596
29              Phone   4,215   4,404   4,593   4,782   4,971   5,160   5,349   5,538
5,727   5,916   6,105   6,294   63,054
30              Supplies        8,674   8,788  8,902   9,016   9,130   9,244   9,358   9,472
9,586   9,700   9,814   9,928   111,612
31              Other   448     500     552     604     656     708     760     812     864    916     968
1,020   8,808
32
33              Total   171,577         183,916         196,255         208,594         220,933        233,272
245,611         257,950         270,289         282,628         294,967         307,306         2,873,298
Click to expand...

Or you can tie to a macro that does
data>filter>autofilter>>>>
with subtotal formulas at the top. See help index for SUBTOTAL for
visible cells
 
Z

zvkmpw

Here's one way to get started.

It helps to keep an eye on Excel's built-in Help for OFFSET().

It looks like "Sep" is in D4 and the months are in B25:M25.

First note that
=MATCH($D$4,$B$25:$M$25,0)
gives 9; that is, how far rightward in the data table the inputted
month is -- "Sep" in this case.

Using this, the "current expense" for "Salaries" would be
=OFFSET(A26,0,MATCH($D$4,$B$25:$M$25,0))

The "average monthly" for "salaries" would be
=AVERAGE(OFFSET(A26,0,1,1,MATCH($D$4,$B$25:$M$25,0)))

The "year to date" for "salaries" would be
=SUM(OFFSET(A26,0,1,1,MATCH($D$4,$B$25:$M$25,0)))

With these three in side-by-side cells, select them and extend
downward for five additional rows.

In the case of "average," at least one number must be present to avoid
a division-by-zero error. If this is troublesome, use something like
=IF(ISNUMBER(B26), <above formula> , "")
 
Z

zvkmpw

Here's an explanation, in two parts.

First, OFFSET(a,b,c) returns the value of a cell that's displaced from
a starting cell, vertically and horizontally. The starting cell is a;
the displacements are b and c. Because b and c can be formulas, the
displacements can be calculated from the values of other cells.

Second, building on the above, the longer form OFFSET(a,b,c,d,e)
returns an array of values. The height and width of the array are d
and e. Because d and e can be formulas, the height and width can be
calculated from the values of other cells. When d and e are both one,
the outcome is the same as with the shorter form in the preceding
paragraph. Because the result is an array here, it can appear in a
formula where an array can appear; for example, SUM(OFFSET(...)) or
AVERAGE(OFFSET(...)) or COUNT(OFFSET(...))or MAX(OFFSET(...))or
SUMIF(OFFSET(...),x,OFFSET(...)).

In the original post, "current expense" uses the shorter form of
OFFSET. The displacement is horizontal only, calculated from the user-
input month.

Next, "average" and "YTD total" use the longer form of OFFSET. The
array is one row high; its width is calculated from the user-input
month.

Hope this helps.
 
Top