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

0
votes
0answers
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
0answers
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
1answer
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
1answer
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
0answers
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
0answers
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
1answer
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
2answers
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
0answers
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
3answers
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
1answer
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
0answers
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
1answer
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
1answer
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
2answers
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
2answers
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
1answer
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
2answers
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
1answer
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
0answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
0answers
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
0answers
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
0answers
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
1answer
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
0answers
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
1answer
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
0answers
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
1answer
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
1answer
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
0answers
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
3answers
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
1answer
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
2answers
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
1answer
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
2answers
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
1answer
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
1answer
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
0answers
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
1answer
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
3answers
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
1answer
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
1answer
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
1answer
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
1answer
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
0answers
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 ...

153050per page