1. And... we're back. You'll notice that all of your images are back as well, as are our beloved emoticons, including the infamous :foo: We have also worked with our server folks and developers to fix the issues that were slowing down the site.

    There is still work to be done - the images in existing sigs are not yet linked, for example, and we are working on a way to get the images to load faster - which will improve the performance of the site, especially on the pages with a ton of images, and we will continue to work diligently on that and keep you updated.

    Cheers,

    Fok on behalf of the entire Styleforum team
    Dismiss Notice

The Excel Questions Thread

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

  1. Bhowie

    Bhowie Senior member

    Messages:
    13,099
    Joined:
    Oct 5, 2008
    Location:
    Running the trap house.
    

    I guess you skipped over high school algebra.

    :facepalm:
     
  2. deadly7

    deadly7 Senior member

    Messages:
    3,145
    Joined:
    Jul 25, 2010
    Excel '03

    How to make it stop autoformatting things as dates? And don't say "format the cell before you enter a value".
     
    Last edited: Jan 7, 2012
  3. Reggs

    Reggs Senior member

    Messages:
    5,531
    Joined:
    Mar 11, 2006
    Location:
    The Internet
    

    Someone did this on a review of it. You really dont save much, or anything at all. The conclusion was, savings is no reason to buy it.
     
  4. Reborn

    Reborn Senior member

    Messages:
    700
    Joined:
    Dec 17, 2007
    I have an excel sheet of 25,000 rows where one column consists of 4-6 digit numbers, which sometimes are followed by a letter(A,B,C,D).

    Is there any way to automatically remove these letters and place them into an adjoining column?

    Existing condition: Column A = 956732C

    Desired result: Column A = 956732 & Column B = C

    I'm thinking some sort of if statement for column b and then a find and replace to get rid of the letters in column a?

    Halp!
     
  5. deadly7

    deadly7 Senior member

    Messages:
    3,145
    Joined:
    Jul 25, 2010
    

    Look into the "Delimiter" function.
     
  6. otc

    otc Senior member

    Messages:
    14,215
    Joined:
    Aug 15, 2008
    

    is prefixing the cell with ' not what you are looking for?

    'January 2005

    will be displayed as January 2005 but not turned into 1/1/2005 internally.
     
  7. stevent

    stevent Senior member

    Messages:
    9,555
    Joined:
    Feb 16, 2010
    Blanking on this right now: I have a bunch of data and have calculated the days of the week (mon, tues, etc. ) how do I count each day of the week? Same thing if I have a bunch of products how do I count each specific product?
     
  8. otc

    otc Senior member

    Messages:
    14,215
    Joined:
    Aug 15, 2008
    

    sumifs (or sumif as an array if you don't have office >=2007)

    Actually, if you just want to count the days (as opposed to add up some other column based on the days), countif is probably what you are looking for...just be careful with its behavior on non-number values.
     
  9. stevent

    stevent Senior member

    Messages:
    9,555
    Joined:
    Feb 16, 2010
    


    Yeah that could work. Ended up just doing pivot tables
     
  10. P. Bateman

    P. Bateman Senior member

    Messages:
    1,146
    Joined:
    Jan 30, 2009
    Location:
    San Francisco
    I need some help with an if then statement. I'm trying to have a cell auto convert currency depending on the currency label:

    =if((AND($F6=1,$AJ6="KRW"),$AI6*Settings!$D$7($F6=1,$AJ6>="JPY"),$AI6*Settings!$D$8))

    This yields an error.

    This works:
    =if(AND($F6=1,$AJ6="KRW"),$AI6*Settings!$D$7)

    but I need to have it be able to pull in different conversion rates from the Settings worksheet depending on the currency label in AJ6.

    Thanks!
     
  11. otc

    otc Senior member

    Messages:
    14,215
    Joined:
    Aug 15, 2008
    you gotta tell us more about the error.

    If you can go into the debug mode, where in the evaluation does it hit a snag? really though if that is copy pasted exactly, you've got an extra parentheses in front of the and and you seem to be missing a comma after Settings!$D$7

    Really though, I think your life would be easier if you created a hidden column with the exchange rate and set that equal to a lookup (or index-match) to the currency conversion table on your settings page....that way it would work with any number of currencies where as now you would need more and more nested if statements.
     
  12. ikemen

    ikemen Well-Known Member

    Messages:
    97
    Joined:
    Jan 4, 2007
    suppose you have the data in cells A1 to A9:

    A1 A
    A2 B
    A3 C
    A4 D
    A5 E
    A6 F
    A7 G
    A8 H
    A9 I

    how do you let the data display such that they get picked in fixed intervals?
    e.g. In a two row jump, it displays in

    A1 A
    A2 D
    A3 G

    Really appreciate any advice you have!
     
  13. rajesh06

    rajesh06 Senior member

    Messages:
    365
    Joined:
    Dec 24, 2004
    You should be able to do this with a filter and a "helper" column. The helper column would be the basis of the filter and might be something like 1, 0, 0 , 1, 0, 0 , 1, 0, 0 (Really anything that would have a unique identifier for the rows that you wanted to display.)
     
  14. Reggs

    Reggs Senior member

    Messages:
    5,531
    Joined:
    Mar 11, 2006
    Location:
    The Internet
    I'm having problems with phone numbers.

    I have data from CRM 1 that lists numbers as (714) 555-5555 and data from CRM 2 that lists numbers as 7145555555. I need to correlate the two for v/h lookup and what not.

    I can format a cell with 7145555555 so that is appears as (714) 555-5555, but the raw data in that cell will always be the original 7145555555.

    How I can turn 7145555555 into (714) 555-5555, or vice versa, without formatting? This has to be a common problem. Everyone deals with phone numbers.

    EDIT: Also, I use Excel 2010. Is there any way to change the right click>paste options to a list like in Excel 2007, and not these dumb icons?
     
    Last edited: Jul 16, 2012
  15. vaulter1

    vaulter1 Active Member

    Messages:
    37
    Joined:
    Apr 19, 2011
    
    Not sure if I understand you correctly but the below formulas will take your phone number from 7145555555 to (714) 555-5555 and back again.


    • This assumes that 7145555555 is in cell A1 =CONCATENATE("(",LEFT(A1,3),")"," ",MID(A1,4,3),"-",RIGHT(A1,4))
    • This assumes that (714) 555-5555 is in cell A2 =CONCATENATE(MID(A2,2,3),MID(A2,7,3),RIGHT(A2,4))


    The power of CONCATENATE is really quite useful :nodding:
     
  16. suited

    suited Senior member

    Messages:
    6,400
    Joined:
    Aug 18, 2008
    

    Meaning you just want to count the number of products?

    =counta(insert cell range here)

    Edit, nvrmind, I missed the word "specific" in your post. OTC's post would apply, then.
     
    Last edited: Jul 17, 2012
  17. rajesh06

    rajesh06 Senior member

    Messages:
    365
    Joined:
    Dec 24, 2004
    Click the arrow to expand "Number" under the Home ribbon. Under the Number tab look for "Special" and then Phone Number.
     
  18. vaulter1

    vaulter1 Active Member

    Messages:
    37
    Joined:
    Apr 19, 2011
    

    My understanding from the OP was that he didn't want to just change the formatting but had to compare phone numbers with 'harcoded' formatting. Though if it's just visual formatting then - easy answer.
     
  19. Reggs

    Reggs Senior member

    Messages:
    5,531
    Joined:
    Mar 11, 2006
    Location:
    The Internet
    

    That's exactly what I was looking for, thanks so much!
     
  20. Sir Humphrey Appleby

    Sir Humphrey Appleby Senior member

    Messages:
    1,921
    Joined:
    May 28, 2011
    Is it possible to convert SQL code into Excel macros? I want to get

    SELECT Employee_Name, Speed, Time
    FROM "Hour"
    GROUP BY Job
    HAVING Time>0.5
    ORDER BY Speed

    Then select the slowest two people who are left in each job (sometimes there's only one).

    If it was in Access I could do it all except for the last bit but I'm less good with Excel.
     
    Last edited: Jul 26, 2012

Share This Page

Styleforum is proudly sponsored by