# LaVOZs

The World’s Largest Online Community for Developers

'; Newest 'excel-formula' Questions - LavOzs.Com

# Questions tagged [excel-formula]

This tag is for Microsoft Excel questions where the question or answers are specifically about the structure, syntax, or output of an Excel formula.

• ### Conditional formatting based on snother cell

I have a list of due dates in column j and a list of dates when the reports were submitted in column k If tge due date is less than today (as in its now overdue) i want to put some formatting on thst ...
• ### DAX constant vs. reference: constant works, reference doesn't

I have a table where I would like to calculate the sum of the hours in the first month of the data. I have a calculated field that dynamically finds the min. month in the table: MinMonth:=CALCULATE(...
• ### How to count the numbers where the absolute value is greater than a cutoff with COUNTIFS in Excel?

I have the following numbers 12,34,-15,-23,-5,45,-50 and would like to find the count of numbers where the abs(x)>=15, which in this case is 5 Is this possible with the use of COUNTIFS ?
• ### If nested function in Excel

I have written the IF nested function as below, but I am getting CC for all the rows. What is wrong here. =IF(C122="Credit Cards","CC",IF(C122="Investing","INV",IF(C122="Business","BUS",IF(C122="EDB","...
• ### nodejs package for parsing excel formula DATEDIF in months?

looking for nodejs package that can read and process formula excel datedif in months im currently using handsontable/RuleJS https://handsontable.github.io/ruleJS/ but there is no datedif in month i ...
• ### VBA calculate commission based on some criteria

I am trying to write on a excel macro to calculate the commission based on 3 criteria 1) Commission Type (A,B,C) 2) Exchange type (US, HKEX) 3) Buy/Sell (Buy, Sell) To make it user friendly for ...
• ### How to list all possible combinations of taking 5 out of N objects in Excel?

The title may not perfectly describe what my intention here is. Let's suppose I have 14 objects in total and I want to pick 5 of them. Using a calculator, we can see that there are 2002 possibilities. ...
• ### Find Confidence via applying excel formula

I'm trying to find out the affinity among two products based on 16 transactions happened. First I calculated the support, which means no. of times the product bought in all 16 transactions. Next one ...
• ### How can I use a Column variable in my vba sum formula?

In VBA I created a variable for a column. How can I use this variable in my sum formula? Right now, the formula evaluates the column to a number which doesn't work. Here are the variables I am ...
• ### Read cell from a file picked based on cell values

I have a main file that, based on todays date, reads the value of a cell in the sheet named after the day, in a file named after the month. I've tried using INDIRECT() and "&" inside the path to ...
• ### Sumifs to sum the column using excel formula

How to sum 4th column satisfying the difference between first and second column equal to 2 and also satisfying a criteria in the third column using excel formulas
• ### Compare 2 Excel tables with 4 columns each using formulas

This is my first post and i hope i did everything right and the screenshot is shown. Unfortunately i don't know how to attach files yet. Now my problem: I have to compare 2 Excel-Tables with 1200 ...
• ### Gathering specific data from multiple excel sheets [closed]

I am trying to automate taking data that is specified with date and another category from several sheets and then doing math operations on them. For example, I need to take the quantity for May 18th ...
• ### How can I get an excel formula to skip over even rows?

I am needing to create a formula that will look at every 2 rows, compare the values, and then provide a reduced value. I will be able to better explain what I am trying to do through example. By ...
• ### Excel formula to derive a percentage calculation

I am looking to find percentages, Please find the table at below, enter image description here Based on above table, I have a benchmark of 20% instead of 100%. If the percentage is less than or ...
• ### Automated calculations and results arrangement

I have this project I'm working on Excel. this table is the frequency for each musical note. based on the main frequency number 432, or any other frequency that I want to use. I need to check a set ...
• ### Adding/removing rows from a table depending on true or false cell

I am trying to add/remove rows to a table in excel automatically dependent on the value of a cell in another table on a different sheet. For example; On a seperate sheet I have one table with the ...
• ### I want to do an Excel indirect lookup to another worksheet

I have an excel sheet where cells have a drop down that refers to a list via an indirect formula. The dropdown cell is meant to be a count value of an instrument e.g. Item Apples Bananas Eggs The ...
• ### Using Parentheses within R1C1 Formula

I am having issues with the R1C1 function. I am trying to search a column (A) in a worksheet and, if a value is found, paste a formula into the cell 8 rows to the right. The formula is using the R1C1 ...
• ### Excel VBA insert formula using variables

I am trying to insert a formula into a cell with a help of VBA and all of the time I receive error 1004: "Application-defined or object-defined error". Where does the problem lie? Worksheets("Sheet1")...
• ### Set the bacgroudn colour of cells via foruma in Excek

In Excel: Cell B1: Hoe goed ken je God? Cell C1: HOE GOED KEN JE GOD? Cell D1: =AND(B1=C1) The macro works and shows TRUE or FALSE What I would now like this formula to do is set the background ...
• ### How to create a reference to a range of a variable number of columns within a formula?

I am creating a schedule for a long long list of clients whos frequency varies. The frequencies of visits vary from every three months to once a year. I am creating a formula that will populate a ...
• ### Incrementing Variable M\$2:M\$8 by 7 each row [EXCEL]

I want to increment the following cell M\$2:M\$8 by 7 each row (See code below). Can someone help me with programming the right function? I feel like their must be a smart way to do this. Thanks in ...
• ### Excel MAC 2019 Help Compare Characters In Cells

I have an Excel sheet with two columns (A & B) each cell containing text, I would like to be able create a third column (C) with the characters that are different between the two. There are 50,...
• ### Can I plot/filter just the peak value per day in excel

I have a dataset which is amount (parts per million) against time (every minute) I need to find the peak for each day, so that I can plot that against other data, I can manually do this using =MAX( ...
• ### Data Cell validation

"AND((ISNUMBER(SUMPRODUCT(SEARCH(MID(BG1,ROW(INDIRECT(\"1:\"&LEN(BG1))),1),\"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))) ,(LEN(BG1)<=34))"; Above code is the data cell ...
• ### If duplicates in column B, check if duplicates in employee names in column D. If yes, only count once

This is my data sample: What I'm trying to do with a formula is to count the number of employees in column D of my table in relation with the data in my column B. If there are duplicates in B, the ...
• ### Autodelete/self destruct excel file on its own after defined time [duplicate]

Is there a way to auto delete/self destruct excel file on its own using vba code after defined time i have come across codes which self destruct after the defined date i want excel to be deleted by ...

How can I take an Excel macro (VBA) and have it available in all of my excel workbooks? Is there any way to distribute it as a package I can just load/install? Can the same be done for a button ...
• ### Partial match when look up value is lengthier than corresponding value in a table or a range

I have table as below. In first column name of the company and in next column corresponding country. The look up value of company name is not exact match in this case The company name from table is a ...
• ### Data Validation- Drop Down lists along with checking Custom value

I have a data validation as: =OFFSET('Details'!\$L\$1,1,0,MAX('Details'!\$K:\$K),1) I want to add another validation on the same cells- =B1<>D1 This is to make sure that two cells in the row don'...
• ### Values in column and those blank in a second one to show as a third value in new one [closed]

I have multiple columns, but I would like to create a new column from data in words that mention when one of the column is empty and one is with dates. I have done if with is date and is empty but it ...
• ### How can I access the values in a named range using a dynamically generated reference (month name) and an adjacent column's values?

I have an Excel workbook that contains 12 named ranges for each month of the year (JanuaryTallies, FebruaryTallies), each containing two columns, Amount and Category. What I am trying to do is get ...
• ### Excel, COUNTIF & LEFT (Only look at first 6 characters)

This formula searches across the entire column B for the value in cell A2. If no match is found, the formula returns "No match in B", an empty string otherwise: =IF(COUNTIF(\$B:\$B, \$A2)=0, "No match ...
• ### Need a formula to solve for X [closed]

I need a formula that would include a variable. h2/3.5%=80%/x Background: 70% is the lowest score 150% is the highest. If you receive a 150% you get 3.5% if you get 70% you get 0% I need this formula ...
• ### Excel: find values in column A but not in column B, return values in column C

Find all values which are in column A but not in column B. This formula searches across the entire column B for the value in cell A2. If no match is found, the formula returns "No match in B", an ...
• ### Excel formula Compare Data to previous 20 entries for change

I have an excel table for raw material offloads. All get tested, but some don't get offloaded right away. I'm trying to create a formula that looks at the future 20 entries for the same railcar and ...
• ### writing a formula for cell formatting in Excel for large dataset: turn outliers red

I have a large dataset (2209 rows x 60 columns) (each column is a compound i.e., ozone, ethane, etc.) of compound concentrations and I have a specific method for calculating outliers. Basically, I ...
• ### Vlookup to fetch adjacent value based on earliest date

I need help to write a code to return values for DF using the ID in the lookup table to fetch value that correspond to the latest date where there are duplications
• ### Kindly help me to convert this specific Excel formula to Javascript

I need to change this excel formula in Javascript logic. Here is the formula- var AA = IF(B2="","",IF(AB2="Delivered",AB2,IF(IFERROR(FILTER(S:S,S:S="Delivered Delivered",B:B=B2))<>"","Delivered"...
• ### How I can create a chart for these data in MS Excel?

I have data like this format and I want to know how I can create a chart for "Total time" I have 2 main columns and addition of KernalTime + UserTime The format for data is hh:mm:ss:millisecond ...
• ### How to remove a value (from the adjacent cell) in the drop-down list?

I have two columns Product 1 and Product 2, both of which lets user select from a dropdown list. But I don't want to show the same value in Product 2 if the user selected it for Product 1 in the same ...
• ### Absences report in Excel

I've got a problem with finding a solution to my report. Purpose is to know how many times and how long employees spend time on breaks. This report is really imperfect to draw a data. Purpose of my ...
• ### Identify duplicate cosecutive rows in a list, if the value in first column changes then, restart the identifying duplicate in Excel

I am trying to identify if the number gets repeated in multiple instances as shown in column A. However when used =IF(COUNTIF(\$B\$2:B2,B2)=1,"x","xx"), this is giving one time identified result only, ...
• ### Formula or VBA that finds duplicate case entries in a column, then identifies the minimum in other column by case

I'm not very good in VBA, so used helper columns for the beginning of my explanation. If you can do all the operations in VBA, feel free to show me how (if not, skip to the next paragraph). So in ...
• ### countifs same criteria in diffrent criteria ranges

Need to countifs values between two dates and need to lookup same criteria in different criteria ranges (columns I,J,K) values.I used below formula in N6.Answer should 3.between given dates how many ...
• ### How to mark column value found False as zero | Excel | Conditional formatting |

I have a scenario where i want to mark the value as zero if found to be FALSE Below is my table of data in above image if you see the column 4 => cell C4 as one 'FALSE' value and correspond to that ...
• ### Self destruct or delete excel using time like in 1 hours or 2 hours [closed]

Can someone help me with the vba code to delete excel file within the mentioned time in vba code like an hour or so, any help will be greatly appreciated
• ### MAX function in Excel: is it possible to provide the range by means of variables?

I'm trying to write an excel formula to perform the MAX of a range. The default formula accepts references as the boundaries of the range of arguments: =MAX(A1:A10) I want to provide the boundaries ...
• ### Evenly Distribute Players between 2 teams in Excel

I have a group of players, 915 in total, each with different engagement scores that I am trying to break out into two evenly distributed groups based on their engagement scores. I tried using Solver ...