Options

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

2»

## Posts

• Options
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
• Options
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:

\

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
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
Heretic Registered User regular
edited May 2009
Setting the format to a Custom Format of 0000 won't suffice?

Infidel on
• Options
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
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
• Options
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
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
• Options
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
Registered User regular
edited May 2009
Infidel is a:

tsmvengy on
• Options
Registered User regular
edited May 2009
tsmvengy wrote: »
Infidel is a:

Limed for truth.

1ddqd on