Array formula doesn't work (CTRL+SHIFT+ENTER)

Hi. First of all: I know how to use array formulas. But on this particular PC, the CTRL+SHIFT+ENTER doesn't work! When I'm using it, it only makes the array formula in the first cell of the array! (I have remembered to mark all cells/the whole range)

I have googled a lot trying to solve this problem. Has it something to do with the language settings or something else?

Im using PC, Win 7 and Excel 2007.

Not sure what array formula you are using and all Just enter it in a single cell and drag it down...

Do you need an array formula here? I would use this formula in C2

=A2*B2

then copy that down

OK, to make it work as an array formula follow these steps

Put the formula in C2, i.e. =A2:A5*B2:B5
Select the range C2:C5
Press F2 key
hold down CTRL and SHIFT keys and press ENTER

.....and you are using a PC not a Mac?

What are the regional and keyboard settings? Is it Danish language version of Excel?

Also if you will start Excel in Safe Mode, does this problem still occur?

Hi.

And the relevant keys are functioning otherwise normally? Your CTRL key, for example?

And none of these three functions have been designated secondary functions on that particular key? I don't think I've ever seen either of these three sharing a key, though it's perhaps not impossible.

Regards

What's if you use like this?

=A2:$A$5*B2*$B$5

CTRL+SHIFT+: should put the time in a cell does that work?

Yes this problem is a pain. But really the solution is quite easy. You are told to enter control + shift + enter
however what you have to do is 1. Place your cursor in the field where your array formula is located
2. Now press f2 + control + shift + enter
Your formula will now become an array formula and will have {} surrounding it.

Sorry, I might be missing something here but, if you just want a simple array function that you want to drag down, I think you'd just put:

Formula:

=$A2:$A5*$B2:$B5

committed with Ctrl-Shift-Enter rather than just Enter.

If you want to get the sum of the arrays, then you'd use:

Formula:

=SUM($A$2:$A$5*$B$2:$B$5)

committed with Ctrl-Shift-Enter rather than just Enter

or, alternatively:

Formula:

=SUMPRODUCT($A$2:$A$5*$B$2:$B$5)

. Note: this is not an Array Formula


Like I say, I might be missing something in my understanding or your requirement.


If the formula is more complex, perhaps you can share it with us.



Regards, TMS

And, actually:

Formula:

=SUM($A$2:$A$5*$B$2:$B$5)

NOT Array Entered and dragged down will give the sums of individual rows.

Regards, TMS

@Jonmo1:

His real array formula is something more complicated.

Then we're all wasting our time trying to second guess what the problem is without seeing the data. There's no point giving an example that bears no relation to reality. We're up to post #19, all because the OP hasn't provided enough information.

To effectively investigate what the problem might be, we need to see the actual formula and, ideally, a sample workbook with some typical data.

Regards, TMS

@Jonmo1: I did say that I might be missing something

However, I don't think there's a lot that can be done without seeing the workbook, or a sample that demonstrates the problem. It could be a) the computer, b) the copy of Excel ... maybe a repair would help, c) the workbook, d) the contents or some other constraint within the workbook, or e) operational error ... though, given the images, that doesn't appear to be the case.

I can create all sorts of examples on my laptop, either Array Entering and dragging, or selecting a range and Array Entering, or using the code example. All no problem at all. But it doesn't get to the heart of the problem.

I wonder if any changes have been made to the system recently, intentionally or inadvertently. I recall Skype Click to Call causing all sorts of problems copying and pasting. With a workbook, and the formula and the range, we can simulate the process and see what we can actually prove ... if anything. If we can input the formula into the range in an empty workbook, that proves one thing. If we can input the formula into the range in the actual workbook, or sample, that proves something else.

I'm not trying to be difficult or confrontational here. Just asking for something to work with

thats corect if you put a1*b1 in a single cell array enter it and drag it down it becomes {=a2*b2}
but if you put the same thing in one cell then select it and the cell immediately below and array enter both appear as {=a1*b1}
so its the process thats at fault the formula is irrelevent

Note: it'd be good if the OP joined in at some point and either helps us help him (maybe, with a workbook) or explains why he's not going to.

@Martin: I understand that ... whereas I might have missed the issue initially ... but my point is that I can sit here and Array Enter as many formulas as I like and it works. But we need a way of eliminating the possibilities.

Originally Posted by Ztv

@Jonmo1: The sub is working! What does that tell us?

That tells us that your Excel Application is indeed 'able' to correctly process an array formula entry.
And that you (we) are not loosing our minds

Are you using a laptop?
Try using the 'other' Enter Key.
Also the other CTRL and SHIFT keys
Try all different combinations of the 2 shift keys, 2 CTRL keys and 2 Enter Keys.

Test 3 works fine for me.

Do you have any programs running which your colleagues do not? If a colleague logs into your machine is the problem the same?

Its a problem in all workbooks!
EDIT: Also if I select new - blank workbook.

The problem "As-is":
test3.xlsx

Please can you upload the "real" workbook, or a subset of it that demonstrates the problem, suitably denuded of any sensitive information BUT, as I said, which still exhibits the problem.

If it makes a difference, in the test workbook with the grey cells, it works. In the Jonmo1 workbook, select all the target cells and choose General format - works OK but, as Jonmo1 said, the cells contain the formatting ;; ... oh, apart from the first one which has hh:mm

I can see the answer to this problem being very upsetting ...

Regards, TMS

@Ztv: we generally find that, when we can see the "real" workbook, or as much of it that it is safe to share ... and it exhibits the problem, we can very quickly get to the bottom of it.

