setting up an algorythm to assign numbers to cells

[Micbrook]

Hi

This should be straight forward enough but im not sure if it just needs a formula or macro. Basically want to set up a sheet where I can enter data and it puts it into place for me. Any advice appreciated

Thanks


algorthym.xlsx

[FDibbins]

Can you walk me through what you are doing here?

[cyiangou]

Enter into cells B1:B37

Please Login or Register  to view this content.

[Micbrook]

FDibbins - Column A is the layout for roulette. the vision is that id enter the last 9 winning numbers into the box in F column which would put them into place in B column inline with A column. ideally, id then want to analize the data and see if I could split the 36 numbers into 4 groups of 9, or 3 groups of 12. Still to investigate the next step, maybe id need to enter the last 20 numbers rather than just 9, once the first step works I can have a play and see what I can come up with.

Cyiangou - thanks but that doesn't seem to work?

[FlameRetired]

Cyiangou - thanks but that doesn't seem to work?

Cyiangou's formula works at my end if I have interpreted correctly. Could you be more specific? What about it does not work?

[Micbrook]

For some reason it only works in B1 yet have copied it as said... Maybe I haven't done it correct

[cyiangou]

I'm not familiar with Office 365. Perhaps it doesn't have an IFERROR function?

This one almost works:

Please Login or Register  to view this content.

[Micbrook]

Ah Ive been using 2007 all night, not my PC with 365. I'll have a look see what I've done wrong must be operator error if the first formula works for others. Thanks

[FlameRetired]

@ cyangou

You might be right about that. The help file lists almost every version post 2003 except 365.

This is often used backwards compatible for .xls files. However it returns text representations of numbers. They will have to be reconverted back to numeric values to be any use as numbers.

Please Login or Register  to view this content.

[Micbrook]

Does it have to be an .xls file? Didn't think of that when I started

[FlameRetired]

No. This will work in all versions. It is often used when IFERROR is not available (.xls files are one example) and saves duplicating calculations in an IF formula. In this case it also suppressed the lone #VALUE! error that cyiangou's second formula generated.

Have you tried the first formula again?

[Micbrook]

still cant do it. I always get the F17 showing in column B. To confirm, 'Enter into cells B1:B37' I highlight the cells and paste the formula into each?!

[cyiangou]

There's your problem: Paste it into B1 only. Then use Excel copy-and-paste to copy it down to B37

[cyiangou]

Never mind, here's your file with the working formula:

algorthym_cy.xlsx

[Micbrook]

Oh yea doh! Many thanks
Will investigate how to split numbers into groups

[Micbrook]

cant decide how to group numbers together... does this sheet make sense? ive looked so long im loosing the plot.. can anyone spot the obvious? would like to make 3 groups of 12 numbers based on the 12 numbers entered

numbers below.xlsx
thanks

[FlameRetired]

Not sure what this part is about, but I notice that there are 2 25s in column F. That gives wrong (?) returns in column B. 25 pairs with 34 when it should pair up with 33. What do you want those changed to?

Edit There are also 2 30s in column M.

[Micbrook]

this is for roulette so will duplicate numbers from time to time. this spreadsheet might be a better idea. basically the intention is to input the last X amount of numbers and try form some sort of number 'groups' for predicting where the ball might go next. not exact science, just looking for a rough guess.
This sheet shows 3 colour groups. light colours show the order of the inputted numbers... the darker shades represent what numbers should also be in the same group, the white group of numbers is the remainding ones.

colour groups.xlsx

[FlameRetired]

Micbrook,

This looks like an intriguing project. The instructions are not clear to me.

I need your help. Can you re-upload the WB from post #18? This time please hand type in the exact outputs you are looking for in the column(s) you want the outputs in. Please include the color coding if you think it will help. Include the arrows again if you think that will help clarify. Also please tell us how you want to handle any duplicate numbers with examples also hand typed in.

[Micbrook]

by all means open for suggestions of other ways to do it!
colour groups v2.xlsx

[FlameRetired]

Unfortunately I'm no clearer than I was. I cannot tell which you are testing for match up to what. Color referencing only is not making it clearer.

From the WB notes:

The instructions arent clear as there isnt any real hard fast rules. Im looking for any vague sort of pattern that 'might' help. The colours merely represent where the ball landed with each spin. With this idea, its trying to colour coordinate and see if anything stands out.

Without having some pattern-hypothesis for testing there are no formulae that are going to help, and I doubt I would recognize suitable patterns anyway. Your only recourse is to hand type numbers in, look for patterns manually and hope for inspired insight. I know of no way to make a computer do that.

If you have testable pattern(s) in mind please post back. I would love to help.

I think I have done all I am able. Wish you the best.