simple formula question, please help

[TomK219]

i am doing a project for school on the lottery. my problem is that i have formulas that reference to cells A1:A10. everytime there is a lottery draw i import the new numbers to cell A1, this causes the previous data to shift down one cell, which is what i want. however the formuals that reference cells A1:A10 tend to follow the numbers as they slide down, instead of statically staying with cells A1:A10 (which is what i want). How do i solve this? I put "$" in front of the cell letters and numbers and it still does this. any help would be appreciated because i'm stuck. thanku.

Tom

 

[VoG]

If you use

=INDIRECT("A1")

it will always point to A1.

 

[TomK219]

thanks VoG. i'll definitely explore that!

Tom

 

[TomK219]

ok follow up question since ive been trying a few things and cant seem to get the syntax correct.

im currently using a frequency formula that uses cells B2:F11 and uses the bins array of H2:H41. =frequency(B2:F11,H2:H41) and this works correctly. however i would now like to insert the indirect function so that it ALWAYS references B2:F11. i tried a few versions and excel doesnt seem to like them. any help on syntax?

thanks

Tom

 

[T. Valko]

ok follow up question since ive been trying a few things and cant seem to get the syntax correct.

im currently using a frequency formula that uses cells B2:F11 and uses the bins array of H2:H41. =frequency(B2:F11,H2:H41) and this works correctly. however i would now like to insert the indirect function so that it ALWAYS references B2:F11. i tried a few versions and excel doesnt seem to like them. any help on syntax?

thanks

Tom

Like this...

=FREQUENCY(INDIRECT("B2:F11"),H2:H41)

 

[TomK219]

thanks for your speedy response. although excel didnt yell at me for the syntax, the formula doesnt produce the correct answer. the correct answer is 3.

when i write it like this i get 0:
=FREQUENCY(INDIRECT("B2:F11"),H2:H41)

when i write it like this i get the correct answer of 3:
=FREQUENCY(B2:F11,H2:H41)

although the second one gives the correct answer, it doesnt take in account that i ALWAYS want to reference B2:F11, even when i import new data and the current data slides down. any ideas? thanku.

Tom

 

[T. Valko]

thanks for your speedy response. although excel didnt yell at me for the syntax, the formula doesnt produce the correct answer. the correct answer is 3.

when i write it like this i get 0:
=FREQUENCY(INDIRECT("B2:F11"),H2:H41)

when i write it like this i get the correct answer of 3:
=FREQUENCY(B2:F11,H2:H41)

although the second one gives the correct answer, it doesnt take in account that i ALWAYS want to reference B2:F11, even when i import new data and the current data slides down. any ideas? thanku.

Tom

Without seeing the actual data I can't tell what result you should get.

What does 3 represent? You should get an array of results not a single result.

Here's an example...

The data...

Book1
	B	C	D
2	67	83	2
3	92	81	42
4	78	50	46
5	39	27	62
Sheet1

The bins and the formula results...

Book1
	F	G
2	10	1
3	20	0
4	30	1
5	40	1
6	50	3
7	60	0
8	70	2
9	80	1
10	90	2
11	100	1
Sheet1

This array formula** entered in the range G2:G11:

=FREQUENCY(INDIRECT("B2:D5"),F2:F11)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[gregtx81]

For an array formula to multiple cells (as the frequency function requires), I believe all the cells must be selected selected at the time ctrl-shift-enter is pressed.

I usually type the formula in the first cell, press enter, select all the cells with the first cell remaining the active cell (i.e. white), press F2, press ctrl-shift-enter.

 

[T. Valko]

For an array formula to multiple cells (as the frequency function requires), I believe all the cells must be selected selected at the time ctrl-shift-enter is pressed.

I usually type the formula in the first cell, press enter, select all the cells with the first cell remaining the active cell (i.e. white), press F2, press ctrl-shift-enter.

I select the range of cells starting from the top cell with the top cell being the active cell.

Then I type the formula in the formula bar. After I'm done typing I hold down both the CTRL key and the SHIFT key then hit ENTER.