Wow, Conditional sums and counts in Excel are UGLY

[BenBurch]

I just spent an afternoon making a sheet to take PayPal data and filter for actual balance-effecting payments and break down my item number. The good news it that it works great, the bad news is that its completely and utterly counterintuitive.

Anybody else ever serve a turn in that hell?

[Worm]

I love Excel. :) I can make it dance....

[Furi]

Stop Bashing XL, it pays my rent, Conditionals can be messy, rather than nesting them all in a single cell or 5, perform each conditional in a corresponding Cell give it a -1,0,+1 logic result and then just mult/sum them up with figures, also helps with tracing,

Reminds me I have an XL sheet somewhere that has network games of Cribbage, connect 4, a sort of turn based multi player maze shooter game, (and I didn't cheat just by embedding a flash game, they use Cell formulae only I was very very bored)

[BenBurch]

If anybody is interested in how to filter PayPal all activity CSV's for subscription & other item sales;

=SUM(IF(PayPalData!E2:PayPalData!E15000=E4,IF(PayP alData!N2:PayPalData!N15000=D5,PayPalData!I2:PayPa lData!I15000,0),0))+SUM(IF(PayPalData!E2:PayPalDat a!E15000=E3,IF(PayPalData!N2:PayPalData!N15000=D5, PayPalData!I2:PayPalData!I15000,0),0))

Where;

PayPalData is the sheet you have pasted the data into
E4 = Column header containing "Web Accept Payment Received"
E3 = Column header containing "Subscription Payment Received"
D5 = Row label containing item ID

This deals with 15000 rows of data - the hoops to jump through to find how many rows there are were just not worth it.

You must enter this formula and finish by pressing control-shift-enter.

The formula will warn on empty cells, that's expected with this data set.

(Note, the message board software introduced some spaces because the formula is too long. Remove them.)

[Furi]

If none of the rows are blank, you could use count() to calculate the row count and then use offset() for referancing the cells allowing for a more dynamic element count.

if the info comes in as a CSV you could run a small import macro to load into an array and then perform the conditionals in vba as opposed to using worksheet functions, and then just use a simple loop to post the results on sheet for review, would be a lot faster and give you a lot more flexibility, even if you wish to use the worksheet functions to perform the calc you can insert the cell formulae corrected for the relevant paste location and element number, it would be also easier to create defined named ranges if variable data quantaties to allow for dynamic charting or reporting to be produced.

Not having seen the data or knowing the final required result format, just shooting in the dark at present.

[JonnyFive]

If anybody is interested in how to filter PayPal all activity CSV's for subscription & other item sales;

=SUM(IF(PayPalData!E2:PayPalData!E15000=E4,IF(PayP alData!N2:PayPalData!N15000=D5,PayPalData!I2:PayPa lData!I15000,0),0))+SUM(IF(PayPalData!E2:PayPalDat a!E15000=E3,IF(PayPalData!N2:PayPalData!N15000=D5, PayPalData!I2:PayPalData!I15000,0),0))

This warms the cockles of my heart, because I use this kind of crap every day.

Gee, I'd sure like to count how many employees on this census are both in NY and also male. Sure would be nice if I had a countif function that took two parameters. What's that, Excel? You say I need to use the SUM command in some arcane combination with the IF command and something you call an "array formula." Of course, that makes perfect sense.

It is counterintuitive, but god damn is it beautiful when everything clicks.

Also, I made a fully playable two-player version of checkers in Excel using cells and VBA code. I had too much time on my hands.

[Wudang]

It can actually be easier to run a perl script that uses win32::ole to read the xls file. One of the properties is the max rows. Max columns seems a bit flakier or it could be me.

eta a colon followed by an O is read as a smilie which I'd correct but rather like the effect

[BenBurch]

It can actually be easier to run a perl script that uses win32::ole to read the xls file. One of the properties is the max rows. Max columns seems a bit flakier or it could be me.

eta a colon followed by an O is read as a smilie which I'd correct but rather like the effect

Sadly, I don't think that is an option on this Macintosh... :-)

[boooeee]

If anybody is interested in how to filter PayPal all activity CSV's for subscription & other item sales;

=SUM(IF(PayPalData!E2:PayPalData!E15000=E4,IF(PayP alData!N2:PayPalData!N15000=D5,PayPalData!I2:PayPa lData!I15000,0),0))+SUM(IF(PayPalData!E2:PayPalDat a!E15000=E3,IF(PayPalData!N2:PayPalData!N15000=D5, PayPalData!I2:PayPalData!I15000,0),0))

Where;

PayPalData is the sheet you have pasted the data into
E4 = Column header containing "Web Accept Payment Received"
E3 = Column header containing "Subscription Payment Received"
D5 = Row label containing item ID

This deals with 15000 rows of data - the hoops to jump through to find how many rows there are were just not worth it.

You must enter this formula and finish by pressing control-shift-enter.

The formula will warn on empty cells, that's expected with this data set.

(Note, the message board software introduced some spaces because the formula is too long. Remove them.)

This isn't incredibly helpful, but the ",0"'s are superfluous in the formula. You don't need them. This works just as well:

=SUM(IF(PayPalData!E2:PayPalData!E15000=E4,IF(PayP alData!N2:PayPalData!N15000=D5,PayPalData!I2:PayPa lData!I15000)))+SUM(IF(PayPalData!E2:PayPalData!E1 5000=E3,IF(PayPalData!N2:PayPalData!N15000=D5,PayP alData!I2:PayPalData!I15000)))

I discovered array formulas out of desperation. My boss was having me generate iteration after iteration of a certain report. The way the file had been setup required manual entry from printed SAS output. I could only take about a month of that.

I use array formulas for everything now. Even summing up 6 rows of data. "Ctrl+Shift+Enter" is embedded into my spinal cord. I could do those keystrokes even if you cut off my head.

[a_unique_person]

If anybody is interested in how to filter PayPal all activity CSV's for subscription & other item sales;

=SUM(IF(PayPalData!E2:PayPalData!E15000=E4,IF(PayP alData!N2:PayPalData!N15000=D5,PayPalData!I2:PayPa lData!I15000,0),0))+SUM(IF(PayPalData!E2:PayPalDat a!E15000=E3,IF(PayPalData!N2:PayPalData!N15000=D5, PayPalData!I2:PayPalData!I15000,0),0))

Where;

Just do a quick n dirty in access and use SQL to do your queries ;)

