Counting number of blank cells until value found in cell

jnutt

New Member
Joined
Mar 11, 2004
Messages
2
My cell range is "E4:AJ4". These cells are either blank or have a "1" in them. I need to be able to count from "E4" until the first "1" is read and I need to display this value. If there is a "1" in "E4" I need to display a "0"; If there is a "1" in "J4" I need to display a "5"; etc. My programming is not strong and I have not been able to find examples close enough to meke this work. Any help would be appriciated.

Thanks
Jack
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
=MATCH(1,E4:AJ4,0)-1

{Edit}Doggone it -- I forgot my manners for the umpteenth time...Welcome to the board! :) {EndEdit}
 

AlohaJulio

Board Regular
Joined
Feb 10, 2004
Messages
188
Welcome to the board.

I think you can use the match function
=MATCH(1,E4:AJ4,0)-1
the -1gets you to start counting at 0 for column E

EDIT: nicely done Greg, cheers (y)
 

andreashak

New Member
Joined
Mar 15, 2010
Messages
30
Welcome to the board.

I think you can use the match function
=MATCH(1,E4:AJ4,0)-1
the -1gets you to start counting at 0 for column E

EDIT: nicely done Greg, cheers (y)


Hey guys I have a similar problem with a row of numbers.
The series is either (annual 1 number every 12 columns, semiannual number every 6, quarterly number every 3 columns, monthly number every column or none remaining in which case there would be no numbers between the current cell and the last cell in the series).
Unfortunately the number that may be found in the series is not constant and therefore I am looking for any number.
The actual function is converting a % from one of the series (annual, semiannual, quarterly, monthly) types into a monthly % series. (1+number)^(1/12)-1

I hope this is clear enough for someone. Unfortunately I cant upload an excel example from work. Sorry.
 

andreashak

New Member
Joined
Mar 15, 2010
Messages
30

ADVERTISEMENT

Ok I am possibly the dumbest person ever.
I used the following formula

=IF(ISBLANK(F37),E63,CHOOSE($E$32,(1+F37)^(1/12)-1,(1+F37)^(6/12)-1,(1+F37)^(3/12)-1,F37))

Basically if the cell in the series (corresponding to the date in the result series) is blank, then get the last cell value in the result series, if its not blank choose the method to convert the above reference to a monthly rate dependant on another drop down.

Thanks.
 

nss123

New Member
Joined
Sep 28, 2017
Messages
1
Thank you for your response. I have a situation below

I have 12 months Jan - December in columns. Contractors with salaries are in the row.
columns B C D E F G H I J K L M
jan Feb mAR apr May Jun Jul Aug Sep Oct Nov Dec
ROW
4 Name 1. 4000 5000 1000
5 Name 2 1000 2000

I am looking for a countblank till I see a dollar/numbers in Cell. For example Name 1 has Jan, Feb, Mar Blank before April , so the count will be 3 blanks.

Thank you


=MATCH(1,E4:AJ4,0)-1

{Edit}Doggone it -- I forgot my manners for the umpteenth time...Welcome to the board! :) {EndEdit}
 

Cargueta

New Member
Joined
Mar 19, 2012
Messages
2
Dear all,

I was wondering if you could help me out with a similar problem.

I have a calendar in Excel, where I would like to mark the start and end of a time lapse, but I would also like to only count the business days.

NBBDBDBDBDNBNBNBBDBDBDBDNBNBBD
MonTueWedThuFriSatSunMonTueWedThuFriSatSunMon
123456789101112131415
StartEnd

<tbody>
</tbody>


So, NB means not a business day, as you can see the Sat and Sun are NB, and there is also a holiday on Monday 8.

Would it be possible to count with a formula (COUNTIF, SUMIF, etc.) the working days between the Start and the End?

Please let me know if you could help me out.

Best regards,
Carlos
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,574
Members
414,390
Latest member
plimbu

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
Top