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.
 

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.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

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

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top