All Questions
694
questions
1
vote
1
answer
45
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
4
votes
1
answer
187
views
Consolidate Data from Multiple Columns into One Column
I have a dataset that looks something like this:
Customer
Col1
Col2
Col3
Bob
111
2221
Michael
112
Anna
113
2223
Rachel
114
2224
334
Person5
115
I'd like to put all data into one column, ...
0
votes
1
answer
80
views
In Excel is there a way to “turn on and off” the =min in a formula?
I am creating a budget spreadsheet in Excel and in some instances I will need the formulas in the spreadsheet to include the =MIN and in some instances I won’t. For example, the formula is:
=MIN(...
- 1
0
votes
2
answers
70
views
Copy paste the values of cells from Excel
I want to copy paste the cell values what we see post pressing F9 in a cell into word. Is there any short and quick way to do this?
For example - if I have a formula:
=AVERAGE(E12/H12,E11/H11,E10/H10)
...
0
votes
1
answer
105
views
How to compare two columns in Excel with a conditional IF statement?
I have three columns that have some expense figures: Actual (column A), Budget (column B), Variance (column C):
For these expenses, if Actual is greater than Budget, I want the variance column result/...
0
votes
1
answer
117
views
Formula for Matching Header to Value
This is a question that I may have difficulty explaining, so bear with me.
On one sheet I have a column which has several cells, some of which are blank, some that have a single value, and some that ...
0
votes
1
answer
69
views
How to count the number of cells that contain certain text while excluding cells that start the same but end different
In our company, the truck numbers in our database all start with "TK" A regular truck would be "TK123" for example. We also have stipend trucks and these all start with "TKPB&...
- 3
0
votes
1
answer
300
views
How to Autoincrement the Column index number by dragging VLOOKUP formula down
I need to drag a Vlookup down and have the Column index number in to increase by 1 every row I drag it down.
So for example:
Row 1:
IF(VLOOKUP(A2,'Jan 2024'!$D:$BP,**35**,0)<=0,"IGNORE",...
- 1
0
votes
3
answers
153
views
How to count for between two dates in 2 colums with different row in Excel
I have a spreadsheet with 2 columns with dates and one with a Room #:
Date In
Date Out
Room No
2/3/2023
2/5/2023
Room 1
2/5/2023
2/8/2023
Room 1
2/8/2023
2/10/2023
Room 1
3/23/2023
3/26/2023
Room ...
- 1
0
votes
1
answer
67
views
Why am i having a #value in my index excel formula?
I am trying to create a formula With Index excel but there ia something wrong and i can't figure it out , below is my formula
=INDEX(TABLES!$D$98:$S$189,MATCH(1,($C$3=TABLES!$D$97:$S$97)*($C$4=TABLES!$...
1
vote
1
answer
4k
views
References to blank cells become 0's
I have a spreadsheet in excel where I need to make some references to an area in another spreadsheet. I simply use ='EARTH'!B9:N323, which is an area mixed with some columns of text and others of ...
- 21
0
votes
1
answer
84
views
How to stop numbers from automatically opening hyper links in Excel?
So I have ID numbers that I hyper link to a website. It's automatically hyperlinking the whole cell, preventing me from editing the text by double clicking the cell.
For example, let's say I have ...
- 3
0
votes
0
answers
60
views
Formula to Round Down Number in cell
I need to round down the highlighted numbers in Column Q of the attached screenshot. I currently have formula of P/F starting on Cell Q5 through Q46. I am diving the quantity on hand for each part ...
- 13
0
votes
1
answer
559
views
Excel Help - If two cells meet a certain criteria input a value into another cell
I'm a bit of an excel beginner so apologies in advance.
I need my worksheet to automatically input a price into a specific cell.
I have a table in one sheet which has tables for each product brand and ...
0
votes
2
answers
210
views
Round each instance in Sumproduct before sum
I am trying SUMPRODUCT Formula on lets say Column A and Column B. Column A has Patient referrals and Column B has percentage referred for each hospital. I am using this syntax:
ROUND(SUMPRODUCT(A1:A10,...
- 1
-1
votes
1
answer
75
views
How to generate a table of names based on complicated criteria in Excel
Screenshot:
Hi experts,
I am trying to make a table in Excel that can generate a "Doc #" for each day of the month (for Monday to Friday only) based on availability. I've included a ...
- 1
1
vote
1
answer
1k
views
Why are my columns disappearing after I refresh the data?
The table below is connected to a query. When I refresh the table below, the last two columns' data, S and T, disappear. All the cells in these two columns contain a vlookup. How can I get the vlookup ...
- 23
0
votes
0
answers
1k
views
I am trying have data appear in the "Values" field in a pivot table, but the pivot table keeps summing the values that have the same date
In the pivot table, when there are two or more values assigned to a date, they sum.
I don't want the values to sum. I want each value listed, even if the date is same for the values. I've tried going ...
- 23
0
votes
2
answers
64
views
Excel - Formula Based Individual Durations and Total Duration Calculation from Slash-Separated Time-Strings
I am trying to calculate (using worksheet formula based solution only) individual time-durations and total time duration from the slash '/' separated time-texts in two cells (each value of first cell ...
-2
votes
2
answers
13k
views
Conditional Formatting with greater than & less than parameters based on date in cell
Trying to conditionally format a cell based on the date in the cell. My ultimate goal is to try and track warranty expiration dates on some of the product we maintain. Within 30 days of expiration ...
- 1
0
votes
1
answer
27
views
Is it possible to seperate cells like based on a variable?
Here I have 2 pages one named Input and The other Output
in Input I have In cell C2 the number of lanes which is 6 in this example and the D2 the number of Heats which is currently 4 :
if this ...
- 3
0
votes
1
answer
156
views
Creating Exception in Excel Sum to Avoid error Value
this is the formula:
=DATE(YEAR(Q14),MONTH(Q14),DAY(Q14)+(R14))
I'm using to add date how do I make exception that it keep the answer blank until I enter the date in Q column
- 1
0
votes
1
answer
487
views
Can anyone please tell me how can I convert 1M, 1B, 1K to number format in excel? [duplicate]
Can anyone please tell me how can I convert 1M, 1B, 1K to number format (like 1K = 1000) using formula in excel.
Advance thanks for help.
0
votes
2
answers
4k
views
#VALUE! Error when trying to use the Filter function to filter on mutiple criteria
original_name
match_name_0
score_0
match_index_0
match_name_1
score_1
match_index_1
AGI Partners, LLC
agi partners llc
100
5273
agi partners llc
100
5273
AXA Investment Managers S.A.
axa investment ...
- 123
0
votes
0
answers
24
views
Have problems sharing an Excel Sheets
I am working on an Excel Book and I want to share it with some people.
All the Sheets are linked to another Excel Files, so I need some help with sharing this Excel without any #REF or ERROR in the ...
- 1
0
votes
2
answers
675
views
Splitting a column in excel into two columns keeping the leading zeros
Trying to find a faster way to change this format from one column (A) split into 2 columns (B and C) as in the example below:
A
B
C
978-BD71151-001-0
978-BD71151-001
0
978-BD71151-002-1
978-...
- 1
0
votes
1
answer
443
views
How to put DATEDIF and IFISBLANK together?
I have 3 total cells I'm working with. One houses today's date (A1), the second has the date the request was made (B1) and the third has the date of the event (C1). My team had previously used DATEDIF ...
- 1
0
votes
0
answers
66
views
SUMPRODUCT Query
I'm trying to use SUMPRODUCT or any other formula to get an end calculation of how many occurences "Louise" appears in column F and then how many Sessions she had in the MONTH of September ...
0
votes
0
answers
161
views
Call a sheet using a variable name
I have this code in wich I add a sheet with a certain name and then I need to call this sheet to get a cell value but it doesn't assume.
Dim nome1 As String
Dim tableName As ListObject
Dim addedRow As ...
0
votes
1
answer
733
views
Drag down formula from multiple sheets on one sheet
I have 19 Sheets on each sheet in cell B17 I have a result.
On my first sheet I have cell numbers from 1-19.
In my first sheet I type in cell one the Formula ='sheet 1'!$B$17. I have the number in ...
- 1
0
votes
0
answers
123
views
How do you generate a list on excel based on complex criteria when the data set is not unique?
I am trying to build a excel sheet that automatically generates the list based on the 3 criteria (each criteria has a dropdown). Below is the template I created.
Criteria list:
Purpose, loan type and ...
- 1
1
vote
1
answer
52
views
An Excel sheet that will have 2-3k columns and 100 rows, is it better to use vlookup/hlookup or index/match from an efficiency/size perspective?
Thinking about sheet design and unfortunately, it looks like it's going to have a lot of rows and columns that will have to be looking up values. From a size/speed/efficiency perspective, is it better ...
- 123
0
votes
1
answer
2k
views
How to set the language for formulas to English instead of the default Excel language?
Excel 2007 is installed in French language. So all formulas are in French, which is not handy:
=RECHERCHEV(...)
instead of
=VLOOKUP(...)
Question: how to set the language to English for formulas, ...
- 1,926
0
votes
1
answer
108
views
How to reference specific cell location for conditional formatting
I'm trying to make the conditional formatting to reference each cell location that is visible. (It has been filtered)
ActiveSheet.Range("A1:P24").AutoFilter Field:=3, Critical:="Tuesday&...
0
votes
2
answers
6k
views
How to make cell in Excel unclickable and unactive even when click?
First and foremost, I do know on how to protect the Excel files and I also know on how to Locked Or Hidden cell. The unlock cells let user to edit the cell while the lock cells will not let the user ...
- 101
0
votes
1
answer
32
views
How to setting and sort row and column simutaneously
From images below, the left is the base table and the right is the table that want to show. Here, what to I want is filter for both column and row. We do choose specific date (I did not put DATE ...
- 101
0
votes
1
answer
86
views
How to sort in column and row in order to get specific data in table Excel?
Given that image below is a simple table where the variable is DATE and TYPE. I do know on how to to filter of things in terms of only using one variable.
For instance, if I create those table, I can ...
- 101
0
votes
1
answer
627
views
Need Formula to calculate number of months into 2023 given duration and start date
In an excel file I have been given the start date of a contract and it's duration. I need to get the number of months this contract will be active in 2023.
So for example if the start data is 10/1/...
- 1
0
votes
1
answer
245
views
EXCEL formula column how to return cell reference
The values in columns E:G are calculated from data in columns A:D. The respective formulas are as follows:
E=A+B
F=B*C*D
G=D-C-B
The excel sheet looks as follows:
Assume I want to find which formula ...
0
votes
1
answer
808
views
Count number of occurences of [x] amount of zeros only after the occurence of first 1 in a row
I have a table which has only 0s and 1s in ~50 columns and ~13k rows.
What I want to do is to calculate how many times did a series of specific amount of zeroes occured in each row, after the first 1 ...
- 1
0
votes
1
answer
546
views
sum of cells if they are less than 1000 if more than 1000 then input difference
I am really not sure how to word this.
I am trying to come up with a formula that the weekly total does not exceed 1000 anything over is non-qualified "sales amount"
- Example:
Monday - ...
- 1
0
votes
1
answer
42
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
0
votes
1
answer
2k
views
Calculate and display Best Seller?
I'm wondering if anybody knows a solution before i start trying and failing for a few hours...
I have a table of all my orders since March, which includes the value of the orders, the date the order ...
- 15
0
votes
1
answer
87
views
how do i show formula result if any two selected cell has value in excel?
I have a scenario where i have two cell for input Data E24 and I24 and one cell for calculation k24. What i want is if there is value in any of those input cell the calculation should show in the ...
0
votes
1
answer
137
views
SUM function returning 0 value
DATA SET PREVIEW
I Used the sum function to calculate the time, as the trip duration is in seconds i used =SUM(cell/60) and then convert them to HH:MM:SS, but some of the cells are returning 00:00:00
...
1
vote
3
answers
2k
views
Calculate average in excel excluding cell contains date or zero or text
In B6 I want to calculate average for range c6:h6 skipping cells which has date, zero or non text values.
Result should be - 400
I tried using averageif with filter ex: =AVERAGE(FILTER(C6:H6,ISNUMBER(...
- 111
1
vote
1
answer
780
views
Is it possible to link an entry in a filtered sheet to the master sheet, matched on a key variable? (Excel)
I am trying to link a column in a filtered sheet to another column in the master sheet using a key category (i.e., id). Below is the illustrated dataset:
[]
As shown above, I have the master sheet, ...
1
vote
2
answers
785
views
Converting positive number values to boolean TRUE in excel
I'm trying to sum up all the entries based on several specific conditions.
The goal is to count all entries that have a 'yes' ticked in them in cells J or K or L or M and have '1st' in H
This is the ...
- 13
0
votes
1
answer
692
views
Microsoft Excel- prioritizing based on two date columns
I have two due dates I have to look out for at work, and I want to have a priority column that assigns a critical, critical,high, medium, low , low priority if either of those dates are within -120 , ...
0
votes
1
answer
64
views
How to perform same operation on non concurrent excel cells
I am trying to perform the same operation on excel on a range of non-concurrent cells.
Let's say I have range C:C, within this range I need to perform this operation IF(ISNUMBER(C2),(C2*A2),(0)). How ...
- 3