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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Hi

You could use:

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

Best regards

Richard

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

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

You are a clever excel god thankyou so much.

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.

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

Hi,

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

HTH

Replies
0
Views
131
Replies
3
Views
163
Replies
14
Views
877
Replies
5
Views
181
Replies
5
Views
598

1,218,740
Messages
6,144,211
Members
450,530
Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back