[BenBurch]

Just do a quick n dirty in access and use SQL to do your queries ;)

Whole mess needs to be emailable and archivable... so a one-file solution is preferred. :-)

[krelnik]

Reminds me I have an XL sheet somewhere that has network games of Cribbage, connect 4, a sort of turn based multi player maze shooter game, (and I didn't cheat just by embedding a flash game, they use Cell formulae only I was very very bored)

I have an Excel sheet that we use to do the "pick a name out of a hat" Christmas gift scheme in our family. Of course it insures each person only buys one gift, everyone gets a gift, and that you won't be buying a gift for anyone you already bought for in the last 5 of so years. (That last thing was why I built it, my sister kept complaining that she'd get the same person two years in a row).

It has formulas like this in it:


=IF( ISNA(MATCH(G$7,OFFSET($C$8,0,0,ROW(G9)-ROW(G$7)-1,1),0)),RAND(),0)

=IF(SUM(E13:O13)=0,NA(),CHOOSE(MATCH(MAX(E13:O13), E13:O13,0),$E$7,$F$7,$G$7,$H$7,$I$7,$J$7,$K$7,$L$7 ,$M$7,$N$7,$O$7))

[Jimcalagon]

I have spent the better part of the last two weeks trying to convert a customer's labyrinthine, cryptic and uncommented macros from excel spreadsheets into VB6 code - can I just comment that you people are sick and urgently need professional help.

(And this is coming from someone who started out using reverse polish maths on PICK databases.)

[JonnyFive]

I have spent the better part of the last two weeks trying to convert a customer's labyrinthine, cryptic and uncommented macros from excel spreadsheets into VB6 code - can I just comment that you people are sick and urgently need professional help.

(And this is coming from someone who started out using reverse polish maths on PICK databases.)

Hey! I comment my labyrinthine, cryptic macros, thank you very much.

Sometimes. You know, if it's important.

[grmcdorman]

Hey! I comment my labyrinthine, cryptic macros, thank you very much.

Sometimes. You know, if it's important.
Right.

If it's hard to write, it should be hard to understand, right? We can't have just anybody looking at macros (and code), after all.

:D

[varwoche]

Ah, so here's where the Excel nerds hang out. Ever since Pivot Tables I (thankfully!) am retired from SUM-IF pasta dishes.

