Copying Array Formulas

[Poogermum]

Hi

Another odd problem.... and probably a silly question. 

I am trying to use  the frequency() formula which I have read is an array formula. As such my book advises to use shift/crtl/enter instead of just enter when finishing formula for the values to calculate. When I press just enter it comes back with a 'spill over' error. Once I ok it the formula is automatically copied down and appears to work fine. However, I am unable to edit any of the rows apart from the original one which caused the spill which I am not happy with. If I press shft/crtl/enter the formula only gets calculated for the cell I am in and not copied down. I am actually unable to copy it down... How do I copy the formula forward without encountering the spill over error or is that just the way the formula works so I should not worry about it?

[#2]

@Poogermum But that's what the DA functions are all about. Enter them in one cell without the need to copy down/across. The #SPILL! error appears when there isn't enough space for the DA formula to display the result.

[#3]

@Riny_van_Eekelen

Hi yes I understand that this is what array functions are all about however, how do I make excel occupy the required cells with the formula in question?!?!

for example:

1. I type frequency (b1:b10,c1:10) into a cell and then press enter it automatically comes back with spill! even if there are lots of cells around the input cell

2. if I type the same formula and press shift/ctrl/enter, the formula is only displayed in one single cell but needs to be in a number of cells to show the full array!

I just don't know how to make it copy down the array to show all the results. 

[#4]

@Poogermum Not an expert on this particular function, but can't recreate what you describe. Can you upload an example file or a screenshot of what you are looking at?

[#5]

@Poogermum 

IMHO, both formulas shall return exactly the same. Regular returns array of 11 elements. with second one you select 11 cells and Ctrl+Shift+Enter to receive the same result.

Anyway, without the sample that's abstract discussion.