Excel Formula Help - Technology - Contractor Talk

Excel Formula Help

 
Thread Tools Search this Thread Display Modes
Old Yesterday, 04:07 PM   #1
I own stock in FotoMat!
 
480sparky's Avatar
 
Trade: 132 on Bush, I've got him at gunpoint!
Join Date: Jan 2009
Location: Iowa
Posts: 11,915
Rewards Points: 11,682

Excel Formula Help


Need help with creating an Excel formula. It's 'cuz I don't even know if it can be done, and if so, what the name of the formula would be.

So I'll try to explain.

Let's say I have Rows 1-100, Column A, populated with a bunch of numbers. Let's say they're random, anywhere from 1-1000.

Somewhere else on the sheet I want to create a list of all the numbers (in list A1:A100) that are between 500 and 599. In other words, generate a 'short list' of all the values that start with 5nn.

I know I can simply 'sort' the column, then scroll down until I run into the 500s, but that's too time-consuming. In reality, I'm using tens of thousands of rows.

I tried creating a 'unique' list using

{=INDEX(list,MATCH(0,COUNTIF(uniques,list),0))}

but with no success.

Anyone? Help? Beuhler?
__________________
This post has been modified from its original version. It has been formatted to fit this screen and edited for content.
480sparky is online now  

Warning: The topics covered on this site include activities in which there exists the potential for serious injury or death. ContractorTalk.com DOES NOT guarantee the accuracy or completeness of any information contained on this site. Always use proper safety precaution and reference reliable outside sources before attempting any construction or remodeling task!

   

Advertisement

 

Old Yesterday, 08:57 PM   #2
Pro
 
Jay hole's Avatar
 
Trade: General contractor
Join Date: Nov 2013
Posts: 774
Rewards Points: 1,631

Re: Excel Formula Help


Maybe a sum if formula???....there is a ton of info on ablebits.com


Sent from my iPhone using Tapatalk

Advertisement

Jay hole is offline  
Old Yesterday, 09:05 PM   #3
I own stock in FotoMat!
 
480sparky's Avatar
 
Trade: 132 on Bush, I've got him at gunpoint!
Join Date: Jan 2009
Location: Iowa
Posts: 11,915
Rewards Points: 11,682

Re: Excel Formula Help


SUM only performs addition.
__________________
This post has been modified from its original version. It has been formatted to fit this screen and edited for content.
480sparky is online now  
Sponsored Links
Advertisement
 
Old Today, 05:59 AM   #4
Pro
 
goneelkn's Avatar
 
Trade: home remodel/repair
Join Date: Jan 2010
Location: Wisconsin
Posts: 860
Rewards Points: 68

Re: Excel Formula Help


=if(a1>499;(if(a1<600;a1;0));0)
goneelkn is offline  
Old Today, 08:16 AM   #5
I own stock in FotoMat!
 
480sparky's Avatar
 
Trade: 132 on Bush, I've got him at gunpoint!
Join Date: Jan 2009
Location: Iowa
Posts: 11,915
Rewards Points: 11,682

Re: Excel Formula Help


Quote:
Originally Posted by goneelkn View Post
=if(a1>499;(if(a1<600;a1;0));0)
Even after editing to make it work, it returns the results of only one cell. Copying the formula down thousands of rows would still require scrolling down through all the cells.
__________________
This post has been modified from its original version. It has been formatted to fit this screen and edited for content.
480sparky is online now  
Old Today, 08:46 AM   #6
LRG WoodCrafting

 
Leo G's Avatar
 
Trade: Maker of Fine Sawdust
Join Date: May 2005
Location: Windsor Locks, Connecticut
Posts: 40,123
Rewards Points: 10,655

Re: Excel Formula Help


Something like this. Probably have to modify it with greater than signs

https://exceljet.net/formula/create-array-of-numbers
__________________
Sawdust Follows Me Everywhere
I can explain it to you, but I can't understand it for you.
Sanding is the bane of my existence
WWG1WGA

Quote:
Originally Posted by HusqyPro View Post
Carpenter by day.
Mad scientist by night.
http://lrgwood.com
Custom Cabinets in Hartford County Connecticut
Leo G is online now  
Old Today, 08:49 AM   #7
LRG WoodCrafting

 
Leo G's Avatar
 
Trade: Maker of Fine Sawdust
Join Date: May 2005
Location: Windsor Locks, Connecticut
Posts: 40,123
Rewards Points: 10,655