[Furi]

I have spent the better part of the last two weeks trying to convert a customer's labyrinthine, cryptic and uncommented macros from excel spreadsheets into VB6 code

Bah! Comments are for Pussies not Kittys (mrewwph), if I do have to comment my code however

' Well this next loop should be obvious
' Come on code rat you don't need me to tell you that you could be out in the sun shooting butterflies with an air rifle instead
' for my next trick I will use compatable variable types for whatever pupose I desire, weep fool weep
' Variable Naming Convention we don't need no steenking naming convention

I like to spice my code (and sheets) up with bizarre code, such as a cat kaiku generator in a pupil database, or an animated neko progress bar in an array processor, or my favourite error state indicators in the version cell

=^--^=, =^..^=, =^■■^=, =^oo^=, =^OO^=, =^xx^=,

that way when doing support all I have to do is ask is the kitty asleep awake suprised or dead,

I will often use some very very very inventive logic and redundant code containing looped refs to non existant sheets (mwuhuhhuhhahaha) just to confuse the poor fool that thinks they can jack my sheet (and I mean sheet both in an XL way and heavily accented speech way).

can I just comment that you people are sick and urgently need professional help.

why thankyou, you say the sweetest things

[Furi]

Ah, so here's where the Excel nerds hang out. Ever since Pivot Tables I (thankfully!) am retired from SUM-IF pasta dishes.

The only use for pivot tables is for management *starts collecting phlegm* to ask for them and to stop all my fun of writing an app to do it, or to ask if I can put a non-pivoting pivot table as a powerpoint *snurrrrrrrrk floothui* presentation.

[JonnyFive]

Right.

If it's hard to write, it should be hard to understand, right? We can't have just anybody looking at macros (and code), after all.

:D

That's right. The peons have no business understanding the material I create. I like to think of it as creating job security. :D

Really though, I do comment my VBA crap when it's not obvious what it's doing. Also, I'm pretty picky about variable and field naming conventions, so the code is generally relatively clear, if a bit verbose.

[Wowbagger]

Bah, I don't use those new-fangled features. Real Excel power users do everything in VBA*!

(*except the ones who do everything in .NET)

[BenBurch]

I suppose I need to learn VBA, huh? 35 years in computing, and the only flirtations with BASIC were in the mid 70s when there wasn't really anything else other than assembler, and that wasn't too suitable for quick calculation. (Anybody remember FOCAL?)

[Blue Bubble]

I suppose I need to learn VBA, huh? 35 years in computing, and the only flirtations with BASIC were in the mid 70s when there wasn't really anything else other than assembler, and that wasn't too suitable for quick calculation. (Anybody remember FOCAL?)


Eh? Mid 70s and not really anything else other than Assembler????
Ben, that's nonsense, and if you've really 35 years in computing, you really should know better.

Heck, I started off in 1969 with Fortran IV and Algol 60.

[krelnik]

Now back in the fall of seventy-one, we were gonna..... Hey! You kids! Get off my lawn!

[BenBurch]

Eh? Mid 70s and not really anything else other than Assembler????
Ben, that's nonsense, and if you've really 35 years in computing, you really should know better.

Heck, I started off in 1969 with Fortran IV and Algol 60.

Well, not on the Altair 8800, or the DG Nova or the HP 2000 I had access to.

Now, when I got my first JOB in computing, the IBM 1130 had FORTRAN II and RPG I.

[Wudang]

RPG I? Wow! The very first Role Playing Game. You got to pretend to be a programmer. ;-)

Seriously though, VBA is ugly. I hate the damned thing and hated having to get to grips with it. But it was the quickest way to do some things with excel. For preference I process spreadsheets (quite specialized data) with perl and the Win32 module.

[JonnyFive]

RPG I? Wow! The very first Role Playing Game. You got to pretend to be a programmer. ;-)

Seriously though, VBA is ugly awesome. I hate the damned thing love VBA and hated loved having being allowed to get to grips with it bask in its awesomeness. But it was the quickest way to do some things with excel. For preference I process spreadsheets (quite specialized data) with perl and the Win32 module. VBA is god and can do everything!

There, I fixed the typos.

[Wowbagger]

Seriously though, VBA is ugly. I hate the damned thing and hated having to get to grips with it. But it was the quickest way to do some things with excel. For preference I process spreadsheets (quite specialized data) with perl and the Win32 module.
Yep. No doubt about it. VBA is UGLY. But, perhaps not as ugly as conditional sums and counts in Excel?

Alternatively, you could use the .NET Framework, with Visual Studio Tools for Office. But, I still end up using VBA for Excel, 'cause it's so quick and dirty!

[JonnyFive]

Yep. No doubt about it. VBA is UGLY. But, perhaps not as ugly as conditional sums and counts in Excel?

Alternatively, you could use the .NET Framework, with Visual Studio Tools for Office. But, I still end up using VBA for Excel, 'cause it's so quick and dirty!

The thing is, VBA is very well integrated into Office, and it does the job. Sometimes you just need something that will get the job done, elegance be damned!

You know, like conditional sums in Excel.

[Worm]

This warms the cockles of my heart, because I use this kind of crap every day.

Gee, I'd sure like to count how many employees on this census are both in NY and also male. Sure would be nice if I had a countif function that took two parameters. What's that, Excel? You say I need to use the SUM command in some arcane combination with the IF command and something you call an "array formula." Of course, that makes perfect sense.

It is counterintuitive, but god damn is it beautiful when everything clicks.


Sounds like a SUMPRODUCT to me.....

=SUMPRODUCT((Gender="Male")*(State="NY"))

(where 'Gender' and 'State' are named ranges, just use B2:B20000 etc. if you prefer....)

Hey, maybe I'm preaching to the converted, but SUMPRODUCT is pretty damn powerful.

I suspect it might address the problem in the OP as well, but I can't quite get my head around the file structure.

[JonnyFive]

Sounds like a SUMPRODUCT to me.....

=SUMPRODUCT((Gender="Male")*(State="NY"))

(where 'Gender' and 'State' are named ranges, just use B2:B20000 etc. if you prefer....)

Hey, maybe I'm preaching to the converted, but SUMPRODUCT is pretty damn powerful.

I suspect it might address the problem in the OP as well, but I can't quite get my head around the file structure.

I just tried it and it doesn't work. There might be a way to work something out with SUMPRODUCT, but It probably is actually more complicated than just using the array formula with the SUM((blah)*(blah)*(blah)).

SUMPRODUCT is cool, yeah, but I find it to be more of a specific tool than SUM. Maybe that's just me, I dunno.

[BenBurch]

RPG I? Wow! The very first Role Playing Game. You got to pretend to be a programmer. ;-)

Seriously though, VBA is ugly. I hate the damned thing and hated having to get to grips with it. But it was the quickest way to do some things with excel. For preference I process spreadsheets (quite specialized data) with perl and the Win32 module.

If you ever SAW RPG I you would know that it is indeed a pretend programming language!

It is an attempt to set into card representation the sort of things you could plug-board-program an old IBM printing tabulator machine hooked to a collator and reader-punch to do.

[Wudang]

There, I fixed the typos.

http://the.infiniteplane.com/images/post/condescending-unix-user.png

[JonnyFive]

http://the.infiniteplane.com/images/post/condescending-unix-user.png

You're just mad because I'm right. :D

[Wudang]

That it's tightly integrated into Office and is often the quickest and best way to deal with an Excel problem? Of course.

Serious question though it may not sound like it - can you actually get decent work doing purely Office-based solutions? My career has been in 2 massive companies. Just an idle question.

[JonnyFive]

Serious question though it may not sound like it - can you actually get decent work doing purely Office-based solutions? My career has been in 2 massive companies. Just an idle question.

In my experience, that would really depend on the scale and complexity of what you're doing. You can actually use a few of the Office products, notably Access, as fairly robust platforms for doing stuff.

Actually, Access (and maybe Excel) can effectively be used as a rapid deployment platform for internal stuff, as long as you're careful about what the users can access (haw, I made a pun) and everyone has the software. It doesn't scale well, but for a small office it's sometimes the quickest, easiest way to do the job.

When I was in the beginning of the second year with the insurance TPA (third-party administrator - we do various things for a bigger insurance company, but we're a pretty small company ourselves), I started working on an Access-based computer underwriting package. It's useful because it can be very quickly updated to fix bugs, is extremely easy to set up, and doesn't require the whole build cycle to deploy if something needs to change.

So I could've done the job in VB.NET or something, but realistically that would've just been more of a pain in the ass than needed. The software is used by around four people (including myself) who do the underwriting here, so it doesn't need to be massively scalable or even that user friendly (although I've done a lot to customize the Access interface to make it behave more like the users expect - for example, hitting CTRL+S saves the record they're working on rather than the form they're viewing).

