Questions about using in-cell functions/formulas in spreadsheet software.

**0**

votes

**0**answers

11 views

### Excel: Compare 2 tables and extract rows from both with a duplicate value

I have two sheets listing out customer name, date, invoice number and comments in columns. The data in both of these sheets are different and in a different order. How would I cross reference column A(...

**0**

votes

**0**answers

14 views

### shifting a load of Electric Vehicle to the next row if a condition is not fulfulled in excel

I am making a scenario in excel where Electric vehicles are being charged in a given time range. i have a neighborhood loads and the load of EV at specific times. the cable capacity is 50 kW (means ...

**0**

votes

**1**answer

19 views

### “=SUMIF” Formula not working with 00/:00 formatting

Good Morning,I have a spreadsheet I am working on and need to have the total time spent at a location automatically be calculated depending on the start and end time. It works if I keep the ...

**0**

votes

**1**answer

9 views

### Split data into new column

As per attachment i have column A having name with underscore "_' and bracket " [ " . I need a formula to get answer of column B means I need formula to get id before underscore and inside bracket ....

**0**

votes

**0**answers

15 views

### Matching Random time entries with a time seires

i was previously facing a problem in generating random time values when the time crosses midnight e.g. 11pm to 5 am but it was solved with the help of one user here. trick was to add 1 to the formula ...

**0**

votes

**0**answers

26 views

### How do I count the result of a selection from a VLOOKUP and add the next sequential number to each one?

I have a spreadsheet for actions (Corrective, NCR, IMP, etc.), and need to have it automatically count the types of actions raised to create individual action numbers. I have used VLOOKUP to be the ...

**0**

votes

**1**answer

22 views

### Excel SUM() Formula Not Working

I have a complex formula: two SUMIFS formula nested in a SUM formula. The concept is to sum amounts given a specific time period and specific account code.This formula seems to work great. I get an ...

**0**

votes

**2**answers

34 views

### Is there a way to use excel's VLOOKUP with multiple criteria?

I'm attempting to create an aggregate of data that looks at a list with a lot of duplication of items and compiles a total number that the duplication appears, and a unique value associated with the ...

**0**

votes

**0**answers

21 views

### Vlookup issue | Macro Heavy Worksheet

Hey I am having quite a weird issue with a file a built that is very macro dependent. It centers around a simple vlookupCurrently my macro creates a line item on one sheet (Sheet A), then copies ...

**0**

votes

**3**answers

32 views

### Better Excel shortcut for current time

Is there a way I can set an easier shortcut for the current time in Excel? I know about "ctrl+shift+;" but I need something that can be used with one hand. Ideally if I could set up and F key to add ...

**0**

votes

**1**answer

27 views

### Auto Calculate Hours for Shift

Good Morning,I am using Excel and have am trying to be able to add a punch in and out time, then have the total time between those automatically be calculated in the next spot.This works when I do ...

**0**

votes

**0**answers

36 views

### Generating random time values in Excel [duplicate]

i am having a problem with Random time generation within a given time limit in excel. problem is if i give a time range from morning to evening the formula works great but if i give the time range ...

**1**

vote

**1**answer

45 views

### Change a function used in a formula, using the function name stored as text in a separate cell

I have this formula=ROUNDUP(D12/(D6-D5),IFERROR(VLOOKUP(G9,reference!$C$2:$D$7,2,0),100))and I want to change the ROUNDUP portion based on text from a separate cell (say, F7, where the text can be ...

**0**

votes

**1**answer

56 views

### VBA code for subtraction of subsequent numbers in a list from the first value

I have a list of numbers. I want to use the first number in the list as a starting value and subtract the subsequent numbers from it, using VBA.I have made several unsuccessful attempts:Function ...

**0**

votes

**2**answers

47 views

### Excel freezes when running macro

Private Sub Worksheet_Change(ByVal Target As Range)Dim KeyCells As RangeDim i As IntegerSet KeyCells=Range("H4:H100")If Not Application.Intersect(KeyCells, Range(Target.Address)...

**0**

votes

**2**answers

39 views

### Count specific items separately in 1 cell

I need a formula like COUNTIF for this problem.Forexample:I write ''a, b, c, d, d'' in 1 cell, but want them to be counted separately like this:a -> 1b -> 1c -> 1d -> 2I tried with ...

**0**

votes

**1**answer

13 views

### Keeping hyperlinks functional after moving directory

I currently have a worksheet in which I have several hyperlinks to folders. I am attempting to have the hyperlinks automatically change when the root path changes.I have figured out how to have a ...

**0**

votes

**2**answers

18 views

### Lookup Function Giving Inconsistent Results in Excel & Google Spreadsheet

I am trying to create a column of looked-up values using the LOOKUP function. The goal is for the formula to look up the "rank" value of each "name" and print it in the "lookup" cell on the same row. ...

**1**

vote

**1**answer

41 views

### Formula to SUM values in mulitple columns, based on criteria from another column

I have a sheet with three columns. The first is called "Account Number" (AccNr). The two other contains values related to each account number. It looks like this:AccNr. | Value 1 | Value 21000 ...

**0**

votes

**0**answers

15 views

### Calculate multiple worksheets using index match sum

I have a formula that works=INDEX('[Test.xlsx]Report 1'!F:F,MATCH(B8,'[Test.xlsx]Report 1'!A:A,0))This will being me back the value 37; in report 1.-report 1 is a worksheet, and there is 6 in ...

**0**

votes

**1**answer

17 views

### Index match finding the first value only

I have a list of employees with duplicates and each employee has a number which represents the number of sales. I'm trying to use the following formula to extract the highest sale for a given name, ...

**0**

votes

**1**answer

88 views

### Identify first login and last logout entries

I have a spreadsheet that needs to determine the first time a user logged in to the system and the last time the user logged out.The login and logout report comes from another system from which I ...

**0**

votes

**1**answer

30 views

### Auto-populate one table from another table if a criteria is met

I have two tables: Information and List.The Information table has two columns: Name and Gender.The List table also has two columns: Male and Female.I'm trying to auto-populate the List table ...

**0**

votes

**1**answer

18 views

### Excel - How to HIGHLIGHT entire row with specific NUMBER value match, (this specific value is output result from another conditional formula)

Excel - How to HIGHLIGHT entire row with specific NUMBER value match, (this specific value is output result from another conditional formula) With this attached excel screen shot "J" and "K" showing ...

**0**

votes

**1**answer

46 views

### Sum Function with Index/Match Function

I have to prepare summary sheet for the expenses, claimed by employees.What I need from this summary:Different expenses claimed by employees month-on-month. I've a sheet named as 'Dataentry' ...

**0**

votes

**0**answers

9 views

### Array Formula Auto-Calculation in Google Forms [migrated]

I have a google form which is returning data into Column A and Column B and I need to perform calculations on that in Column C,D & E. I have been using Array Formula so that C & D auto-...

**0**

votes

**0**answers

17 views

### Google Sheets: replace formula with its value after evaluation [migrated]

In Google Sheets, I'd like to replace a formula with its evaluated value immediately after evaluation. Specifically, given two empty cells A1 and B1, I'd like B1 to be filled with the timestamp of ...

**0**

votes

**0**answers

31 views

### How to generate rows by linking to a number in a cell in excel [on hold]

Can you generate a number of rows by linking the amount of rows generated to a number in a cell in excel? for example each column has calculations, if I would like to generate 60 rows of calculations ...

**1**

vote

**1**answer

47 views

### how to grab data if value=yes

So I have a worksheet that has 2 columnscolumn A(name) Column B(Yes/No) Column cName1) YES Name1Name2 NO Name3Name3 YES ...

**0**

votes

**0**answers

36 views

### Excel spreadsheets to pull data only if cells have quantity numbers [on hold]

I have a price sheet to compare vendor costs. Once i input the quantity the rest of the spreadsheet figures out the cost per for each of the vendors. However, once all the data is complete I want to ...

**1**

vote

**1**answer

20 views

### conditional formatting getting rid of cell color

I am making a floorpan of a building for an architecture company on excel with each cell representing a room. I have used conditional formatting to put polka dots inside a cell (which represents a ...

**-2**

votes

**0**answers

11 views

### Excel moves a row down after Form submission [migrated]

I have a Jotform that dumps into a Google Sheet. Every time the form submits an entry, the form moves/skips a row down. How can I fix this, so that my other sheets with calculations can ...

**0**

votes

**1**answer

58 views

### Return a yes, no value from another sheet using If Index Match formula

I have a master sheet which pulls data from another workbook.the workbook is called example.xlsx. This workbook has multiple sheets inside of it.In this Master sheet I have created the formula ...

**0**

votes

**1**answer

21 views

### Is there a way to highlight excel cells using the values of adjacent cells?

I want to highlight a cell[x,y] ifcell[x,y+1] is equal to cell[x,y] andcell[x-1,y] is not equal to cell[x-1,y+1]Is there any easy work sheet function to do this or do I have to write a python ...

**-3**

votes

**0**answers

71 views

### Formula to calculate many times each person used the cafeteria and also to check the time for each visit to the cafateria [closed]

I work in a company where I have to monitor everyone who uses the company's cafeteria and file a report every single day. The report consists of how many times each person used the cafeteria and how ...

**0**

votes

**3**answers

37 views

### How can I populate a year field from a string of text? [closed]

I have a list of sample IDs that all being with a number (ex: 04ABC190) where the first number corresponds with a year. How can I populate another column such that the first two numbers of my sample ...

**0**

votes

**1**answer

16 views

### ISERROR - Too many arguments - Excel

I am attempting to place the following function into an excel cell; however, I am told that their are too many arguments in the function.Here is the function:=IF(ISERROR(M12/SUM(E12:I12),0, M12/SUM(...

**2**

votes

**2**answers

89 views

### Excel formula for calculating days

I buy and sell different items. I am trying to write a formula that will work out how long an item stayed in stock. For example:A1 is Purchase Date, so I enter 1st June 2018A2 is Sale Date, A3 is ...

**1**

vote

**1**answer

30 views

### How can I count multiple variables in a column without counting the same cell twice?

I have a column with a lot of text. I want to count how many times 8 different words show up in that column. However, some of those words are within the same cells. So I want to ensure the returned ...

**1**

vote

**2**answers

31 views

### COUNTIF Formula not returning expected values

I'm currently attempting to create a formula for my spreadsheet that will calculate the total completion percentage of the form by counting all "Pass" and/or "Fail" results found within columns B ...

**0**

votes

**1**answer

53 views

### How add text to a cell in Excel 2013 based on the absence of specific words/sentence?

Lets say in column A I have cells that contain the following text approximately 25% of the time:"This is a super special cell."I'm trying to find a function that could read column A for the above ...

**0**

votes

**1**answer

24 views

### Editing an existing formula to add multiple IF formulas and other requirements

I've been using a formula for accounting to calculate sales within a bunch of states that looks like this:=IF(COUNTIF(G2150,"MA")+COUNTIF(G2150,"CT")+COUNTIF(G2150,"RI"),IF(EXACT(L2150,"Apple"),(...

**-1**

votes

**0**answers

35 views

### How do I use a SUMIFS(INDIRECT(…)) but have the INDIRECT be one long list of variables? [on hold]

I've seen several examples of the INDIRECT being used with the SUMIFS on this website, however I am trying to use only one INDIRECT for the entire SUMIFS. Is this possible? I've gotten to something ...

**0**

votes

**1**answer

38 views

### Compare two data sets by rank with Excel

I have searched everywhere including this site and either I do not understand how to search for what I am looking for or it just isn't something folks are doing.Basically, I have a data set from ...

**2**

votes

**3**answers

33 views

### I need an excel formula to tell how many numbers and alphabets are in a cell

Hey Guys I need help sorting series of plate numbers out on excel.so I have a sample BMA759JA, and I need a formula that tells me how many Alphabets are contained in the cell and how many numbers ...

**0**

votes

**1**answer

89 views

### Filling in the missing times of a broken time series set of data

I downloaded per minute data from Google Analytics. However, it only provides a line item for each minute that there is a value greater than zero.I would like to graph this data, but before I do I ...

**0**

votes

**1**answer

38 views

### Formula for checking time differences

I have the following setup: In cell A1 the time is=09:30 In cell B2 the time is=16:30 In cell C3 I would like to display a percentage based on the following criteria:If B2-A1 is less than ...

**0**

votes

**1**answer

49 views

### How to remove #NUM! from calculation and return zero value

I am creating a contract inventory file with inputs for all contracts. I have 2 columns, one for start date & another for end date. And a seperate cell which indicated today's date using=TODAY()....

**0**

votes

**1**answer

86 views

### Alternative to SUMPRODUCT part 2

For two durations that are in the same row (just different columns) and two whole numbers that are also in the same row, I'm trying to compute the equivalent formula as in my previous question which ...

**0**

votes

**0**answers

16 views

### Mailmerge with specific cells for a contract

In the course of my summer internship, I have to design a convenient way to generate contracts with the help of an Excel spreadsheet, representing a single person, containing data like name and ...