Need some Excel help
|
Joined: Sep 2004
Posts: 5,422
axiomite
|
OP
axiomite
Joined: Sep 2004
Posts: 5,422 |
I am actually pretty handy with Excel, but ran into something today that I've wanted to "automate" via a formula for some time.
Here is what I've got.
I work in IT support and have a report of all of my technicians' tickets. I am trying to report back on how many of their tickets were "late" per technician.
Just for this situation, lets say that all of the tickets are sorted alphabetically by technician, and that there are 3 columns (there are more, but this will be all that matters for the formula). Column A is ticket number Column B is technician's name Column C is either blank or it has a note about a ticket being late.
For getting total ticket counts I've been using the COUNTIF function to count how many times a technician's name shows up on the report. But for this, I want a count of how many times that technician has something entered in Column C.
Only late tickets have an entry in Column C, otherwise it is blank like I said.
I am sure that this will be one of those "oh, duh" moments, but I'm not getting it yet.
Thanks everyone.
Farewell - June 4, 2020
|
|
|
Re: Need some Excel help
|
Joined: Oct 2006
Posts: 484
devotee
|
devotee
Joined: Oct 2006
Posts: 484 |
You're close. CountIf() is going to give you trouble. Use CountA() instead, as in: =COUNTA(C:C)-1 The "minus 1" is to account for your Comment column heading. I never thought that I'd see an Excel question here! What's next, Monarch questions?
|
|
|
Re: Need some Excel help
|
Joined: Sep 2004
Posts: 5,422
axiomite
|
OP
axiomite
Joined: Sep 2004
Posts: 5,422 |
Actually, I got it just a couple of minutes ago with COUNTIFS.
I named the data in Column B as "Tech" and Column C as "RLate"
Formula is: =COUNTIFS(Tech,A5,RLate,"<>")
As a note, A5 is on a second sheet and relates to a technicians' name. I could have plugged in "Tom Smith" or something there.
Anyway, it worked.
Farewell - June 4, 2020
|
|
|
Re: Need some Excel help
|
Joined: Sep 2004
Posts: 5,422
axiomite
|
OP
axiomite
Joined: Sep 2004
Posts: 5,422 |
It wouldn't take much to convert from COUNTIF to COUNTA, but what is the difference for that part?
Last edited by nickbuol; 08/23/10 07:56 PM.
Farewell - June 4, 2020
|
|
|
Re: Need some Excel help
|
Joined: Oct 2006
Posts: 484
devotee
|
devotee
Joined: Oct 2006
Posts: 484 |
If you're getting accurate results with the current formula, I don't see the need to change it, (er, I just noticed that you've edited the post), but maybe you're just wanting a consistent approach or something. (?)
Do you mean the difference between CountIf and CountA?
CountIf allows you to specify comparison criteria, whereas CountA counts cells containing text within the specified range.
Or are you referring to something else?
|
|
|
Re: Need some Excel help
|
Joined: Dec 2003
Posts: 10,420
shareholder in the making
|
shareholder in the making
Joined: Dec 2003
Posts: 10,420 |
This forum never ceases to amaze me with the depth of help we can get here.
Jason M80 v2 VP160 v3 QS8 v2 PB13 Ultra Denon 3808 Samsung 85" Q70
|
|
|
Re: Need some Excel help
|
Joined: Jan 2010
Posts: 578
aficionado
|
aficionado
Joined: Jan 2010
Posts: 578 |
This forum never ceases to amaze me with the depth of help we can get here. On that note, I want to do dynamic ranges... If I add something to a dropdown (thru Validation/List)(and the corresponding row) i'd like it to automatically be added to a range or list (dropdown range for list) Any thoughts on that? To be available for summaries or a VLOOKUP Thanks Jeff
Last edited by wordgasm; 08/24/10 12:06 AM.
Fronts/Wides:M22s in/on Center:VP150/VP100 in/on Hghts,SS,SR:QS8s Sub:EP500 AVR:Onkyo 3008
|
|
|
Re: Need some Excel help
|
Joined: Apr 2007
Posts: 4,877
connoisseur
|
connoisseur
Joined: Apr 2007
Posts: 4,877 |
Jeff,
I'm pretty handy with excel but wouldn't call myself and expert. I am not entirely sure what you are trying to accomplish. If you just want a dropdown list to have new choices appear when you enter new entries into a column associated with the dropdown list then the answer is simple.
For the cell that has the data validation/list in it just modify the range to include rows beyond your existing last row. when new entries are made they will automatically be added to the choice in the dropdown list.
Example: Say you have data in cells A2:A20 and this corresponds to an existing drop down list. In the cell that contains the data validation/dropdown list just make the range something like A2:A100 (or whatever you think the greatest row number will ever be including future entries.) If you enter data into A:21 it will show up in the dropdown list. if you add data into cells A22 it will then be added into the list and so on.
There may be a better way to accomplish this but this seems the easiest. Forgive me if this is not what you are trying to accomplish.
Last edited by terzaghi; 08/24/10 01:18 AM.
-David
|
|
|
Re: Need some Excel help
|
Joined: Oct 2006
Posts: 484
devotee
|
devotee
Joined: Oct 2006
Posts: 484 |
Hi Jeff, there's a bit of a description of dynamic range names on my site (see "4 Sure-Fire Ways to Build Efficient Excel Reports"). Once you have your range name, select the Data Validation, select List from the Allow drop-down, and enter your range name for the source. Be sure to use =lstValues for the range name lstValues (for instance), not just lstValues. Lookups (such as VLOOKUP or HLOOKUP) are great for small workbooks, but will bog down performance for larger workbooks. Use a combination of MATCH and INDEX instead of lookups in those cases. An additional benefit of dynamic ranges used in conjunction with data validation is that the source data for the validated listed need not reside on the same worksheet, which allows for much more flexible and easier to maintain designs. Post back if you have further questions.
Last edited by Kruncher; 08/24/10 02:58 AM. Reason: additional benefits
|
|
|
Re: Need some Excel help
|
Joined: Jan 2010
Posts: 578
aficionado
|
aficionado
Joined: Jan 2010
Posts: 578 |
Hi Jeff, there's a bit of a description of dynamic range names on my site (see "4 Sure-Fire Ways to Build Efficient Excel Reports"). Once you have your range name, select the Data Validation, select List from the Allow drop-down, and enter your range name for the source. Be sure to use =lstValues for the range name lstValues (for instance), not just lstValues. Lookups (such as VLOOKUP or HLOOKUP) are great for small workbooks, but will bog down performance for larger workbooks. Use a combination of MATCH and INDEX instead of lookups in those cases. An additional benefit of dynamic ranges used in conjunction with data validation is that the source data for the validated listed need not reside on the same worksheet, which allows for much more flexible and easier to maintain designs. Post back if you have further questions. Thanks Krunch came up with this per your examples and works as stated. =IF(MATCH(K31,DEALERLIST,0),IF(ISNA(K28),"",INDEX(DEALERRANGE,K28,2))) Next on to dynamic ranges! I've taught myself more than basic excel over the last couple years at work as time allows, and it all works, but I have no idea if I am doing it "right". I've got many workbooks with thousands of VLOOKUPS (they all seem to save in 10-15 seconds. Will probably take about a full day for each workbook to update with ATCH/INDEX and test. But if you say it's worth it, I'll take the plunge. And yes many formulas with double VLOOKUPS in each calulation) Example: =IF($G2="",0,(VLOOKUP($G2,BASERANGE,5,0)*$H2)+IF($I2="",0,(VLOOKUP($I2,BASERANGE,5,0))*$J2)+IF($K2="",0,(VLOOKUP($K2,BASERANGE,5,0))*$L2)) I also use IF/AND/OR alot, any better options for those? Example: =IF($F2="","",IF($F2="Oval",3.1416*(SQRT(2*((POWER($CE2,2)+(POWER($CF2,2))-((POWER(($CE2-$CF2),2)/2.2)))))),0)) =IF($M2="","",IF(OR($M2="Bevel",$M2="Waterfall"),BA2-1,IF($M2="Shaped",BA2-0.625,BA2))) Thanks Again Jeff
Fronts/Wides:M22s in/on Center:VP150/VP100 in/on Hghts,SS,SR:QS8s Sub:EP500 AVR:Onkyo 3008
|
|
|
Re: Need some Excel help
|
Joined: Oct 2006
Posts: 484
devotee
|
devotee
Joined: Oct 2006
Posts: 484 |
I wouldn't worry too much about a few thousand VLOOKUPS, especially when the workbook recalcs in under a minute. I once inherited a workbook with hundreds of thousands of VLOOKUPS that took over 40 minutes to recalculate. Not a typo. Minutes. I got it down to about 40 seconds by converting the approach. It took a couple of hours to change the approach. Maybe it's just me, but I prefer to simplify the logic when building IF formulas, breaking out the calculation type work into separate cells, and then referencing those cells in the IF. Not only does it make it easier to review (and build) ALL of the formulas in the sheet, but because of how Excel recalculates it often helps to improve recalculation performance. See the comments on volatile functions. I've seen references that say that the INDEX function is not volatile, despite what some MS documentation says. My results back that up. And no, I don't know of any better options for the IF and the Boolean functions. So long as you understand what you're building, you won't get surprises using those. I'm self-taught too. You're definitely on the right track and are asking the right questions. Keep it up!
|
|
|
Re: Need some Excel help
|
Joined: Jan 2010
Posts: 578
aficionado
|
aficionado
Joined: Jan 2010
Posts: 578 |
I wouldn't worry too much about a few thousand VLOOKUPS, especially when the workbook recalcs in under a minute. I once inherited a workbook with hundreds of thousands of VLOOKUPS that took over 40 minutes to recalculate. Not a typo. Minutes. I got it down to about 40 seconds by converting the approach. It took a couple of hours to change the approach. Maybe it's just me, but I prefer to simplify the logic when building IF formulas, breaking out the calculation type work into separate cells, and then referencing those cells in the IF. Not only does it make it easier to review (and build) ALL of the formulas in the sheet, but because of how Excel recalculates it often helps to improve recalculation performance. See the comments on volatile functions. I've seen references that say that the INDEX function is not volatile, despite what some MS documentation says. My results back that up. And no, I don't know of any better options for the IF and the Boolean functions. So long as you understand what you're building, you won't get surprises using those. I'm self-taught too. You're definitely on the right track and are asking the right questions. Keep it up! Once again many thanks... If I'm not taking advantage ..... but I prefer to simplify the logic when building IF formulas, breaking out the calculation type work into separate cells, and then referencing those cells in the IF.If I understand, your talking about performing the base calculations in there own cells and then "IF" them all in the same IF calculation? For this particular workbook (and a few others), I have to keep all data for each "record" on one row (I'm printing the calculated results of the user's input options choices to a form/report using an excel mail merge) and I'd run out of columns breaking out all the individual If's/options. But I wouldn't mind an example of "simplifying the logic when building IF formulas, breaking out the calculation type work into separate cells, and then referencing those cells in the IF", from one of my formulas or something you have available, if time allows. Once again, if I'm not taking advantage. Thanks Jeff
Fronts/Wides:M22s in/on Center:VP150/VP100 in/on Hghts,SS,SR:QS8s Sub:EP500 AVR:Onkyo 3008
|
|
|
Re: Need some Excel help
|
Joined: Oct 2006
Posts: 484
devotee
|
devotee
Joined: Oct 2006
Posts: 484 |
In the end you've got to do what you've got to do, and if that would exceed the number of columns then, 1) Wow, that's some wide sheet!, and 2)"If it ain't broke, don't fix it". Could you reorganize the sheet to be top to bottom instead of left to right? This allows for many more items and is often just as easy to manage. But if you're already working with a large number of rows, then that's probably not possible for you. Think of this primarily as a design choice, and a choice that make it easier to maintain and review at that. No more, no less. That said, you're correct, break out the True and False conditions of the If functions to separate cells when you can. So your example of =IF($F2="","",IF($F2="Oval",3.1416*(SQRT(2*((POWER($CE2,2)+(POWER($CF2,2))-((POWER(($CE2-$CF2),2)/2.2)))))),0)) in say C2, could change as follows: A2 gets =IF($F2="Oval",B2,0): And B3 becomes: =3.1416*(SQRT(2*((POWER($CE2,2)+(POWER($CF2,2))-((POWER(($CE2-$CF2),2)/2.2)))))) I think that I got the ( and ) count right there. C2 becomes: =IF($F2="","",A2,0)) IMO, much more readable, and easier to build. An aside, if you'll allow me. If you're regularly connecting data via VLOOKUP functions, you owe it to yourself to have a hard look at Monarch. Simply put (really simply), it's a data management tool for the everyman. I'm not going to go off on some big sales pitch here (I don't sell it anyway), but clearly I'm a bit passionate about it. Spend a few minutes on my site, check out some videos and other posts, and email me if you have further questions. I'd been using spreadsheets for years before I discovered Monarch software, and it really has changed my work, heck, my career, for the better. Excel + Monarch is like Lennon + McCartney. Each is good on its own, but together, it's magic.
|
|
|
Re: Need some Excel help
|
Joined: Jan 2010
Posts: 578
aficionado
|
aficionado
Joined: Jan 2010
Posts: 578 |
In the end you've got to do what you've got to do, and if that would exceed the number of columns then, 1) Wow, that's some wide sheet!, and 2)"If it ain't broke, don't fix it". Excel + Monarch is like Lennon + McCartney. Each is good on its own, but together, it's magic. Mucho GrassyAss. Thanks for all the help! Might need more when I have time for the "Dynamic Ranges"! I'll check into MOnarch. I've already saved the sight, after noticing your passion! Jeff
Fronts/Wides:M22s in/on Center:VP150/VP100 in/on Hghts,SS,SR:QS8s Sub:EP500 AVR:Onkyo 3008
|
|
|
Forums16
Topics24,945
Posts442,486
Members15,617
|
Most Online2,082 Jan 22nd, 2020
|
|
0 members (),
1,192
guests, and
2
robots. |
Key:
Admin,
Global Mod,
Mod
|
|
|
|