Formula Needed

[baggarwal]

Hi All:

I have the following simple table:

Book1
	A	B	C	D	E	F
3	45	56		250	567	788
4
5				200.89
6				455.63
7				633.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.

 

[Richard Schollar]

Hi

You could use:

=$A$3/$B$3*OFFSET($C$3,,ROW()-4)

Best regards

Richard

 

[baggarwal]

How does the formula work? It works like a charm I just don't understand it.

 

[Richard Schollar]

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]

You are a clever excel god thankyou so much.

 

[baggarwal]

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]

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]

Hi,

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

HTH