All Questions
693
questions
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,916
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
626
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
243
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
805
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
545
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