FHB Logo Facebook LinkedIn Email Pinterest Twitter X Instagram Tiktok YouTube Plus Icon Close Icon Navigation Search Icon Navigation Search Icon Arrow Down Icon Video Guide Icon Article Guide Icon Modal Close Icon Guide Search Icon Skip to content
Subscribe
Log In
  • How-To
  • Design
  • Tools & Materials
  • Restoration
  • Videos
  • Blogs
  • Forum
  • Magazine
  • Members
  • FHB House
  • Podcast
Log In

Discussion Forum

Discussion Forum

MS Excel Question

CAGIV | Posted in Business on January 29, 2006 08:34am

Question 1 answered,

time for question 3

I’m trying to find a way to find a way for excel to calculate for me the number of occurences a value is greater then x and less then y in a range of numbers

example, I have a range of numbers say it’s ;   255, 1800, 4,800, 7,500 13,000, 15,050, 89,000,  etc.

What I want to know is  how many occurences there that are

1, Greater then 0, less then 2500

2 between 2,501 and 5,000

3 between 5,001 and 10,000

4 between 10,001 and 25,000

etc.

anyone?

and if anyone know’s of a friendly excel forum?

View Image


Edited 1/29/2006 5:13 am ET by CAGIV


Edited 1/29/2006 5:13 am ET by CAGIV

Reply
  • X
  • facebook
  • linkedin
  • pinterest
  • email
  • add to favorites Log in or Sign up to save your favorite articles

Replies

  1. Catskinner | Jan 29, 2006 08:45am | #1

    Look into the Help section for Conditional Testinf and the IF function.

    A little time with IF will get you what you are looking for.

    Also on the Insert dropdown go to Function and then Logical. There should be a wizard in there to walk you through.

    A king can stand people fighting but he can't last long if people start
    thinking. -Will Rogers, humorist (1879-1935)

    1. CAGIV | Jan 29, 2006 09:03am | #2

      Beautiful,

      =SUMIF is the function, or at least one that works

      Second Question ;)

      and I'm not sure this is possible.

      I'm cataogizing jobs, example, Basements, Bathrooms, MISC Repairs, Additions, etc.

      If I created cell's that were labeled Basements, Bathrooms at a different point in the spread sheet, does excel have the ability to look through the column "Job Type" and tell me how many times the word "basement" or "Bathroom" etc appears?

       

      1. richk1 | Jan 29, 2006 02:49pm | #3

        I think it can be done by using the COUNTIF function. Use the job type column for the range and "bathroom" as the criteria. You just have to make sure that the spelling is consistant because it matches what's in the quotes exactly.Rich Knab

        1. User avater
          BillHartmann | Jan 29, 2006 05:18pm | #5

          I don't use excel, but does it have a drop down menu for fill in the banks. Using that you can setup and option that will drop down a list of options (exterior, kitchen, bathroom, master suite, basement, etc) and you can only pick one from those options. Not only does it solve the spellinng problam, but also limits the catatgories so that some one does not make up new ones each time.

          1. MAsprayfoam | Jan 29, 2006 10:19pm | #12

            you can do that.  In the help area type in "DROP DOWN MENU" (of all things) and you will find how to do it.

            Stu

          2. CAGIV | Jan 30, 2006 05:26am | #19

            Stu,

            I tried the help menu and was unable to figure it out, do you know how to do it?

          3. EWong | Jan 30, 2006 05:48am | #20

            hmmm - *this* computer has Excel2000 and cant seem to do it either.

            What version of Excel are you using?

          4. MAsprayfoam | Jan 30, 2006 05:52am | #21

            I made a file with the pull down options and I put the directions in there.  Of course this will be my first attempt attaching a file in this forum so.... here goes nothin!

             

             

            File format
          5. CAGIV | Jan 30, 2006 06:59am | #22

            Ewong, I think it's 2k but I'm not sure? maybe not

            MA, thanks I needed that, was getting ready to start pulling hair

             

          6. CAGIV | Jan 30, 2006 01:08am | #14

            Bill,

            I've been looking in help but can figure out how to make the drop down menu for different job types, any ideas?

             

          7. pm22 | Jan 30, 2006 03:48am | #16

            If you are serious about pursuing Excel, the book to get is the Excel Bible by John Walkenbach, available from Amazon, library, etc. It includes a work-around to do the histogram thing.

            ~Peter

            Why didn't our Maximum Leader stop or even warn us about the Seattle-Tacoma earthquake. Our dear leader is so powerful but he failed us here.

          8. Lansdown | Jan 30, 2006 04:30am | #17

            I was once shown this cool trick with Excel 95, where if you depressed a certain sequence of keys the page launched into what looked like a video game and displayed all the developers names.

          9. junkhound | Jan 30, 2006 07:44am | #23

            You are absolutely correct, but I don't recall the sequences. 

            Youngest son wrote the calculator for windows (even got 'about' credit on 3.1 and '95 under 'help' until he retired) , another kid in the scout troop had a hand in Win 95 excell. 

            According to them, those who know how can access all the hidden files on who wrote what part of what programs from excell to word and whatever else.  Also apparently some of the gurus who worked on the original programs have personal ways to go in and decompile/compile portions to add their own upgrades.   

            PS: even with access to some of the 'gurus',  I needed a few columns to do  hyperbolic trig functions on complex numbers.

            Edited 1/29/2006 11:46 pm ET by junkhound

          10. Lansdown | Jan 30, 2006 02:34pm | #25

            I'm glad you remembered and it wasn't a dream I had. I wrote it down somewhere, it was only a couple of keystrokes. If you ask your son, let me know.

          11. User avater
            SamT | Jan 30, 2006 05:35pm | #26

            JH,

            They're called "Easter Eggs."

            XL 97:

            The Pyramid Excel Easter Egg1. Open a new workbook 2. Press F53. Type in the expression following the hypen exactly, and with no spaces - X97:L97 4. Press Enter5. Press Tab6. Press the Ctrl and Shift buttons simultaneously and then click the Chart Wizard button on the toolbar while holding down the buttons.

            You should see a full screen animation and you can use your mouse to fly around the landscape and explore. At some point in your travels you should see a grey pyramid. Fly around it until you reach a side that's black - there you will find the credits.

            XL 2000

            CreditsLaunch Excel and press F5. Type: x2000:|2000 and press Enter. Hold Ctrl + Shift. Click the Chart Wizard and enjoy the credits.

            Game!Open Excel and on the blank worksheet, click File > Save As and choose Save as web page. Click on Publish and Add interactivity and save the file with any name you like. Load the page with Internet Explorer and go to row 2000 Column WC. Highlight all of row 2000 and press Tab to make column WC the active column. Hold down Ctrl + Alt + Shift and click on the Office Icon. Use the arrow keys to drive your car. The "0" key to drop paint slicks. The space bar to shoot. The "H" key for headlights. Note: You need DirectX for this to work.

            Word 97

            Easter Egg - Pinball Game (Word 97 only) An "Easter Egg" is a secret message or feature hidden somewhere in a program, that can be revealed by entering some unusual combination of commands. To get to Word's hidden Pinball Game:

            Create a new document.

            Type "Blue" (without the quotation marks).

            Highlight the word (double-click it) and select Format, and choose Font.

            In the Font Style, select Bold. In the color box, select Blue.

            Click OK.

            Add a space at the end of the word Blue.

            Select Help, and choose About.

            While holding down the CTRL and Shift keys, click on the Word logo. (See graphic at right.[ed: See link.])

            Use Z for the Left flipper and M for the Right flipper! (The ESC key will exit the game.)

            Word 2000

            Word Bug 1: 1:Open Word document and type = rand (200,99)2:Press enter and wait 3 seconds...

            Word 97

            Snake Eat Men and Mouse :1: Start a new document.2: Hold down Ctrl, 3, g, w. [Ed: Doesn't work on mine.]

            Many more can be found at http://www.eggheaven2000.com/

            SamT

          12. User avater
            BillHartmann | Jan 30, 2006 05:20am | #18

            I don't have Excel so I don't know.If fact when I posted the comment I said "IF excel has that feature".

      2. EWong | Jan 29, 2006 07:16pm | #9

        Somone already pointed out - the COUNTIF function.As pointed out - computers are finicky - "Basement" is NOT the same as "basement"Now the NEAT thing is you cna use the SUMIF function with a criteria set to a differnt column - in other words you could sum up all the "basement" work done.Now if you DONT KNOW how many types of descriptors you have used - ACCESS is a bit easier to use becuase it can pull a report breaking out each descriptor seperately.In the sample I also coded the critera in the column to the left. This allows easy cut and past of the formulas.

        Edited 1/29/2006 11:19 am ET by EWong

        File format
        1. EWong | Jan 29, 2006 07:21pm | #10

          opps - cant edit by adding an attachment.../

          File format
        2. CAGIV | Jan 29, 2006 11:18pm | #13

          Thanks that helps, I'm going to try the suggestion of a drop down menu for choices of job type and referal source.  What I'm doing is probably much better suited for Access, however we do not have Access on the work computers, and from what I remember of Access in college classes,  I didn't like it very much ;)

           

      3. Catskinner | Jan 30, 2006 07:57am | #24

        We've got a whole bunch of Excel users here, don't we? I should have guessed. It's a great tool. Yep, just another tool. <G>As has been already pointed out, yes, you can make Excel count these. But what I have found more useful is the Sort operation.Drop down Data, hit Sort, you can look at the database any way you want to any time you want and then put it back to the way it was with a few mouse clicks.Once you get the Sort start messing with Subtotals (also under Data dropdown) and I think you'll get the answers you are looking for.A king can stand people fighting but he can't last long if people start
        thinking. -Will Rogers, humorist (1879-1935)

        1. CAGIV | Jan 30, 2006 09:19pm | #27

          Last question, I swear ;)

          Those are the fields I have under Sold Jobs, the last piece of this puzzle I'm trying to do would be to have a sub-total of all work based on the Job Type

          Is there a way for me to to have a function that could look through the l Job types column, and everytime it saw "Window" it would take that value and sum it with all other "window" jobs found in the job type column?

          I'm trying to use the help menu's but so far they haven't been much help at all.

          Edited 1/30/2006 1:20 pm ET by CAGIV

        2. CAGIV | Jan 30, 2006 09:24pm | #28

          Whoops, need to attach the file

          File format
          1. JMadson | Jan 31, 2006 12:12am | #29

            First Sort by Job Type. The sort function is under Data. (see the jpeg attached)

            After it's sorted, run the Subtotal function, also under Data. It's very easy and does exactly what you need without any formulas

            Joe

            File format
          2. CAGIV | Jan 31, 2006 01:05am | #30

            Joe, thanks, I'll play with that a little.

            I want to use a forumla so I can just look at one cell, deterimine the percentage of each job type as well as each lead type, and ratio of those in dollars to total either sold job or proposed jobs, the reason I want to do that with a formula/function, if possible, is because I want to be able to enter the data once, and have all the information calculated for me, with out having to do anything else.

            You guys have been great helping me out with this, thanks again Neil

          3. CAGIV | Jan 31, 2006 04:35am | #31

            Got it to work

            =SUMIF(C2:C7,"Window",D2:D7)

          4. Catskinner | Jan 31, 2006 04:57am | #32

            <<Got it to work>>Way cool. Congrats. I don't know how I worked without Excel. <G>A king can stand people fighting but he can't last long if people start
            thinking. -Will Rogers, humorist (1879-1935)

          5. CAGIV | Jan 31, 2006 09:56am | #34

            http://forums.taunton.com/tp-breaktime/messages?msg=68928.15

            The product of all your guy's help is in that message if anyone is interested

          6. Catskinner | Feb 04, 2006 06:01am | #39

            Nice work.What is Column B?It's interesting looking at the picture that emerges when you start analyzing your business, isn't it?Success is not the key to happiness. Happiness is the key to success.
            -Albert Schweitzer, philosopher, physician, musician, Nobel laureate
            (1875-1965)

          7. CAGIV | Feb 05, 2006 12:31am | #40

            Columb B is simply a place holder, I put a 1 in for every lead that comes in, and at the bottom of the column it totals it up, that total is used in the other equations as the total number or leads for the month.

            I realize now I could have probably done the same thing with a "count" function, however at the time I knew nothing about excel other then how to use it like a basic calculator.

            I wish it was my business, but I'm an employee, though I am trying to better understand what makes the company run and how to improve it, the more the company makes the more I can make ;)

          8. User avater
            SamT | Feb 05, 2006 01:27am | #41

            Another piece of analysis you can do on your Sheet with COUNTIF and SUMIF is compare sales to calls and proposals.

            SamT

          9. CAGIV | Feb 05, 2006 01:34am | #42

            Sam,

            If you look further down the page, or on one of the last workbooks, I have it comparing the sales/proposal ratio in both dollars and number.

            or am I missing what you are telling me?

            wouldn't be the first time one flew over my head.

            Neil

          10. User avater
            SamT | Feb 05, 2006 03:56am | #43

            CAG,

            am I missing what you are telling me?

            Probably not, since I missed that part of that page.

            My Bad.

            Well,. . .I think there is a chance of some possibility that it may be that I am misunderstanding your wrong communications(|:>)

            Also I want to try your Workbook out after it's perfected.

            Leads. In our business we don't use Leads. A Lead is when you get a name of someone who may never have heard of you. You call them.

            We use RFB's by some name or another. That's when someone calls us. That's what you mean when you use "Lead."

            We also use Calls. A Call is a selling type communication. For us, that usually means a meeting. A walk in is a kind of Call.

            I'm sorry about being pedantic, but I want to insure we are talking about the same things.

            Then there are Proposals. The Proposal comes after the initial Call, but before the Sales Closing. You can do a Call and not get to the Proposal. You can do a Proposal and not get the Sale.

            What you're not tracking is the number of Calls that don't lead to a Proposal. I would think there was value in knowing; 1] Why no Proposal; 2] What type job; 3] value of job. That's information you're missing regarding 63% of your leads. IOW, money that is equal to twice your revenue.

            Something I would suggest is that you take all your monthly sheet calculations and tabularise them on one worksheet called maybe "Trends" or "Monthlys."

            You should become very familiar with "Insert>Name>Define" and "Insert>Name>Create". Using defined Names AKA Named Ranges allows you to write formulas like =January!Jobs_Sold and =SheetName!ColumnName SheetName!RowName where the space in the formula is the Intersection Operator. It's also really handy for reducing the amount of typing.

            Fer instinze: Get all the formulas in the row for January fixed and ctrl+D them into place for every month, then use the "Edit>Replace" command to replace January with February in Febs slot, March in . . . See my attachment for what I mean.

            Hmmmnnn. . .It might not display properly cuz it references another workbook. Lemmee know, OK?

            SamT

          11. CAGIV | Feb 05, 2006 07:02am | #44

            Sam,

            First, the link locked my PC up when I tried to open it, so it didn't work.

            As for the "perfected" version of the spread sheet I'd imagine that's down the road a bit I want to test drive it as for a few months and see how it works for me.  I have found a serious calculation error though.  The % of work sold vs proposed calculation references the wrong cell in the monthly workbooks.  Other then error checking do you have any suggestions now?  Other then the Trends sheet, which I think is a good idea.

            What does RFB stand for?  Request for Bid, Request for Business?  I've never heard what you are referring to as anything but a lead though I'll admidt to having limited experience in sales.  I've been at this all of a year now.

            Just so I understand, a Call would be any meeting where I'm trying to sell work?  what else would a "call" be?

            What you're not tracking is the number of Calls that don't lead to a Proposal. I would think there was value in knowing; 1] Why no Proposal; 2] What type job; 3] value of job. That's information you're missing regarding 63% of your leads. IOW, money that is equal to twice your revenue

            This is where you loose me, If I go on a call, and make a proposal, and do not close the sale, I'm confused about how you calculated the 63%.  The way I'm looking at the sheet is so far I have not closed on 63% of the work I have proposed,  there is no information in regard to the number of "calls" I go on that I do not make proposals on? 

            I'm going to stop now until I'm sure I'm using these terms correctly?

          12. User avater
            SamT | Feb 05, 2006 04:25pm | #45

            Proposal = estimate. . .workup . . .bid. . . some guy counting studs, guesstimating labor, poreing over plans, calling subs, adding up numbers. On a whole house or major remodel a Proposal can take days to work up. Not something you want to spend Labor on unless you're pretty darn sure of closing the sale.

            Many contractors won't do a Proposal without being paid for it.

            It sounds like your sales are one-meeting closes.

            In the Sales glossary a lead is contact info about a prospect you haven't met. Nigerian scammers buy email addresses for lead lists. Telemarketers can use the phone book as a lead list. I use the permit application list of my county and send letters to the owners. Coliquially used as if the person was the lead, as in "Boy this lead looks good. He lives in the best part of town."

            A Referal is the other side of Lead. A Referal is where the Prospect is given contact info about a seller they haven't met.

            If your client tells YOU, "Here's my sisters number. Please call her. She needs a new kitchen." That's a Lead.

            If she tells her sister, "CAGS a great contractor. Call him about your kitchen." That's a referal.

            Cold Call: Initial Call on a Lead.

            A Prospect is a Lead you have contacted.

            A Close is an action or statement designed to cause a contract signing decision. "Just sign here." is a crude, but surprisingly effective, Close. Close is also used to mean 'getting the signature.' "I Closed that sale in 10 minutes!"

            A Close is to a salesman what a nailset is to a trim carpenter. When you proposed to your wife you Closed. If you hadn't Closed her, you would never have gotten married no matter how you discussed your future with her.

            RFB means Request for Bid. You should also use it as "Request for Business" which would be used for the initial contact by the prospect with your company. Sounds like a Lead if you don't know the technical definition of Lead.

            Estimate should mean a ballpark price and that's the way I use it. Many use it to mean Proposal.

            A Proposal is the formal Scope of Work and price offering you present to the client for their approval. The Proposal will be part of the contract.

            A Call would be any meeting where I'm trying to sell work?  Yes. A large project requires at least two Calls. One to discover what is wanted and one to present the Proposal and get the signature on the contract.

            What else would a "call" be? A phone conversation with someone. Call your wife.

            Here's a little story that may illustrate the above. It's a Salesman reviewing his day.

            "I made five Calls today. The first one was a Cold Call on a Lead I got from a friend. She just wanted a deck, so I gave her the square foot price and she signed on my first Close.

            The next Call was an RFB for a bathroom remodel from our Penny Saver ad. They didn't have the budget to cover what they wanted, so I turned down the sale. (Ed: Did not Close.)

            The next Call was a Follow Up to present the Proposal Jim started Last week, he finished it yesterday. Everything looked great, they had the money and liked our company and really needed a new kitchen. They didn't buy. Later, I realised I forgot to Close them.

            My next Call was an RFB for a Master Suite add on. They were Refered by another client. I got a check for the Proposal so Jim won't be working for free on this one. Jim needs to finish the Proposal by the 15th. I will Call on them then to sell the project."

            The Last Call was also a Follow Up. I gave the Proposal, which they liked, but I still had to Close them three times before they would sign."

            5 Calls. 1 Cold Call, 2 RFB's, and 2 Follow Ups.3 Sales, 1 Proposal and 2 Projects.5 Closes, 1 Sale needed 3.1 Lead, 1 Referal, 1 Ad. You would need to look at Contact History to know if the 2 Follow Ups were Referrals, Leads, or Ads.

            SamT

          13. CAGIV | Feb 05, 2006 07:42pm | #46

            I get it.

            like I said this sales gig is new to me, and of all the tasks I perform at work, it's the hardest, for me anyway.

             

          14. User avater
            SamT | Feb 05, 2006 11:11pm | #47

            Cag,

            Whew,

            I'm glad I was helping and not insulting.

            SamT

          15. LorinBoyer | Jan 31, 2006 07:45am | #33

            After you figure out the basic formulas to count and sum, you will want to look into something called a "Pivot Table". It will do everything you need. The way you are inputting the data in your "Example.xls" spreadsheet is perfect for a pivot table. However, let me warn you that the pivot table is an advanced feature of Excel and can take some time to wrap your brain around the concepts it is based on.
            I put together a quick spreadsheet that demonstates the basic formulas talked about in this thread and a Pivot Table example.

            File format
          16. CAGIV | Jan 31, 2006 09:57am | #35

            A friend of mine had me trying to trying to create pivot tables for this the other night, I gave up on it until I either dig out one of my old books on Excel or purchase a new one, but they do look very useful

             

          17. LorinBoyer | Jan 31, 2006 06:51pm | #36

            Pivot tables are difficult to understand the first time through. I think your spreadsheet looks good. However, you will find that updating it or making changes to it will be a real pain in the future. Just imagine adding one more Job Type or adding one more column of information to analize (it would take a while). You may be better off keeping all the data in one big long list and using Autofilter and Pivot Tables. Spend the couple of hours learning about those features and it will save you a tremendous amount of time in the future. I'll post an example below. When I created the spreadsheet below, I recorded in a flash video some of my actions (I didn't edit the videos so they might be a little rough). You can view those videos here:
            http://www.maderasoft.com/LeadTracker/CreateDropDownLists.htm
            http://www.maderasoft.com/LeadTracker/CreatePivotTable.htmEdited 1/31/2006 10:54 am ET by LorinBoyer

            Edited 1/31/2006 11:04 am ET by LorinBoyer

            File format
          18. ravz | Jan 31, 2006 06:55pm | #37

            Try this tutorial, its quite easy to follow.

            File format
          19. CAGIV | Jan 31, 2006 07:28pm | #38

            Thanks to both of you

  2. rvillaume2 | Jan 29, 2006 03:56pm | #4

    Cut and pasted this from excel help. I think this is exactly what you're looking for. I've used this and it works well. When you get the results you can then graph it if needed, or do anything else with it you'd like.

    ***

    Calculates how often values occur within a range of values, and then returns a vertical array of numbers. For example, use FREQUENCY to count the number of test scores that fall within ranges of scores. Because FREQUENCY returns an array, it must be entered as an array formula.

    Syntax

    FREQUENCY(data_array,bins_array)

    Data_array is an array of or reference to a set of values for which you want to count frequencies. If data_array contains no values, FREQUENCY returns an array of zeros.

    Bins_array is an array of or reference to intervals into which you want to group the values in data_array. If bins_array contains no values, FREQUENCY returns the number of elements in data_array.

  3. Lansdown | Jan 29, 2006 05:48pm | #6

    Try 'DCOUNTA' in the database category of the function. I have a spreadsheet I made that did just that if I can find it I'll e-mail it to you and you can modify for your own use if you like.

  4. User avater
    EricPaulson | Jan 29, 2006 06:03pm | #7

    You should try a shout out to Jerrald Hayes, he's a wiz at this sorta stuff.

    E

    [email protected]

     

     

    It's Never Too Late To Become What You Might Have Been

     

     

     

  5. EWong | Jan 29, 2006 06:42pm | #8

    As far as I know only ONE critera can be specified for the critera component of the "SUMIF" function.

    So of course one has to do a math "cheat" to do this

    You sum up all of the values that are GREATER than your minimul value
    and then you SUBTRACT all the values that are greater than your MAXIMUM value.

    Now I realise no HUMAN would do it this way as you are doing FAR too much addition. But the computer will do this happily all day.

    In the example attached Ive put the values you want added in cells B3 through B9.
    Ive put your lower boundry value in B11 and the upper boundry in C11

    So the formula looks like:
    =SUMIF(B3:B9,">"&B11) -SUMIF(B3:B9,">"&C11)

    Now if you look in the sheet the formula is differnt.
    There are $ in the locations.
    This is to "lock" the locations so that the formula can be cut and pasted and the criter changed.
    See rows 12, and 13

    File format
    1. MAsprayfoam | Jan 29, 2006 10:16pm | #11

      You might be able to do "nested" SUMIF statements.  I know you can with the IF-ELSE statement. 

      About nesting functions within functions

      View ImageShow All

      Hide All

      In certain cases, you may need to use a function as one of the arguments (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) of another function. For example, the following formula uses a nested AVERAGE function and compares the result with the value 50.

      View Image

      Valid returns    When a nested function is used as an argument, it must return the same type of value that the argument uses. For example, if the argument returns a TRUE or FALSE value, then the nested function must return a TRUE or FALSE. If it doesn't, Microsoft Excel displays a #VALUE! error value.

      Nesting level limits    A formula can contain up to seven levels of nested functions. When Function B is used as an argument in Function A, Function B is a second-level function. For instance, the AVERAGE function and the SUM function are both second-level functions because they are arguments of the IF function. A function nested within the AVERAGE function would be a third-level function, and so on.

       

      This is really powerful stuff here.  It takes some time and logic to get it right.  Once it's right though it is a god send from then on!

       

      Stu

  6. JMadson | Jan 30, 2006 03:09am | #15

    CAGIV,

    Your question can be answered by using the formulas in the other posts. Or you can you use the "Histogram" function already built in to Excel. It does exactly what your looking for. Click on Tools in the top menu and then Data Analysis and then select Histogram.

    One column is your data, numbers, values, etc. The next column is your break points. In your example it would be 2500, 5000, 10000, and 25000. That's it. (see the jpeg attached for an example)

    Look up "Histogram" in the help menu. You may not have it installed if you chose to use the "Standard Installation" when you installed Excel but it can always be added if you have your disks.

    If you get used to it, it's the easiest thing to use - no formulas needed.

    Joe

     

