The Excel Questions Thread

[JoelF]

Originally Posted by dcg

Here's a question for the OP (or anyone else that might feel like answering).

Say you have a list of a few thousand cells that you need deduped. Ex:

1
1
2
5
10
13
13
13
16
22
....

Assume your list starts in A2. When I've had to accomplish this I've entered

=IF(A2=A1, 1, 0)

into B2, then copied -> paste special -> values both columns, then sorted both columns by column B ascending, giving you a deduped list in column A for all rows where column B is 0.

Is there an easier way to dedupe a list?

In Excel 2007 it's just Data=>Eliminate Duplicates, I assume you're using a previous version where that does not exist.

 

[Dewey]

Interesting answers. Thanks for the excel help.

 

[CunningSmeagol]

There was this, back in August. A fun distraction. Jinda made a better version but it's long gone now.

 

[Kent Wang]

I need this in Google Spreadsheet, which has a similar but smaller set of functions.

I have a grid of numbers. I want to sum only the negative numbers. The way I do this now is create an extra column and set all the values to the formula =IF(A1<0, A1,), then I sum that column. Can I do this without that extra column?

 

[MetroStyles]

Originally Posted by Kent Wang

I need this in Google Spreadsheet, which has a similar but smaller set of functions.

I have a grid of numbers. I want to sum only the negative numbers. The way I do this now is create an extra column and set all the values to the formula =IF(A1<0, A1,), then I sum that column. Can I do this without that extra column?

sumif(A1:A10, "<0")

 

[whacked]

Originally Posted by MetroStyles

This is my new favorite threak /nerd.

+1. I learn some(or a bunch of)thing new everyday.


Is there a good (read: well-organized, preferably free) book/website where I can learn about macros and Excel functions from the ground up? TIA,

 

[CunningSmeagol]

Originally Posted by whacked

+1. I learn some(or a bunch of)thing new everyday.


Is there a good (read: well-organized, preferably free) book/website where I can learn about macros and Excel functions from the ground up? TIA,

J Walkenbach's books are really good. There's one for formulas and one for VBA (Excel 2003: Power Programming with VBA). Not free, but very easy to follow and goes from the ground up.

 

[Dewey]

The kitonbrioni wardrobe generator is classic. Thanks for re-posting that.

===============

New problem

Say I have 25 rows of numbers, ranging from 0 to 9, across 40 columns. How do I average the 30 highest numbers in each row?

I have solved this but it requires a huge sheet and some manual editing at the end.

 

[CunningSmeagol]

@Whacked, also check out:

http://www.cpearson.com/excel/mainpage.aspx

 

[Kent Wang]

Originally Posted by MetroStyles

sumif(A1:A10, "<0")
Wow, I feel dumb for not knowing about this function.

But now I realize I also need to ignore where column B = "Transfer". I've figured out how to do each, e.g. ignore where B=Transfer and ignore where C<0, but don't know how to combine them.

 

[dsgNYC]

Originally Posted by AntiHero84

How can I fit an oversized excel table to a 1-page word document?

Copy from Excel, Edit --> Paste Special in Word (or PPT). You can play around with how you want it to look, but you can choose options that display the Excel data as:

a) A picture of the table ("Picture", "Bitmap")
b) An editable table format ("Formatted Text", "Unformatted Text", etc)
c) An Excel object, which allows you to format and play with the table within the Word document just like you normally would in Excel

You also have the choice to paste a linked version of your table so that any updates you make to the original Excel table are also updated automatically in the table that you pasted into your Word document. Instead of choosing the "Paste:" radio button on the Paste Special dialogue box, choose "Paste Link:". You can do the same copying and pasting in PPT.

 

[MetroStyles]

Originally Posted by Kent Wang

Wow, I feel dumb for not knowing about this function.

But now I realize I also need to ignore where column B = "Transfer". I've figured out how to do each, e.g. ignore where B=Transfer and ignore where C<0, but don't know how to combine them.

Hmm, don't know how to do this without an array formula.

 

[CunningSmeagol]

Originally Posted by Dewey

The kitonbrioni wardrobe generator is classic. Thanks for re-posting that.

===============

New problem

Say I have 25 rows of numbers, ranging from 0 to 9, across 40 columns. How do I average the 30 highest numbers in each row?

I have solved this but it requires a huge sheet and some manual editing at the end.

This is my last one for the day, but it's an interesting problem.

I would just do each row at a time, generate 25 averages, one for each row. The formula I would use, assuming your numbers were in columns A through AN (starting on row 3) would be:

{=AVERAGE(LARGE($A3:$AN3,COLUMN(A$1:AD$1)))} and drag down 25 rows. Type the formula without the brackets and press Ctrl+Shift+Enter to enter the array formula. (A to AD is 30, which tells excel you want the top 30 largest numbers)

Select the cell you put this formula in and use Tools -> Formula Auditing -> Evaluate Formula to see each step of how it works.

Then average the 25 numbers. I made a smaller model of what you need, attached picture. I assumed top 5 largest numbers.

 

[dsgNYC]

Originally Posted by whacked

+1. I learn some(or a bunch of)thing new everyday.


Is there a good (read: well-organized, preferably free) book/website where I can learn about macros and Excel functions from the ground up? TIA,

I use mrexcel.com to find solutions for a lot of my Excel problems. They also have an area where they offer lots of "Tutorials and Tips" (http://www.mrexcel.com/articles.shtml) but it looks like they're more for solving specific problems as opposed to teaching you general concepts.

I bought a book on VBA for Excel a few years back because I wanted to get better at creating more robust macros...probably the best investment I've ever made.

 

[CunningSmeagol]

Originally Posted by MetroStyles

Hmm, don't know how to do this without an array formula.

Does Google allow using sumproduct as a multiple criteria filter? I wouldn't have thought so, but that list of formulas is impressive.

In Excel I would:

=sumproduct(SumRange*(SumRange<0)*(AdjacentRange<>"transfer"))

..which is the same as

{=sum(SumRange*(SumRange<0)*(AdjacentRange<>"transfer"))}