Now, I for one, and seemingly several others have spent all day generating play workbooks and entering Array Formulas, entering and dragging, selecting and entering, and generally wasting time. Now you have uploaded three toy workbooks, none of which are incapable of accepting an Array Entered formula in a range of pre-selected cells.

So that's why I'd like to see the real workbook ... so that we can eliminate that from our investigations. I said, a while back, it could be your computer, or it could be your copy of Excel. We can't eliminate those for you. But, if it's a desktop with a separate keyboard, or if it's a laptop in a docking station with a separate keyboard, I would suggest that you check those ... and, by check, I mean by swapping it with the keyboard of one of your colleagues who is able to enter Array Formulas. As for Excel, I can't think what it might be ... again, maybe compare your configuration to another that works ... it might be some sort of compatibility setting.

Now, I'm sorry if there may appear to be an edge creeping in but I often find that I can play guessing games for hours and, eventually the OP uploads a sample workbook ... and the problem is solved in minutes.


Regards, TMS

@Ztv: you are right, I mean you no disrespect and I apologise for being patronising, if that's the way it came across. It was not intended as such ... more frustration at not being able to get to the bottom of the problem.

@ragulduy: I am inclined to think that it is not the workbook and probably not Excel. If I were a betting man, I would be putting my money on a physical problem, most likely the keyboard. I would want to swap it with another that works on another machine and put the original keyboard on the second machine to see if the fault moves. From the description, I suspect that, for some reason, the key combination is not holding and only the Enter key being pressed is sensed.

@Jonmo1: with regard to your PM, you also are correct ... I pushed too hard, and unnecessarily, to see the "real" workbook. That was for the satisfaction of proving that it was absolutely NOT an issue with the workbook, content, formulas or formatting.

As Sherlock Holmes said: "Eliminate all other factors, and the one which remains must be the truth."

Anyway, I'm going to back right off now and patiently wait for a successful outcome.

Regards, TMS

@TMShucks: Thank you.

Btw, my keyboard is working correctly. And, for example, if I open Microsoft word 2007 and do the CSE, it makes a page break. It's only in excel that its [ Deleted text ] making this error, which is that it's only making the array formula in the first cell of the target area.
Recall: PC, WIN7 and office 2007

It's only in excel that its like... "ignoring" the CTRL + SHIFT part and only executes the ENTER part..

I do not think this is correct. After.jpg which you provided shows formula is array-entered, but only in first cell.

Does it make any difference if Windows is in Safe Mode?

@Ztv: In MS Word, Ctrl-Enter causes a Page break. Ctrl-Shift-Enter makes a Column break.

@SunSound:It shouldn't, in fact, doesn't, make a difference whether the cursor is in the body of the formula or at the end.

@PROBLEM SOLVER: I think the assumption is that you have typed, or copied and pasted the formula into a cell and the cell is still in Edit mode. You can/should, at that point, press Ctrl-Shift-Enter to commit the formula. If, however, you have already exited the cell, perhaps by pressing Enter or Tab, you do need to select the cell, press F2 to go into Edit mode, and then press Ctrl-Shift-Enter.

@Mtb10: Welcome to the forum ...

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Old threads are often only monitored by the original participants.

... as in this case, it comes up in my User Control Panel. Not everyone will see it or take any notice given the number of responses. Unfortunately, I have no idea what the response to your specific problem might be. However, you will need to provide a lot more information ... in your own thread ... including your formula and, ideally, a sample workbook with some typical data.

Note that I will create a new thread and link it here (in a second) to hopefully get more visibility on this question, but just thought I would post here since I felt it might contribute to the discussion if anyone still is interested.

EDIT: I can't post URL's apparently since my account is brand new, but the thread is called " Array Formula's not working for all workbooks ".

Not sure if anyone will see this / take the time to respond to this (seeing as this is an old thread), but I am having an identical problem to the OP. I have downloaded the test workbooks he or she provided, and while it seems that for other commenters these workbooks worked for them with no problem, for me, they acted exactly as they did for the OP.

Some possible points of interest:
- If I enter a range into a cell (like so ={15, 16, 17}) and press Ctrl+Shift+Enter, the cell will only display 15 and the function bar thing will show this: {={15, 16, 17}} (which I think means Excel understands that it is an array formula).
- Furthermore, I can even enter some array formulas into a cell, highlight them, and then press F9 to evaluate them in the cell, and that seems to work fine. As an example, what brought me here was wanting to add values in every other column (formula: =SUMPRODUCT((MOD(COLUMN(rng)-COLUMN(rng.first)+1,n)=0),rng) ). Within the cell that contains the aforementioned formula, I can evaluate (by pressing F9) the ranges, the ranges within a COLUMN(), the subtraction of the COLUMN(range)'s, the addition of 1 to that resulting range, and the MOD with the resulting range and specified n. However, at that point I cannot evaluate whether the range resulting from the mod (a range of 1's and 0's if n=2) equals 0. So I can't evaluate {1, 0, 0, 1, 1, 0} = 0 to get the range {FALSE, TRUE, TRUE, FALSE, FALSE, TRUE} like I should be able to.

Additional notes:
- I am running Excel 2013.
- This is an Excel-wide issue for me, not a per-workbook problem. My original workbook (my budget) and both the OP's test and test3 workbooks all do not seem to be able to handle array formulas like they're supposed to.
- Can guarantee that all of my keys, but namely all Ctrl, Shift, Enter keys work correctly on my keyboard. I'm a programmer so I like keyboard shortcuts and use these keys all the time.

When you joined the forum recently you agreed to abide by the Forum Rules, but in haste I fear you might not have actually read them. Please stop and take a moment to read them now. We all follow these rules for the benefit of all, as must you. Thanks.

(link above in the menu bar)