Hi to all,
this formula:
find the values but if it does not find values, it displays 0,Please Login or Register to view this content.
How to not see 0?
thank you
john
Hi to all,
this formula:
find the values but if it does not find values, it displays 0,Please Login or Register to view this content.
How to not see 0?
thank you
john
how about adding an IF in the front, like ... =IF(IFERROR(INDEX($A$2:$A$15,MATCH(0,INDEX(COUNTIF(B$1:B1,$A$2:$A$15&""),),0)),"")=0,"")
Another option I have found is that you can format the end result so that using conditional formatting >> new rule >> format only cells that contain >> make cell value equal to and put 0 in it then go to format and format as font color and pick white or whatever background color your cell has so it disappears.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Hi sambokid
the new formula not work result FALSE
what are you trying to do with the formula? It looks to me like it returns the first value it finds from A2 to A15 and if that value is a 0 it returns the 0. Without changing your formula and there are no values in A2:A15 it returns blank for me. So the only reason I see it returning a 0 is because the first value it finds in that range is a 0 (like if A2 and A3 are blank and A4 has 0 and A5 has 12, it returns the 0). Have you tried the option using conditional formatting to make the 0 disappear?
this seems to have removed the zero ... =IF(IFERROR(INDEX($A$2:$A$15,MATCH(0,INDEX(COUNTIF(B$1:B1,$A$2:$A$15&""),),0)),"")=0,"",IFERROR(INDEX($A$2:$A$15,MATCH(0,INDEX(COUNTIF(B$1:B1,$A$2:$A$15&""),),0)),""))
However, if the first value it finds in A2:A15 is a 0 (like in A4) and there is a value greater than that, say in A5 that will simply return a blank and not the value which as best as I can tell is what your formula would've done anyway.
By the way, the other recommendation I could give is that if any of the results in A2 through A15 are being generated by a formula you might want to change the formula so that it returns blank instead of a zero then your formula might work fine, just a thought.
O.k. sambo kid when work well,
thank you
Another problem in workbook attached sheet grafico why in the coolonna C if B is empty is number 682?
john
If I understand your intention, and you are trying to return unique values and ignore zeros you may try this array formula.
***Array formula. must use ctrl+shift+enter key combination.
Formula:Please Login or Register to view this content.
v A B 1 2 10 10 3 21 21 4 0 45 5 45 321 6 321 4 7 0 32 8 4 9 4 10 32
Last edited by AlKey; 06-18-2019 at 05:04 PM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Thankyou alkey your formula work well.
john
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
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.
A help for post #6?
Try this:
=IF(B2="","",IF(COUNTIF(Input!$D$6:$D$690,B2)=0,"",COUNTIF(Input!$D$6:$D$690,B2)))
Your formula will do countif two times. If your data is large, this will cause Excel slowly.
I recommend using this formula instead of your formula.
Please Login or Register to view this content.
Thank you huutang_bd and aligw
Your formula work well.
john
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks