Any excel wizards?

[Beach Hut]

Bloody excellent guys. I can now see where we are falling behind on our SLA's and can point to clear evidence.

Anyone want some sad geeky advice on ITIL change management process and policy in return, please do let me know.

Cheers

ITIL really is a pathetic standard just like ISO

 

[FatSuperman]

I have a table of 6 columns which details date, a datum figure and then the gain each day and the percentage gain,

Is there a simple easy way to convert this is to a pie chart or block graph?
Please

I'm not sure if this is a genuine question. To be fair, I'm not sure any of them are, but there you go. You can't really depict anything meaningful on a pie chart, especially not things with completely different measurement criteria. You could represent your data in a stacked bar chart.

Personally I'd sack it all off and use this pie chart:

 

[FatSuperman]

Is it required as the result on an Incident, is is the change part of the incident resolution? If so do the change off the back of the incident ticket and when you get in tomorrow, after the change has been made, contact the change manager to call a eCAB to retrospectively discuss and approve the change.

I love meeting like-minded saddos. We actually have an expedited process so that's the route we've gone down. It's an interesting* question as to whether it was an incident. It's just a report, but going into our ERP (SAP). It's already live, but doesn't work due to performance issues - works perfectly the PT environment, which it turns out has 150m rows in the particular table in use, where production has over 60bn. A slight discrepancy. So effectively, it's poor design which was ratified at every step until it failed in prod.


*Technically, it's not at all interesting

 

[Dave the OAP]

ITIL really is a pathetic standard just like ISO

Oi.... you are dissing my life here!

 

[Beach Hut]

Oi.... you are dissing my life here!

Really it is just like Prince Practioners etc just a industry standard which ticks boxes.

If you just use your common sense you can achieve any project at work (proviso being the company you work for has the resources)

 

[FatSuperman]

If you just use your common sense you can achieve any project at work

Not if you are the only one using common sense! Which is unfortunately why you need these standards, some of it really isn't immediately obvious. I prefer small companies as there is so much less red tape and defined process, obviously riskier but easier to remedy issues.

That said, I've been working for big companies for about a decade instead

 

[Bad Ash]

thanks.

So what formula do i use to instruct excel to count the amount of cells with * in them ?

Also, how do i get excel to total the amount of runs in a column that includes runs with * against them ?

E.g:

A2 45
a3 32*
a4 12

total (formula) should be 99- the problem being that excel may have a problem counting with an * in a cell. It doesn't seem to count the total of all the cells. It ignores the one with the *
innings (formula) should be 3 edit: Got that using 'counta'
not out (formula) should be 1 edit: Just got that one using your formula

=sumproduct(value(substitute(a2:a4,"*","")))

 

[KZNSeagull]

I love meeting like-minded saddos. We actually have an expedited process so that's the route we've gone down. It's an interesting* question as to whether it was an incident. It's just a report, but going into our ERP (SAP). It's already live, but doesn't work due to performance issues - works perfectly the PT environment, which it turns out has 150m rows in the particular table in use, where production has over 60bn. A slight discrepancy. So effectively, it's poor design which was ratified at every step until it failed in prod.


*Technically, it's not at all interesting

So someone has put something live that does not work properly, but it isn't an incident? That's a bit weird.
Have you now suggested that the development of future reports against this table, or indeed any high volume table, include a pre-production performance test against the production data?

 

[Bad Ash]

SAP, CAB, eCAB, incidents, changes, ITIL - this is far too much like being at work!

 

[eaglejez]

Thanks.

So what formula do I use to instruct Excel to count the amount of cells with * in them ?

Also, how do I get Excel to total the amount of runs in a column that includes runs with * against them ?

E.g:

A2 45
A3 32*
A4 12

Total (formula) Should be 99- the problem being that Excel may have a problem counting with an * in a cell. It doesn't seem to count the total of all the cells. It ignores the one with the *
Innings (formula) Should be 3 EDIT: Got that using 'COUNTA'
Not Out (formula) Should be 1 EDIT: Just got that one using your formula

SUM(VALUE(LEFT($A$2:$A$4,LEN($A$2:$A$4)-(RIGHT($A$2:$A$4)="*")))) will work. Remember this is an array formula so you need to enter it with <ctrl>+<shift>+<enter>

 

[Garage_Doors]

