All Questions
172
questions
1
vote
1
answer
46
views
Having a hard time using Microsoft Excel IF function
I have been working on a simple tracking database, and I'm having a hard time using the IF function (or if this is even the right function to use for my problem). I want to create an ID number for the ...
- 13
-2
votes
1
answer
93
views
How to count occurrences of rows that have certain value only if preceding by certain value, and count how many rows of that
I have some data spread over one column and 120 rows
there is a repetition of the value 1 and 0 corresponding to certain operation mode
so it looks as per the image attached
My question is how to ...
0
votes
0
answers
42
views
Excel SUM function and + operator different for blank text
The + operator in Excel gives an error when adding two cells whose values are empty text, while the SUM function treats these cells as zero (as I expected). I am trying to understand if this is the ...
- 101
0
votes
1
answer
47
views
2 problems, complicated copying of data based on a "Number" and dates
The first problem I have is that I have a timestamp in one cell which includes the date and time and I would like to know if there is any automated way to separate them into 2 different columns (Date ...
- 1
1
vote
1
answer
31
views
How to vlookup all columns and sum
I have two table.
The first table contains menu and price:
Menu
Price
Pizza
20
Pie
10
Beer
5
Burger
10
The second one contains a buyer and menu. I want to get the sum of price based on menu like ...
0
votes
1
answer
71
views
How to count instances of repeated values in a cell range while ignoring blank cells
I have a dataset of subjects (one per row), each with scores taken over several days. Each day has multiple scores, but the number of scores varies such that there are many blank cells.
See a small ...
- 1
0
votes
2
answers
65
views
How can I create a formula that counts the number of duplicates, returns the value in a cell of my choosing, and then deletes the duplicates?
I've got a list that, after I sort it, looks like this:
rough list
I need a formula that will return the number of times each entry is listed and delete all the duplicates. Typically, I simply count ...
0
votes
0
answers
26
views
Importing a range of formulas from csv or txt file starts with ' in excel and sheets [duplicate]
I am generating some image URLs with a python script and converting them to a formula. I have tried saving it to both a CSV and a TXT file as others suggested, but both lead to the same problem:
After ...
1
vote
1
answer
71
views
Coloring cells across multiple excel sheets in an xls file based on a condition
I am new to excel. I have an xls file with 10 sheets. In each sheet I have two tables of size 10 x 10, one starting at position A4 and the other starting at N4. All the entries are numbers.
(Note that ...
- 113
0
votes
2
answers
317
views
Get unique IDs with date 1 or date 2. For IDs with both date, obtain row with most recent of the 2 dates
I have been trying to retrieve the row in the following sheet (sheet1) where the dates are matching dates in sheet2 without duplicating the IDs in Sheet1 (Column B)
Sheet1
Sheet2
In other words, I ...
0
votes
1
answer
117
views
Is there a formula for retrieving data from multiple cells on a different spreadsheet and adding to one single cell on another spreadsheet?
I am working on a project in which I need to retrieve the Parent Items for a list of scrap material. The problem is that most of this material is used in multiple Items. When I run a separate report ...
- 13
0
votes
1
answer
235
views
Can I have excel automatically swap text in a column from one word to another without using another column?
I'm fairly new to Excel so excuse my noobish question. I am working on a roster spreadsheet and have a bunch of shop codes, essentially abbreviations of the shop name in a column, and I have been ...
- 1
1
vote
1
answer
948
views
Turn cell references in Excel hyperlinks into the text in the referenced cells
I have a column of bare URLs in Excel. Say it's Column A. I want to replace each URL with a friendly text link, like "here." Suppose A1 contains the link https://www.website.com/content/...
- 23
1
vote
1
answer
121
views
What's the mathematical, =SUM() equivalent of the =NPV() formula, in Excel or Google Sheets?
Is there an Excel/Google Sheet SUM formula equivalent to the NPV formula, ie. one that produces an exact same answer given the same input figures?
The question might be similar to these ones but the ...
- 177
1
vote
1
answer
212
views
Drag to fill duplicates values
I want to create a sequence of dates that populate a column.
A
2023-12-31
2023-12-30
2023-12-29
...
Format Cell is set to Date.
Calculation Options is set to Automatic
Enable fill handle and ...
- 393
0
votes
1
answer
2k
views
Find next cell within column that is a number, starting from reference cell in another sheet
I think this sounds simple but I'm having a bit of trouble figuring it out.
I have 2 sheets, the Sample Sheet attached and an Input Sheet.
In the Input Sheet, I input one of the green shaded values (...
1
vote
1
answer
82
views
Custom Information Message for user based on another cell value
I am busy with an optimization spreadsheet which needs to display an information message, maybe similar to the Input Message for data validation (but I am open to suggestions). This message will be ...
- 21
0
votes
1
answer
83
views
Print Excel Spreadsheet using Template?
So Let's say I have a spreadsheet
No
Name
Class
Gender
1
Jack
Science
Male
2
Rose
Science
Female
3
Kevin
Social
Male
4
Olive
Social
Female
and I have template
No
Name
Gender
1
2
3
4
How to ...
- 1
0
votes
1
answer
37
views
Microsoft excel formula to connect codes of smaller areas with those of the bigger ones and sum up their values
I am working with geographic information and I have bigger areas called Regso that concist of several smaller areas called Deso. The data I have is for the smaller Deso areas but I would like to ...
- 103
8
votes
4
answers
9k
views
Formula to see if a series of numbers is increasing in value?
I have a spread sheet with thousands of sets of numbers. That is too say, there are ten columns and each column has a number - and there are thousands of rows of these numbers.
What I want to do is ...
- 225
0
votes
1
answer
33
views
Excel - The most spending occurred around this date
I feel like as a human I can understand this, but I can't figure out how to get it into an Excel formula.
Given the dates and amounts spent on those dates below, how can I find a date on which the ...
- 103
-1
votes
1
answer
583
views
Excel: How to count how many times a consecutive text value appears
I'm making a work schedule and I have to pay attention to a specific rule that, no worker should be scheduled to work more than 6 days in a row.
I got this spreadsheet where I make those schedules but ...
-1
votes
3
answers
2k
views
Excel: How To Transpose Every Random amount of N Rows From One Column To Multiple Columns?
I have a set of multiple choices questions with ABCD as answers, all answers are stacked on a column, but not every question has the same amount of answers.
Instead of transposing every nth value, my ...
0
votes
1
answer
43
views
Adding column description of one spreadsheet sheet in another spreadsheet of same excel
I have an excel sheet having 4 different spreadsheets. I need description of column header of three spreadsheets in 4th spread sheet.
Example:
Suppose I have sheet 1 with 6 headers for 6 columns, ...
- 11
1
vote
2
answers
31k
views
How to select and copy entire row based on text-value in a cell in excel
I would like to do the following:
Find all cells containing a certain keyword in a column in an excel spreadsheet.
Select and copy the entire row which includes the cells which have the keyword ...
- 11
0
votes
0
answers
1k
views
Evaluate function not working
In the example, entering the combined values in K2 as a formula produces the result in J2. J2 is value copied and pasted in J4. To confirm that it is a formula that will not produce an error, the ...
- 13
0
votes
2
answers
1k
views
Excel Sort Command not working AT ALL as expected
I have a table in excel (formatted as a Table), that does allow me to sort, but its results are incorrect in at least two ways:
the numeric value column I am sorting on A-Z or Highest-to-lowest comes ...
- 3
0
votes
1
answer
72
views
Microsoft Excel - How can I convert the following text to 'date' only format?
I have text in a cell with the following syntax: Jan 6, 2022 10:18 AM
The standard format adjustment from 'general' to 'date' does not work. I need to convert the text into a date-only format (ideally ...
- 1
2
votes
1
answer
94
views
Change shelves into separate columns
Every day I have an inventory to do. 100 or so items on 15 or so shelves. I also have an xml report that shows what should be available in the room - but this xml does not show what shelves the items ...
- 21
0
votes
1
answer
1k
views
How to combine the values from multiple rows in a column (separated by commas) where there are duplicates in another column
I have a large dataset where I want to combine the values of multiple rows in one column where there are duplicates in another column. See my Example below:
CURRENT DATA
Product Type
Email
Product 1
...
- 135
1
vote
1
answer
25k
views
How to sum a column based on existance of value in another column
I have the following sheet:
I want a sum of individual spending by Frank, John and Nancy in the yellow cells. Each person's spending is denoted by an x. I tried the SUMIF formula, but I don't know ...
- 18.8k
0
votes
1
answer
213
views
In Excel I am wondering how if a value is met in a column then to sum up the cell to the left of that value across multiple rows if that makes sense
For example where the cell says David I want to sum all the cells on the left of David in £ up to a total like at the bottom not sure how I'd go about this.
£4500 IWI 14/12 DAVID
...
- 1
0
votes
1
answer
57
views
If Statement Gives Blank Results
I can't get the following IF statement to produce the desires results. Result comes up blank.
=IF(B3="168375","500","")
Cell A3 = 168375 so am i missing something that is ...
- 1
0
votes
1
answer
46
views
Excel - I want to get all the cells adjacent to a keyword on a page onto another sheet
I'm using Excel to track what I do every day, which includes a lot of qualitative data, and at the end of every day I write down how many hours are spent in each of several categories. I definitely ...
0
votes
1
answer
24
views
Need help in Excel Adding 2 columns
i just want to add column 2 to column 1 if and only if column 2 has just 1 text character in its cell .enter image description here
- 1
0
votes
1
answer
511
views
How to automatically sum columns datas of a grouped row subset with common attributes (excel csv spreadsheet)
I have a big csv file (one million rows) I imported in excel.
I have to do a simple operation, easy to be done manually, but I should do it for thousands groupings so I am looking for an automated ...
- 3
0
votes
1
answer
59
views
How to FIlter/Match spreadsheet data based on two row headers?
I need to find a value based on a specific column and header, that also fall within another merged header (spans 3 cells) like the example below:
|January 2020 |February 2020 |...
- 259
0
votes
1
answer
154
views
How can you format each row from a pivot table to display transposed with the headers repeating themselves?
From a pivot table, I am attempting to take each row of data and format it transposed with the headers from the pivot table present in each instance. Here is a link to a Google Sheet that has a sample ...
0
votes
0
answers
250
views
How to bring sum of previous column in to next column automatically, in Excel or Google Sheets?
I'm new to this. I'm trying to make a personal budget sheet, where I can bring the previous months' sum into the next month's column as "savings from last month". Basically I want to show ...
- 205
0
votes
2
answers
1k
views
Excel SUMIF/SUMPRODUCT with Dates Identifier?
So I am currently working with a spreadsheet of data that has info on a set of loans and how they payback over time. I'm trying to create a table that sums their payback based on what month they were ...
- 13
0
votes
3
answers
52
views
Microsoft Excel - Rolling Average with Skips
I have a table of costs among several different locations for the past x years. For each location (A, B, C), I would like to find the average cost of the past three visits. Unfortunately, locations ...
- 23
0
votes
1
answer
166
views
How to Calculate the Average after a certain date
Im trying to get the average of days taken to send to college but i only want it to take in to consideration dates received after 17/05/2021.
can anyone help
0
votes
2
answers
1k
views
Excel Formula to increment day by one except if it's a weekend
Basically what i want is for weekdays to increment by one every day but for weekends i want each day to display twice, so each weekday will have one row and each weekend will have two the current ...
2
votes
1
answer
1k
views
CONCATENATE cells in a column IF condition in other colum is met
Using Google sheets (or Excel if Google sheets can't do it). I'm trying to concatenate any cell in B column, if its corresponding A cell is "include". The columns are very long and therefore ...
- 25
0
votes
1
answer
236
views
Excel - sort one spreadsheet by exact order of second spreadsheet based on one column with same data
I trying to find a way how to sort one spreadsheet into the same order like second spreadsheet - based on their one same column for example.
Here's an example what i need, i will make more sense like ...
0
votes
1
answer
1k
views
i want to return value( eg;- sum) only if the source is not blank,other wise return blank !!not zero!! in excel, wps
see this screenshot for reference
see this screenshot..
I used SUM formula in the botton cells to get sum of all values from each row , the bottom cells display TOTAL
and i will assign the TOTAL ...
1
vote
1
answer
451
views
How to copy rows of specific columns based on a criteria (and then continuously update)?
I have 2 sheets, let's call them sheet 1 and sheet 2.
In sheet 1, I have some data where essentially at its core its a Scoring tally, something like this:
Name
Age
Gender
Score
Jack
12
Male
155
...
1
vote
1
answer
286
views
How to calculate max sum held over a month?
I need help with a formula that needs to calculate the max sum held at one time for each month.
In this example it would be 54 for March (12+13+14+15, held on the 29/03/2021). Month is determined by &...
- 23
0
votes
1
answer
2k
views
Based on cell formula, display another cell value
I want Excel to look into the formula of a cell and then display a different cell based on that. confused yet?
Column A on sheet 1 has a size and column B on sheet 1 has a tolerance.
Could be same ...
- 11
0
votes
1
answer
321
views
Count number of rows with a specific MONTH in the date of a specific cell and a boolean condition in another cell
Let's say I have a number of dates and whether they occupy full or half a day...
DATE | WHAT | HALF?
...
2021-4-1 | Something Something | false
2021-4-2 | Something Something | ...
- 557