Problem with a Excel formula

%3CLINGO-SUB%20id%3D%22lingo-sub-98123%22%20slang%3D%22en-US%22%3EProblem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-98123%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Not%20sure%20what%20I've%20done%20but%20the%20only%20thing%20I've%20changed%20is%20the%20number%20of%20cells%2F%20lines%20in%20the%20range%20from%3A%20%3DMAX(IF('Draw%20Results'!C1%3AC2248%3D%7B3%2C4%7D%2C'Draw%20Results'!D1%3AD2248))%3C%2FP%3E%3CP%3Eto%20%3DMAX(IF('Draw%20Results'!C1%3AC2283%3D%7B3%2C4%7D%2C'Draw%20Results'!D1%3AD2283))%3C%2FP%3E%3CP%3EWhat%20it%20does%20is%20to%20show%20the%20last%20draw%20number%20entered%20in%20in%20column%20A%20where%20either%203%20or%204%20are%20entered%20in%20column%20D.%20The%20answer%20should%20be%202257%20but%20it%20comes%20out%20as%20%220%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20second%20formula%20was%20changed%3C%2FP%3E%3CP%3Efrom%3A%20%3DMAX(IF('Draw%20Results'!C1%3AC2248%3D%7B3%2C4%7D%2C'Draw%20Results'!A1%3AA2248))%3C%2FP%3E%3CP%3Eto%20%3DMAX(IF('Draw%20Results'!C1%3AC2283%3D%7B3%2C4%7D%2C'Draw%20Results'!A1%3AA2283))%3C%2FP%3E%3CP%3EWhat%20it%20should%20show%20is%20the%20date%20of%20the%20last%20draw%20entered%20in%20column%20A%2C%20where%203%20or%204%20is%20entered%20in%20column%20D.%20the%20answer%20should%20be%2008%2F07%2F17%20but%20comes%20out%20as%2000%2F01%2F00.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEach%20formula%20is%20entered%20with%20ctrl%2Bshift%2Bebter%3C%2FP%3E%3CP%3ETried%20other%20excell%20help%20sites%20but%20got%20no%20help%20so%20please%20don't%20let%20me%20down%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-98123%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETips%20and%20Tricks%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-124288%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-124288%22%20slang%3D%22en-US%22%3E%3CP%3ESteven%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20could%20be%20something%20else%2C%20but%20i%20see%20no%20errors%20in%20my%20copy%20of%20the%20file.%20How%20your%20date%20is%20entered%20you%20may%20check%20in%20formula%20bar.%20If%20as%20text%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DateText.JPG%22%20style%3D%22width%3A%20213px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F23555i12A5A13898228498%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22DateText.JPG%22%20alt%3D%22DateText.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eyou%20have%20an%20error%2C%20if%20as%20date%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DateAsDate.JPG%22%20style%3D%22width%3A%20160px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F23556i298FE6B56ADF492C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22DateAsDate.JPG%22%20alt%3D%22DateAsDate.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eall%20works%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-121986%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-121986%22%20slang%3D%22en-US%22%3EHi%20service.%20Tried%20all%20you%20said%20and%20looked%20at%20the%20file%20you%20attached%20but%20still%20won't%20work.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-116506%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-116506%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sorry%20it%20took%20so%20long%20to%20reply%20but%20my%20laptop%20has%20been%20away%20for%20repair.%20Will%20try%20your%20suggestion%20as%20sson%20as%20i%20can.%20thank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-112567%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-112567%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Steven%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20the%20date%20you%20have%20to%20enter%20the%20date%20in%20your%20locale%20format%20(12%2F07%2F2017%20by%20default%20for%20UK)%20or%20simply%20the%20number%2042928%20(which%20is%20equivalent%20of%20that%20date).%20Or%20by%20refrence%20on%20previous%20date%3C%2FP%3E%3CPRE%3E%3DB248%2B4%3C%2FPRE%3E%3CP%3EAnd%20only%20after%20that%20apply%20format%20if%20necessary.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20the%20file%20in%20my%20previous%20post.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-112427%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-112427%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei.%20Ive%20done%20as%20you%20have%20suggested%20but%20still%20getting%20the%20same%20probelm.%20Ive%20attached%20a%20PDF%20of%20a%20screen%20shot%20of%20what%20i%20get%20when%20i%20try%20to%20open%20the%20file%20to%20work%20on%20it.%20I%20have%20circled%20the%20cells%20that%20have%20%23Value%20in%20them%20incase%20the%20image%20is%20not%20great.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-111463%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-111463%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Steven%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20since%20in%20B249%20you%20enter%20the%20text%20%2212.7.17%22%20instead%20of%20the%20date%20in%20your%20locale%20format.%20If%20change%20on%20date%20(actually%20number)%20formulas%20work.%20Please%20see%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-111287%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-111287%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sorry%20about%20that.%20i%20had%20to%20delete%20some%20of%20the%20none%20essential%20page%20and%20deleted%20the%20wrong%20one.%20Ill%20try%20again.%20Please%20try%20this%20one.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-109244%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-109244%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Steven%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnfortunately%20in%20the%20file%20you%20attached%20i%20don't%20see%20the%20formulas%20(only%20%23REF)%20since%20'General%20Lotto%20Info'%20tab%20is%20removed.%20Could%20you%20please%20send%20full%20file%20to%20check%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20care%20about%20the%20confidentiality%20send%20the%20link%20where%20i%20can%20download%20it%20with%20in-private%20message.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-109018%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-109018%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Again.%20Ive%20attached%20the%20sheet.%20wot%20ive%20done%20is%20and%20your%20formula%20to%20the%20cells%20in%20row%20317.%26nbsp%3B%20Ive%20added%20the%20a%20new%20set%20of%20draw%20results%20in%20to%20row%20249.%20If%20you%20look%20at%20cells%20K317%2C%20R317%20etc%20you'll%20see%20%23VALUE%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-105588%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-105588%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Steven%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%2C%20missed%20this%20your%20reply.%20I%20tried%20to%20play%20with%20the%20file%20you%20send%20a%20week%20or%20so%20ago%20and%20was%20not%20able%20to%20reproduce%20the%20error.%20But%20that%20was%20no%20data%20in%20row%20249.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20please%20send%20me%20the%20file%20where%20you%20see%20the%20error%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-104936%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-104936%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Again.%20seems%20i've%20not%20got%20it%20figured%20out.%20I%20have%20attached%20the%20sheet.%20As%20you%20can%20see%20ive%20put%20the%20draw%20results%20in%20row%20249.%20and%20in%20row%20317%20under%20the%20draw%20results%20then%20%23VALUE%20appears%20in%20the%20that%20cell.%20not%20sure%20whay%20but%20could%20you%20please%20let%20me%20know%20what%20ive%20done%20wrong.%20Thank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-104909%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-104909%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20again.%20sorry%20it%20took%20so%20long%20to%20reply.%20My%20last%20message%20i%20said%20about%20it%20not%20working%20ok.%20SORRY%20it%20seems%20its%20my%20fault.%20i%20had%20not%20put%20the%20formula%20in%20right.%20It%20seems%20ok%20but%20ill%20let%20you%20know.%20thank%20you%20for%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-103136%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-103136%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Steven%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can't%20reproduce%20that.%20Whatever%20i%20enter%20in%20cells%20from%20E1%20to%20E247%20i%20have%20no%20error%20in%20E317.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20file%20you%20attached%20with%20previous%20post%20with%20error%3F%20And%20please%2C%20what's%20your%20Excel%20verson%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-103056%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-103056%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei.%20%23VALUE%20appears%20in%20the%20cell%20E317.%20not%20sure%20what%20is%20going%20on%2C%20weather%20it%20me%20or%20some%20problem%20with%20the%20program%20iam%20not%20sure.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-102332%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-102332%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Steven%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStrange.%20I%20have%20no%20problems%20with%20your%20file%2C%20no%20%23VALUE%20in%20the%20cells.%20Both%20for%20array%20formulas%20(row%20322)%20and%20lookup%20formula%20(row%20317).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20please%20indicate%20in%20which%20exactly%20cell%20you%20add%201%20(e.g.%20F248)%20and%20in%20which%20cell%20%23VALUE%20appears%20(e.g.%20F317)%20after%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20on%20which%20version%20of%20Excel%20you%20are%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-102313%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-102313%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei.%3C%2FP%3E%3CP%3E%26nbsp%3BThank%20you%20for%20your%20help%20on%20this.%20I%20am%20not%20sure%20what%20or%20if%20iam%20doing%20anything%20wrong%20but%20the%20original%20formula%20still%20doesn't%20work%20even%20though%20i've%20shortened%20the%20range%20and%20i%20am%20using%20ctrl%2Bshift%2Benter.%26nbsp%3BThe%20second%20formula%20works%20ok%20but%20when%20i%20add%20another%201%20or%202%20in%20the%20colloum%20it%20stops%20working%20and%20shows%20%23VALUE%20in%20the%20cell.%20This%20is%20the%20same%20with%20the%20other%20formulas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIam%20going%20out%20on%20a%20limb%20and%20iam%20sending%20you%20the%20full%20sheet%2C%20almost%20as%20i%20use%20it%2C%20as%20i%20am%20beginning%20to%20think%20that%20the%20shhet%20may%20be%20iver%20complicated%20and%20this%20might%20be%20the%20problem.%20Let%20me%20know%20what%20you%20think.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-101211%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-101211%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Steven%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20sure%20-%20if%20your%20Excel%20version%20is%202007%20or%20any%20later%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-101199%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-101199%22%20slang%3D%22en-US%22%3EHI%20Again%20could%20I%20save%20a%20file%20created%20in%20.xls%20format%20as%20a%20.xlsx%20file.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-101197%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-101197%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20again.%20i%20will%20try%20them%20out%20when%20i%20can.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-101078%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-101078%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Steven%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%20it's%20clear%20now.%20First%20comment%20-%20why%20do%20you%20use%20outdated%20format%20for%20Excel%20file%20(.xls)%3F%20I%20attached%20both%2C%20try%20one%20with%20.xlsx.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESecond%20-%20your%20initial%20array%20formula%3C%2FP%3E%3CPRE%3E%3DINDEX(%24B1%3A%24B309%2CMAX(IF((E1%3AE309%26gt%3B0)%2CROW(E1%3AE309)%2C0)))%2BROUND(E316%2C0)%3C%2FPRE%3E%3CP%3Eworks%20fine.%20Most%20probably%20you%20didn't%20enter%20it%20as%20an%20array%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20do%20that%3C%2FP%3E%3CP%3E1)%20Double%20click%20on%20cell%20with%20your%20formula%3C%2FP%3E%3CP%3E2)%20Press%20and%20hold%20CTRL%20and%20SHIFT%20simultaneously%3C%2FP%3E%3CP%3E3)%20Press%20ENTER%3C%2FP%3E%3CP%3E4)%20Release%20all%20keys%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurly%20brackets%20shall%20appears%26nbsp%3Bat%20the%20beginning%20and%20end%20of%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20formula%20could%20be%20bit%20simplified%3C%2FP%3E%3CPRE%3E%3DMAX((E1%3AE309%26gt%3B0)*%24B1%3A%24B309)%2BROUND(E316%2C0)%3C%2FPRE%3E%3CP%3Eabove%20is%20also%20array%20formula.%20Here%20you%20find%20maximum%20value%20in%20B1%3AB309%20filter%20it%20by%20column%20E%20(all%20rows%20with%20any%20positive%20numbers).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20to%20use%20array%20formulas%20another%20couple%20of%20variants%20could%20be%3C%2FP%3E%3CPRE%3E%3DMAX(INDEX((E1%3AE309%26gt%3B0)*%24B1%3A%24B309%2C))%2BROUND(E316%2C0)%3C%2FPRE%3E%3CP%3Ethat's%20similar%20to%20previous%20one%2C%20however%20no%20array%20formula%20is%20required.%20INDEX%20returns%20an%20array%20with%20all%20dates%20in%20B1%3AB309%20for%20which%26nbsp%3Bthere%20is%20any%20positive%20number%20in%20next%20column%2C%20otherwise%26nbsp%3Bzero.%20We%20take%20maximum%20date%20in%20that%20array.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20more%20variant%20for%20not%20array%20formula%3C%2FP%3E%3CPRE%3E%3DLOOKUP(2%2C1%2F(E1%3AE309%26gt%3B0)%2C%24B1%3A%24B309)%2BROUND(E316%2C0)%3C%2FPRE%3E%3CP%3ESecond%20parameter%20returns%20a%20vector%20with%201%20or%20errors%20as%20results%20of%20dividing%20the%201%20on%20TRUE%20and%20FALSE.%20We%20lookup%202%20in%20this%20vector%20(actually%20any%20number%20greater%20than%201)%2C%20since%20no%20such%20number%20in%20the%20vector%2C%20the%20position%20of%20latest%201%20will%20be%20returned.%20Finally%20the%20date%20in%20B1%3AB309%20at%20this%20position.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20attached%20files%20all%20above%20formulas%20are%20sequentially%26nbsp%3Bin%20E325%2C%20E326%2C%20E327%2C%20E328%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-101057%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-101057%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei.%20Ive%20attached%20a%20copy%20of%20the%20sheet%20have%20a%20look%20please%20the%20formula%20you%20sent%20is%20on%20line%20317.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-100881%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-100881%22%20slang%3D%22en-US%22%3E%3CP%3ESteven%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20give%20the%20sample%20file%20with%20the%20data%20up%20to%2010%20records%20with%20the%20comment%20on%20it%20what%20shall%20be%20received%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-100663%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-100663%22%20slang%3D%22en-US%22%3EHi%20again.%20The%20formula%20seems%20to%20be%20working%20but%20the%20date%20that%20it%20gives%20is%20in%20the%20past.%20It%20should%20show%20the%20next%20date%20that%20number%20might%20be%20drawn.%20Any%20suggestions%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-100522%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-100522%22%20slang%3D%22en-US%22%3E%3CP%3ESorry%2C%20it%20was%20misprint%3C%2FP%3E%3CPRE%3E%3DINDEX(%24B1%3A%24B309%2CMATCH(MAX(E1%3AE309)%2CE1%3AE309%2C0))%2BROUND(E316%2C0)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-100412%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-100412%22%20slang%3D%22en-US%22%3E%3CP%3EhI%20Sergei.%20i%20have%20imput%20the%20formula%20you%20sent.%20If%20you%20have%20a%20look%20at%20the%20attachment%20youll%20see%20what%20it%20coming%20up%20with.%20Thank%20you%20for%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-100348%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-100348%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Steven%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20general%20your%20formula%20shall%20work.%20You%20may%20use%20equivalent%20which%20doesn't%20require%20array%20formula%3C%2FP%3E%3CPRE%3E%3DINDEX(%24B1%3A%24B309%2CMATCH(MAX(E1%3AE309)%2CE1%3AE309%2C0)))%2BROUND(E316%2C0)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-100181%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-100181%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei.%3C%2FP%3E%3CP%3ESorry%20i%20took%20so%20long%20to%20reply%20but%20ive%20been%20working%20hard.%20Thank%20you%20for%20your%20help%20with%20that%20formula%20but%20i%20have%20manged%20to%20work%20around%20the%20problem%20using%20a%20different%20set%20of%20formulas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20ever%20i%20do%20have%20a%20problem%20with%20another%20formula%20on%20the%20sheet%20(note%20for%20some%20reason%20formulas%20with%20the%20same%20long%20cell%20ranges%20all%20stopped%20working%20when%20they%20reached%202248)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20ill%20explain%20what%20the%20formula%20does.%20The%20sheet%20is%20a%20Lotto%20program%20that%20uses%20information%20on%20the%20sheet%20to%20predict%20a%20date%20when%20the%20number%20in%20question%20is%20likly%20to%20come%20out%20again%2C%20based%20on%20when%20it%20was%20last%20drawn%20and%20the%20average%20number%20of%20days%20between%20draws%20for%20each%20number%26nbsp%3B(dont%20laugh%20i%20know%20this%20is%20impossible%20to%20be%20100%25%20correct%20due%20to%20random%20chance).%3C%2FP%3E%3CP%3E(note%3B%20the%20number%20of%20rows%20has%20been%20reduced)%3C%2FP%3E%3CP%3EThe%20formula%20in%20cell%20E317%20is%20%3DINDEX(%24B1%3A%24B309%2CMAX(IF((E1%3AE309%26gt%3B0)%2CROW(E1%3AE309)%2C0)))%2BROUND(E316%2C0)%3C%2FP%3E%3CP%3EColum%20B%20holds%20the%20date%20of%20each%20draw.%3C%2FP%3E%3CP%3EColum%20E%20represents%20the%20number%26nbsp%3B1.%20(numbers%201-%2059%20are%20in%20coloums%20E%20to%20BK)%26nbsp%3BEach%20time%20number%26nbsp%3Ba%20is%20drawn%20then%20and%20numer%26nbsp%3B1%20(when%20drawn%20as%20a%20main%20ball)%20or%20number%202%20(when%20drawn%20as%20a%20bonus%20ball)%26nbsp%3Bis%20entered%20in%20the%20relevent%20colum%20next%20to%20the%20date%20it%20was%20drawn.%3C%2FP%3E%3CP%3ECell%20E316%20contains%20the%20day%20average%20(this%20is%20the%20day%20total%20from%20the%20the%20first%20to%20the%20last%20draw%20entered%20devided%20by%20the%20number%20of%20times%20drawn).%3C%2FP%3E%3CP%3EThe%20problem%20is%20that%20when%20i%20use%20ctrl%2Bshift%2Benter%20the%20formula%20return%20%23VALUE%20in%20cell%20317.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20questuion%20please%20let%20me%20know.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESteven%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-99716%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-99716%22%20slang%3D%22en-US%22%3E%3CP%3ESteven%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20i%20understood%20correctly%20the%20only%20thing%20you%20did%20is%20change%202248%20on%202283%20in%20your%20formulas%3F%20And%20perhaps%20that's%20misprint%20in%20your%20description%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CP%3Eto%20%3DMAX(IF('Draw%20Results'!C1%3AC2283%3D%7B3%2C4%7D%2C'Draw%20Results'!D1%3AD2283))%3C%2FP%3E%3CP%3EWhat%20it%20does%20is%20to%20show%20the%20last%20draw%20number%20entered%20in%20in%20column%20A%20where%20either%203%20or%204%20are%20entered%20in%20column%20D.%20The%20answer%20should%20be%202257%20but%20it%20comes%20out%20as%20%220%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FBLOCKQUOTE%3E%3CP%3EAnd%20i%20guess%20it's%20misprint%20in%20your%20description%20-%203%26nbsp%3Bor%204%20are%20entered%20in%20column%20C%2C%20not%20D.%20Number%20is%20returned%26nbsp%3Bfrom%20column%20D%2C%20not%20A.%20At%20least%20that's%20what%20your%20formula%20do%20-%20returns%20max%20in%20D%20for%20the%20rows%20where%203%20or%204%20in%20C.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyway%2C%20zero%20means%20formula%20doesn't%20find%203%20or%204.%20Another%20reason%20could%20be%20cells%20in%20C%20are%20formatted%20as%20text%20and%20you%20have%20text%20%223%22%20instead%20of%20number%203.%20You%20may%20check%2C%20for%20example%2C%20by%20ISNUMBER%20on%20the%20cell%20with%20this%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20shorten%20the%20range%20for%20the%26nbsp%3Btest%20you%20may%20take%20C2256%3AC2258%20(instead%20of%20C1%3AC2283)%2C%20same%20for%20other%20ranges.%20Evaluate%20shall%20show%20something%20like%3C%2FP%3E%3CPRE%3EMAX(IF(%7BFALSE%2CFALSE%3BTRUE%2CFALSE%3BFALSE%2CFALSE%7D%2CD2258%3AD2258))%3C%2FPRE%3E%3CP%3Eon%20second%20step.%20If%20in%20C2257%20is%20number%203.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-99697%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-99697%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20again.%20ive%20tried%20that%20and%20even%20shortened%20the%20range%20but%20it%20is%20still%20the%20same.%20Not%20sure%20what%20iam%20looking%20at%20when%20i%20do%20the%26nbsp%3B%20evaluate%20thing.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-98235%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-98235%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Steven%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20just%20an%20assumptions%20-%20your%20formula%20always%20returns%20zero%2C%20other%20words%20always%20returns%20FALSE%2C%20thus%20most%20probably%20doesn't%20work%20on%20entire%20range%2C%20that%20could%20means%20it's%20entered%20not%20as%20array%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStay%20on%20the%20cell%20with%20your%20formula%2C%20shift%20into%20cell%20edit%20mode%20(F2%20or%20double-click)%20and%20Ctrl%2BShift%2BEnter%20then.%20%7B%7D%20shall%20appear.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20check%20how%20it%20works%20in%20ribbon%20Formulas%20-%26gt%3B%20Evaluate%20formula%20and%20follow%20the%20steps.%20But%20better%20to%20shorten%20your%20range%20for%20such%20tests.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-98191%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-98191%22%20slang%3D%22en-US%22%3EHi%20Sergie.%20How%20do%20I%20do%20that%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-98138%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-98138%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Steven%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20re-check%20your%20updated%20formula%20are%20array%20ones%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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.

 

