If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional functions on portions of cell data
I manage a strength report for my employer that accounts for each employee
using a unique number assignment system. This system is designed to allow me to identify which of nine teams an employee belongs to, identifying their specific assignment within that team and also their employment status (i.e., contractor, intern, volunteer). For example, I am 621-03. The "6" indicates which team I am on. The "2" identifies my assignment (Quality Assurance). And the "1" indicates employment status. My strength report needs to provide an accountability breakdown for teams and broken down further by employment status. Is there a function that can extract a specific digit/character from a cell entry? Currently, I am specifying cell ranges for each group. As the organization expands, this will present an accuracy problem as valid cells are possibly excluded from count. How do I do this? |
#2
|
|||
|
|||
Conditional functions on portions of cell data
try the Left and Mid functions as :
from your example 621-03 to extract 6, use =LEFT(B2) in C2 to extract 2, use =MID(D18,2,1) in D2 change the above formula's argument from 2 to 3 to extract 1 in E2 -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "sabow71" wrote: I manage a strength report for my employer that accounts for each employee using a unique number assignment system. This system is designed to allow me to identify which of nine teams an employee belongs to, identifying their specific assignment within that team and also their employment status (i.e., contractor, intern, volunteer). For example, I am 621-03. The "6" indicates which team I am on. The "2" identifies my assignment (Quality Assurance). And the "1" indicates employment status. My strength report needs to provide an accountability breakdown for teams and broken down further by employment status. Is there a function that can extract a specific digit/character from a cell entry? Currently, I am specifying cell ranges for each group. As the organization expands, this will present an accuracy problem as valid cells are possibly excluded from count. How do I do this? |
#3
|
|||
|
|||
Conditional functions on portions of cell data
This works if I am only checking a single cell... I need to run a tally of
entries that fit multiple criteria (e.g., all employees on the same team, same employment status, and that there is an employee filling the slot number). My roster is a tally sheet of all available positions within our organization. In other words, there are more numbers than employees. So when I run my tally, I need to count only those positions that are filled, then sort my count by team and employee status. On my spreadsheet, Column A has the number assignment. Column D contains the name of the person filling that position. Column E contains their status for the day (present, sick, vacation...) The function I am working with is he =COUNTIFS(A:A, IF(MID(A:A,1,1)=0,TRUE,FALSE), D, =TRUE, E:E, "Annual") So the first range and criteria is number assignment and validation for correct team using an IF/MID combination. The second range/criteria validates that there is a person filling that position. The last range/criteria checks for a status of "Annual". It's not working. What am I doing wrong? =MID("621-03",2,1) would give you your assignment and =MID("621-03",3,1) would give you your employment status. Is that what you are looking for? |
#4
|
|||
|
|||
Conditional functions on portions of cell data
sabow71 wrote:
I manage a strength report for my employer that accounts for each employee using a unique number assignment system. This system is designed to allow me to identify which of nine teams an employee belongs to, identifying their specific assignment within that team and also their employment status (i.e., contractor, intern, volunteer). For example, I am 621-03. The "6" indicates which team I am on. The "2" identifies my assignment (Quality Assurance). And the "1" indicates employment status. My strength report needs to provide an accountability breakdown for teams and broken down further by employment status. Is there a function that can extract a specific digit/character from a cell entry? Currently, I am specifying cell ranges for each group. As the organization expands, this will present an accuracy problem as valid cells are possibly excluded from count. How do I do this? =MID("621-03",2,1) would give you your assignment and =MID("621-03",3,1) would give you your employment status. Is that what you are looking for? |
#5
|
|||
|
|||
Conditional functions on portions of cell data
I figured out a portion of my problem... Here is what I have so far:
=COUNTIFS(A:A,"=0?1-??",E:E, "=Annual") The first range/criteria validates the correct team and employee status throughout the entire column rather than a specified finite range. The second range/criteria is for the employee's daily status. In this case, counting only those who are on annual leave. Each row of data represents a position in the organization. If a person's name is in the name field then the position is filled. Otherwise, it is vacant. I don't care about counting vacancies. The remaining problem: How do I count only those rows that are filled by a person in addition to the other criteria already listed? "sabow71" wrote: I manage a strength report for my employer that accounts for each employee using a unique number assignment system. This system is designed to allow me to identify which of nine teams an employee belongs to, identifying their specific assignment within that team and also their employment status (i.e., contractor, intern, volunteer). For example, I am 621-03. The "6" indicates which team I am on. The "2" identifies my assignment (Quality Assurance). And the "1" indicates employment status. My strength report needs to provide an accountability breakdown for teams and broken down further by employment status. Is there a function that can extract a specific digit/character from a cell entry? Currently, I am specifying cell ranges for each group. As the organization expands, this will present an accuracy problem as valid cells are possibly excluded from count. How do I do this? |
Thread Tools | |
Display Modes | |
|
|