1. Welcome to the new Styleforum!

    We hope you’re as excited as we are to hang out in the new place. There are more new features that we’ll announce in the near future, but for now we hope you’ll enjoy the new site.

    We are currently fine-tuning the forum for your browsing pleasure, so bear with any lingering dust as we work to make Styleforum even more awesome than it was.

    Oh, and don’t forget to head over to the Styleforum Journal, because we’re giving away two pairs of Carmina shoes to celebrate our move!

    Please address any questions about using the new forum to support@styleforum.net

    Cheers,

    The Styleforum Team

    Dismiss Notice

The Excel Questions Thread

Discussion in 'General Chat' started by CunningSmeagol, May 4, 2009.

  1. dsgNYC

    dsgNYC Well-Known Member

    Messages:
    422
    Joined:
    Dec 9, 2008
    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.
     
  2. MetroStyles

    MetroStyles Well-Known Member

    Messages:
    15,831
    Joined:
    May 4, 2006
    Location:
    New York Shitty
    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.
     
  3. CunningSmeagol

    CunningSmeagol Well-Known Member

    Messages:
    3,933
    Joined:
    Oct 15, 2006
    Location:
    New York
    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.
     
  4. dsgNYC

    dsgNYC Well-Known Member

    Messages:
    422
    Joined:
    Dec 9, 2008
    +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.
     
  5. CunningSmeagol

    CunningSmeagol Well-Known Member

    Messages:
    3,933
    Joined:
    Oct 15, 2006
    Location:
    New York
    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"))}
     
  6. AntiHero84

    AntiHero84 Well-Known Member

    Messages:
    2,425
    Joined:
    Aug 28, 2008
    Location:
    Long Island
    Do you want to embed it or are you okay with just having the image? Excel to word/ppt is a pain and I'm no good at it.

    I would like to embed it, but an image is just as good.

    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.


    Thanks, I'll give these a shot a shot.
     
  7. Kent Wang

    Kent Wang Well-Known Member

    Messages:
    5,720
    Joined:
    May 5, 2005
    Location:
    London
    Hmm, don't know how to do this without an array formula.
    Ah, Google does have array formulas. So I did this by using Filter then SumIf. Thanks for pointing me in the right direction.
     
  8. yerfdog

    yerfdog Well-Known Member

    Messages:
    1,334
    Joined:
    Sep 23, 2006
    There was this, back in August. A fun distraction. Jinda made a better version but it's long gone now.

    hahaha, awesome
     
  9. Dewey

    Dewey Well-Known Member

    Messages:
    3,485
    Joined:
    Jul 19, 2007
    This is my last one for the day, but it's an interesting problem

    wow, solution looks great, i will try this at work tomorrow. thank you.
     
  10. JAY13

    JAY13 Member

    Messages:
    21
    Joined:
    Sep 3, 2006
    Just curious how long it took some of you Excel users to get to this level of expertise?

    Is this level of proficiency a result of everyday work experience or books read and course taken.

    I am currently beginning my Excel quest in school and would also like to one day be able to impress nerds with my L33t Excel codez!
     
  11. MetroStyles

    MetroStyles Well-Known Member

    Messages:
    15,831
    Joined:
    May 4, 2006
    Location:
    New York Shitty
    Just curious how long it took some of you Excel users to get to this level of expertise?

    Is this level of proficiency a result of everyday work experience or books read and course taken.

    I am currently beginning my Excel quest in school and would also like to one day be able to impress nerds with my L33t Excel codez!


    Don't bother trying to "learn" Excel on your own. Get the "right" job and you will be an ace in no time.
     
  12. CunningSmeagol

    CunningSmeagol Well-Known Member

    Messages:
    3,933
    Joined:
    Oct 15, 2006
    Location:
    New York
    Just curious how long it took some of you Excel users to get to this level of expertise?

    Is this level of proficiency a result of everyday work experience or books read and course taken.

    I am currently beginning my Excel quest in school and would also like to one day be able to impress nerds with my L33t Excel codez!


    Work. Imagine doing something for every day all day for 3 years. Think of how easy breathing is. It's like Kenyans and running. If you can learn on your own, more power to you, but I think you really need the deadlines and the knowledge that your livelihood depends on it to really motivate yourself. And by livelihood I mean slightly nicer apartment and shoes than everyone else.
     
  13. MetroStyles

    MetroStyles Well-Known Member

    Messages:
    15,831
    Joined:
    May 4, 2006
    Location:
    New York Shitty
    Work. Imagine doing something for every day all day for 3 years. Think of how easy breathing is. It's like Kenyans and running. If you can learn on your own, more power to you, but I think you really need the deadlines and the knowledge that your livelihood depends on it to really motivate yourself. And by livelihood I mean slightly nicer apartment and shoes than everyone else.

    How did you enjoy being an I-Banking analyst? [​IMG]
     
  14. kwilkinson

    kwilkinson Well-Known Member

    Messages:
    33,650
    Joined:
    Nov 21, 2007
    Location:
    Chicago
    Jesus H. this thread is one of the nerdiest I've ever read on here.
     
  15. CunningSmeagol

    CunningSmeagol Well-Known Member

    Messages:
    3,933
    Joined:
    Oct 15, 2006
    Location:
    New York
    How did you enjoy being an I-Banking analyst? [​IMG]

    I wasn't, but I wasn't dissimilar, obviously. And you?
     
  16. whacked

    whacked Well-Known Member

    Messages:
    7,364
    Joined:
    Sep 24, 2006
    You two should get a room and start a romantic dialogue in consultant-speak.
     
  17. CunningSmeagol

    CunningSmeagol Well-Known Member

    Messages:
    3,933
    Joined:
    Oct 15, 2006
    Location:
    New York
    Why get a room when I can shoot him an email?
     
  18. MetroStyles

    MetroStyles Well-Known Member

    Messages:
    15,831
    Joined:
    May 4, 2006
    Location:
    New York Shitty
    I wasn't, but I wasn't dissimilar, obviously. And you?

    Did an ibanking internship and now in consulting. Although your knowledge is quite advanced I must say. Much more so than the average consultant or banker.
     
  19. Dewey

    Dewey Well-Known Member

    Messages:
    3,485
    Joined:
    Jul 19, 2007
    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.
    Wow, I got this to work, and I gawk in amazement. But I cannot get it to work if the data is transposed. Say the data is 1 column of 10 numbers, cells A3 to A12, and I want the average of the 3 highest numbers. What do I enter for xxxx, below: {=AVERAGE(LARGE($A3:$A12,ROW(xxxx)))}
     
  20. jgold47

    jgold47 Well-Known Member

    Messages:
    1,629
    Joined:
    Mar 23, 2008
    Location:
    The Mitten
    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?


    I do it that way.
     

Share This Page

Styleforum is proudly sponsored by