The Excel Questions Thread

Yeah, skip the pivot table for this, itll be a headache. A very simple =COUNTIF(A:A, A1) formula in a new column (where A is the company name) will give you the count. Then just sort on your new column.

i have a list, say a thousand names. And one column specifies their city, another their birth month and another their mortgage payments.

How can I calculate the 25th and 75th percentiles of the mortgage payments for each city and then birth month? I'd normally just pivot, but it only has min/max/average and I need percentiles.

Originally Posted by gomestar

Click to expand...

i have a list, say a thousand names. And one column specifies their city, another their birth month and another their mortgage payments.

How can I calculate the 25th and 75th percentiles of the mortgage payments for each city and then birth month? I'd normally just pivot, but it only has min/max/average and I need percentiles.

I can't think of a real elegant to do this. You could add columns for each city and each birth month. The cell would be blank if the city for that record was not the same as the column heading. If it was the same - you would reference the column with the mortgage payment. Then you would use the =PERCENTILE function on each column.

and =TRANSPOSE to summarize.

Originally Posted by gomestar

Click to expand...

i have a list, say a thousand names. And one column specifies their city, another their birth month and another their mortgage payments. How can I calculate the 25th and 75th percentiles of the mortgage payments for each city and then birth month? I'd normally just pivot, but it only has min/max/average and I need percentiles.
Excel has a PERCENTILE function. The trick for you will be getting it to only count certain cities and birth months in the argument. To do this you need to coerce an array function. I'm not at a windows machine, but I believe MONTH(serial) returns the numeric value of the month. Normally you'd do this with just one month. However, if you have a list in A1:A12 of months (say from Feb through Jan), you can return an array based on the list {=MONTH(A1:A12)} will return {2,3,4,5,6,7,8,9,10,11,12,1} By itself, this is useless unless you stick a function around it that operates on arrays such as SUM, or COUNT, or MAX, MIN, etc. The cool thing about this is you can also use the = operator with this array to select for all instances of a single month that you specify: {2,3,4,5,6,7,8,9,10,11,12,1}=3 will return {FALSE,TRUE,FALSE,FALSE...} You can coerce a list of trues and falses into 1's and 0's by using the double unary operator: --{FALSE,TRUE,FALSE,FALSE...}={0,1,0,0...} OR, by multiplying them by another array. They will act like 1's and 0's. Say you want the list of mortgage payments by all March birthers in array form. Mortgage payments are in B1:B12. (month(A1:A12)=3)*(B1:B12) will return {0,1200,0,0...} The problem is you have a bunch of zeros in there that will **** up your percentile calculation. You can fix this by sticking it in an IF() function: IF(((month(A1:A12)=3)*(B1:B12))=0,"",(A1:A12=3)*(B1:B12)) which will return {"",1200,"",""...} The ""s are empty quotation marks. These are not considered by array taking functions. Thus, your 75th %ile of all March mortgage payments is something like: {=PERCENTILE(IF(((MONTH(A1:A12)=3)*(B1:B12))=0,"",(MONTH(A1:A12)=3)*(B1:B12)),.75)} Just to be safe, your ranges should be locked, i.e. $A$1:$A$12 instead of A1:A12, so that you can fill and copy/paste without things moving around on you. This result is meaningless given that we only have one march entry. You probably have a lot of march birthers in ur mortgage data. I can't imagine what you're trying to mine out of it. Standard disclaimer applies - I probably fucked up somewhere b/c I haven't had a chance to try this out. Just trying to show that it can be done and give you some ideas. Curly brackets are not typed; you enter the formula by pressing Ctrl+Shift+Enter, and they will appear. That is how you force an array function. Finally, you can match months and cities by name. (A1:A12)="March" works as long as you're calling March "March" consistently. What you decide to do depends a lot on how dates are entered.

Gomestar, if the list is static and there aren't too many cities, it seems like you could do it way faster manually.

Sort by city and make a new data set for each, and then sort by birth month and make a new data set for each of those, and probably you would end up spending way less time than you would setting it up to calculate automatically.

Or maybe you simplified the problem, and you actually do need it done automatically...

nice, thanks guys. I need to play around with CS's suggestions to see if I can get it to work. In the mean time, I was able to get the data using Access.

Originally Posted by Green Lantern

Click to expand...

I will attest to this. I spent 2 hours trying to get my formulae to work. I needed the "$"

F4 baby.

Originally Posted by CunningSmeagol

Click to expand...

F4 baby.

note: it's just F4, not Alt F4 like I just did

What does alt F4 do?

From the article the article above references

or

=CONTAINEDWITHINLINK(LINKFROMLINK(http://goingconcern.com/editors-note...tion-06-10-11/))

We agreed that, generally speaking and based on surveys of class participants in our respective Excel CPE classes, industry users are more advanced than public accounting users. Within public accounting, regional firm users are more advanced than local and Big 4 users.

Click to expand...

Bolded returns TRUE.

Is it possible to make excel think that

2:15 is 2 minutes and 15 seconds, as opposed to 12:02:15 AM or 2:15 AM?

Originally Posted by Kajak

Click to expand...

Is it possible to make excel think that

2:15 is 2 minutes and 15 seconds, as opposed to 12:02:15 AM or 2:15 AM?

I don't think so via direct entry.
You could convert to a serial time by using a the following formula:
(m + s/60)/(24*60) and then format the cell as mm:ss (Format - Number - Custom).

Originally Posted by Kajak

Click to expand...

Is it possible to make excel think that

2:15 is 2 minutes and 15 seconds, as opposed to 12:02:15 AM or 2:15 AM?

you can work around by extracting minutes and seconds. then use a cell reference

= A2 & " minutes and " & B2 & " seconds"

where A2 = cell reference for minutes, and B2 = seconds