All told, it works really well. It would scale horribly, and some of the code required to get Access to behave is a little funky (why in the everloving hell did they use banker's rounding?! I had to write a piece of code to freaking round numbers properly), but to the users it's a dream and it was something I could develop part-time, since that isn't my primary job at the company and our IT department was too overworked to come up with something for the underwriting department in a reasonable time frame.

Sorry for the sermon, but the too long; didn't read answer is "yes." Basically. Sometimes. For smaller companies. :)

Also, I made full working versions of checkers (two-player, no AI) in Excel and Tetris in Access. Toot toot!

[Wudang]

Oh yeah - I learned VBA as a departed colleague had written a fairly big app in it but such a bad data model and "personal" coding that I decided it was quicker to start again. My wife's IT manager for a company and all their work is VBA/SQL server and she gets a lot of CVs saying "I hav lerned Ofice and can do VBA. I want job as senier programmer" and was wondering if they were as delusional in as many ways as I thought.

eta - to be fair she also gets lots of "Career : 20 year down t'pit. 6 1-hour classes in java oop t'night school. Position sought: senior java architect".

[Worm]

I think it depends what you define as 'decent work'

I have done a fair bit of Office-based development in one way or another, but it's not been what I would consider programming or even system development.

More like 21st Century Admin than anything...

[Furi]

That it's tightly integrated into Office and is often the quickest and best way to deal with an Excel problem? Of course.

Serious question though it may not sound like it - can you actually get decent work doing purely Office-based solutions? My career has been in 2 massive companies. Just an idle question.

As Johnny 5 has said, depends on scale and time, there are many situations where I will rig up a quick and dirty excel soln (not everyone has access installed) smaller companies love it as it is covered by their immediate requirement using existing licenses, and think they can get pretty much anyone to support it (their thoughts not even close to the the truth :p) it can be used for prototyping and workflow testing prior to any major comitment, as a lot of people can explain what they need in spreadsheet terms.

unfortunately (as in a lot of cases outside of tooling up via office,) the attitude of "The test system worked so why should we continue to hire that Stunningly handsome and mentally stable* developer to do that super duper efficient tight nice and tidy code in a dedicated application, lets just run this uncompiled xl app with all the commented out code redundant segments and kitten referances in, until it collapses as it wasn't designed for full deployment then we can blame it all on him when we hire the next developer" seems to prevail

*DISCLAIMER: words shown are for display purposes only, and may not be representative of actual words used

[JonnyFive]

Oh yeah - I learned VBA as a departed colleague had written a fairly big app in it but such a bad data model and "personal" coding that I decided it was quicker to start again. My wife's IT manager for a company and all their work is VBA/SQL server and she gets a lot of CVs saying "I hav lerned Ofice and can do VBA. I want job as senier programmer" and was wondering if they were as delusional in as many ways as I thought.

It's a stupid attitude to have, yeah. VBA would be an absolutely horrid place to learn programming in. I've got some experience in other VB flavors (mostly VB.NET), some C++, and some Java from school, and VBA really doesn't seem to offer anything in terms of learning structure or technique.

I think some people don't realize there's a lot more to programming and software development than simply knowing a particular programming language. I mean, just knowing a language doesn't mean you know how to organize code flow, structure data, implement data conventions, or even name things so that a new user can easily figure out what the hell the program is doing.

I think it depends what you define as 'decent work'

I have done a fair bit of Office-based development in one way or another, but it's not been what I would consider programming or even system development.

More like 21st Century Admin than anything...

I guess it depends. What I worked on was definitely programming and development work. It wasn't really complex, high-level stuff, but it involved a significant chunk of code to get everything to function properly, and a lot of design work to create the framework to build off of.

I'd say it was programming/development in the sense that a mathematician might use C to develop a program to solve a complex problem. I used the Access/VBA platform to solve a very specific set of problems for a very specific user base.

When you start to move beyond that level of things (or scale to large numbers of users), it's just too much of a pain in the ass to make Office behave. At a certain point it stops saving time to use Office for rapid deployment and makes more sense to just use something more robust and go for a standard internal deployment model.