Log in or create an account to post a comment.

Sign up Log in

Become a member and get full access to FineHomebuilding.com

Video Shorts

Categories

  • Business
  • Code Questions
  • Construction Techniques
  • Energy, Heating & Insulation
  • General Discussion
  • Help/Work Wanted
  • Photo Gallery
  • Reader Classified
  • Tools for Home Building

Discussion Forum

Recent Posts and Replies

  • |
  • |
  • |
  • |
  • |
  • |
View More Create Post

Up Next

Video Shorts

Featured Story

Versatile Vise

The IQ Vise has angled jaws, a simple locking mechanism, and solid holding power.

Featured Video

How to Install Exterior Window Trim

Learn how to measure, cut, and build window casing made of cellular PVC, solid wood, poly-ash boards, or any common molding material. Plus, get tips for a clean and solid installation.

Related Stories

  • Podcast Episode 692: Introduction to Trade Work, Embodied Carbon, and Envelope Improvements
  • FHB Podcast Segment: Embodied Greenhouse Gas Emissions and the Building Codes
  • Old Boots Learn New Tricks
  • Install Denim Insulation Like a Pro

Highlights

Fine Homebuilding All Access
Fine Homebuilding Podcast
Tool Tech
Plus, get an extra 20% off with code GIFT20

"I have learned so much thanks to the searchable articles on the FHB website. I can confidently say that I expect to be a life-long subscriber." - M.K.

Get home building tips, offers, and expert advice in your inbox

Signing you up...

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
See all newsletters
See all newsletters

Fine Homebuilding Magazine

  • Issue 332 - July 2025
    • Custom Built-ins With Job-Site Tools
    • Fight House Fires Through Design
    • Making the Move to Multifamily
  • Issue 331 - June 2025
    • A More Resilient Roof
    • Tool Test: You Need a Drywall Sander
    • Ducted vs. Ductless Heat Pumps
  • Issue 330 - April/May 2025
    • Deck Details for Durability
    • FAQs on HPWHs
    • 10 Tips for a Long-Lasting Paint Job
  • Issue 329 - Feb/Mar 2025
    • Smart Foundation for a Small Addition
    • A Kominka Comes West
    • Making Small Kitchens Work
  • Issue 328 - Dec/Jan 2025
    • How a Pro Replaces Columns
    • Passive House 3.0
    • Tool Test: Compact Line Lasers

Fine Home Building

Newsletter Sign-up

  • Fine Homebuilding

    Home building tips, offers, and expert advice in your inbox.

  • Green Building Advisor

    Building science and energy efficiency advice, plus special offers, in your inbox.

  • Old House Journal

    Repair, renovation, and restoration tips, plus special offers, in your inbox.

Signing you up...

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
See all newsletters

