Formula Help Please

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,831
Office Version
  1. 2010
Platform
  1. Windows
Can anyone help with a formula please… I have rows of data and what I want to know are the rows that have a zero anywhere between numbers greater than 1…

So B1:H1 is a row of interest because zeros appear between numbers greater than 1… Where B2:H2 is not of interest because all the numbers greater than one are in one single block… My workbook is much bigger than this but I’ve just used this as an example…

Would be great if the formula could going in A1,A2,A3, please…
 

Attachments

  • Numbers.jpg
    Numbers.jpg
    32 KB · Views: 10

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Very similar to something from yesterday.
MrExcelPlayground5.xlsx
ABCDEFGH
1TRUE1001500
2FALSE0011100
3TRUE10101010
Sheet10 (3)
Cell Formulas
RangeFormula
A1:A3A1=SUMPRODUCT(--(B1:H1=0),--(COLUMN(B1:H1)>=MIN(IFERROR(1/(1/((SEQUENCE(COLUMNS(B1:H1))*(--(INDEX(B1:H1,1,SEQUENCE(COLUMNS(B1:H1)))>0))))),""))+COLUMN(B1)-1),--(COLUMN(B1:H1)<=MAX((SEQUENCE(COLUMNS(B1:H1))*(--(INDEX(B1:H1,1,SEQUENCE(COLUMNS(B1:H1)))>0))))+COLUMN(B1)-1))>0
 
Upvote 0
Thanks James... I'm getting #Value error... Finger trouble with me or is it due to me using xl2010...?
 
Upvote 0
James' formula uses some Excel 365 functions. See if this works:

Book1
ABCDEFGH
1TRUE1001500
2FALSE0011100
3TRUE10101010
Sheet10
Cell Formulas
RangeFormula
A1:A3A1=COUNTIF(INDEX(B1:H1,AGGREGATE(15,6,(COLUMN(B1:H1)-COLUMN(B1)+1)/(B1:H1<>0),1)):INDEX(B1:H1,AGGREGATE(14,6,(COLUMN(B1:H1)-COLUMN(B1)+1)/(B1:H1<>0),1)),0)>0
 
Upvote 0
Thanks Eric, that's exactly what I hoped for...
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top