Questions tagged [worksheet-function]
Questions about using in-cell functions/formulas in spreadsheet software.
8,138
questions
0
votes
1
answer
71
views
Excel IF / AND Condition
I have two formulas in an Excel file and I want to merge them in one cell:
=IF(AND(D2="VSE",G2>=100%),F2*0.65%)
=IF(AND(D2="VSE",H2<=18000),H2,"18000")
to ...
0
votes
1
answer
3k
views
Excel formula. Find partial text in column and return cell value from row
This seems like simple concept at least to my limited excel knowledge, but I'm unable to find a formula that does the following.
I have two columns of text, column A & column B. I want to search ...
1
vote
2
answers
149
views
how can I add an AND function into an IF nest within Excel to check multiple cells
We try to track how our product is manufactured and if it was completed on time, we have Column J record where the product is ON TIME, LATE or EARLY using the following formula:
=IF(K8>0,"LATE&...
- 11
-1
votes
1
answer
78
views
Excel: Conditional Formatting a cell based on an roster on another sheet
So I'm new to using excel for work, I need to make an easy to read sheet where we can type in an employee's name and the conditional formatting will check the roster on say Sheet2 then format the cell ...
- 3
1
vote
1
answer
45
views
LARGE function throws error
I would like to compute average scores over top k exams. That's why I need to get top k scores. There is a large function in excel, but I am not even able to execute it in a simple list in excel ...
- 113
1
vote
2
answers
213
views
LibreOffice SUMIF specific time
I'd like to sum order totals and have 2 subtotals for orders taken before and after 17:00.
The formula: =SUMIF(C:C,">17:00",D:D)
where C and D columns are:
The formula does not work well....
- 111
0
votes
1
answer
53
views
Excel Cost Tracking Equation
I need to track the cost of individual items in Excel and the total of everything together.
As shown in the image, I need BL6 to equal (AX6*$AX$39) through (BF6*$BF$39).
Is there an equation that ...
- 3
0
votes
1
answer
424
views
Creating different structures of the same table in a different excel sheet
I have a sheet with a table that has membership names, birthdays and other information. I need to be able to view this information in more than one way.
This is to say that, for example, some day I ...
- 1
1
vote
1
answer
756
views
Counting the number of times a name appears next to a date
I currently have a pivotTable where each row is a date and each column is an employee. The values in the intersection are the sum of the hours worked that day. From another sheet/tab, I would like to ...
- 113
0
votes
1
answer
174
views
I need to convert date strings in CSV to Date types when imported into Excel
I am working on an iSeries platform. I have programs which will create a CSV for the data that I need, and an RPG program which will run a Java program to take the CSV data and insert it into a .xls ...
- 101
1
vote
3
answers
3k
views
VLOOKUP record with latest date
I'm creating a spreadsheet to monitor the last time we contacted a customer. In tab "Overview" I have a list of all the customer names and addresses for example:
A
B
C
D
E
F
Name
Address
...
- 25
0
votes
0
answers
35
views
Calculate work hours between two dates with different work schedules
I am working on a vacations sheet. I have the start and end dates of the vacations and I need to calculate the working hours in between.
I have found many answers on google, but my problem is that not ...
0
votes
1
answer
53
views
How to extract characters from a cell in Excel?
kind regards
I am working on some data and I need to extract from column A:A exactly only the dates that start in 2017 and end in number, that is to say with a length of 8 characters [20171015], the ...
0
votes
1
answer
158
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
2
answers
431
views
How do you combine IF, AND and NOT statements in Microsoft excel
I'm trying to compare Column A with Column C , and Column B with Column D and to display the desired results as shown in Column E using the following formula: =IF(NOT(IF(AND(A2=C2,B2=D2),"Names ...
-4
votes
2
answers
85
views
amount calculation in excel
I have a payment amount of 2628.46. This payment should be used to pay off, in turn, three components listed in columns BZ, CA and CB. So, first I subtract the April Principal from the May payment. If ...
0
votes
2
answers
145
views
Excel- Add sequential names to column based off of named range but skip names if duplicate name exists in adjacent column
I am creating a simple schedule for our engineering rotation. Some background information, our team currently has 6 engineers that must go on a weekly 5 am rotation. There is also a 2nd shift rotation ...
1
vote
1
answer
2k
views
Circular reference that can't be shown
I have a worksheet with a whole lot of formula's in it, about 4000 I think (including conditional formatting). I sent it to a colleague and they said that an error showed. I re-opened it and was ...
- 133
0
votes
1
answer
129
views
Count number of times a date repeats x times in another sheet within given date range
In one sheet I have a set of dates (in column A). Each date entry logs an execution with different variable values:
Sheet 1:
05/03/2021
05/03/2021
05/03/2021
05/04/2021
05/04/2021
05/05/2021
05/05/...
- 5
0
votes
1
answer
424
views
Count occurrences of "Monday" in a different sheet from a given date range
In one sheet I have a set of dates (in column A):
Sheet 1:
05/03/2021
05/03/2021
05/04/2021
05/05/2021
...
05/28/2021
In another sheet, I have 2 named cells: from_date and to_date (for a report range)...
- 5
0
votes
0
answers
46
views
Matching 2 Data Set rows with similar column headings
I have 2 data sets A-Q (left) and U-AA (right)
I am trying to match the rows on the (right) U-AA with the rows on the (left) A-Q
Once a record on the right matches the first like record to the left ...
1
vote
2
answers
126
views
How to create a column in Excel that tracks when data was input in a neighboring cell?
I have some columns in an Excel spreadsheet like this:
Name Date Time
I would like for when users add a name to the first column (column A), that the Date and Time column automatically ...
- 149
-2
votes
1
answer
71
views
Writing an Excel Spreadsheet Formula
I need an Excel formula that will find the most efficient combination of products based on the value of a single cell.
e.g., I have a product list as follows: 400 / 220 / 150 / 110 / 72 / 60.
I need a ...
- 1
1
vote
1
answer
45
views
Excel/Sheets: Average only the input data in a range of data?
I've got an annual budgeting spreadsheet in which each month is listed along with given categories of items. I'm trying to find my average monthly expenditure on any given category, but don't know the ...
-1
votes
5
answers
123
views
Reformatting multiple columns and rows of data into a single row
I have data across four columns where every two cells is related. In other words, every 4x2 block of cells. In the below, every letter is in it's own cell. I've been playing with INDEX and OFFSET ...
0
votes
2
answers
2k
views
How to convert R1C1 string into A1 string inside excel using other formulas (no VB)?
So the thing that I use to evaluate excel files is called Apache POI and it does not know how to work with R1C1 formulas. I got a document with formulas like this:
=INDIRECT(B$1&"!R"&...
- 91
0
votes
1
answer
2k
views
How to create "workaround" MAXIFS and MINIFS formulas in Excel?
I am using the following formula to calculate the average daily temperature value in column B (see image below) for each day over a 15-year meteorological record (temperature values are in column B ...
- 101
0
votes
1
answer
535
views
Libre-office calc: copy cells from different file with automatic cell number addressing
In libre-office I need to load a lot of data from one file into another file.
To load the cell content, I use to code below:
=DDE("soffice";"file://path-to-my-file/my-file.xlsx";&...
- 1
0
votes
2
answers
2k
views
EXCEL: How to fix a nested AND OR that is returning FALSE when TRUE
The code is to look for a string in the first row and then perform some action with the nearby rows and columns according to the results of the boolean formulas. Eventually, I will change the "...
1
vote
1
answer
38
views
Creating a looping if statement in Excel
I'm trying to make my budget more user friendly.
The part I'm struggling with is calculating the total for each account.
I have my budget set out in columns.
The final column is the main section (F2:...
2
votes
1
answer
1k
views
Excel: Get sheet name from Drop down Cell for formula
I have sheets each with an employee name.
I have a Summary Sheet that gets the weekly total from each employee sheet using VLOOKUP.
On the summary sheet, I have a drop down cell with all employees ...
- 23
-1
votes
1
answer
117
views
Excel: conditional running total formula based of value of another cell
I have an Excel file with two sheets like below
and
In the Donations sheet in Column F, I'd like to subtract the sum of Column E from the cell in column D in Paychecks sheet in which the value of ID ...
- 209
1
vote
1
answer
117
views
Excel: Clone cells and have the exact same value & formating
Yesterday I tried to make another step with my network table to give an easy view on the current network cabling / patching of our company. I remember that I did something similar in the past but I ...
- 324
0
votes
2
answers
51
views
Sum multiplications of horizontal ranges of cells, starting in different columns in Excel
The input
I have two tabs with input.
The first sheet shows, for each type of object, an evolution of a value over its first 24 months. Consider it a measured evolution.
The second sheet sets, for ...
- 153
0
votes
1
answer
294
views
VBA - IBAN validation not working
I have a list of IBANs in Excel and wish to validate them via scraping data through VBA from this website: https://www.iban.com/iban-checker. In column A (cells A2:A3000), I have the IBANs and in ...
3
votes
1
answer
333
views
Sorting fomulas in an Excel table
For my project I need to arrange locations based on the lowest driving time to different locations. I simulated the driving distances (Row D, E & F), but the height depends on the products which ...
- 55
0
votes
1
answer
9k
views
XLOOKUP Function Disappeared
I have been using xlookup for several months across several files just fine but today it stopped working across all Excel files. I invested a lot of effort moving from vlookup to xlookup and now it's ...
- 1
0
votes
3
answers
2k
views
Automatically stacking excel sheet data on top of each other in a new sheet
Is there a formula (can be a user defined VBA formula) that will automatically stack specified sheets on top of each other?
The formula can't involve the "Indirect" Excel Formula due to ...
- 125
-1
votes
1
answer
110
views
Alternative to MS Excel for non-calculating (text & images) tables [closed]
In my company and in millions of other companies, Excel is misused for word processing (creation of tables containing mainly text, for example project plans and lists of all sorts), basically just ...
- 526
3
votes
2
answers
4k
views
Generating dummy text in Excel table
When I want to create a lorem ipsum-like text in MS Word I use
=rand()
and it works perfectly. I immediately get a passage of mumbo-jumbo text which I can use for my aims.
I wasn't able to find the ...
- 1,325
0
votes
1
answer
465
views
Highlight a cell based on multiple conditions of multiple cells
I thought a simple OR function with a few ANDs might do what I want, but I cannot get it to work. Either I'm being stupid, or I've chosen the wrong formula.
I want to highlight a cell outside of my ...
- 15
1
vote
1
answer
786
views
How to return the value of one cell based on the max of multiple ranges of other cells in excel?
I am a bit new to excel and have a spreadsheet where I am trying to use the MAX of a range of cells in order to return the name of the row that that MAX belongs to. So, I have a cell returning the MAX ...
- 19
3
votes
2
answers
638
views
Count from multiple columns with multiple conditions in Excel
I have data that is arranged like
(C)
(D)
(E)
(F)
(G)
4
High; Average
High; Low
Low
1.1, 1.2
2.4, 5.6
5
High; Low
Low
Average
1.1.2,
1.1, 1.3
6
Average; Low
2.4, 1.2.3
1.2, ...
- 33
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
2
answers
136
views
MS Excel - Locate like terms in cells
I am trying to locate and identify neighboring lines with a matching word.
For example:
| --Col A-- | -Col B- |
| That Car!! | [blank] |
| Blue Car$ | [blank] |
| Ponies^!_ | [blank] |
|...
- 111
0
votes
1
answer
7k
views
Excel : Change "Connection Only" back to query
In Excel, some other user changed my query. It went from connection to an excel tab (parallel process shown below in pic)
to a "Connection Only", as shown below in picture:
.
Power Query ...
- 21
0
votes
1
answer
43
views
How to compress/merge a table that shares the same vaule in column A, and append all data to the end row
I'm trying to find a way to go from Original table into a more Compressed table.
I have column A which will always have "Corp Name", followed by a list of contact information. This issue is ...
0
votes
2
answers
64
views
How to create nested if in Excel function
i have below formula now i want to add extra if so if A2 is not present in Sheet1 show "NoData"
=IF(ISNUMBER(SEARCH(INDEX(Sheet1!$N$2:$N$205,MATCH($A2,Sheet1!$C$2:$C$205,0)),$K2,1)),"...
- 95
0
votes
1
answer
37
views
Excel FV formula
I want to apply a percentage to get a specific value.
For example:
Product price: $1000
Selling fee: 10%
If I want to get $1000 (product value) after the site applies its commission, what formula ...
1
vote
1
answer
37
views
Excel Lookup from different sheet
i have sheet in below formats
Sheet1
Sheet2
Now i have to validate my Sheet1 as per Sheet 2 if ID exist and country present its "Match" else "No Match" if no entry then "No ...
- 95