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?
Edited 1/29/2006 5:13 am ET by CAGIV
Edited 1/29/2006 5:13 am ET by CAGIV
Replies
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)
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?
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
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.
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
Stu,
I tried the help menu and was unable to figure it out, do you know how to do it?
hmmm - *this* computer has Excel2000 and cant seem to do it either.
What version of Excel are you using?
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!
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
Bill,
I've been looking in help but can figure out how to make the drop down menu for different job types, any ideas?
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.
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.
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
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.
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
I don't have Excel so I don't know.If fact when I posted the comment I said "IF excel has that feature".
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
opps - cant edit by adding an attachment.../
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 ;)
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)
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
Whoops, need to attach the file
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
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
Got it to work
=SUMIF(C2:C7,"Window",D2:D7)
<<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)
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
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)
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 ;)
Another piece of analysis you can do on your Sheet with COUNTIF and SUMIF is compare sales to calls and proposals.
SamT
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
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
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?
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.
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
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.
Cag,
Whew,
I'm glad I was helping and not insulting.
SamT
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.
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
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
Try this tutorial, its quite easy to follow.
Thanks to both of you
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.
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.
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
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
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
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