Questions tagged [worksheet-function]
Questions about using in-cell functions/formulas in spreadsheet software.
8,139
questions
-1
votes
0
answers
32
views
Need help using index/match/vlookup
enter image description here
I need assistance creating a formula with match statement(s). My current formula isn't reading all my columns and gives me an "N/A" the moment I change "FOB&...
0
votes
1
answer
35
views
Want to give more options in an IF formula
Current formula is
=IF(AND(RIGHT(A3,2)="00",B3="Vinyl",H3="No",I3="01 - UMGI",G3="Catalogue"),"8M Block required","None")
Instead ...
0
votes
0
answers
30
views
Linking columns on different sheets
I'm very new to VBA coding (only dabbling for this specific project), so I would really appreciate any advice on an issue I'm having. I'm attempting to independently link multiple column ranges on ...
0
votes
3
answers
184
views
How do I get the sum of hours:minutes in MS Excel in the status bar and in a formula if the cells come in with a text format?
I know that there are questions around that are much the same, like Sum the number of hours in Excel, which asks you to format [h]:mm, which I do and it still does not work.
Also not so far is How do '...
0
votes
1
answer
27
views
Chain array formulae
Is it possible to chain array formulae?
i.e.)
Assume a column of values such as "Max", "Michael", "Kyle", "Michael" starting in A1
Then a formula such as =...
1
vote
1
answer
25
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 ...
1
vote
1
answer
16
views
Excel formula - returning a list of values from multiple criteria
I’ve had a real struggle with this one and can’t crack it for the life of me.
I need to create an excel formula that does the following:
*Looks for all Values in Forces!A1:A5000 that match the value ...
1
vote
1
answer
24
views
limiting excel columns to a maximum
After I import a csv file and double-click to make the columns wide enough to show data for each column contextually, many columns are way too wide.
Is there a routine I can run in an xlsx file that ...
0
votes
1
answer
44
views
How to add cell color based of values with color gradient from red to yellow to green with a formula
I have values like this:
If the values are higher than -5 or +5 they are also red. everything towards 0 gets greener like the screenshot.
Formatting with the conditional format for each column is a ...
-1
votes
0
answers
27
views
Automatically populate a template in excel from table with input values
I would like to have an table with input. Press a button. And use excel to generate 1-20 lines with the same input. For every row of input there are 3 output lines.
Simplified Example input table:
...
-1
votes
1
answer
34
views
Excel formula that allocates weight into 3 different sized containers
I have an excel sheet that i use to calculate a raw material cost per kg for my customer.
effectively i want the excel sheet to calculate how many jerry cans are needed for the total weight of the ...
0
votes
0
answers
22
views
Word document with two inserted excel spreadsheets, cross referencing cells between them
I have a word document in which I have inserted two separate excel spreadsheets using the Insert tab, then Table button, then Excel Spreadsheet in the drop down menu in Word. Let's call them sheet1 ...
0
votes
1
answer
65
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 ...
0
votes
1
answer
60
views
Join/Mix dynamic and static values on Array = alternative to HSTACK that works in older MS Excel version (No VBA, please)
I need to join some value(s) from some cell(s) with a "static" bunch of other values into an Excel array... BUT I'd like to avoid using VBA for it and I need something that works also with ...
0
votes
0
answers
77
views
ATAN2 in Excel doesn't work with references
I tried to convert a pair of columns for x-values and y-values into two columns for radii and angles.
For the radii I could write =SQRT($x^2 + $x^2), but for =ATAN2($x,$y) I get complaints about ...
-2
votes
0
answers
28
views
Compare old new and new sheet rows and columns
I have 2 sheets. 1 - Old Data, 2 - New Data which are updated quarterly.
Both have 22 columns (in same order) and several 1000 lines of data.(not same qty)
I need to compare both sheets to identify ...
0
votes
2
answers
81
views
Can an Excel Filter function base its include on a whole row?
I have a Table from which I pull a list of unique values, then I want to pull all their keys and merge them using textjoin. But the Filter function the criteria to be compared against a single value, ...
0
votes
1
answer
29
views
Excel - Increment number based on another cell's value
I have a spreadsheet with two columns. One column has a list of customer account numbers. I need to create "group numbers" that increment by account number. The example is what I'm trying ...
-2
votes
0
answers
52
views
Excel - How to split value in one row to different row [closed]
Question:
Excel - How to split value in one cell that have comma in sequence order format into separate row based on the quantity of the same item
Explanation as below:
Input
Item On Hand with ...
0
votes
0
answers
13
views
How to select random cell in Excel with condition?
I want to select randomly cell in B row with number "1" or "2" in its left cell and put it in C row. What formula should I put in?
0
votes
2
answers
59
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
30
views
Excel Conditional Formatting Cells in a row
I have an excel spreadsheet organizing material orders, I have 2 columns "Date Expected" and "Date Delivered" I have 1 row functioning the way that I want it to which is if "...
1
vote
1
answer
30
views
modify Excel 2019 formula selecting everything after a character, to make first letter of result in Capital
Windows 10
EXCEL 2019
The formula below looks at the contents of cell H2 and returns the characters after a full stop.
Example
Before H2 contains "some data.orange"
After H2 contains "...
-1
votes
1
answer
47
views
Excel Formula that increments academic grade each year
I am having significant trouble creating a simple formula in Excel. Imagine I have a table of students and their current academic grade. I would like to have a formula that automatically recognizes ...
0
votes
2
answers
57
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 ...
-2
votes
0
answers
44
views
Error in profit margin formula
I am trying to calculate a total profit margin with Purchase Price(C4), Actual Sell Price(F4), and Quantity Purchased(D4). Currently, my formula is:
=((F4-C4)*D4)/F4
However I am receiving a #VALUE! ...
-1
votes
3
answers
83
views
How do I create a formula in excel for this fee structure?
I have a cell that has a value A1 = 3,500,000.
I want to calculate a fee on that $3.5M that follows this formula:
7% on the first million, plus.
5% on the second million, plus.
3% on the third ...
0
votes
0
answers
41
views
How do I check that a list of cells have the correct values in Excel, where some cells can have more than one correct value?
I'm trying to create a "Complete?" box for a spreadsheet I'm making of certain cocktails and the necessary ingredients for each. I've managed to wriggle everything into acknowledging if ...
-1
votes
0
answers
29
views
Two worksheets with two columns and indicates match if both columns are the same
I would love some help. I have two worksheets with the same data columns, State and Email. I am attempting to cross reference if the two sheets have matching information.
EG: To verify "email@...
2
votes
2
answers
139
views
Find specific values and then return corresponding value
I am beginning to write a formula in B9. This formula will be applied to all similar rows down the sheet.
What I am wanting to do is for B9 to check for al instances of "In-progress" in its ...
1
vote
1
answer
40
views
How do I autofill a formula to skip a row
I am trying to drag data from a excel master sheet onto another excel file but the numbers are skipped per row.
This is what I'm trying to do
='[MASTER ITEM LIST 2024.xlsx]'!$E15+'[MASTER ITEM LIST ...
0
votes
0
answers
8
views
Google Sheets - Multiply/Concatenate String Arrays [migrated]
Suppose I have three columns importing data from elsewhere, like so:
Name
Company
Division
Adam
Meta
Ops
Betty
Nvidia
Sales
Carl
OpenAI
Development
The number of values in each column may change ...
0
votes
1
answer
13
views
How can I reset the Math Symbol Autocorrect in word?
How can you
Reset the math autocorrect pairing between shortcuts of e.g. the form \scriptF and the mathematic symbol to default settings?
How can you add a shortcut to a mathematic symbol that ...
2
votes
1
answer
34
views
Can I make a conditional design based on other cells (Excel)?
I'm trying to make this by Excel rules:
That any cell (in column B) that is near (beside) "1" (in A) will be red, and any cell near (beside) "2" will be green.
Is there a possible ...
0
votes
0
answers
16
views
Insert MODE.SNGL into a conditional formula pulling data from one worksheet to another
Windows 10
Excel 2019
I have a workbook with two worksheets, Product Data and Product List.
The data in Product List is generated each time the formula I am looking for is run.
The number of products ...
0
votes
0
answers
47
views
Identifying a Specific Table Hierarchy... Don't Know Where to Start
I am trying to identify/mark records where an entry exists in a specific column. While that may be pretty straightforward using a loop, I must also mark those records representing the "Parent&...
0
votes
0
answers
33
views
How can I standardize a list of Excel instructions so that it happens automatically when a file is opened?
I'm not sure what I'm asking entirely, so it's made it difficult to Google. Thank you in advance for the assistance!
I have a spreadsheet that generates each day with different information from the ...
1
vote
1
answer
69
views
Excel - put login times into neat hours
I have this info in an excel spreadsheet ..
representing the amount of time spent logged in to a computer..
logins time of day amount of time logged in (h:mm)
------ ----------- ---------------...
0
votes
1
answer
33
views
Excel VBA - Looping an IF( & VLOOKUP( starting with a partial string
Good day to an amazing community,
I am trying to develop a macro to loop through two arrays. Here is an example of how the data is set up:
Raw data is found in C4:C1500
$ data is found in D4:D1500
...
0
votes
1
answer
38
views
How to convert a simple VLOOKUP to XLOOKUP in Excel?
The VLOOKUP works as intended. The XLOOKUP is giving "#NAME" error:
screenshot of the formula:
the working VLOOKUP:
=VLOOKUP(C12,B24:C29,2,FALSE)
Which I'm reading as "take the ...
0
votes
1
answer
28
views
How to get an exact match with VLOOKUP into the current cell
The name "Dodson" is on A5, the lookup is from B5, the table A16:B19, the value in the second column of the table, but am looking for the precise result of a VLOOKUP.
The formula I have is:
=...
0
votes
1
answer
18
views
How to use conditional formatting based on a different column?
Looking to format a column background color using conditional formatting based on the integer value from a different column. Where or how is the second column referenced?
Bing AI, copilot, says:
...
0
votes
1
answer
28
views
I am trying to count values in a spilled array that are not blank or NA
I have a table where I have created a unique list using
=UNIQUE(IF(Name1="A",Name2,""),FALSE,FALSE)
The resulting list is a spillover array and has a blank space at the top (for ...
0
votes
2
answers
29
views
Want the difference between 2 columns to show as 0 and - based on certain criteria
I have a template file with more than 300 rows and I am preparing the difference between 2024 and 2025 in columns B and C respectively. The difference is in column D.
Not every row has an amount, ...
0
votes
0
answers
16
views
CountIFs Beyond
I am trying to compile a summary sheet that takes the Employee Name from Row 5, summarizes how many red (2) or yellow (1) are in any given month.
I have figured out the COUNTIF for it as a whole, but ...
0
votes
1
answer
44
views
How to change a cell colour based on input of 3 other cells
I am looking to create a conditional format for a name for when 3 columns (E,F,G) all display the value of "Y". I have tried following a similar query from within StackExchange but i get a ...
0
votes
2
answers
81
views
Best way to define a 2D lookup in excel using lambdas?
I'm surprised that Excel still doesn't have a simple function 2D_LOOKUP(array, row, col). For instance, I have a complicated excel sheet I'm managing and I frequently want to pull the value for a ...
0
votes
1
answer
25
views
How to use excel lambdas and/or array formulas to generate the cross-product of two arrays?
As a late entrant to using lambdas with excel, I"m extremely excited to see how they can simplify my company's sheets.
One area we would love to see simplified: we have a list of values that look ...
0
votes
0
answers
11
views
Editing Calc formula, when clicking cell, it does not enter cell reference
When I am writing a formula, I can type =A1+A2 and it works, but if I type = and then click on A1, the system does not put A1 in the formula bar. It just enters A1 in edit mode.
1
vote
1
answer
51
views
Excel: SUMPRODUCT and cells containing ""
In cell N30 I have this formula:
=(SUMPRODUCT(A2:B2;$A$29:$B$29)/SUMPRODUCT(--(A2:B2<>"");$A$29:$B$29))
This is a simplified version, hopefully it still makes sense. If the cells in A2:...