I need aformula at F2 and draga down ,which work like:if A2 is a number and in K1:K18 there are numbers(in case I put manually) and A2 matches in the range of K1:K18 write 1 if not exist write 0. the most important is when there no numbers at K:1K18 should show embty/blank.!! so the formula works only in case there are numbers at K1:k18.
thanks so much for your help at this forum!!!
I think this is what you want:
=IFERROR(IF($A2="","",MATCH(A2,$K$1:$K$18,0)),"")
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
i make an example:lets say we are at spin 10(A10=8) and the same time at K1:K18 there are numbers ,if 8 is in the range ofK1:K18 formula should show 1if no 0,at the next spin the same..,the formula should takeinto consideration the existing of numbers in K1:K18(becouse in case i dont put numbers there manually ,the formula at F1:F36 should show empty
Try this instead:
=IF(OR($A2="",COUNT($K$1:$K$18)=0),"",IF(ISERROR(MATCH(A2,$K$1:$K$18,0)),0,1))
Or maybe this:
=IF(OR($A2="",COUNT($K$1:$K$18)=0),"",IF(ISERROR(MATCH(A2,$K$1:$K$18,0)),0,A2))
or this:
=IF(OR($A2="",COUNT($K$1:$K$18)=0),"",IF(ISERROR(MATCH(A2,$K$1:$K$18,0)),"",A2))
the formula should take into consideration A:A(spins) together with the range k1:k18 and count only if there are numbers in k1:k18, if the spins continuew and there are no munbers at k1:k18 should show empty
iam not so sure)
OK, then use the second option in post #5, which seems to match your last attachment.
i have another example with a forula i you gave me be fore some months,the problem with this formula it shows 0 if there are no numbers T K1:K18 and it should show empty,ineed to change the formula to empty,please see attached file
ok, iwill try also post#5
i check post 5# the second form ,yea.., it shows empty when the number is not in the range but it should show 0,becouse what i need is ::i want to count the 0s later with another formula(the zeros in case there are numbers atK1:K18 always,in case the number at A:A is not in range
i hope you understand me)),thank you so much
maybe we can compine two formula? i mean the second is :if there is a number at K1:K18 start to count ,i am dont know ,is hard for me vey difficult
You keep changing your requirements!
Try this (last attempt):
=IF(OR($A2="",COUNT($K$1:$K$18)=0),0,IF(ISERROR(MATCH(A2,$K$1:$K$18,0)),0,A2))
i dont know what to do, maybe what iam asking is not possible to excel
I doubt it, but you are not making a very good job of clearly explaining what you want.
i agree.i will try one more time:if there are numbers at range k1:k18 start counting and if there are no numbers the cells to be embty.only if thre are numbers at k:k the formula should start work
counting means that:the formula look at roulette spins at A:A and if the the last spin(number) exist in the numbers at k1:k18 to show 1 ,otherwise zero(0).
(the final thing or aim is that i want to know how many times the numbers at A:A is not in the range of K1:18.so if we have 4 zeros for example in a row it will mean that 4 times did not win)
important note:the K1:K18 some times is embty or there are 18 numbers which i put manually whenever i want,the formula should be smart)) to know wheh there are numbers there).
Last edited by zzz444; 08-02-2020 at 11:01 AM.
What do you mean by the last spin number? What are we supposed to be counting?
Already covered in three of the formulae I've already given you.important note:the K1:K18 some times is embty or there are 18 numbers which i put manually whenever i want,the formula should be smart)) to know wheh there are numbers there).
Last edited by AliGW; 08-02-2020 at 11:10 AM.
spin are goes to A100 lets say,so when there are numbers at k1:k18 and the lat number at spins range is in not the numbers of k1:k18 count 0,then if the next number is also not in k1:k18 count 0 (so we have to 0s 0 ,if exists write 1 ,if there are no numbers =embty
0
It this does NOT do what you want, provide a workbook with this formula in place and annotate it to show where it goes wrong.
=IF(OR($A2="",COUNT($K$1:$K$18)=0),"",IF(ISERROR(MATCH(A2,$K$1:$K$18,0)),0,1))
Last edited by AliGW; 08-02-2020 at 11:25 AM.
yes Ali its ok but it the trick is at th following example :lets say that th 19 th spin number is the result that will make me put manually 18 random numbers at k1:k18,i want the formula to start counting from that event otherwise i will not be able to count how many lose i will have from the time that numbers exist in k1:k18
A formula cannot do this: it can only work based on what is there or not there, not on when that data is added.lets say that th 19 th spin number is the result that will make me put manually 18 random numbers at k1:k18,i want the formula to start counting from that event
Sorry, but you are going to need to use VBA coding for this.
I cannot help you any further. Because your thread title is so poor given what we now know about your requirements, I am going to mark this thread as SOLVED and ask you to start again, with a MUCH better tttle AND a MUCH better description of what you want in the VBA section of the forum.
Thank you for your co-operation.
yes is working !!!thanks so much
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks