Forum Discussion
Problem with a Excel formula
Steven,
Do i understood correctly the only thing you did is change 2248 on 2283 in your formulas? And perhaps that's misprint in your description
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"
And i guess it's misprint in your description - 3 or 4 are entered in column C, not D. Number is returned from column D, not A. At least that's what your formula do - returns max in D for the rows where 3 or 4 in C.
Anyway, zero means formula doesn't find 3 or 4. Another reason could be cells in C are formatted as text and you have text "3" instead of number 3. You may check, for example, by ISNUMBER on the cell with this value.
To shorten the range for the test you may take C2256:C2258 (instead of C1:C2283), same for other ranges. Evaluate shall show something like
MAX(IF({FALSE,FALSE;TRUE,FALSE;FALSE,FALSE},D2258:D2258))on second step. If in C2257 is number 3.
Hi Sergei.
Sorry i took so long to reply but ive been working hard. Thank you for your help with that formula but i have manged to work around the problem using a different set of formulas.
How ever i do have a problem with another formula on the sheet (note for some reason formulas with the same long cell ranges all stopped working when they reached 2248)
First ill explain what the formula does. The sheet is a Lotto program that uses information on the sheet to predict a date when the number in question is likly to come out again, based on when it was last drawn and the average number of days between draws for each number (dont laugh i know this is impossible to be 100% correct due to random chance).
(note; the number of rows has been reduced)
The formula in cell E317 is =INDEX($B1:$B309,MAX(IF((E1:E309>0),ROW(E1:E309),0)))+ROUND(E316,0)
Colum B holds the date of each draw.
Colum E represents the number 1. (numbers 1- 59 are in coloums E to BK) Each time number a is drawn then and numer 1 (when drawn as a main ball) or number 2 (when drawn as a bonus ball) is entered in the relevent colum next to the date it was drawn.
Cell E316 contains the day average (this is the day total from the the first to the last draw entered devided by the number of times drawn).
The problem is that when i use ctrl+shift+enter the formula return #VALUE in cell 317.
Any questuion please let me know.
Steven
Hi Steven,
In general your formula shall work. You may use equivalent which doesn't require array formula
=INDEX($B1:$B309,MATCH(MAX(E1:E309),E1:E309,0)))+ROUND(E316,0)
hI Sergei. i have imput the formula you sent. If you have a look at the attachment youll see what it coming up with. Thank you for your help.
Sorry, it was misprint
=INDEX($B1:$B309,MATCH(MAX(E1:E309),E1:E309,0))+ROUND(E316,0)