if

Hi I am trying to figure out a formula that would give me the average tenure per year of the Active employees for that year. I have a list of the employees with start and end dates and their Departments, and their tenure for each year they worked. Here are the two lists: What I need is to get...

Hi all, I'm struggling to make a condition to a value to show a result based on the value of rows below in the same column. The idea is to give either R, A or G result, based on what is below. So if i have X amount of rows below, and each one has different values in combinations of R, A...

I have a spreadsheet which holds contract data i need to check if a contract has ended part way through the contract term and amend the end date to renewal date but also return nothing if the contract has no end date This is the formula i was using AA=IF(AND(Q1<>"",Y1=0 O1=12)...

I'm trying to make something like this (I'm a noob on excel don't know if this is possible): If beetween J33:J54 the number 4 repeats then show the number 3 on J55 but if it doesn't repeat show the lowest number on that column, Also if there's a number lower then 4 in j33:j54 even if 4 repeats...

Hi, I am struggling to make Textjoin work with an additional criteria. =TEXTJOIN(", ",TRUE,UNIQUE(FILTER(RawData[ID],RawData[Issue Status]="",""))) I am using the above code, which works, but then i want to use the same one, but adding an additional condition, which should be the below...

Hello, I am looking for a bit of code to add into the below but I am struggling. What it needs to do is say if the cell A1 is = to cell B4 then it needs to speak as per the below code. However this needs to loop constantly so everyday at the the shown in B4 it speaks aloud. I have it...

Hey guys I seem to be stumped! I have a spreadsheet and I am trying to get a value in an individual cell based on two rows of data on two different tabs and then multiply by another row of data :oops: Sheet 1 has column A, 63 lines with numbers column B, 63 lines with numbers Sheet 2 has...

I got a task in which i have 2 Files One is a Master File containing the names of the clients and the EMI amount with Payment 1 detail And there is Another file containing Daily Collection with payment 2 Detail In Master File there are two column Pay 1 Pay 2(Values Coming From Another File...

I need a macro to, IF i5=-99 THEN delete A5:C5, then it needs to move to cell i6 and perform the same check IF i6=-99 THEN delete A6:C6. Needs to keep repeating this line by line check until reaching i1057. I don't expect necessarily to see the cursor moving through the sheet, just to end up...

I'm trying to design a spread sheet I will also uses as a display. I am wanting to add the row, but if the cell under if says "Half" I want it to divide the number above by 2 then add it. So in my picture B4 is 1 but since B5 says Half I want it to add as .5 will C4 still adds as 2 in U4. So U4...

Hello, I am need of assistance creating a chart. Currently we track performance on paper and I want to digitize but I cant get my conditional formatting correct. I want a line to be shaded green if below goal and where we finished but be shaded red if above goal and shaded where the performance...

Hello all, I recently updated my MS Office version, and am encountering a new problem using IF(AND with named ranges. As can be seen in the image below, the named ranges work fine for a simple IF function, and the IF(AND formula works fine if I use the cells. But when I use the named ranges...

Hi! I have a spreadsheet that we use to track contracts, Purchase orders, and Invoices/payments. I am trying to create a field that will show me the next soonest invoice date that doesn't yet have a dollar amount populated (because that date is the next invoice that we haven't yet paid... the...

I am in the process of building a student database on excel which includes 30+ classes and 4 variables per class namely, 1. enrolled date, 2. city, 3. country, and 4. payment method. As this database will be used by a company that has no employee who can use excel well, I will have to avoid...

I am trying to create a formula that answers the following question: What is the Tenure at the end of 2018 (12/31/2018) for a person that has a HIRED DATE before 12/31/2018 and has a TERMINATION DATE that is either within or after 2018. I really appreciate the help, I just cant get the...

Dear All, Appreciate your assistance to my inquiry below. I wanted to extract the highlighted decimal number (put in Column B) from the text string cell (Column A) as below. Anyone can assist on the formula? Source - text string cell in Column A 1/11/21 960 C P GL 71,959.26 71,959.26 CR...

I have just tied to use the hyperlink function in an if formula in a cell. Essentially the formula if condition is if there is no value in another cell then hyperlink to that cell (I use the # prefix ) and if there is a value then use that value. In simple terms if is: =If($A$1=“”...

Hello, community I would like to ask for your help, i have a column that has random numbers from 1 to 12 and the format is Time (7:05). In an another column i try to create an if statement =IF(E2<3,RANDBETWEEN(4,8),IF(AND(E2>3,E2<8),RANDBETWEEN(30,40),RANDBETWEEN(8,14))), thus, if it is below...

Hey all, I am needing to shorten this formula so I can put it into a data validation drop down list. =IF($c$11="1", 'LI'!$A$9:$A$99, IF($c$11="2", 'LI'!$b$9:$B$99, IF($c$11="4", 'LI'!$d$9:$d$99, IF($c$11="5", 'LI'!$e$9:$e$99, IF($c$11="6", 'LI'!$f$9:$f$99, IF($c$11="7", 'LI'!$g$9:$g$99...

Hi! I thought I had this - turns out I am rubbish. I basically want to return a value in N3, if F3 (text data input) contains a value in N2 (header), otherwise return 0 I currently have : =COUNTIF($F3,"*"&N$2&"*") which works great to return a numerical value, but i want to later get a string...