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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
=MATCH(1,E4:AJ4,0)-1

{Edit}Doggone it -- I forgot my manners for the umpteenth time...Welcome to the board! :) {EndEdit}
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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}
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

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