Follow

  • Fine Homebuilding

    Dig into cutting-edge approaches and decades of proven solutions with total access to our experts and tradespeople.

    Start Free Trial Now
    • Facebook
    • Instagram
    • X
    • LinkedIn
  • GBA Prime

    Get instant access to the latest developments in green building, research, and reports from the field.

    Start Free Trial Now
    • Facebook
    • YouTube
  • Old House Journal

    Learn how to restore, repair, update, and decorate your home.

    Subscribe Now
    • Facebook
    • Instagram
    • X
  • Fine Homebuilding

    Dig into cutting-edge approaches and decades of proven solutions with total access to our experts and tradespeople.

    Start Free Trial Now
    • Facebook
    • Instagram
    • X
    • LinkedIn
  • GBA Prime

    Get instant access to the latest developments in green building, research, and reports from the field.

    Start Free Trial Now
    • Facebook
    • YouTube
  • Old House Journal

    Learn how to restore, repair, update, and decorate your home.

    Subscribe Now
    • Facebook
    • Instagram
    • X

Membership & Magazine

  • Online Archive
  • Start Free Trial
  • Magazine Subscription
  • Magazine Renewal
  • Gift a Subscription
  • Customer Support
  • Privacy Preferences
  • About
  • Contact
  • Advertise
  • Careers
  • Terms of Use
  • Site Map
  • Do not sell or share my information
  • Privacy Policy
  • Accessibility
  • California Privacy Rights

© 2025 Active Interest Media. All rights reserved.

Fine Homebuilding receives a commission for items purchased through links on this site, including Amazon Associates and other affiliate advertising programs.

  • Home Group
  • Antique Trader
  • Arts & Crafts Homes
  • Bank Note Reporter
  • Cabin Life
  • Cuisine at Home
  • Fine Gardening
  • Fine Woodworking
  • Green Building Advisor
  • Garden Gate
  • Horticulture
  • Keep Craft Alive
  • Log Home Living
  • Military Trader/Vehicles
  • Numismatic News
  • Numismaster
  • Old Cars Weekly
  • Old House Journal
  • Period Homes
  • Popular Woodworking
  • Script
  • ShopNotes
  • Sports Collectors Digest
  • Threads
  • Timber Home Living
  • Traditional Building
  • Woodsmith
  • World Coin News
  • Writer's Digest
Active Interest Media logo
X
X
This is a dialog window which overlays the main content of the page. The modal window is a 'site map' of the most critical areas of the site. Pressing the Escape (ESC) button will close the modal and bring you back to where you were on the page.

Main Menu

  • How-To
  • Design
  • Tools & Materials
  • Video
  • Blogs
  • Forum
  • Project Guides
  • Reader Projects
  • Magazine
  • Members
  • FHB House

