Any excel wizards?

[Eeyore]

I think what you're asking is a way of higlighting the batsman was not out but you dont want a column that says out or not out? correct?

Yes. I'd rather just run a single linear column where something can identify that the innings was 'not out'.

 

[8ace]

What's the identifier for him not being out. Sorry could you explain a bit clearer?

*

 

[Dave the OAP]

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

 

[FatSuperman]

I'll step out of the ring - I will fall down once we get passed V/H look ups and it will be painful for everyone having to see us go there

PRS, please note you are a prime candidate for learning 'INDEX MATCH' rather than vlookup. MUCH better performance and not much more difficult to understand either. Check it out and report back

 

[KingstonSeagull]

PRS, please note you are a prime candidate for learning 'INDEX MATCH' rather than vlookup. MUCH better performance and not much more difficult to understand either. Check it out and report back

Completely agree, index match has saved me hours in loading. When you are dealing with 420k rows vlookup just doesnt cut it.

 

[FatSuperman]

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

YES! I have a change I need to put in as an emergency, it's all tested and complete but I don't want to go through the tedious change management process as this adds extra delays due to change advisory board meetings etc. Please advise how I can best circumvent the red tape without getting caught out?

 

[Paul Reids Sock]

Completely agree, index match has saved me hours in loading. When you are dealing with 420k rows vlookup just doesnt cut it.

PRS, please note you are a prime candidate for learning 'INDEX MATCH' rather than vlookup. MUCH better performance and not much more difficult to understand either. Check it out and report back

I have looked it up - I like the look of it. Will give it a run tomorrow and report back!

 

[Uh_huh_him]

Another question on Excel for much needed help...

I need to put together comprehensive averages for my village cricket club.

I want to know a formula that will recognise an innings that the batsman was 'not out' in when calculating completed innings- without having to produce another column for said innings.

Is this possible ?

CountIf or Countifs will be the likely formulae to use to give you the number of out innings to use as your divisor.
Woking out what the criteria are for the formula will depend on how the rest of the data is represented.
When looking at the spreadsheet can you identify if the innings was a not out innings? If so what indicator do you check for this?
This will be the value you will need to use as your criteria for the formula. i.e. is there a column for how out? or is the innings represented as not out with an asterisk?

 

[Uh_huh_him]

PRS, please note you are a prime candidate for learning 'INDEX MATCH' rather than vlookup. MUCH better performance and not much more difficult to understand either. Check it out and report back

Thanks for the tip. I massively overuse VLOOKUP. I will check it out too.

 

[Eeyore]

CountIf or Countifs will be the likely formulae to use to give you the number of out innings to use as your divisor.
Woking out what the criteria are for the formula will depend on how the rest of the data is represented.
When looking at the spreadsheet can you identify if the innings was a not out innings? If so what indicator do you check for this?
This will be the value you will need to use as your criteria for the formula. i.e. is there a column for how out? or is the innings represented as not out with an asterisk?

I'm trying to work out the best criteria whilst only using one column for batsman's match scores. Asterisk would be ideal, but might screw up the totaling function.

 

[FatSuperman]

I'm trying to work out the best criteria whilst only using one column for batsman's match scores. Asterisk would be ideal, but might screw up the totaling function.

You can use sumif / countif to work with asterisks as long as there is never more than one in any given cell (Excel will only count 1 even if there are multiple in the cell). Use char(42) as the thing to search for rather than * as it makes the formula much simpler.

 

[Dave the OAP]

YES! I have a change I need to put in as an emergency, it's all tested and complete but I don't want to go through the tedious change management process as this adds extra delays due to change advisory board meetings etc. Please advise how I can best circumvent the red tape without getting caught out?

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.

Otherwise set up an expedited process with sign off by the relevant management to approve the emergency change without cab approval....BUT it must be brought back to cab at the earliest opportunity.

Cheers

 

[Dave the OAP]

YES! I have a change I need to put in as an emergency, it's all tested and complete but I don't want to go through the tedious change management process as this adds extra delays due to change advisory board meetings etc. Please advise how I can best circumvent the red tape without getting caught out?

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.

Otherwise set up an expedited process with sign off by the relevant management to approve the emergency change without cab approval....BUT it must be brought back to cab at the earliest opportunity.

Cheers

 

[Uh_huh_him]

You can use sumif / countif to work with asterisks as long as there is never more than one in any given cell (Excel will only count 1 even if there are multiple in the cell). Use char(42) as the thing to search for rather than * as it makes the formula much simpler.

Not sure this will work as the sumif formula won't recognise 108* as 108 and will ignore this value in the sum won't it?

My guess you could use a User Defined Formula to have a little macro run through each value and create the average.

 

[mikey25]

Wow. I was confused first. Hehe. This is just like programming if else.

 

[Eeyore]

You can use sumif / countif to work with asterisks as long as there is never more than one in any given cell (Excel will only count 1 even if there are multiple in the cell). Use char(42) as the thing to search for rather than * as it makes the formula much simpler.

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

 

[Garage_Doors]

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

 

[spongy]

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.

 

[FatSuperman]

Not sure this will work as the sumif formula won't recognise 108* as 108 and will ignore this value in the sum won't it?

Yep, I don't know how to do it in a single formula. Can try and work it out but it might be really complex, UDF is probably the best bet. Or another hidden column

The value in A10 will be 229 - the sum of all innings where the batsman was not out (denoted by asterisk). Please note I may have totally misunderstood the original request

	A	B
1	Runs	Hidden column
2	32	=IFERROR(VALUE(LEFT(A2,FIND("*",A2)-1)),A2)
3	112*	=IFERROR(VALUE(LEFT(A3,FIND("*",A3)-1)),A3)
4	27	Etc
5	1	Etc
6	99*	Etc
7	18*	Etc
9	77	Etc
9
10	=SUMIF(A1:A8,CHAR(42),B1:B8)

 

[FatSuperman]

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.

Highlight the cells in question, right click, choose format cells.
Under the 'Alignment' tab you will see a box on the right called 'Orientation'... should be self-explanatory from there