Change the ranges formulas refer to

[Green1972]

Hi Ive been of excel for a while and have forgotten what to do so please help.

I have the formulas listed below and have hade to change the range they refer to. When I enter t=it and hit return the answer in the cell is wrong.

=MAX(IF('Draw Results'!C1:C2283={0.3,4},'Draw Results'!A1:A2283))
=MAX(IF('Draw Results'!C1:C2248={3,4},'Draw Results'!D1:D2283))

It worked fine before I increased the range (C2241 up to C2283 and A or D2241 upto A or D2283)
Please help.

 

[mrhstn]

Re: Help

I'd be inclined to say keep the ranges the same.

I think those might want to be confirmed with ctrl+shift+enter not just enter.

 

[Green1972]

Re: Help

Hi. I had to increase the range as the number of lines increased. I have also tried ctrl+shift+enter but had no effect

 

[mrhstn]

Re: Help

OK, so is the answer you're getting actually wrong... You haven't given a way to determine what's wrong.

I did test a few things. So created a similar range and replicated some results. Your formulas do require CSE entry

Book1
	H	I	J
1	Array	Params	Test
2	196547	0.3	195936
3		4	196547
4		Max	196547
5	996526	3	994743
6		4	996526
7		Max	996526
Sheet6

Cell Formulas
Range	Formula
J2	=MAXIFS($A$1:$A$2800, $C$1:$C$2800, I2)
J3	=MAXIFS($A$1:$A$2800, $C$1:$C$2800, I3)
J4	=MAX(J2:J3)
J5	=MAXIFS($D$1:$D$2800, $C$1:$C$2800, I5)
J6	=MAXIFS($D$1:$D$2800, $C$1:$C$2800, I6)
J7	=MAX(J5:J6)
H2	{=MAX(IF(C1:C2800={0.3,4}, A1:A2800))}
H5	{=MAX(IF(C1:C2800={3,4}, D1:D2800))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Also, I note your formulas above have unequal ranges, this might cause you the bother...see what happened when I put erroneous ranges into my test data

Book1
	N
1	Range Incorrect
2	199262
3
4
5	998535
6
Sheet6

Cell Formulas
Range	Formula
N2	{=MAX(IF(C1:C2800={0.3,4}, A2:A2799))}
N5	{=MAX(IF(C4:C2803={3,4}, D5:D2802))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Green1972]

Re: Help

Hi Not sure what I've done but the only thing I've changed is the number of cells/ lines in the range from:
=MAX(IF('Draw Results'!C1:C2248={3,4},'Draw Results'!D1:D2248))
to
=MAX(IF('Draw Results'!C1:C2283={3,4},'Draw Results'!D1:D2283))
What it does is to show the last draw number entered in in column A where either 3 or 4 are entered in column D. The answer should be 2257 but it comes out as "0"

The second formula was changed from:
=MAX(IF('Draw Results'!C1:C2248={3,4},'Draw Results'!A1:A2248))
to
=MAX(IF('Draw Results'!C1:C2283={3,4},'Draw Results'!A1:A2283))
What it should show is the date of the last draw entered in column A, where 3 or 4 is entered in column D. the answer should be 08/07/17 but comes out as 00/01/00

 

[mrhstn]

Re: Help

I can replicate this error by confirming formula with enter only. You must confirm the formula using ctrl+shift+enter to get the result.

 

[Green1972]

Re: Help

Thanks for your help. I am using Ctrl+Shift+enter but no luck. excel does not put the {} around the formula.

 

[mrhstn]

Re: Help

Go to the cell that contains the formula, press F2, then press and hold ctrl, at the same time press and hold shift, then hit enter...

If this doesn't work then you are likely to have an issue with your keyboard. An alternative might be to put those formulas in using VBA

 

[Green1972]

Re: Help

Thank you again I will try it tomorrow when I get chance. Might be a daft question but what is VBA?

 

[Green1972]

Re: Help

Hi I have tried it but I'm getting the same result. Any other suggestions?