Welcome Guest
You last visited January 18, 2017, 11:21 pm
All times shown are
Eastern Time (GMT-5:00)

# Looking for Excel Formula

Topic closed. 19 replies. Last post 1 year ago by AllenB.

 Page 1 of 2
ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
5963 Posts
Offline
 Posted: August 20, 2015, 10:02 am - IP Logged

Need an Excel formula that will measure skips using the <= and >= commands. Anyone have one and would like to share, I would be much obliged.

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
5963 Posts
Offline
 Posted: August 20, 2015, 10:38 am - IP Logged

Need an Excel formula that will measure skips using the <= and >= commands. Anyone have one and would like to share, I would be much obliged.

This is the formula, I am hoping someone can explain the syntax. Keep getting formula contains an error.

=IF(COLUMNS(\$R10:R10)<=COUNTIFS(\$B\$2:\$B\$5316,">=1",COUNTIFS(\$B\$2:\$B\$5316,"<=50",COUNTIFS(\$B\$2:\$B\$5316>=1,COUNTIFS(\$B\$2:\$B\$5316<=50,ROW(\$B\$2:\$B\$5316)-ROW(\$B\$2)),COLUMNS(\$R10:R10))

New Jersey
United States
Member #17843
June 28, 2005
51064 Posts
Offline
 Posted: August 20, 2015, 11:25 am - IP Logged

This is the formula, I am hoping someone can explain the syntax. Keep getting formula contains an error.

=IF(COLUMNS(\$R10:R10)<=COUNTIFS(\$B\$2:\$B\$5316,">=1",COUNTIFS(\$B\$2:\$B\$5316,"<=50",COUNTIFS(\$B\$2:\$B\$5316>=1,COUNTIFS(\$B\$2:\$B\$5316<=50,ROW(\$B\$2:\$B\$5316)-ROW(\$B\$2)),COLUMNS(\$R10:R10))

=IF(COLUMNS(\$R10:R10)<=COUNTIFS(\$B\$2:\$B\$5316,">=1",COUNTIFS(\$B\$2:\$B\$5316,"<=50",COUNTIFS(\$B\$2:\$B\$5316,">=1",COUNTIFS(\$B\$2:\$B\$5316,"<=50",ROW(\$B\$2:\$B\$5316)-ROW(\$B\$2)),COLUMNS(\$R10:R10))

Missing ,">=1"

A mind once stretched by a new idea never returns to its original dimensions!

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
5963 Posts
Offline
 Posted: August 20, 2015, 11:37 am - IP Logged

Thanks for responding, where is that entered into the formula??

New Jersey
United States
Member #17843
June 28, 2005
51064 Posts
Offline
 Posted: August 20, 2015, 1:09 pm - IP Logged

Thanks for responding, where is that entered into the formula??

Compare the formula you posted with the copy & pasted formula posted with corrections applied.

A mind once stretched by a new idea never returns to its original dimensions!

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
5963 Posts
Offline
 Posted: August 20, 2015, 1:16 pm - IP Logged

Compare the formula you posted with the copy & pasted formula posted with corrections applied.

Still getting error message.

https://app.box.com/s/tbonkt9m0jxv2q6hed2zfj8domuygz9j

New Jersey
United States
Member #17843
June 28, 2005
51064 Posts
Offline
 Posted: August 20, 2015, 1:49 pm - IP Logged

=IF(COLUMNS(\$R10:R10)<=COUNTIFS(\$B\$2:\$B\$5316,">=1",COUNTIFS(\$B\$2:\$B\$5316,"<=50",COUNTIFS(\$B\$2:\$B\$5316,">=1",COUNTIFS(\$B\$2:\$B\$5316,"<=50",ROW(\$B\$2:\$B\$5316)-ROW(\$B\$2)),COLUMNS(\$R10:R10))

Missing ,">=1"

Oops missing )'s

=IF(COLUMNS(\$R10:R10)<=COUNTIFS(\$B\$2:\$B\$5316,">=1"),COUNTIFS(\$B\$2:\$B\$5316,"<=50"),COUNTIFS(\$B\$2:\$B\$5316,">=1"),COUNTIFS(\$B\$2:\$B\$5316,"<=50"),ROW(\$B\$2:\$B\$5316)-ROW(\$B\$2)),COLUMNS(\$R10:R10))

Was it your intention to include an AND or an OR?

A mind once stretched by a new idea never returns to its original dimensions!

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
5963 Posts
Offline
 Posted: August 20, 2015, 3:07 pm - IP Logged

No, the formula still gives an error message.

=IF(COLUMNS(\$R10:R10)<=COUNTIFS(\$B\$2:\$B\$5316,">=1"),COUNTIFS(\$B\$2:\$B\$5316,"<=50"),COUNTIFS(\$B\$2:\$B\$5316,">=1"),COUNTIFS(\$B\$2:\$B\$5316,"<=50"),ROW(\$B\$2:\$B\$5316)-ROW(\$B\$2)),COLUMNS(\$R10:R10))

Pennsylvania
United States
Member #2218
September 1, 2003
5396 Posts
Offline
 Posted: August 20, 2015, 7:27 pm - IP Logged

Bob,

Not sure is this is what you wanted:

=MATCH(1,--(B2:B5136>=1)*--(B2:B5136<=50),0)  Ctrl Shift Enter

Michigan
United States
Member #81740
October 28, 2009
41988 Posts
Offline
 Posted: August 21, 2015, 3:13 am - IP Logged

Need an Excel formula that will measure skips using the <= and >= commands. Anyone have one and would like to share, I would be much obliged.

Wish I even knew how to use it.

Be Happy.

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
5963 Posts
Offline
 Posted: August 21, 2015, 3:42 am - IP Logged

Bob,

Not sure is this is what you wanted:

=MATCH(1,--(B2:B5136>=1)*--(B2:B5136<=50),0)  Ctrl Shift Enter

 1726 956 748 48

Thanks Steve, that works, with one exception. Next question, just to clarify. The values listed are in descending order, current to oldest. I count 3 draws as being current skip, right or wrong? Or is it 4, which is what your formula returns. If my count is right, I can compensate with a -1. Just want to be sure of the count.

It only works for one instance. It doesn't work if I change the range from >=50 & <=100, returns wrong value.

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
5963 Posts
Offline
 Posted: August 21, 2015, 7:10 am - IP Logged

You can forget this thread, figured out a different way of doing the same thing. Thanks to Raven62 and Steve, much appreciated.

New Jersey
United States
Member #17843
June 28, 2005
51064 Posts
Offline
 Posted: August 21, 2015, 8:26 am - IP Logged

Wish I even knew how to use it.

You can: Teach Yourself, Take a DVD Course, Attend a Class at the Library or Local School where you live.

A mind once stretched by a new idea never returns to its original dimensions!

New Jersey
United States
Member #17843
June 28, 2005
51064 Posts
Offline
 Posted: August 21, 2015, 8:30 am - IP Logged

You can forget this thread, figured out a different way of doing the same thing. Thanks to Raven62 and Steve, much appreciated.

If you're willing to share the alternate way of doing the same thing I'd like to hear about it.

A mind once stretched by a new idea never returns to its original dimensions!

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
5963 Posts
Offline
 Posted: August 21, 2015, 12:48 pm - IP Logged