Excel formula needed.

bloubul

Member
Hi t*t
Thanks it does remove the 2nd digit from the double digits, but it also removes the single digits as well.

BlouBul :cool:
 

time*treat

Member
Conditionally -- handles single and double digits.
=IF(x>9,INT(x/10),x)

example
=IF(RC[-1]>9,INT(RC[-1]/10),RC[-1])
 

Frank

Member
Well theres more than one way of doing this, either by a combination of COUNTIF and IF statements to not count duplicate occurrences, per ball, or by using an OR function that you say counts the duplicates ? Not the way I write them. The OR function is less wordy in its raw form, and has to be used 5 times in each result, one per drawn ball, then adding the different 5 comparisons to get a total number of unique matches.
I would normally use hidden columns to store part results if it were for me, but you can combine all 5 functions into one long line of functions.
However you do it, a problem crops up for L5 and L10 when you cannot go back in time beyond the topmost result. You cannot look back 10 results if you are already checking any of the 9 (oldest) topmost results, for matches in the past which you dont have on the sheet. These bring up #REF! errors. I dont like to see those, so then you need the whole thing wrapped up in an IF(ISERROR .... function to replace the #REF! with something else. I have replaced #REF! with a dash -.

Sheet 1 and sheet 2 are the same, but sheet 2 has some notes attached.
These formulas only work for the current layout, the formula has to be on the same row as the result being checked (against others in the past). Presumably you know that if you click in the formula cell, then click on the formula bar, Excel shows you what the formulas are working on.

They can be safely copied downwards to new results, but not copied upwards where a dash exists ( say you inserted new rows and added earlier data at the top) the row referencing gets muddled when you ask it to do the impossible.
Anyway see what you make of this ...

http://www.mediafire.com/file/3flg1k2o056t0jn/Draws.xlsx/file
 

Frank

Member
Nice to see you too Blitzed, not many people post sensible items any more. It’s all been said before, but I still keep my brain ticking over doing spreadsheets, more to solve math puzzles these days.
 
Top