Simple Expense Tracking With New Excel 2007 Functions

Simple Expense Tracking With New Excel 2007 Functions - Click & Solve

Simple Expense Tracking With New Excel 2007 Functions - Click & Solve — Tue, 10 Feb 2009 04:29:21 GMT

PingBack from http://www.clickandsolve.com/?p=5557

re: Simple Expense Tracking With New Excel 2007 Functions

Carmen — Tue, 10 Feb 2009 11:28:06 GMT

Why not use Pivot Table instead?

re: Simple Expense Tracking With New Excel 2007 Functions

Neal O — Tue, 10 Feb 2009 12:40:39 GMT

Exactly, why not use pivot tables? I'm missing the point here, has anyone found a compelling case to use Excel 2007 tables at all???

re: Simple Expense Tracking With New Excel 2007 Functions

Neal O — Tue, 10 Feb 2009 12:41:00 GMT

Exactly, why not use pivot tables? I'm missing the point here, has anyone found a compelling case to use Excel 2007 tables at all???

re: Simple Expense Tracking With New Excel 2007 Functions

Jonah Feld — Tue, 10 Feb 2009 19:43:24 GMT

Tales and these functions are my favorite new features in 2007. Using SUMIFS() on a Table you can easily parameterize conditions and work with multidimensional data more easily than with GETPIVOTDATA() and much faster than with array formulas. Plus the Table is dynamic, and unlike a dynamic range, does not rely on a volatile function. The structured references are very readable, and IntelliSense makes them very quick and error resistant to write.

In this simple example, sure, a PivotTable would be just as good or better, but not always.

re: Simple Expense Tracking With New Excel 2007 Functions

Harlan Grove — Wed, 11 Feb 2009 03:44:15 GMT

@Jonah -

Are SUMIFS formulas referring to tables faster than equivalent SUMPRODUCT formulas?

G2:

=SUMPRODUCT(Table1[Amount],

(Table1[Category]=G$1)*(Table1[Date]=$F2))

? And how are SUMIFS etc useful when the condition is OR? E.g., add a few more entries under Category and consider summarizing under Necessary (rent, utilities) and discretionary (movies, books). Simple enough with SUMPRODUCT.

=SUMPRODUCT(Table1[Amount],

*(Table1[Category]={"movie","books"})

*(Table1[Date]=$F2))

Yes, I know my DeMorgan's Law,

A or B == not ((not A) and (not B))

so it's possible to contrive SUMIFS formulas as

=SUMIF(Table1[Date],$F2,Table1[Amount])

-SUMIFS(Table1[Amount],

Table1[Category],"<>movie",

Table1[Category],"<>books",

Table1[Date],$F2)

but I doubt that'd recalc faster than the equivalent SUMPRODUCT formula.

I have nothing against tables. It's SUMIFS etc that seem pointless to me.

re: Simple Expense Tracking With New Excel 2007 Functions

Joseph Chirilov — Wed, 11 Feb 2009 05:31:30 GMT

Carmen, Neal: as is often the case with Excel, there's more than one way to skin a cat. Pivot Tables are a good alternative approach for this problem. The point of this blog post was to educate people on some of the new functions in Excel by way of example; it wasn't trying to make a statement about the ideal way to solve this particular problem. I totally agree though, if you are someone who is very familiar with PivotTables, then that might be a better solution for you.

Though that does give me an idea about a future blog post: pros/cons of pivot-based solutions vs. cell-formula equivalents.

re: Simple Expense Tracking With New Excel 2007 Functions

sam — Wed, 11 Feb 2009 20:28:52 GMT

@Jonah,

Dynamic names need not be volatine

Client =$a$1:Index($a:$a,counta($a:$a)) is not volatile unlike the Offset version

Tables in 2007 are not truly dynamic.

Define a table, copy data from another sheet paste it two rows below the last line of the table.

Delete the blank rows. The Table does not expand

@Harlan.

SumIF/s countIF/s are unfortunately much faster than Sumproduct

Data 65536 rows - 3 conditions

Array Entered Sum - 1.13 sec

Sumproduct with Double unary syntax - 1.12 sec

Dsum-Sum - 0.5 Sec

SumIFs - 0.09 sec

Sumifs wins hands down

The only problem is the inability to incorporate an OR logic in SumiF/s

But then its is difficult to do it in Sumproduct as well without array entering it

= Sumproduct((Name="abb")+(City="NewYork"),(Amt)) gives wrong results

TRUE+TRUE = 2 not 1

Sumproduct(IF((Name="abb")+(City="NewYork"),1,0)),(Amt)) - Array Entered works

@Joseph

Pivot Table V/s Formulas.

There are no improvements in Calculated fields / Calculated items in 2007 -Inability to refer to ranges/use Array Formulas in Calculated fields/Items is a major drawback....but this is compensated by the fact that we do have more shades of Blue for formatting the table...no doubt due to the overwhelming demand for it from the customer feedback program...submitted by "real" users

re: Simple Expense Tracking With New Excel 2007 Functions

stanscott — Wed, 11 Feb 2009 20:45:58 GMT

