I have a pivot table which accesses a LARGE external DB, frequently resulting in as many as 10,000 or more rows, many of which are zero value. How can I exclude these rows from showing up, so I can keep the file size manageable, and use the resulting table for further analysis?
ANXIOUSLY AWAITING REPLIES!!
Thanks!
That's EXACTLY what I've been looking for!
Thank You!!
:tumble:
I understand why many programmers would rather use boolean formulas instead of if-then's , as I suggested (saves space in large files). However, many times I would rather multiply by 1 or 0 than multiply by TRUE or FALSE, which can result in zero's or blanks, as you may have experienced.
I have a solution that will work if you don't mind adding a couple of columns, one immediately to the left of the source column (D) and one immediately to the left of your results column (currently column A).
(This also assumes that the identifying characteristic of a "good" value is one in which the 3rd character is "/").
The column next to column D should contain the following formula:
C1: If(mid(D1,3,1)="/",1,0)
C2: If(mid(D2,3,1)="/",int(C1)+1,C1+0.001)
Copy C2 down the rest of the list.
Now insert a new column to the left of column A. Start A1 with the value 1. Fill the column by series for as many rows as you think you'll ever need (i.e. in your example fill from 1 to 14.)
(Columns C & D are now D & E.)
In the new column B, enter a vlookup formula as follows:
=vlookup(A1,D$1:E$14,2,0)
and copy this down for as many rows as necessary.
I've used this method in many situations, creating a kind of "custom index". I hope it works for you.
I have successfully used arrays in formulas to solve similar problems.
Try:
=sumproduct(if(F3:F11=B16,1,0),if(G3:G11=B17,1,0),if(B3:B11="Fuel",1,0),C3:C11)
Remember to use Ctrl-Shft-Enter when typing in array formulas.
This should work.
You can get fancier too by forgetting about the extra columns for Month and Year you say is to the right of your grid, and including a phrase like this as a parameter in the above formula:
...if(Month(F3:F11)=B16,1,0)...
I THINK this will work.
Good luck!
Years ago I used to create formulas in Lotus 1-2-3 by incorporating text (using string concatenation). As a simple example, I have a column of letters from A to E in cells A1:A5. I want to enter a formula in B1 that looks at cell A1, takes the letter 'A', and incorporates that using a formula which evaluates to an address or range name of another cell in the worksheet (or workbook.) Then I want to copy that formula down to cells B2:B5, and those formulas would reference their appropriate ranges.
Used to use @@ function in 1-2-3. Can't figure out how to do this in Excel. Any ideas?
