Options

How you can use [EXCEL] to get womens* (*not guaranteed)

2»

Posts

  • Options
    InfidelInfidel Heretic Registered User regular
    edited May 2009
    DigDug2000 wrote: »
    Infidel wrote: »
    Then change

    =SUMPRODUCT((A1:A10>=180)*(B1:B10<50000),C1:C10)

    to

    =SUMPRODUCT((A1:A10>=180)*(B1:B10<50000),B1:B10)
    My Excel Fu is pretty weak, but if you're just doing a sum, it seems easier to just use SUMIFS (may be Excel 2007 only for all I know):

    =SUMIFS(B3:B12, A3:A12, ">=180", B3:B12, "<50000")
    =SUMIFS(column to sum, criteria range 1, criteria 1, criteria range 2, criteria 2)

    There is no similar function for SUMPRODUCTIFS that I know of though.

    SUMIFS looks like it would work, just looked it up. And yes, it's 2007 only though.

    Infidel on
    OrokosPA.png
  • Options
    1ddqd1ddqd Registered User regular
    edited May 2009
    Ok new problem. I'm trying to build a pivot table based on an access table. It is located in a DB that isn't used by anyone, so I have exclusive rights to it. This is what happens when I go to Excel and attempt to pull data into the spreadsheet:

    error.jpg\

    Both the spreadsheet and the Access DB are local, why the F*** can't it pull any data? It reads the records, just fails to display anything in the sheet (shows 3,034 records read in the bottom right corner)

    1ddqd on
  • Options
    1ddqd1ddqd Registered User regular
    edited May 2009
    Ok, turns out there is an issue with that database - the same database has been used in Access since 2003 and finally got corrupted. We're circumventing this issue with a new database.

    However: new problem.

    I need to write a formula that inserts a leading 0 based on how many digits are in a cell.

    If a cell has the number 5, it should have 3 zeroes.
    If a cell has the number 55, it should have 2 zeroes.
    If a cell has the number 555, it should have 1 zero.

    This is the suffix of a phone number. When I pull data from the source and import to Excel, it throws away the leading zero - the output is formatted as a number, so there's no way to store it as text first.

    I tried this: =if(H30<10,"000",(if(H30>10<100,"00",(if(H30>99,"0",""))))) but it won't work because the file is Excel 2003, so I assume any solution I do should work in Excel 2003.

    Any thoughts?

    1ddqd on
  • Options
    InfidelInfidel Heretic Registered User regular
    edited May 2009
    Setting the format to a Custom Format of 0000 won't suffice?

    Infidel on
    OrokosPA.png
  • Options
    1ddqd1ddqd Registered User regular
    edited May 2009
    It doesn't plug in the zeroes where I need it to.

    The problem is I have over 3000 cells that need this done. I need to be able to insert a column, type in this formula (or paste it) throughout the sheet. Since it varies between needing 1 and 3 leading zeroes, even if I sort it, I'd be doing this for 20 minutes. I do this every day, so it needs to be efficient and fast.

    1ddqd on
  • Options
    InfidelInfidel Heretic Registered User regular
    edited May 2009
    Why wouldn't it put zeroes where you need em? They're leading zeroes right?

    A 0000 format means 21 will be 0021, 317 will be 0317, etc.

    Infidel on
    OrokosPA.png
  • Options
    1ddqd1ddqd Registered User regular
    edited May 2009
    Infidel wrote: »
    Why wouldn't it put zeroes where you need em? They're leading zeroes right?

    A 0000 format means 21 will be 0021, 317 will be 0317, etc.

    I just tried it, success. Here's the kicker - I need to combine this suffix cell with 2 others (area code and prefix). When you do =A1&B1&C1, the result shows the original number without leading zeroes.

    1ddqd on
  • Options
    InfidelInfidel Heretic Registered User regular
    edited May 2009
    You can apply custom formats in a formula by using the TEXT() function.

    So try something like =A1&B1&TEXT(C1, "0000")

    Infidel on
    OrokosPA.png
  • Options
    1ddqd1ddqd Registered User regular
    edited May 2009
    Infidel wrote: »
    You can apply custom formats in a formula by using the TEXT() function.

    So try something like =A1&B1&TEXT(C1, "0000")

    GENIUS! This does the trick! Thanks man, I didn't know that was possible.

    1ddqd on
  • Options
    tsmvengytsmvengy Registered User regular
    edited May 2009
    Infidel is a:

    spreadsheet_ninja_tshirt-p235405332509765966q6wh_400.jpg

    tsmvengy on
    steam_sig.png
  • Options
    1ddqd1ddqd Registered User regular
    edited May 2009
    tsmvengy wrote: »
    Infidel is a:

    ninja.jpg

    Limed for truth.

    1ddqd on
Sign In or Register to comment.