Using Sumproduct with multiple criteria really isn't difficult. In the example above, assume Name in column A, City in column B, and Amt in column C.

=SUMPRODUCT((A2:A4="abb")*1,(B2:B4="New York")*1,C2:C4)

This formula gives you the total of the items in column C, where Name is abb, and City is New York. The "*1" is used with the first two arguments to convert TRUE to 1.

Stan Scott

New York City

re: Simple Expense Tracking With New Excel 2007 Functions

Harlan Grove — Wed, 11 Feb 2009 20:55:54 GMT

@sam -

Fair point about recalc speed. Wonder whether SIMIFS's speed is due to short circuit boolean evaluation (no need to process additional criteria when an earlier criterion is FALSE).

However, you need to spend more time reading the newsgroups. For mutually exclusive OR (same range equal to one of many alternatives), redundancy works.

=SUMPRODUCT((A1:A100={"A","E","I","O","U"})

*C1:C100)

For different fields, still fairly simple.

=SUMPRODUCT(--((A1:A100="A")+(B1:B100<>"X")>0),

C1:C100)

re: Simple Expense Tracking With New Excel 2007 Functions

sam — Thu, 12 Feb 2009 07:19:43 GMT

@stan,

I was looking to sum column Amt where Name = ABB OR City = New York

@Harlan..

Thanks for the Solution..I learn more from the comments than the Posts

re: Simple Expense Tracking With New Excel 2007 Functions

faramond — Sun, 15 Feb 2009 09:38:03 GMT

I for one would like to see the pro/con post. There are probably lots of issues I haven't yet encountered but will (and spend time pulling out my hair, trying to determine why Excel's not working as I expect.) Here are a couple examples:

* Certain chart types (e.g. scattergraph) are not available in pivot tables

* Software that imports Excel files often can't read/instantiate/calculate pivot tables

* Function set very limited in pivot tables (e.g., only average--no median!)

re: Simple Expense Tracking With New Excel 2007 Functions

Jonah Feld — Mon, 16 Feb 2009 18:55:49 GMT

@Harlan

I never tested SUMPRODUCT() for speed myself, but never had an issue with SUMIFS(). An MSDN article (http://msdn.microsoft.com/en-us/library/aa730921.aspx#office2007excelperf_TipsOptimizingBottlenecks) on optimizing Excel bottlenecks recommended use in this order: SUMIFS(), SUMPRODUCT(), then array formulas.

@Sam,

Thanks for the detailed test results, and for showing how to write a dynamic range without OFFSET(). I wish that method occurred to me before 2007 came out. I'd been relying on a VBA function to restate ranges as static instead. Even though your example points out how to trick a Table into not dynamically expanding, the Table is dynamic with normal use.

Adding to the topic of Table merit...

The Tables I use are almost always tied to an external data source. It's very helpful to be able to manage these objects differently than Named Ranges.

Calculated Fields in Tables have an important advantage over Calculated Fields in PivotTables; Tables can reference values outside of the object. For example, let's say you have a Table of purchases and corresponding US States. Outside of the Table, you have pairs of US States and their sales tax rate. The Table can have a Calculated Field that references the tax rate. The PivotTable's Calculated Field cannot.

With VBA controlling PivotTables, it's very easy to inadvertently wreck/corrupt your PivotTable, and difficult to detect when this has happened. I haven't encountered any corruption problems with Tables.

If I had to choose only one, I'd take PivotTables, but Tables are an excellent addition to 2007.

re: Simple Expense Tracking With New Excel 2007 Functions

sebastien — Mon, 23 Feb 2009 02:35:07 GMT

Thank you for these functions.

- In prior xl versions, I could teach all my users SUMIF, but only a fraction of the them would understand SUMPRODUCT to sum with multiple criteria and only that fraction could use it without making a big mess.

- Also I like that SUMIF/SUMIFS can operate on ranges containing Error values. SUMPRODUCT cannot without workaround which makes SUMIF(S) more user friendly for the average user.

re: Simple Expense Tracking With New Excel 2007 Functions

silk1203 — Tue, 03 Mar 2009 13:51:11 GMT

In Table1, I've entered data like this:

a1: monument b1:guide

a2: taj mahal b2: 750

a3: varanasi b3: 102

a4: bodhgaya b4: 50

a5: city palace b5: 300

........

In Table 2, I've created a list to get the data from Table1, ColumnA. So, for example if Table 2 has got data as under:

a2: bodhgaya

a3: taj mahal

a4: left blank

a5: left blank

I need the calculation at a15. Let assume for the above entry, the a15 shows 800 (value of bodhgaya + value of taj mahal)

What formula should I've to enter? Please solve.

Abhay

re: Simple Expense Tracking With New Excel 2007 Functions

silk1203 — Tue, 03 Mar 2009 14:00:24 GMT

TABLE 1

A B C

1 Monument Guide

2 Taj Mahal 750

3 Varanasi 102

4 Bodhgaya 50

5 City Palace 300

6 Rajgir 50

Analyzing Data: Functions or PivotTables

Microsoft Excel — Mon, 23 Mar 2009 22:31:47 GMT

Today’s author, Monica Poinescu, a Software Developer in Test on the Excel team, discusses two different