7,052 questions
1
vote
1
answer
35
views
Excel formula for Sumif and query generation
In my Excel file, I have two sheets. One is named Airjet and contains detailed data with numerous columns:
Column I lists all the months
column Z lists the yarn counts
I want to extract data into ...
1
vote
0
answers
129
views
How to set ListObject (Excel table) formulas with structured references without error 1004
I’m trying to insert formulas programmatically into an Excel table (ListObject) using VBA.
The formulas use structured references (e.g. [@ID], Table_Lookup[Column]) rather than standard A1 ranges.
...
0
votes
1
answer
66
views
How to filter data into another spreadsheet based on two values in the same column in google sheets
I'm making a book spreadsheet where I'd like formats to filter into a spreadsheet for physical books (Hardcover and paperback) and another for Digital books (ebooks and audiobooks). I can only seem ...
-2
votes
2
answers
67
views
Split randomly names into 6 different groups [closed]
is there any formula which will help me split randomly a dynamic list of names into 6 different groups?.
I would like each group to have as equal as possible amount of names.
Here is a google sheet ...
- 11
-3
votes
2
answers
100
views
Get Previous Month Date - Google Sheets
Using the formula =EDATE(A2, -1) to subtract one month from a date that falls on the 30th, when it reaches February, which has only 28 days, the formula returns the 28th. For the following months, it ...
1
vote
1
answer
66
views
Google Sheets QUERY with IMPORTRANGE: contains fails on alphanumeric IDs
I’m combining multiple sheets with IMPORTRANGE and then querying them. The issue is with Col3, which contains IDs like:
ABC10000000001
ABC10000000001
ABC10000000001
or
52685485
59521458
55152655
=...
- 611
0
votes
0
answers
108
views
Export data from SQL Server to Excel with OpenRowSet
I'm trying to export data from SQL Server to Excel with OpenRowSet.
The data does get exported to the Excel sheet datosOtraManera, BUT formulas are not automatically recalculated because Excel doesn't ...
- 1
0
votes
1
answer
50
views
Line-by-Line Formula Parsing and Exploration of Metaprogramming
I want to parse and calculate formulas line by line. The formulas depend on the given variables x1 and x2. The number of formulas per line is not fixed, and there are dependencies between the results ...
- 19
0
votes
2
answers
75
views
Microsoft Word formulae: How check if fields are valid before adding them in next calculation
I have a MS Word template that is used to produce Invoices.
It contains two table ('LabourTable' & 'PartsTable').
At the end of each table is a Total. Produced by the formula: {=SUM(above)}
...
- 1,382
2
votes
2
answers
186
views
Add interaction terms in formula
I would like to write an R function that adds interaction terms to a formula.
For instance, the function takes the formula mpg ~ cyl + gear + disp, the treatment variable cyl and a character vector of ...
- 5,364
0
votes
0
answers
46
views
Excel VBA - Apply complex formula within Formula Bar [duplicate]
Good evening,
Wondering if I can get some help with some VBA code.
In summary, I'm compiling multiple reports out of our ERP system into an overall summary table. However, as some of the information ...
3
votes
0
answers
238
views
R: very long formula -- can create, but not assign to variable
Here, genes is a vector of ~19,000 gene names. (I've removed any troublesome special characters from the names.) If I want to regress on all gene activity levels at once, well, I need them all in a ...
- 221
0
votes
0
answers
21
views
ZKTime 5.0 Reporting Issue
I have a time clock system with a report. The system is ZKTime 5.0. The report layout is as follows:
I want to exclude the first three from the report:
To do this, in the file column, I use the ...
- 223
-5
votes
1
answer
160
views
How to create an algorithm to find all combinations for a given formula (add 2 or substract 3)
What I have is the following 'formula' : < +2 or -3 > (for example).
One can 'add 2 to' or 'substract 3 from' a starting digit recursively to form a next digit and add that digit to the the ...
1
vote
1
answer
69
views
Count the Number of Weekends in a Period of Time in Tableau
Based on a single dimension called [codes], I have a [start_time] for each user that took the code (they can take the same code multiple times). The start times can repeat for every record as a user ...
- 11
0
votes
1
answer
142
views
How to find invisible characters in Excel
I have here an Excel file that checks the presence of:
long hyphen (–)
curly apostrophe (’)
ï (e.g., naïve)
Non-breaking hyphen (‑)
Screenshot 1
Screenshot 2
Cell formula after each header above ...
- 785
1
vote
1
answer
99
views
Follow up to Using ActiveX Checkboxes Conditions to check or disable another Checkbox that results in Formula
So this is a follow up to a previous question. That was answered wonderfully. However now I have a problem where I added another column and have specific formulas needed depending on certain ...
- 35
3
votes
2
answers
301
views
Non standard evaluation in formula argument
Problem
I would like to fit multiple logistic regression models in R for different values of i:
glm(mpg_20 ~ poly(horsepower, i), data = Auto)
My problem is that the call argument of the resulting ...
- 105
0
votes
1
answer
71
views
excel COUNT formula
I am trying to count how many instances of a specific number occur on one range of cells depending on the value of another range of cells directly left of the count range. I then need to multiply the ...
-3
votes
1
answer
54
views
Crystal report to show no period or comma
I have a numeric value 1,999.89. How do I format in formula to remove comma and point (decimal) that still show decimal place value. Result should be 199989.
1
vote
1
answer
87
views
How to incorporate INDIRECT to reference a tab name Into a Index and match formula in google sheets
I am trying to create a search box in a google sheet that finds the value of a cell from the time row and the date column in a specific tab
I have found the formula to use when I can enter the ...
- 13
0
votes
1
answer
279
views
How can I use VLOOKUP across multiple sheets in Google Sheets?
I'm trying to pull price data from a 'Pricing' tab into my 'Inventory' tab using VLOOKUP.
I want to match product IDs and return their prices, but I’m not sure how to structure the formula when the ...
1
vote
1
answer
66
views
ALL() function returns only non repeated values in PowerBI formula
We are studying Power BI and DAX by creating different scenarios to understand the results of various formulas. Right now, we are reviewing the ALL() function.
Let’s suppose we have these two simple ...
- 936
2
votes
2
answers
113
views
What formula does Gini() from ineq package use to get the Gini coefficient in R?
I'm writing a paper and I want to declare how I obtained some Gini coefficients using the Gini() function from the ineq package, but when looking for what formula Gini() from ineq uses, I do not find ...
- 51
1
vote
2
answers
129
views
How to transpose grouped values next to dates dynamically in Google Sheets?
I have a dataset in Google Sheets where dates appear in Column A and values (like item names) appear in Column B.
The data looks like this:
A
B
4/1/2024
Item A
Item B
Item C
4/5/2024
Item D
Item E
4/...
- 611
-3
votes
1
answer
102
views
Countifs with and/or not working on multiple criteria
I am trying to get a countifs formula to work where the final criteria is an 'or' statement.
The formula looks like this but is not returning the correct value.
=sum(COUNTIFS('Source Data'!A:A,"...
- 51
0
votes
2
answers
85
views
Google Sheets Map LAMBDA function
I have the following data.
I need a formula to calculate how many complete shots have been completed by Dominic for example. I have the following formula:
=SUM(MAP('Shot List'!A:A,'Shot List'!D:D,'...
-1
votes
1
answer
162
views
Countifs for another tab and with multiple selection
What would be the Google Sheet formula?
In the sheet "Employee View," I want to count the number of times the value "Camilla" is included in Column A in the sheet named 'Projects ...
1
vote
0
answers
26
views
Is there a way to lengthen an arrow without stretching it?
Ran across this problem while writing a nuclear decay formula in LibreOffice Writer. Using widevec{~~~~~~~~~} makes the arrow appear stretched. Is there an alternative to widevec, to make an arrow ...
- 11
1
vote
2
answers
92
views
Returning Max Value Using Vlookup with Multiple Results
Trying to work on a VLOOKUP formula for a calculator that I am working on that returns the highest value from column B where columns A = True. Unfortunately what I am getting with my current attempts ...
- 421
3
votes
1
answer
96
views
Mandelbrot coordinates to Julia set
I was working on a Mandelbrot set at first, then added a Julia set to the same window. The problem is I don't know the formula for my exact situation. Also, the Julia set slid to the bottom-left side. ...
- 255
-1
votes
1
answer
124
views
How to skip calculating a cell if its left cell has an error value
I have a raw data here.
A
B
C
D
E
F
Name
Score
Complexity
Points
Total with Points
Total without Points
Tom
5
3
0.25
105.00%
100.00%
Brenda
5
4
0.5
110.00%
100.00%
Mark
5
#VALUE!
100.00%
L
M
...
- 785
1
vote
2
answers
224
views
How to highlight lowest and second lowest values in a row, including duplicate values and ignoring zero
I'm trying to create conditional formatting for range K6:AF78 to highlight the lowest value(s) in each row green, and second lowest value blue. The problem I'm running into is when there are duplicate ...
- 13
1
vote
3
answers
115
views
Is there a way to extract the text from a cell by referencing the cell?
Transaction Data
Example Transaction Data
Header
1.0 X Cold Drinks - Events (Coca Cola)
1.0 X Cold Drinks - Events (Energade)
6.00 X Cold Drinks - Events (Still Water)
1.0 X Cold Drinks - Staff (Still ...
0
votes
1
answer
79
views
Parallax effect in Js: Is there a formula for the scroll modificator so the background image and the website content always reach the bottom together?
let me start by wishing you a wonderful day!
To summarise my website: The body is made of a header and a main section. The main section has a background-image and elements. Those elements's (combined) ...
- 1
0
votes
0
answers
35
views
YoY in Power BI, without doing the SUM of the entire Year?
I know that this YoY question for Power BI is asked a lot but in my case. The value in the data is just the total for each month, which means that when the Year Ends the real value of that year is ...
- 1
0
votes
2
answers
75
views
Is there an Excel/Sheets formula to pull a value from the last row of a table that keeps growing?
I'm building a safe counter on Google Sheets to calculate the required banking order after the safe is counted.
I've got a Google Form to enter in the safe count, and that feeds into a Google Sheet, ...
1
vote
1
answer
77
views
INDEX MATCH Function Inconsistent
I have an excel table (Sales Table) that uses an INDEX MATCH function within a few columns. There are 2 specific columns (Product and Part Number) which are using a material number column to pull ...
- 37
0
votes
2
answers
93
views
convert a table of name/value pairs in a spreadsheet into name( value1, value2, ...) strings
I have table of contestants and events:
Row
Contestant (Col B)
Event (Col C)
4
Harold
1500M
5
Alice
1500M
6
Eve
400M
7
Alice
400M
8
Alice
100M
9
Eve
100M
10
David
100M
11
Flynn
100M
12
Greg
1500M
13
...
- 65
-1
votes
4
answers
74
views
Formula needed to solve the following pattern/list in excel
What formula could i put starting with cell A2 that would create the following computational list of numbers all the way down. I want to start with a number then in cell A1 and then in the cell below ...
- 11
1
vote
2
answers
76
views
Convert each name and corresponding data into separate columns
I have this data in google sheets and the name can be more or less based on the data I want to convert it into separate column.
Name
Note
Josh
anything1
Mike
anything2
Peter
anything3
Nicole
anything4
...
- 611
0
votes
1
answer
68
views
Writing a formula that looks at multiple open tickets against systems
I am trying to write a formula that looks at a register of tickets that calculates the length of time the system was down if multiple tickets were open at the same time.
For example
Ticket 1 for ...
- 29
0
votes
1
answer
75
views
Using AVERAGEIFS with multiple criteria in separate tabs
None of the existing questions/answers seem to answer my question, so hopefully there's someone who can help me solve my issue. I'm not super familiar with all of the Excel functions, so forgive me if ...
- 13
0
votes
1
answer
64
views
Excel: How to lookup different values with the same criteria [duplicate]
Ex:
Sheet1 - Kollekte Ontvang
Column 1 Column 2 Column 3
Date ProjectCodes Amount
01/11/2024 G172 1000
01/12/2024 G172 ...
0
votes
0
answers
33
views
Formula in VBA not match in may range [duplicate]
Please Help me for this Script on VBA Excel:
Range("A7").Formula = "=SUMPRODUCT((Table_Query_dBF_Monde[INVOICENO]<>"")/COUNTIF(Table_Query_dBF_Monde[INVOICENO];...
- 1
0
votes
0
answers
30
views
Syntax highlighting disappears in Alteryx Formula Tool when completing an IF statement
When I type the last variable or ENDIF, the formula loses code recognition and highlighting. This issue also occurred with IF statements, particularly after typing ENDIF. Without highlighting, my code ...
- 1
0
votes
3
answers
109
views
google sheets formula to count years and year ranges presented different ways
I'm looking for an array formula that can count the number of years in each cell from a single column. Here's a link to a shared google sheet showing the input data in column E and the desired output ...
- 41
0
votes
0
answers
31
views
Combine tables into one table using TRANSPOSE with OFFSET [duplicate]
In Excel I have many little tables with same (vertical) headers but different content. Im trying to use TRANSPOSE with OFFSET but I cant make it work. Any help on how to achieve this?
Those 3 already ...
1
vote
1
answer
49
views
creating a Instrument variable formula form formula object
Given a formula object like:
obj1 <- formula(y~x1+x2+x3)
I want to create a formula for an instrumental variable approach ( e.g.: aer::ivreg), replacing x1 by z1.
I want to achieve obj2 <- ...
- 503
0
votes
1
answer
52
views
How to add a zero in the middle of this PNK103 should end up as PNK0103: excel formula working with "," not ";"
This is an employee number. I need to add that missing zero as it's messing with my column sorting and other formulas I have running from this column. Have been struggling with this for hours but ...
- 3