- Apr 5, 2014
- 23,587
Yes. I'd rather just run a single linear column where something can identify that the innings was 'not out'.
Got something to say or just want fewer pesky ads? Join us... 😊
Any excel wizards?
- Thread starter Publius Ovidius
- Start date
Yes. I'd rather just run a single linear column where something can identify that the innings was 'not out'.
Publius Ovidius
Well-known member
- Thread starter
- #23
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
Anyone want some sad geeky advice on ITIL change management process and policy in return, please do let me know.
Cheers
FatSuperman
Well-known member
- Feb 25, 2016
- 2,830
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
New member
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
Well-known member
- Feb 25, 2016
- 2,830
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
Well-known member
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
Well-known member
- Sep 28, 2011
- 10,686
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
Well-known member
- Sep 28, 2011
- 10,686
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.
- Apr 5, 2014
- 23,587
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
Well-known member
- Feb 25, 2016
- 2,830
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.
Last edited:
Publius Ovidius
Well-known member
- Thread starter
- #32
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
Publius Ovidius
Well-known member
- Thread starter
- #33
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
Well-known member
- Sep 28, 2011
- 10,686
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.
- Apr 5, 2014
- 23,587
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
Last edited:
Garage_Doors
Originally the Swankers
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
Is there a simple easy way to convert this is to a pie chart or block graph?
Please
spongy
Well-known member
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.
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
Well-known member
- Feb 25, 2016
- 2,830
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
Well-known member
- Feb 25, 2016
- 2,830
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