Re: Excel Formula Help


Another way to do it, the long way around. Is to assign a formula to each cell and if the number shows up in your selected range it shows. It won't give you a nice short compact list in a small area. But it will be easy for you to find the numbers because they show up and all the numbers above or below show a null cell.
__________________
Sawdust Follows Me Everywhere
I can explain it to you, but I can't understand it for you.
Sanding is the bane of my existence
WWG1WGA

Quote:
Originally Posted by HusqyPro View Post
Carpenter by day.
Mad scientist by night.
http://lrgwood.com
Custom Cabinets in Hartford County Connecticut
Leo G is online now  
Old Today, 09:33 AM   #8
I own stock in FotoMat!
 
480sparky's Avatar
 
Trade: 132 on Bush, I've got him at gunpoint!
Join Date: Jan 2009
Location: Iowa
Posts: 11,915
Rewards Points: 11,682

Re: Excel Formula Help


Quote:
Originally Posted by Leo G View Post
Another way to do it, the long way around. Is to assign a formula to each cell and if the number shows up in your selected range it shows. It won't give you a nice short compact list in a small area. But it will be easy for you to find the numbers because they show up and all the numbers above or below show a null cell.

That's the issue. I can easily create a formula that will show the desired results in Column B with a simple IF statement, but it's just as long as Column A. Endless scrolling, and the results are stretched down the sheet.

I end up with



501
































579


588







517











545











560



571











522























594






















563
















501



























































583

















577









541



































516































588



























554












503






































511






539



































594

























539





































528

593









530
__________________
This post has been modified from its original version. It has been formatted to fit this screen and edited for content.
480sparky is online now  
Old Today, 10:08 AM   #9
LRG WoodCrafting

 
Leo G's Avatar
 
Trade: Maker of Fine Sawdust
Join Date: May 2005
Location: Windsor Locks, Connecticut
Posts: 40,123
Rewards Points: 10,655

Re: Excel Formula Help


Yep, I know. It's not an elegant work around.
__________________
Sawdust Follows Me Everywhere
I can explain it to you, but I can't understand it for you.
Sanding is the bane of my existence
WWG1WGA

Quote:
Originally Posted by HusqyPro View Post
Carpenter by day.
Mad scientist by night.
http://lrgwood.com
Custom Cabinets in Hartford County Connecticut
Leo G is online now  
Old Today, 10:15 AM   #10
LRG WoodCrafting

 
Leo G's Avatar
 
Trade: Maker of Fine Sawdust
Join Date: May 2005
Location: Windsor Locks, Connecticut
Posts: 40,123
Rewards Points: 10,655

Re: Excel Formula Help


https://www.get-digital-help.com/200...lumn-in-excel/

This is interesting.
__________________
Sawdust Follows Me Everywhere
I can explain it to you, but I can't understand it for you.
Sanding is the bane of my existence
WWG1WGA

Quote:
Originally Posted by HusqyPro View Post
Carpenter by day.
Mad scientist by night.
http://lrgwood.com
Custom Cabinets in Hartford County Connecticut
Leo G is online now  
Old Today, 10:23 AM   #11
LRG WoodCrafting

 
Leo G's Avatar
 
Trade: Maker of Fine Sawdust
Join Date: May 2005
Location: Windsor Locks, Connecticut
Posts: 40,123
Rewards Points: 10,655

Re: Excel Formula Help


Ya, that's the ticket. Here's the video that goes along with the page


Advertisement

__________________
Sawdust Follows Me Everywhere
I can explain it to you, but I can't understand it for you.
Sanding is the bane of my existence
WWG1WGA

Quote:
Originally Posted by HusqyPro View Post
Carpenter by day.
Mad scientist by night.
http://lrgwood.com
Custom Cabinets in Hartford County Connecticut
Leo G is online now  


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Join Now... It's Fast and FREE!

I am a professional contractor
I am a DIY Homeowner
Drywall Talk is for
PROFESSIONAL CONTRACTORS ONLY!

At DrywallTalk.com we cater exlusivly to professional contractors who make their living as a contractor. Knowing that many homeowners and DIYers are looking for a community to call home, we've created www.DIYChatroom.com DIY Chatroom is full of helpful advices and perfect for DIY homeowners.

Redirecing in 10 seconds
No Thanks
terms of service

Already Have an Account?