Hi,
I have a data list of numbers. I wish to find the next number in the data list. Does anyone know how to do this with a nested IF statement? I tried this one, but, it doesn't seem to work.
=IF(A1="","",IF(A1=(A2-1),"",IF(MATCH(A1+1,A1:A91),A1+1,MAX(A1:A91)+1)))
I also tried this one, but it dosn't work either:
=IF(ISNA(MATCH(TRUE,(A2:A91-A1:A90)>1,0)),MAX(A1:A91)+1,INDEX(A1:A91,MATCH(TRUE,(A2:A91-A1:A90)>1,0))+1)
Thanks,
Bonkley
Not sure what you mean by "find the next number". Do you want to find a number that's actually in the list or one that isn't? Perhaps you can give an example, sample data with expected result......
It would help if you gave us a example of the list you're trying to search through... are there empty cells, or are they just not in order or what?
Chad
Originally Posted by daddylonglegs
The number not in the list... that's the next number.
ok 86*3.1427 thats not in the list so that must be your answer!
you need to provide an example of what you have and what you expect the answer to be. it may be clear to you but the rest of us dont have a crystal ball lol
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Sure. Look at my original post... I said, I have a data list (a1:a91). I am trying to find the next expected value, based on the data list. Here is the list:
17
23
8
8
2
20
6
5
1
7
5
5
2
2
13
17
3
1
21
3
7
16
19
10
2
2
14
1
7
16
7
15
8
6
3
7
4
19
6
23
19
5
1
16
10
15
13
5
3
16
18
9
4
19
1
26
4
4
5
2
5
25
4
10
14
14
3
1
1
18
9
4
13
17
5
7
18
41
12
2
13
5
5
6
14
3
2
10
7
8
19
Based on the formulae in my original post, does anyone know how to write a similar formula that would give an expected value, based on the data list, for A92?
Nevermind.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Just put this in cell 92:
=RANDBETWEEN(1, 59)
Whatever it says is right. The list is, after all, random. So every answer is correct.
(http://www.excelforum.com/excel-prog...-sequence.html)
is there a logic behind this list?
Not again! We've been through this in http://www.excelforum.com/excel-prog...-sequence.html
Whether you can calculate the next value, or what it might be, would depend on how those numbers are generated.
If they are independent of each other then the next number could presumably be any number within the constraints you have set.
If they aren't independent then one would need to know the relationship between them.
according to the forum rules this is a duplicate post.
Bonkley, could you please stick to one thread for the same problem.
And please understand that random numbers can not be predicted. You'v received many comments and even some formulas in the other post.
Let's not start again.
that's it !
in the words of Dragons den.
"I'm out"
I find it hard to believe that, in spite of the foregoing formulae I posted, in plain view, NO ONE
I find it hard to believe that, even with the foregoing formuae I posted as a basis for finding the nth number in a data list, NO ONE is resourceful enough (frequencies, d values, etc) to even formulate the logic. I am starting to wonder if this is just "paper moon" stuff, to where people pretend to be omniscent, and brandish their surety reps, but all the while know jack shi*.... geezzz!
Careful...! We're all volunteers here, no need to get insulting.
ok I'm in for a second go!
but you said neither of them work!in spite of the foregoing formulae I posted, in plain view
so how does that clear things up.
just checking my calender is this April the 1st?
now i'm really out
Bonkley, one of the definitions of random, courtesy of dictionary.com isIf we could predict it with a formula, it really wouldn't be random. Read shg's answer to me in the last thread, where he posts Microsoft's algorithm for random numbers. The one for 2007 produces about 10^13 numbers before it repeats. It's complex simply because it's not meant to be easily solved and put into a short formula. We're not denying you an answer because we're all pretentious noobs as you imply (some of the mods are even Microsoft MVPs), we're denying you an answer because there is no answer.Statistics. of or characterizing a process of selection in which each item of a set has an equal probability of being chosen.
Bonkley, I find it easy to believe that someone can come up with a question for which there isn't actually a satisfactory answer. (to them)
I find it easy to believe it can be discussed calmly. Quite easy in fact.
I find it easy to believe that when faced with a consensus from many "somewhat knowledgeable" people... that a person can still choose to believe something completely different. You've proven that all by yourself.
I find it easy to let you do that and not laugh at you. Surely you can have others tell you things you don't want to hear without getting emotional about IT. Surely.
If you're referring to your posited query, I would have to agree. Very likely a paper moon pursuit.I am starting to wonder if this is just "paper moon" stuff...
Oh, you mean you don't like what you've heard so everyone here must be idiots? How truly surprising. All these people taking time out of their day to engage you in your chosen topic and the best you can offer back is "oh yeah...you're stupid"...?...to where people pretend to be omniscent, and brandish their surety reps, but all the while know jack shi*.... geezzz!
Surely you can do better than that. Surely.
I think an apology is in order, and would cost you nothing more than a few moments of typing to provide...
Surely you'll choose make your mother proud and to offer one. Surely.
Last edited by JBeaucaire; 11-16-2009 at 08:21 PM.
... and some of the other contributors, too, but they don't advertise it.some of the mods are even Microsoft MVPs
OK, three strikes.
this is the third time I'm asking you not to use Private Messaging for exchanges pertaining to threads.
I feel harassed.
Mods, Can I ask for some Moderation, please"Formulas"?... I don't recall anyone giving a formula, other than myself; and, even at that, no one took the time look at them, or try to understand the question. How can I put it in more reduced terms? Let me try one last time... how do I get Excel to read a data list of numbers, and then based on the data list, either give me the nth term, OR, the probability (between 0 and 1) for the nth term of the list??
Get it now??
Bonkley, if you have something to say, please say it in the thread.
Entia non sunt multiplicanda sine necessitate
Thread Tools > Unsubscribe > "ahhhhh"
@JB
LOL! Ditto: click here, then User Lists > Add to Ignore List
Peace.
PS. Edit: Absolutely friggin' brilliant: I don't see any of the ignored poster's posts in any thread, nor can I see any of the PMs. To quote JB: Ahhhh!
Last edited by teylyn; 11-17-2009 at 05:15 AM.
me to! got my own PM
well i am apparently a buffoon
well i must say i resemble that remark.
also i reckon i qualify as
A jester, joker, jokester, fool, wit-cracker, prankster .
thanx for the compliment.
I have a feeling Bonkley is taking a time-out, so let's all just relax and help others who appreciate it.
vibes ....
FWIW - I am closing this thread given this is in essence a duplicate (and the latter posts bear no resemblance to the question).
Bonkley, whether or not you resolve your issue with the assistance of the board please remain courteous at all times. Those trying to provide solutions do so in their own time and at no charge.
If you feel the standard of the board is insufficient to meet your requirements then we would suggest you post in an alternative forum going forward (of which there are many).
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks