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!E2ayPalData!E15000=E4,IF(PayPalData!N2ayPalData!N15000=D5,PayPalData!I2ayPalData!I15000,0),0))+SUM(IF(PayPalData!E2ayPalData!E15000=E3,IF(PayPalData!N2ayPalData!N15000=D5,PayPalData!I2ayPalData!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!E2ayPalData!E15000=E4,IF(PayPalData!N2ayPalData!N15000=D5,PayPalData!I2ayPalData!I15000,0),0))+SUM(IF(PayPalData!E2ayPalData!E15000=E3,IF(PayPalData!N2ayPalData!N15000=D5,PayPalData!I2ayPalData!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:le 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:le 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!E2ayPalData!E15000=E4,IF(PayPalData!N2ayPalData!N15000=D5,PayPalData!I2ayPalData!I15000,0),0))+SUM(IF(PayPalData!E2ayPalData!E15000=E3,IF(PayPalData!N2ayPalData!N15000=D5,PayPalData!I2ayPalData!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!E2ayPalData!E15000=E4,IF(PayPalData!N2ayPalData!N15000=D5,PayPalData!I2ayPalData!I15000)))+SUM(IF(PayPalData!E2ayPalData!E15000=E3,IF(PayPalData!N2ayPalData!N15000=D5,PayPalData!I2ayPalData!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!E2ayPalData!E15000=E4,IF(PayPalData!N2ayPalData!N15000=D5,PayPalData!I2ayPalData!I15000,0),0))+SUM(IF(PayPalData!E2ayPalData!E15000=E3,IF(PayPalData!N2ayPalData!N15000=D5,PayPalData!I2ayPalData!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.

 

[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.

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

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)