Horizontal Vs. Vertical + Nested formula problems

**alnoor** · 01-26-2008, 03:06 PM

I am facing two problems in excel, I hope you can help me with them..

First: I am trying to reference a very long column of cells A2:A50, to a row (i.e. C1:AY1), so the most left cell in the row=A2, the next =A3, the next =A4 and so on. When I try to drag the formula right it does not seem to want to follow the pattern i.e. advance the number by one in the formula. Can this be done to save me having to do each one manually?

Second: I have a problem with a formula that shows the result in the insert function dialog

(The formula is =SUMPRODUCT(A4:A6;TRANSPOSE(C2:E2)))

But after clicking OK the cell indicate value error

WHY?? Please see the attached images for more details.)

Pic_1.jpg
Pic_2.jpg

Thanks very much in advance...

**oldchippy** · 01-26-2008, 04:11 PM

To your second problem try,

=SUMPRODUCT((A4:A6)*TRANSPOSE(C2:E2))

**oldchippy** · 01-26-2008, 04:26 PM

I've had another go at this and got different results???

Take a look at C6 and D6 and A13, which is what it should be??

**mikerickson** · 01-27-2008, 01:52 AM

As to problem one

In C1 put =INDEX($A$2:$A$50,Columns($C$1:C1),1) and drag right.

OR

Select C1:AY1
Type =TRANSPOSE(A2:A50)
press Ctrl+Shift+Return (Cmd+Return for Mac)

**alnoor** · 01-27-2008, 04:23 AM

Thanks mikerickson,

You solved it

!! But could you please explain to me what is happening

?

oldchippy, I really appreciate your help.

Regarding the attached file, the result should be as in A13 (but you didn't use transpose in that cell!!)

I am really annoyed

, how Excel shows the answer in the dialog then it returns value error in the sheet

Hope I will get an answer to that..

Thanks again to all of you for your kind help

**mikerickson** · 01-27-2008, 06:11 AM

In the INDEX formula, the column number is determine the index of the Row returned.
INDEX(range,column_of_callingCell,1)

The Transpose; I don't understand array formulas well enough to explain it. It's pretty neat though.

**daddylonglegs** · 01-27-2008, 09:18 AM

You need to use CTRL+SHIFT+ENTER to get the correct result I believe, i.e. try the formula

=SUM(A4:A6*TRANSPOSE(C2:E2))

confirmed with CTRL+SHIFT+ENTER.

To do this, select cell with formula, press F2 then hold down CTRL and SHIFT keys and press ENTER so that curly braces appear around the formula in the formula bar.

oldchippy:

The different results you see with SUMPRODUCT are due to the placement of the formula, you get 75 in C6 because it's multiplying the value immediately above (in C2) with each value in A4:A6 and summing the results, For D6 it's the same but D2 used instead of C2

**oldchippy** · 01-27-2008, 09:47 AM

Thanks daddy,

I can see now you have spread some light over it! Actually if you Ctrl+Shift+Enter on my formulas it gives the correct answer as well!

**alnoor** · 01-27-2008, 11:46 AM

Cooool

Thanks daddy,

Also mine

if you Ctrl+Shift+Enter on my formulas (i.e. =SUMPRODUCT(A4:A6;TRANSPOSE(C2:E2)) ) it gives the correct answer as well!

Thanks oldchippy,daddylonglegs & mikerickson

I am really happy with your prompt replies and help...

It is my pleasure to be a member in this forum

**daddylonglegs** · 01-27-2008, 01:06 PM

Originally Posted by alnoor

if you Ctrl+Shift+Enter on my formulas (i.e. =SUMPRODUCT(A4:A6;TRANSPOSE(C2:E2)) ) it gives the correct answer as well!

Yes that's right

SUMPRODUCT is often preferable to SUM because it normally means you don't need CSE but here TRANSPOSE needs CSE so probably I'd marginally favour SUM.........however the advantage of using SUMPRODUCT (and ; not *) is that the formula will still work should any of the ranges contain text