# Count number of equal values in two rows in Excel

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

# look up values for formula in another table by checking date is within start and end date

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

# Excel month to repeat 179 times

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), ,";")

# EXCEL – Indirect function not working if tab name has for example 11+12 but simple names like 1 or 2 works

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

# Is there a straightforward way to combine these two steps into one formula? (Part 2)

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)

# Get the latest date in two different tables and return the corresponding value

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

# Spill table column with specified number of blank rows between entries in excel

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

# How to use sum(x:y) when x and y are coming from other cells?

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))