I'm not sure if this is a genuine question. To be fair, I'm not sure any of them are, but there you go. You can't really depict anything meaningful on a pie chart, especially not things with completely different measurement criteria. You could represent your data in a stacked bar chart.

Personally I'd sack it all off and use this pie chart:
View attachment 74059

Very pretty pie that

Ok how do i transpose the data i have into a stacked bar chart.?

 

[Bad Ash]

SUM(VALUE(LEFT($A$2:$A$4,LEN($A$2:$A$4)-(RIGHT($A$2:$A$4)="*")))) will work. Remember this is an array formula so you need to enter it with <ctrl>+<shift>+<enter>

My previous sumproduct solution could be switched to SUM(VALUE(SUBSTITUTE(A2:A4,"*",""))) if using an array formula. I find sumproduct slightly easier as I often forget the key combo to enter an array formula, though not nearly as frequently as I forget how to enter a new line in the text box!

 

[The Andy Naylor Fan Club]

Why the f#€k did I open this thread? :

 

[Eeyore]

SUM(VALUE(LEFT($A$2:$A$4,LEN($A$2:$A$4)-(RIGHT($A$2:$A$4)="*")))) will work. Remember this is an array formula so you need to enter it with <ctrl>+<shift>+<enter>

Thanks. When I enter the formula it just enters it as text rather than formula. I hold the CTRL+SHIFT down then press ENTER at the same time.

 

[Eeyore]

Erm.

I opened this thread as I'm doing a course and need to show functional skills such as Internet, word processing, Excel etc etc.

I don't use computers generally and only have a good basic knowledge of word processing really as I left school circa 1995. We only had one computer room then.

I passed a recent test with 100% in Internet skills and word processing. Got 5% in excel and formulas.

I opened this thinking it might have been a piss taking thread. I just need to know how to make the text read vertically instead of horizontal on a table so it isn't 4" wide and fit on a page.

Fml.

You may be on the same course as me in Hove.

 

[Eeyore]

SUM(VALUE(LEFT($A$2:$A$4,LEN($A$2:$A$4)-(RIGHT($A$2:$A$4)="*")))) will work. Remember this is an array formula so you need to enter it with <ctrl>+<shift>+<enter>

Done it now. Thanks.

 

[Bad Ash]

Thanks. When I enter the formula it just enters it as text rather than formula. I hold the CTRL+SHIFT down then press ENTER at the same time.

Did you put a = at the beginning? so =SUM(...
The = at the beginning indicates its a formula rather than just text.
Still think mine is simpler because it doesn't require ctrl + shift + enter at the same time to turn it into an array forumula:
=sumproduct(value(substitute(a2:a4,"*","")))

 

[Eeyore]

Did you put a = at the beginning? so =SUM(...
The = at the beginning indicates its a formula rather than just text.
Still think mine is simpler because it doesn't require ctrl + shift + enter at the same time to turn it into an array forumula:
=sumproduct(value(substitute(a2:a4,"*","")))

No I forgot the = and, yes, I used yours in the end. I know the rest so I can do the averages in far less time.

And I've got 30 years worth to do !

 

[clapham_gull]

I'm not an Excel wizard - but a computer scientist by education. So I know my way round it if you get my drift.

I'm just designed an Excel course with help and had the first pilot last week. Basically take a bog standard course - rip out the examples and put your own in. In my world it was actually rip out the financials and get the thing to work out something logistical. So a lot of conditional logic in there particularly around dates.

Fascinating experience. Many people who can do Pivots and Graphs but can't write even an a basic formula. Surviving on Macros without really understanding what they do.

Step 1: NO MACROS
Step 2: Back to basics.

Encouragingly their appetite to learn if the examples surrounded a daily problem they have was amazing.

Probably gonna run it now across the whole department now.

Oddly at my place - nearly everyone can do a "VLOOKUP" but mostly never heard of AND, OR or even IF.

So midway through introduced them to INDEX and MATCH. If you are a VLOOKUP addict - stop now.

Also some very good advice from the very experienced Excel trainer (and also a deeply clever computer buff) - stop your vanity formulas and break them down into smaller steps. Just like development. Nothing clever in making it indecipherable to the poor ******* who has to pick up your work when you leave....

 

[MattBackHome]

I absolutely LOVE these threads.