Forum Discussion
Problem with a Excel formula
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)
- Hi again. The formula seems to be working but the date that it gives is in the past. It should show the next date that number might be drawn. Any suggestions?