# Formula Needed

#### baggarwal

##### Well-known Member
Hi All:

I have the following simple table:
Book1
ABCDEF
34556250567788
4
5200.89
6455.63
7633.21
Sheet1

In the cells highligted I have a simple multiplication formula. I need a better formula.

I just want to transpose the numbers in columns D,E,F and multiply by (A/B). I want this embedded in a formula though so everything is still linked. I can do a paste special transpose but it does not perserve the links. Can someone please help me create a formula transposing the values so I can easily copy them down.

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### Richard Schollar

##### MrExcel MVP
Hi

You could use:

Code:
``=\$A\$3/\$B\$3*OFFSET(\$C\$3,,ROW()-4)``

Best regards

Richard

#### baggarwal

##### Well-known Member
How does the formula work? It works like a charm I just don't understand it.

#### Richard Schollar

##### MrExcel MVP
The OFFSET function returns a cell reference to the formula (ie D3,E3,F3 etc) and it does so by specifying a starting point which is C3. The next argument in the Offset is how many rows to offset from, but you want to remain on row 3, so this is left blank (ie the two sequential ,,).

Next is the column offset, which is where we tell the function to go to D,E,F,G etc. Starting point is C3, so we want to go over one column, so 3rd argument of Offset needs to return a 1.

ROW() returns the row number of whatever the formula is on (as we haven't included any range reference within the ROW braces). So, in this case formula is on D5, so ROW() returns 5. We only want to go over 1 column, so we need to take 4 from this number ie 5-4=1. Next row down (ie D6) we will need 2 columns over. Row() returns 6 less 4 = 2.

Make sense?

Richard

#### baggarwal

##### Well-known Member

You are a clever excel god thankyou so much.

#### baggarwal

##### Well-known Member
Is there another formula that could be used to achieve the same result. The only problem with this formula is that if the position of the cell changes the value will be different.

#### Gary Drumm

##### Active Member
Play around with the absolute reference symbols, such as:
=\$A\$3/\$B\$3*OFFSET(\$C\$3,,ROW()-4)
vs
=A\$3/B\$3*OFFSET(C\$3,,ROW()-4)
or
=\$A3/\$B3*OFFSET(\$C3,,ROW()-4)

I've experimented with this a little bit, and there is a way to make it work

#### Krishnakumar

##### Well-known Member
Hi,

=\$A\$3/\$B\$3*INDEX(\$D\$3:\$F\$3,ROWS(\$A\$5:\$A5))

HTH

Replies
4
Views
75
Replies
9
Views
64
Replies
11
Views
110
Replies
2
Views
43
Replies
3
Views
123