I have a task. I want to start at the right of an array, and look at every second cell towards the left. I need to count the number of zeros I find.
The diagram shows which cells I am referring to. The sequence goes 0,8,0,8,1 so I need to return the value '2' to show that I found 2 zeros before I hit a non-zero value.
ChatGPT is usually good at formula and macro creation but this was beyond its ability.
Any ideas?
countzeros2.jpg
This is probably doable with a formula - is that OK?
Firstly, which version of Excel are you using now?
Secondly, there are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.
A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.
Thirdly, I have it on very good authority that code generated by ChatGPT is often pretty poor.
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.
Thanks, I am attempting to attach a worksheet. A formula would be fine. I use excel 2016 (I think).
Not good enough.I use excel 2016 (I think).
Please check your version and then update your profile accordingly. If you had Excel 365, this would be very much easier.
Profile done. It is definitely Excel 2016.
OK - thanks. I can't help with that version, but someone else will.
You probably have to close this with ctrl+shift+return (arrayformula)
Please Login or Register to view this content.
Try this in B5
=(COLUMN($BJ5)-MAX(COLUMN($A5:$BJ5)*(ISEVEN(COLUMN($A5:$BJ5))*$A5:$BJ5<>0)))/2
committed with Ctrl+Shift+Enter and copied down.
Thanks. I need help with this. I am not familiar with array formulae. Looking up array formulae its says 'Earlier, legacy array formulas require first selecting the entire output range, then confirming the formula with Ctrl+Shift+Enter.'
I want the formulae to go from BN5 to BN30. Do I select that range, enter the formula and press Ctrl+Shift+Enter?
Here a Power Query solution too. You find both the formula and query in the file.
Just enter the formula in the first cell with ctrl shift enter. Then pull down.
PHP Code:let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
cols = Table.SelectColumns(Source, List.Transform({1..31}, each Text.From(_))),
nCol = Table.AddColumn(cols, "Output", each List.PositionOfAny(Record.FieldValues(_),{1..100})),
out = Table.SelectColumns(nCol,{"Output"})
in
out
Enter the formula in the first cell of the range and confirm it by pressiong CTRL+SHIFT+ENTER (NOT just ENTER). Once this is done, and the curly brackets have appeared, you can drag copy it to the rest of the range in the usual way.
Thanks. The example by windknife seems to work fine. I will try and copy that to my spreadsheet and see if it works.
Many thanks to everyone. I have no idea how it works - that will take some time to figure out. But I put windknife's solution it in my spreadsheet with altered cell references and it works!
Brilliant
If you want an explanation, just ask.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
Another solution for all Excel versions (from 2007):
Formula:
Please Login or Register to view this content.
[QUOTE=HansDouwe;5849052]Another solution for all Excel versions (from 2007):
That also works. Thanks.
I see one problem. My spreadsheet grows with time, extending columns to the right. A quick test shows that you can't simply drag the array formula to a new position and have it automatically change cell references. Is there a way around this?
Here's a custom function you could use.
Just paste the code into a module and in your example you would use it like this, then copy down.
Formula:Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by skywriter; 07-15-2023 at 02:19 PM.
Click the * Add Reputation button in the lower left hand corner of this post to say thanks.
Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.
Another way... here i have assumed your last column will be BP. Ajust that in BOTH formulae, as needed. I moved the result to the LEFT hand side, to remove the need to constantly move them as the table extends rightwards.
F1, copied across:
=IF(ISODD(COLUMN()),"",MAX(0,(AGGREGATE(14,6,COLUMN($F$2:$BP$2)-COLUMN($F$2)+1/($F$2:$BP$2<>""),1))/2-INT((COLUMNS($F1:F1)-1)/2)))
B2, copied down:
=INDEX(F$1:BP$1,MATCH(2,1/($F2:INDEX(2:2,AGGREGATE(14,6,COLUMN(F2:BP2)/(F2:BP2<>""),1))<>0)/ISEVEN(COLUMN($F$1:$BP$1))))-1
Please refer to sheet for adjusted layout.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
If you want drag the formula to right if your spreadshees grows, please try first the formula below and drag to right when the spreadsheet grows:Formula:Please Login or Register to view this content.
Last edited by HansDouwe; 07-15-2023 at 02:04 PM.
Thanks, this method makes it easy to adjust as the table grows to the right.Originally Posted by skywriter
Is it possible to use the contents of a cell as a cell reference in =CountZeros(BJ5) instead of specifying BJ5 so I could enter a new starting point in one cell instead of changing the reference and dragging down?
Or am I being too lazy?
Last edited by GeorgeC47; 07-16-2023 at 04:47 AM.
BN5
Committed with Ctrl+Shift+Enter and copied down.Please Login or Register to view this content.
You can increment dynamically data.
Thanks. In the real spreadsheet my data top left starts at cell DZ22. I am sure $A$5 must be changed to $DZ$22 and $A5 becomes $DZ22.
Are there other changes to be made, for examples the 5:5 ?
if you data start DZ22, you can modify 5:5 to 22:22
I'm not sure what you man by this.
I wrote the function to work with it placed to the right of your data so you only select the right most cell in your data.Is it possible to use the contents of a cell as a cell reference in =CountZeros(BJ5) instead of specifying BJ5 so I could enter a new starting point in one cell instead of changing the reference and dragging down?
Or am I being too lazy?
It can be written where you have to select all the data you want the function to work on or it can be written to select the left most cell in your data.
Just be aware that when I write it to where you choose the right or left most cell in your data there can be no empty cells between the first and the last cell.
The coding changes are all very simple.
Perfect! It works like a dream. Thanks for your help.
Many thanks to all those who contributed. There is a lot of talent on display here!
You are welcome.
To windknife,
This is the sheet with added data.
The function stops working at row 74, but if you drag the column with the functions further to the right, then it works for more rows.
Very strange.
Last edited by GeorgeC47; 07-21-2023 at 11:53 AM.
Try this in BT5 (change $A$5 to $A5)
=(LOOKUP(2,1/($A5:INDEX(5:5,,COLUMN()-1)<>""),COLUMN($A$5:INDEX(5:5,,COLUMN()-1)))-MAX(COLUMN($A5:INDEX(5:5,,LOOKUP(2,1/($A5:INDEX(5:5,,COLUMN()-1)<>""),COLUMN($A5:INDEX(5:5,,COLUMN()-1)))))*(ISEVEN(COLUMN($A5:INDEX(5:5,,LOOKUP(2,1/($A5:INDEX(5:5,,COLUMN()-1)<>""),COLUMN($A5:INDEX(5:5,,COLUMN()-1))))))*$A5:INDEX(5:5,,LOOKUP(2,1/($A5:INDEX(5:5,,COLUMN()-1)<>""),COLUMN($A5:INDEX(5:5,,COLUMN()-1))))<>0)))/2
committed with Ctrl+Shift+Enter and copied down.
Marvellous! I did wonder why some were $A$5 and others $A5.
Many thanks.
If you only copy down the formula you don't need a $-sign before the rownumbers, but if there is a $-sign it does not care.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks