Excel 2003 - does it have 'the ribbon'?

[kabbes]

What gets me, is they're always touted as sime holy grail, when people would be much better learning how to really work a sumproduct first.

You don't want to EVER use a sumproduct, really. You want to use array formulae properly. Sumproduct, for example, should be

{=SUM(IF(range1 = range2, range3 * range4, 0))}

It's a hell of a lot more powerful, because it allows you the full access of possible formulae rather than just summing the product if one value equals another.

 

[kabbes]

I hate the fact that they lose all the formatting whenever you pivot them. I construct my own dynamic tables and graphs using VLOOKUPS etc instead.

You can get of that too. It's in the table properties -- turn off "Automatic Format".

 

[nicksonic]

it's almost like we're doing work!

 

[kabbes]

it's almost like we're doing work!

I'm putting it in my CPD -- 2 hours' Excel training.

 

[Corax]

You can get of that too. It's in the table properties -- turn off "Automatic Format".

It still loses your custom formatting/doesn't adapt it to the new table though, no?

 

[Throbbing Angel]

right, so I know what to look at Excel-wise, cheers guys

Same question but for ACCESS please bearing in mind I have mainly end user and limited experience of Access and that the Job Description states:
"• Maintain an accurate on-line computer database in accordance with the legislation relating to Council Tax and Business Rates and carry out procedures to ensure prompt collection and enforcement of arrears"

is Access 'an on-line computer database' or am I likely to be working with some sort of specially written software that the Revenues office of a County Council uses?

 

[Corax]

Purely as intuition, I'd expect it to be purpose designed for security issues at the least.

 

[tarannau]

What do you mean by 'on line computer database?'

TBH Access isn't that different from Excel imo. As long as you can get your head around the basics of relational databases and URNs you should be fine from a user perspective. I'm assuming that they won't expect you to be query building from the off

 

[Throbbing Angel]

What do you mean by 'on line computer database?'

TBH Access isn't that different from Excel imo. As long as you can get your head around the basics of relational databases and URNs you should be fine from a user perspective. I'm assuming that they won't expect you to be query building from the off

The online database quote is pulled straight from their Job Description, so I have no idea I'm afraid.

I've just installed ACCESS and EXCEL from an old Office 2k Pro disc I have just found. The 2007 disc won't play on this machine and was bought with the intention of going on my new laptop when I get it. Bugger.

Anyway, I would imagine that the role is mainly focused on dealing with people and trying to get them to pay for their business rates etc etc and that the information I gather is then input into the available systems. So I don't think I will be writing queries etc....the salary isn't big enough to be expected to do that kind of stuff I reckon.

DO all Excel queries work the same in Access then or do I have to 'SQL them up?'

 

[tarannau]

I suspect they probably do, not that you'd ever need to do that really - the two packages tend to run side by side and the queries wanted tend to be different. If you need to do quick charting and data manipulation you tend just to export from Access to Excel for example. It's a long time, to be fair, since I've had to get my hands dirty with SQL queries in Access

I suspect for all the job spec bumph it's basically referring to inputting data into Access forms and perhaps running the odd pre-arranged query - eg find me all debtors between the dates of x and x. I wouldn't get too concerned yet. Without trying to patronise you, it's probably easiest to think of the typical Access database as a selection of Excel sheets, linked by by an URN (unique reference number) for each contact/company etc. You can create forms in Access to guide and make inputting data more of a controlled process (ie you don't see the Excel like column and row underpinnings. I suspect that you'll be using (a web based) form to put info in, followed by simple report queries to bring up info

 

[bluestreak]

Knowing how to use vlookups and pivot tables in the public sector is ADVANCED skills. Learning them added nearly 5K a year to my salary. No shit.

 

[Corax]

Knowing how to use vlookups and pivot tables in the public sector is ADVANCED skills. Learning them added nearly 5K a year to my salary. No shit.

Learn a bit of basic VBA and people start avoiding meeting your gaze and backing away from you in corridors. They think it's witchcraft.

 

[BlueSquareThing]

I wouldn't call it pointless at all - non-essential sure, but pointless, no.

To be honest, with public sector funding the two things are pretty much the same in many cases - non-essential = pointless...

 

[BlueSquareThing]

right, so I know what to look at Excel-wise, cheers guys

Same question but for ACCESS please bearing in mind I have mainly end user and limited experience of Access and that the Job Description states:
"• Maintain an accurate on-line computer database in accordance with the legislation relating to Council Tax and Business Rates and carry out procedures to ensure prompt collection and enforcement of arrears"

is Access 'an on-line computer database' or am I likely to be working with some sort of specially written software that the Revenues office of a County Council uses?

Wouldn't suprise me entirely if their database is an Excel file actually...

e2a: Tarannau's probably right - I doubt it'll be much more than using forms and so on effectively.

 

[Throbbing Angel]

I suspect they probably do, not that you'd ever need to do that really - the two packages tend to run side by side and the queries wanted tend to be different. If you need to do quick charting and data manipulation you tend just to export from Access to Excel for example. It's a long time, to be fair, since I've had to get my hands dirty with SQL queries in Access

I suspect for all the job spec bumph it's basically referring to inputting data into Access forms and perhaps running the odd pre-arranged query - eg find me all debtors between the dates of x and x. I wouldn't get too concerned yet. Without trying to patronise you, it's probably easiest to think of the typical Access database as a selection of Excel sheets, linked by by an URN (unique reference number) for each contact/company etc. You can create forms in Access to guide and make inputting data more of a controlled process (ie you don't see the Excel like column and row underpinnings. I suspect that you'll be using (a web based) form to put info in, followed by simple report queries to bring up info

This all makes sense.I most likely am worrying too much about it. The systems will be in place already and I will be chucking data into or digging data out of 'it'.

The web based thing though has me confused. Didn't SAP go 'on0line' a few years back. That's much more tweakable iirc for reports that are of use to dogsbodies in large companies as they only need to know where to look rather than how to construct the query iirc.....anyway

Knowing how to use vlookups and pivot tables in the public sector is ADVANCED skills. Learning them added nearly 5K a year to my salary. No shit.

Can I have a job at your place pls?
What do you do Bluestreak...and who for (generally) out of interest.
I only know you as a bearded wonder on here y'see

Learn a bit of basic VBA and people start avoiding meeting your gaze and backing away from you in corridors. They think it's witchcraft.

Nice.
I did VB at Uni for a year (2001) wrote a payroll program and did some other shit in QuickBasic and passed that year....maybe it'll all come flooding back huh.

I shall have a look at Excel and Access and google what the most common things people do at wiork with these things are and cross my fingers.

Any more on Access?

 

[Ted Striker]

You don't want to EVER use a sumproduct, really. You want to use array formulae properly. Sumproduct, for example, should be

{=SUM(IF(range1 = range2, range3 * range4, 0))}

It's a hell of a lot more powerful, because it allows you the full access of possible formulae rather than just summing the product if one value equals another.

You can do the above on SumP (I think?)...I was always taught array sumifs were notably slower than SumP's (not to mention SumP's are traditionally easier to input/more literal)

 

[kabbes]

You can do the above on SumP (I think?)...I was always taught array sumifs were notably slower than SumP's (not to mention SumP's are traditionally easier to input/more literal)

OK then -- how do you, for example, sum the log of the sum of two ranges if three other criteria are met in the row whilst using SUMPRODUCT? Obviously you can't -- you can only do that either by creating as many columns as you need to do it in stages or by using array formulae.

IFs do always slow it down though, I'll give you that. It's good to avoid them if speed is your aim.

To be honest, I tend to do most of my calculation in a piece of specialist model-building software that has array-handling that would make Excel weep. Five-dimensional stochastic arrays with stochastic bounds? No problems. Excel is actually a bit shit as a model building tool if you want anything complicated done. Which means if I *am* using Excel, I'm probably not that worried about speed.

 

[Ted Striker]

OK then -- how do you, for example, sum the log of the sum of two ranges if three other criteria are met in the row whilst using SUMPRODUCT? Obviously you can't -- you can only do that either by creating as many columns as you need to do it in stages or by using array formulae.

IFs do always slow it down though, I'll give you that. It's good to avoid them if speed is your aim.

To be honest, I tend to do most of my calculation in a piece of specialist model-building software that has array-handling that would make Excel weep. Five-dimensional stochastic arrays with stochastic bounds? No problems. Excel is actually a bit shit as a model building tool if you want anything complicated done. Which means if I *am* using Excel, I'm probably not that worried about speed.

Of course - I mean if we're talking logs then you're using it way beyond what most people use it for!

Sumproduct is impotant to anyone that's needed a vlookup.

(In fairness a bunch of nested do loops in VBA are usually always a more preferable solution as, importantly, you can trigger the calculation)

There are better database tools, there are better modelling tools, and there are better presentational tools, though if you're using the aforementioned to a lite degree, and you need to mix them all up, it is a very powerful tool.

 

[nick]

OK then -- how do you, for example, sum the log of the sum of two ranges if three other criteria are met in the row whilst using SUMPRODUCT? Obviously you can't -- you can only do that either by creating as many columns as you need to do it in stages or by using array formulae.

Arrays are relatively slow - though they have the advantage that anyone else trying to edit the sheet will probably end up destroying it - thus making your role indispensible

Excel 2007 has SUMIFS which allows multiple criteria, avoiding the need for arrays that was caused by only having SUMIF in earlier versions of Exccel.


Fuck - I really need to get out more

 

[Throbbing Angel]

Interview over, not one question about Access or Excel

ffs

 

[Corax]

Interview over, not one question about Access or Excel

ffs

Go back and demand one. It'll show your enthusiasm.

 

[Throbbing Angel]