Podcasts

  • FHB Podcast
  • ProTalk

Webinars

  • Upcoming and On-Demand

Podcasts

  • FHB Podcast
  • ProTalk

Webinars

  • Upcoming and On-Demand

Popular Topics

  • Kitchens
  • Business
  • Bedrooms
  • Roofs
  • Architecture and Design
  • Green Building
  • Decks
  • Framing
  • Safety
  • Remodeling
  • Bathrooms
  • Windows
  • Tilework
  • Ceilings
  • HVAC

Magazine

  • Current Issue
  • Past Issues
  • Magazine Index
  • Subscribe
  • Online Archive
  • Author Guidelines

All Access

  • Member Home
  • Start Free Trial
  • Gift Membership

Online Learning

  • Courses
  • Project Guides
  • Reader Projects
  • Podcast

More

  • FHB Ambassadors
  • FHB House
  • Customer Support

Account

  • Log In
  • Join

Newsletter

Get home building tips, offers, and expert advice in your inbox

Signing you up...

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
See all newsletters
See all newsletters

Follow

  • X
  • YouTube
  • instagram
  • facebook
  • pinterest
  • Tiktok

Join All Access

Become a member and get instant access to thousands of videos, how-tos, tool reviews, and design features.

Start Your Free Trial

Subscribe

FHB Magazine

Start your subscription today and save up to 70%

Subscribe

Enjoy unlimited access to Fine Homebuilding. Join Now

Already a member? Log in

We hope you’ve enjoyed your free articles. To keep reading, become a member today.

Get complete site access to expert advice, how-to videos, Code Check, and more, plus the print magazine.

Start your FREE trial

Already a member? Log in

Privacy Policy Update

We use cookies, pixels, script and other tracking technologies to analyze and improve our service, to improve and personalize content, and for advertising to you. We also share information about your use of our site with third-party social media, advertising and analytics partners. You can view our Privacy Policy here and our Terms of Use here.

Cookies

Analytics

These cookies help us track site metrics to improve our sites and provide a better user experience.

Advertising/Social Media

These cookies are used to serve advertisements aligned with your interests.

Essential

These cookies are required to provide basic functions like page navigation and access to secure areas of the website.

Delete My Data

Delete all cookies and associated data