returns number of blank cells between values

**danroll** · 12-15-2009, 05:32 AM

ok so:

i have a list of 100 product codes in row b2:cw2, colum A contains dates in the month, yesterdays first and then each previous day before that going down, and the columns B:CW are the number of each particular product sold on that day.

I'd like to insert a formula in row 1 which will look down each column and count the number of blank cells from B3 to the first cell to contain a value, i.e. the number of days since this particular product was sold.

any ideas?

thanks in advance.

**teylyn** · 12-15-2009, 05:38 AM

Hi,

if the cells contain numbers, you could try

=MATCH(1,B3:B100,-1)-1

adjust ranges to suit.

hth

**danroll** · 12-15-2009, 05:58 AM

sorry, some of the cells contain text, reasons for non sale etc.

is there a formula that could take this into account?

**DonkeyOte** · 12-15-2009, 06:02 AM

If I've understood - assuming you wish to find first numeric value etc to determine days where total sales days can be determined by number of dates in Column A then perhaps:

Please Login or Register  to view this content.

convoluted but should be pretty robust.

**danroll** · 12-15-2009, 06:36 AM

thanks but that's still not quite right. maybe i've worded it wrong.

basically, i want to insert a formula in cell b1 which will count the number of blank cells between b3 and the first cell going down to contain any value.

so if the first cell to contain the value is b9 the formula should return 6.

thanks for all the help i really appreciate this.

**DonkeyOte** · 12-15-2009, 06:50 AM

You say row 3 is yesterday and the entire question would imply you do not have sales for all products on all days.

On that basis I am confused by your requirements - on which basis the most basic of questions is what happens if B3 is number/non-number ?

The formula provided previously assumes that if B3 is non-number 0 is the result whereas were B3 non-number and B9 first number then the output would be 6 as you say.

If that is not what you want (and seemingly it is not) then to prevent a game of 20 questions I would suggest you post a sample file which outlines the various nuances you face and the expected output for each.

**danroll** · 12-15-2009, 07:04 AM

hi,

please see attached.

in cell b1 i would like to insert a formula that looks down column b and counts all the blank cells from B2 to the first cell that contains a value. the value can be either the number of sales or a specific reason for non sale i.e. due to non delivery of stock.

so in the sample file for example b1 would return 4, c1 would return 2, d1 would return 3 etc.

thank you.

**DonkeyOte** · 12-15-2009, 07:08 AM

So row 3 is in fact completely blank...

The prior formula for B1 does what you want you just need to change the reference to B3 to be B4 - ie the first days records.

EDIT: scrap that - no it doesn't - you need to change ISNUMBER test - revised below

If you're inserting rows such that you can't use the reference B4 (ie will become B5 when a new row is physically inserted) then use INDEX instead, ie:

Please Login or Register  to view this content.

else if you prefer to keep B3 then subtract 2 rather than 1 from the result (to account for row 3 being ineligible)

**danroll** · 12-15-2009, 07:12 AM

can i just ask why your formula references column A. column A is irrelevant to this issue. i only want to count blank cells from b onwards?

thanks

**DonkeyOte** · 12-15-2009, 07:15 AM

It is used to account for the possibility that a given a column may contain only blanks (wasn't stipulated either way) - in these circumstances the result is in essence the number of days as listed in Column A.

**danroll** · 12-15-2009, 07:23 AM

thanks for all your help, much appreciated.

**danroll** · 12-15-2009, 07:28 AM

sorry, just one more question:

if i then wanted to insert a line underneath row 1 which calculated the average number of days were between each cell with a value would that be possible.

so, the average number of cells between sales for product 1001 (using previous sample) is roughly XX.

thanks

**DonkeyOte** · 12-15-2009, 08:54 AM

If you wanted say the average length of any non-sale streaks then first off I would create a name to determine row of last day, eg:

Please Login or Register  to view this content.

Then making use of the above and having inserted a row between rows 1 & 2 in your original file (such that first data now appears in A5 rather than A4)

Please Login or Register  to view this content.

Note: this is not averaging the number of days without sales (where sales were possible) rather the average length of an avoidable non-sales streak.
For the former you would simply take the COUNTBLANK of the B range etc and divide by the COUNT of A.

I should add I suspect there's a better method than the above I just couldn't think of a decent way to get Frequency to handle opening / closing sales streaks

returns number of blank cells between values

LinkBack

Thread Tools

Rate This Thread

Display

returns number of blank cells between values

Re: returns number of blank cells between values

Re: returns number of blank cells between values

Re: returns number of blank cells between values

Re: returns number of blank cells between values

Re: returns number of blank cells between values

Re: returns number of blank cells between values

Re: returns number of blank cells between values

Re: returns number of blank cells between values

Re: returns number of blank cells between values

Re: returns number of blank cells between values

Re: returns number of blank cells between values

Re: returns number of blank cells between values

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions