I have a spreadsheet that looks like this: A, B, C, D, 10 A, B, X, Y, 10 A, B, C, D, 20 The first line is a template. Latter lines are actual data. I want to count number of correct values for each line. Correct value is a value that is equal to corresponding… Read More Count number of equal values in two rows in Excel

I have the following staff salary table called EmployeeSalaryTbl: and a table called EmployersNIContributionTbl with the following tax information: and I’m looking to create a formula to calculate the associated Employers NI Monthly based on their Salary Start Date that will replicate this pseudo code: (Salary Monthly-Secondary Threshold) * Employers Contribution % Where Salary Monthly… Read More look up values for formula in another table by checking date is within start and end date

I want to month "Feb" 179 times vertically in excel. Is there any way I can do it by formula, please suggest. >Solution : You could do it with an array formula: =TEXTSPLIT(REPT("Feb;", 179), ,";")

I am running an Indirect function to look through different tabs and return values from specific cells, for example: =IFERROR(INDEX(INDIRECT(J13&$P$6),MATCH(J13,INDIRECT(J13&$P$5),0)),"n/a") J13 is the tab name // P6(!Q80 in this case) is the cell where the data is // P5(!J5 in this case) is the cell in which J13 will match However, if J13 is 1… Read More EXCEL – Indirect function not working if tab name has for example 11+12 but simple names like 1 or 2 works

I am having a significant mind blank when trying to suss this one out. I want to return the date from a few rows above if a cell contains a certain value. Image 1: Image 1 Image 2: How I want it to look Image 2 I tried using a similar sum to the one… Read More Excel: Return the value of a few rows above in the same column if the value meets criteria

I have some data in cells A1:A18. In cells C1:C14, I calculate the standard devation of subarrays in A1:A18 of length 5. This is the intermediate step. Finally, in cell C20, I take the median of the values of the previous step. Is there a way to skip the intermediate step and have cell C20… Read More Is there a straightforward way to combine these two steps into one formula? (Part 2)

I’m trying to get the latest date in two different tables and return the corresponding value in excel. This is what I would want it to be like: I’m new at excel so I’ve just been looking all over the internet for a solution but cant find anything. >Solution : Posted an an Answer, as… Read More Get the latest date in two different tables and return the corresponding value

Is there a cell based function that can spill the entries from a table column whilst inserting a defined number of blank rows between each entry? For example, In the following image I have the function =FILTER(DisciplineTbl, {1,0,0,0}) in cell P32: In column S I have inserted what I am hoping to achieve, ideally through… Read More Spill table column with specified number of blank rows between entries in excel

Headline basically states it: Imagine A1=15, and A2=30 What I want is sum(B15:B30) where 15 and 30 are coming from the other two cells (or another formular for that matter). How to accomplish this? >Solution : Use INDEX: =SUM(INDEX(B:B,A1):INDEX(B:B,A2))

0 A B C 1 Year From 1974 2 2004 To 2005 3 2016 Result 3 4 1996 5 6 no info 7 1974 8 no info 9 10 11 12 In Cell C3 I want to count the rows in Column A where the year is a) >= the value in Cell C1 AND… Read More FILTER function on column containing numbers, text and empty cells