Formula Needed

baggarwal

Well-known Member
Joined
Jul 10, 2002
Messages
591
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.
 

Some videos you may like

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

baggarwal

Well-known Member
Joined
Jul 10, 2002
Messages
591
How does the formula work? It works like a charm I just don't understand it.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Jul 10, 2002
Messages
591

ADVERTISEMENT

You are a clever excel god thankyou so much.
 

baggarwal

Well-known Member
Joined
Jul 10, 2002
Messages
591
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
Joined
Feb 22, 2005
Messages
462
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,787
Messages
5,524,877
Members
409,609
Latest member
Channingz

This Week's Hot Topics

Top