All Questions
Tagged with array-formulas or worksheet-function
8,321 questions
1
vote
2
answers
30
views
Look in data cluster in a row and fetch the first cell value of the row
I have a sheet where the first column has the names of people, next 4 columns have various dates. I want a function to check the cluster of dates and fetch the list of people allocated for a ...
0
votes
2
answers
126
views
IF (OR (AND () function does not work as it should be
When I apply that command, for example P16 = 0.1, S16 = 0.1, it should output "Đ" but it outputs "KĐ". Please help me with this
=IF(OR(AND(P16<0,(P16<=R16)),AND(P16>=0,P16&...
-1
votes
0
answers
18
views
Adding a column with formula driven text and amounts
I have a spreadsheet and I need to add up column with both text and monetary amounts in. But, because what I am trying to total are the results of a formula, I can't get it right.
My spreadsheet is ...
1
vote
1
answer
50
views
Formula based on the cell that has content
Is there a way to have a formula look at the below cells and run a formula based on the cell that contains data? Ideally I would like it create the breach time based on which column has a time and ...
- 11
1
vote
2
answers
47
views
How to list all values in a column if they have values associated with them in another column in Excel?
I need to return a list in a third column of only the values from Col 1 that are fully filled out in Col 2.
In this case it would list A and C. In other words if all values A in Col 1 have ...
- 11
0
votes
1
answer
38
views
Return value of cell if another cell contains values
Take this table:
I need to look in the column "Name" for Peter, Ann or both and if found, then return the value from the Bonus cell.
I've tried several suggestions from Copilot - for ...
- 265
0
votes
0
answers
45
views
Excel: Sumproduct with date range
I'm looking to do a sumproduct with a date range and I am very new to using arrays. My formula regularly has 115 rows and 30 columns, but I reduced it below for troubleshooting:
=SUMPRODUCT(('History'!...
- 3
-3
votes
1
answer
81
views
How to take a week number, convert it to a date, add a number of days and convert back to a week number?
On my team, we have to constantly manually change week numbers. Since its a report done once a week where we have to use the same template. I wanted to figure out a way where we just change 1 cell to ...
2
votes
2
answers
47
views
How to calculate percentile on array with conditions and value errors with Excel?
I have an array comprised of both numbers and value errors, and I want to calculate a percentile value from numbers (excluding the errors) in the set where certain conditions are met in another array ...
- 489
0
votes
0
answers
19
views
Copying table data across multiple table using Formulas
So, I have/had an Excel spread sheet that I have been using for years and worked perfectly until my system crashed and the sheet was corrupted. After restoring my system the only back up I can find ...
1
vote
0
answers
45
views
Can the WEBSERVICE function in Excel return anything malicious? [closed]
When using the WEBSERVICE function to return data from a website, what data type does it return? Can it return something like a macro? If a website originally has benign data, can it later be changed ...
- 11
0
votes
0
answers
38
views
Excel formula to get SUM quantity with multiple criteria
=INDEX('JO with Comp'!$G$31645:$G$200000,MATCH(1,($A2911='JO with Comp'!$K$31645:$K$200000)*((VLOOKUP($A2911,'JO with Comp'!$K$31645:$R$200000,8,0)='JO with Comp'!$R$31645:$R$200000)),0))
I need ...
- 15
0
votes
2
answers
46
views
Find the last row in an Excel table column where value is not 0
Using an Excel formula, how can I find the last 'Period' in a table where the 'Contribution' is not 0?
Take the example table below. In this case I'm trying to make a formula that would find 'Period' ...
- 1,517
0
votes
1
answer
28
views
How to write a formula pulling data from multiple cells and drop down options
I really tried reviewing other answers that seemed similar but I just couldn't get it to do what I needed. I need to create a daily/weekly total of our sales in multiple divisions. I have a table, w/ ...
0
votes
2
answers
51
views
How to sum in Excel row values from the row that had a specific value in another column
If a row from another column has a word (like Sum), how to make the other column sum up all the numbers from below the last Sum and until the previous row?
I need a formula to put in O5, O8 and O13. ...
- 1,245
0
votes
1
answer
36
views
Excel document - How do I hide a formula/ value if there is no new data in a row?
enter image description here
I have created an excel document to calculate how many hours staff are still owed from working overtime and taking their hours. The calculations/ formulas are all correct, ...
- 1
12
votes
1
answer
883
views
In Excel, when I select data for a graph, how to prevent arrow keys from selecting cells?
For any given graph, when a data range is specified, is there a way to type with arrow keys that does not result in a different cell being selected?
For example in the images below, I'd like to ...
- 613
0
votes
0
answers
52
views
Excel: Dynamic Hyperlink to Other Worksheets
I have a desktop excel workbook that is autosaved to OneDrive with several tabs in the workbook. I want a dynamic HYPERLINK() function to open the link to another worksheet in the same workbook ...
- 1
1
vote
3
answers
46
views
IFERROR *error*
not sure why it’s returning “0” despite asking it to return blank (“”).
Need it to return blank when corresponding column B is blank
0
votes
0
answers
30
views
How to avoid particular cells which are having conditional formatting in the Spreadsheet?
I have prepared a spreadsheet with conditional formatting and following are the details.
Cell=K39 is having Data Validation clause.
Excel cell with Data Validation Clause
when the K39>= 3, ...
- 1
0
votes
1
answer
28
views
Excel Search any item in a column in the current row (reverse search)
Hello I have data in such a format
Date | Usage | Value
I search in this (long) table using sumif
and sum the up in a new table
Category | Keyword| Sum
using:
=SUMMEWENN(INDIREKT("Data[" &...
0
votes
0
answers
22
views
Excel formular with indirect to select column in tabular fails with wrong code message
I want to sum over a tabular with a search value in a selected column
Without Indirect it looks like this
=SUMMEWENN(Data[Verwendungszweck];"*"& [@Stichwort] & "*";Data[...
0
votes
0
answers
50
views
How to make the result of a function permanent
I would like to know if exists a formula that, if a condition is satisfied, return me the value stored in a cell. I know that I can use the feature "To Actual" or copy the cell and pasting ...
- 1
1
vote
3
answers
126
views
How to get an daily average across many years of data
I have a large amount of data and I am trying to calculate median values for each day of the year across 25 years of data.
The median values will be useful so that we can compare current figures to ...
- 11
2
votes
2
answers
410
views
How to populate a cell with text which is made up using characters from data in other cells?
I have a table in Excel which has a header row and data in cells A2 (text), B2 (text), C2 (text), D2 (text) and E2 (number). A2, B2, C2, D2 are drop downs with different selectable data. E2 is a ...
- 23
0
votes
1
answer
35
views
If this information is in range, yes
I've searched for this but can't seem to find the exact answer.
As an example, I have cells a2:a100 with information on departments, finance dept, marketing, sales, etc.
I'll be listing all ...
2
votes
1
answer
47
views
CountIfs Formula to Include Blank cells
I'm working with this current formula below and what I want to do is count cells that are <10 or are blank. How do I update what's in bold below to also include blank cells? Thanks.
=COUNTIFS(...
- 23
0
votes
3
answers
48
views
Calculating an If/Then Based on Certain Criteria in a Column
I'm trying to write the final part of my formula to calculate grade reductions based on absences. As you can see in the table below, I can write a calculation that adds up the number of days missed ...
- 11
1
vote
0
answers
56
views
Formula in Excel that adds up numbers in a column (F) if columns A and B match criteria
I'm stuck on writing this formula.
Basically, I'm trying to add up the number of total absences that a student would have by hour.
So if the student (A) is in hour 6 (B), their absences (C) are ...
- 11
0
votes
0
answers
46
views
Match Index breaks when slicer is applied to lookup array
TL:DR I have a lot going on in this workbook, but it seems to boil down to an issue with a slicer affecting the way a match index is looking up values and breaking in part, but not in whole. In other ...
0
votes
1
answer
34
views
How do you lock certain cells and hide the formula without protecting the whole worksheet?
I am working in excel, trying to lock my cells and hide the formulas so that they can't be changed without protecting the whole sheet. I've done the format cells and locked and hid formulas. It says ...
- 15
0
votes
1
answer
19
views
How do I include a cell value as the context when using the Excel LARGE function without macros/VBS?
I'm not sure how to phrase the question. I have a matrix of data that lists applications on the rows and teams that requested them on the columns. An X is used to mark the intersection for a team and ...
- 153
1
vote
1
answer
136
views
How to hide the 1900 dates in my cells until dates are entered in a previous cell?
I am using this formula in conditional formatting for column E =AND(E4<>"",E4-TODAY()=30).
I'm needing to hide the 1900 dates until the open date is entered, then the Columns E is +30 ...
- 15
0
votes
1
answer
39
views
Excel lookup by column name
Here is a scenario -
I have quantity table like this
100
200
$1
$3
$2
$4
I have more columns with other values like 300, 400, 500 and so on. Also, each row has a name associated but I am new to ...
3
votes
2
answers
621
views
How to invert a particular submatrix?
Suppose I have a matrix of numbers like this one in cells F2 to I5:
a_1 a_2 a_3 a_4 x
b_1 b_2 b_3 b_4 y
c_1 c_2 c_3 c_4 z
d_1 d_2 d_3 d_4 w
Suppose that I want to solve the system of equations given ...
- 195
0
votes
2
answers
62
views
How to drag vertically (in row) but increase vertically (in colum) in excel?
So, I have data in B10:B117 (in column B) that I want to put in row B140:DE140 (row 140). I know that I can just special paste transpose, But I want to write it like, =B10, =B11, =B12, so if the value ...
- 49
0
votes
1
answer
87
views
OpenOffice/Libre Office Calc - counting how many records contains non-empty fields
I was forced to use Calc instead of my beloved, well-known Excel, because open source, free, yada yada yada.
And need help. I have a table with the structure:
Column A - group name;
column B - ...
- 23
0
votes
3
answers
62
views
Checkbox cell value, multiple IF formulae
I'm trying to correlate a checkbox with a number value as per below i.e. if D4 is TRUE then value shown in G4 = 3
So far I can get G4 =if(D4,3,0) to produce this but cannot apply the rule to the whole ...
- 1
0
votes
2
answers
42
views
look up the value of a page based on the values on a page
I have a main table with one column listed as Wk 1, Wk 2 etc to Wk 53. I want columns B to H to show the dates in tabs Wk1 to Wk53 cells F3 to L3 respectively. so B3 is ='Wk 1'!F3 etc.
but I dont want ...
- 15
1
vote
1
answer
85
views
How to get values from sub-arrays and put them into new arrays?
I have a text like "foo 1, bar 2, baz 3" in cell C1.
I want to separate this into 2 arrays:
["foo", "bar", "baz"] and [1, 2, 3]
I currently have this formula:
=...
- 23
0
votes
1
answer
40
views
Excel: finding a value on sheet 2, if a combination happens in sheet 1
I have a set of values combinations in 1 sheet (countries from and to) and another sheet with combinations and values of those combinations.
What I need is, when a combination happens in sheet 1, and ...
-2
votes
2
answers
37
views
Excel Sum a range based on the values from a database and sum the column corresponding to it
I need help getting the formula for this
Sum the equivalent value of a range based on a predefined database
Example:
Rob1 Ann1 Dory1 Rob1 Ann2 Dory2 Total 180
Rob1 10
Ann1 20
...
- 3
2
votes
1
answer
86
views
Combine SUBTOTAL and SUMIF formulas
I am trying to mix the SUBTOTAL and SUMIF functions so I can have a total that ONLY counts the positive number lines in column I, and have that total broken down by the journal entry number in column ...
user2284151
0
votes
2
answers
60
views
Excel: Find Duplicates for combined rows
I'm trying to figure out how to find duplicated row combinations.
My data uses text so I've been having a hard time finding a method that works with it.
A
B
C
D
T001
Apple
Apple
Apple
T002
Apple
...
- 3
1
vote
1
answer
98
views
How to search in 1 excel column to return value from another column?
I need help to achieve what I need. I have data in 2 Columns (image attached), The first column (A) is for Master Agent and Column (B) is for Sub-agents. I also have a search box in Cell D1 and what I ...
- 13
1
vote
1
answer
30
views
MS Excel - create table which filters input data based on criteria, including input data headings
I’m trying to get my head around an excel formula but am struggling… any help would be appreciated.
Scenario
Input Data:
I have a table detailing a list of people and dates on which they are to ...
- 13
0
votes
0
answers
29
views
Excel Formula for Accrued Income over Promotion Period
My workbook has columns from Y:CD with dates
Included in the rows beneath this is customer revenue data
Contract end date is in S2 promotion start date is in t2 and promotion end date is in u2 cease ...
0
votes
1
answer
28
views
Formula based Data Validation in Excel Web sheets
This is a follow up from this post. If cell A2 is empty and a value is being entered in cell B2, I want a dialog box to prompt entry in cell A2
I tried the method suggested by the contributor in ...
- 103
1
vote
3
answers
156
views
Creating a dynamic array formula with INDEX MATCH
I'm a teacher and I've tried to make an Excel spreadhseet that can automatically pick out a specified number of random keywords and their corresponding definitions from a master list:
The number of ...
- 13
-1
votes
1
answer
50
views
Why do I see a box outlined with my email address in it pointing to the corner of a cell of my Excel spreadsheet? What is it? How do I get rid of it?
In Excel 2010, my spreedsheet has automatically produced a box with my email address in it and pointing to a SUM(Name) cell.
What is that? How can I remove it?