Each formula is entered with ctrl+shift+ebter

Tried other excell help sites but got no help so please don't let me down

32 Replies
Highlighted

Hi Steven,

 

I can't reproduce that. Whatever i enter in cells from E1 to E247 i have no error in E317.

 

Is file you attached with previous post with error? And please, what's your Excel verson?

Highlighted

Hello again. sorry it took so long to reply. My last message i said about it not working ok. SORRY it seems its my fault. i had not put the formula in right. It seems ok but ill let you know. thank you for your help.

Highlighted

Hi Again. seems i've not got it figured out. I have attached the sheet. As you can see ive put the draw results in row 249. and in row 317 under the draw results then #VALUE appears in the that cell. not sure whay but could you please let me know what ive done wrong. Thank you

Highlighted

Hi Steven,

 

Sorry, missed this your reply. I tried to play with the file you send a week or so ago and was not able to reproduce the error. But that was no data in row 249.

 

Could you please send me the file where you see the error?

Highlighted

Hello Again. Ive attached the sheet. wot ive done is and your formula to the cells in row 317.  Ive added the a new set of draw results in to row 249. If you look at cells K317, R317 etc you'll see #VALUE

Highlighted

Hi Steven,

 

Unfortunately in the file you attached i don't see the formulas (only #REF) since 'General Lotto Info' tab is removed. Could you please send full file to check?

 

If you care about the confidentiality send the link where i can download it with in-private message.

Highlighted

Hi Sorry about that. i had to delete some of the none essential page and deleted the wrong one. Ill try again. Please try this one.

Hi Steven,

 

That's since in B249 you enter the text "12.7.17" instead of the date in your locale format. If change on date (actually number) formulas work. Please see attached.

Highlighted

Hi Sergei. Ive done as you have suggested but still getting the same probelm. Ive attached a PDF of a screen shot of what i get when i try to open the file to work on it. I have circled the cells that have #Value in them incase the image is not great. 

Highlighted

Hi Steven,

 

As the date you have to enter the date in your locale format (12/07/2017 by default for UK) or simply the number 42928 (which is equivalent of that date). Or by refrence on previous date

=B248+4

And only after that apply format if necessary.

 

Please see the file in my previous post.

Highlighted

Hi Sorry it took so long to reply but my laptop has been away for repair. Will try your suggestion as sson as i can. thank you

Highlighted
Hi service. Tried all you said and looked at the file you attached but still won't work.
Highlighted

Steven,

 

That could be something else, but i see no errors in my copy of the file. How your date is entered you may check in formula bar. If as text

DateText.JPG

you have an error, if as date

DateAsDate.JPG

all works