All Questions
3,347
questions
-3
votes
0
answers
26
views
Excel - Way to map out charging info for a team that ultimately max's out at 40 hours and redistributes reduced funding to other assigned members [closed]
I'm looking for a better method of accomplishing my goal, I'm entirely open to a complete overhaul of what I have now.
I'm part of a team of 70+ members that support numerous programs, and even more ...
- 1
0
votes
1
answer
40
views
Excel trailing zeros
Given this formula in excel =TEXT(ROUND(R22,C13),"0.00")
How do I make so that "0.00" is calculated automatically depending on the number of digits inputted in C13.
Ex1
C13 = 3
and ...
0
votes
3
answers
68
views
I need an excel formula that if a number in cell A1 is 1,2 or 3 then put number 8 in cell A2. If not leave blank
I need an excel formula that if a number in cell A1 is 1,2 or 3 then put number 8 in cell A2. If not leave blank.
I tried a bunch of formulas but I don't know what this one would be called. Sounds ...
0
votes
0
answers
19
views
How to set time interval between automatic calculations in Excel
I have a spreadsheet which source data from external sources and many formulas depends on these source data. The source data are updating frequently (sub-second), but my spreadsheet is too heavy to ...
- 165
0
votes
0
answers
39
views
REF error in libreoffice calc, when trying to get address from another book
I need to get address from anoter book in LO calc. I do that in excell with success, but calc return me a ref error. I use 3 functions: match, index and cell.
calc
=CELL("address";INDEX('...
0
votes
1
answer
25
views
How to find the three largest values among cells in different columns in Excel in Portuguese?
I'm trying to calculate the three largest values among cells that are distributed across different columns in Excel, using the Portuguese version of the software. The cells I want to compare are in ...
0
votes
0
answers
29
views
IF V16 is greater than 0, does S5=D17? True do nothing, False highlight
I want to get a cell to highlight is the value in cell V16 is greater than 0. I then want it to check if S5=D17. If this is True, no action is required. But if it is False, I want it to highlight.
=IF(...
0
votes
1
answer
52
views
Weird hidden character in Excel that counts up in COUNTIF
I have an Excel sheet which is automatically created by an export of a time scheduler system. I want to count some columns content (to filter out all rows with no content). But weird: some cells ...
- 194
0
votes
0
answers
75
views
VBA - change cell formatting based on another cell's format
How to change cell formatting based on another cell's format?
I tried the following code:
Range("A9:A9").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FormatConditions.Add ...
- 1
0
votes
1
answer
35
views
Can VBA be used to set print area as a formula in a sheet?
I am using VBA to create sheets from a template and then creating named ranges scoped for each sheet created. Each named range corresponds to the print area that would be a page when printed. I also ...
0
votes
1
answer
47
views
Excel Formula Combine MATCH , INDEX, COUNT
I have 2 sheets. the first one called 'Employee' the second 'Database'
In 'Employee' sheet in cell 'AO3' I want to bring the sum of comment ' Meet Expectation' how much declared for 'x' employee based ...
0
votes
3
answers
53
views
excel get sheet name from address
i am trying to get the full sheetname dynamically from the address with the below formula in another sheet. is there a better way ?
address : '[Airport - Daily Burn Rate_2024.05.08.xlsx]HQ 2024'!$A$1
...
- 1,388
-1
votes
1
answer
391
views
Excel: Lookup '1' and return multiple values
This seems rather simple be cannot currently find a solve for this.
I am currently in a spreadsheet, which has a column that is returning a binary value on the basis of random sampling.
I need to ...
- 3
0
votes
0
answers
41
views
Excel: Countif with multiple criteria
Sheets("Create Summary").Range("B3").Value2 = _
Excel.WorksheetFunction.CountIfs( _
wsSourceData.[P:P], "Airports", wsSourceData.[AQ:AQ], "Not completed&...
- 11
0
votes
0
answers
11
views
In Excel, I use a dropdown list which populates cells. I have enabled multiple select in the ddlist, but now the cells do not populate?
I have a dropdown list on excel. When an item is selected it prepopulates cells using VLOOKUP from the table/dropdown list. Now I have modified the VBA to enable multiple selection in the dropdown ...
- 1
0
votes
1
answer
35
views
Excel compute of time duration based on criteria
I want to fill a formula in Column D that computes the status duration based on the agent on Column B.
Example: I need a formula that computes On Call duration on Cell D2 for Agent John from 8AM until ...
- 143
-6
votes
2
answers
53
views
Formula assistance
I am trying to create a formula and cant get it to work.
So -
IF x is >=1000 - 100 however IF x is <999 *80%
So the equation if I entered 1000 would equal 900, but if I entered 800 it would ...
1
vote
2
answers
85
views
Trying to get names from a box on the right to automatically appear in another box when a different box has a value in it
hope the title wasn't too confusing.
this is my project:
I'm trying to figure out how to get the names on the right (NICK-GL) to stack on top of each other at the bottom, when one of the mon-fri ...
user23626448
0
votes
1
answer
794
views
excel formula to find the first row that contains specific text
I am trying to find the cell address of the first row that contains the letters "H" "A" "B" "C" "T" separately.
at the moment I have formulas to find ...
- 33
1
vote
2
answers
253
views
Excel formula to compare date/time and report if it is less than 24 hours
I'm trying to create an excel formula to check the difference between cells C2 (opening time) and O2 (closing time) and add value within 24 hours or over 24 hours
INC6531515 4/03/2024 20:56 5/03/2024 ...
1
vote
1
answer
127
views
Automatically change cell reference in formulas, based off of cell updating
I have a couple of formulas that are currently being manually updated every time someone wants to change the row that the formula is referencing. The current formula is:
="Call raised for a "...
- 13
0
votes
0
answers
75
views
Averaging Multiple Ranges to ignore 0's and N/As
I am trying to make an excel formula that averages across multiple ranges. I've tried 2 different methods but neither have worked 100%.
The first formula I tried is: =IFERROR(AVERAGE(IFERROR(VLOOKUP(...
0
votes
2
answers
124
views
Calculating percent error of each element of a list given a list of ratios
On Excel/Google Sheets, I have a set of measurements (U to Z for this example) that need to match a set of target ratios (for this example, U to Z must follow a ratio of 2:1:14:7:1:1, so ideally W ...
- 23
0
votes
0
answers
82
views
VBA Formula Instead of FormulaR1C1
Following code works as intended. However I am trying to avoid copying the formulas in each cell inside my FormulaArray using FormulaR1C1. It slows down my data collection in my other array. Because ...
- 155
0
votes
1
answer
115
views
EXCEL- Trying to count specific dates between two but also in specicif Years. Excluding the days before or after the dates asked
I've been looking and searching for this for way too long. I made some dates and added some for tests which I will share.
I'm trying to count days between two dates, leaving it blank if a cell is ...
0
votes
1
answer
27
views
Can I Translate a Pasted Time Value in an Excel Cell So That It Is Stored and Displayed Correctly?
Whenever I input a time in the Excel cell it is not recognized. Given the custom format for the cell(s) that I am using, a recognized value inputted into the formula bar will be displayed correctly as ...
- 19
-2
votes
3
answers
58
views
Excel nested IF function [closed]
I'm having trouble with a nested IF function. I want to assign a "T" value to a cell based on its number value between a Max and Min number. The number resides in cells within Column H. ...
- 169
0
votes
0
answers
60
views
moved an excel file between two computers and curly brackets was added to formulas
Moved a file from my laptop to desktop. Laptop has Office 2021, Desktop has 2019.
Formulas like this:
=IFNA(INDEX(Earning!$A$1:$N$1000,MATCH(CONCATENATE(ROW()-ROW(G$2),"_",VALUE(E$1)),...
- 1
0
votes
0
answers
22
views
Excel: Is this possible: Extract last line from within file linked in excel?
I am trying to figure out if there is an easy way within excel to extract a line of text from a linked file in a cell?
Example: we are trying to track down the last user who used a computer, we have ...
- 41
0
votes
1
answer
60
views
How can I avoid or change the Error Format via VBA?
I have to export regularly from an external program an Excel table.
In this table, there are phone numbers in a list. Those exported numbers give me a kind of format error that can´t be fixed changing ...
- 159
0
votes
2
answers
77
views
Extract the number and increment the value using formulae
I am trying to compare the cell value and extract some number from it and increment that number. While as shown in figure, I want to increment 0 to 1, this formulae works.
=IF(AND(A1 >= "00&...
- 1,473
0
votes
0
answers
78
views
Excel Macro to show applied formulas as Text
I want to loop through all columns and check the row 2 which has the data to check if there are any columns with references to other cells or if there are any formulas in them. So, if there is no ...
0
votes
0
answers
64
views
IF Formula to multiply different criteria by different values
I need to write an IF formula so that anything less than 100 is multiplied by 7, and anything 100 and over is multiplied by 1.
I have tried the following formulas:
=G2*IF(G2>1,7,IF(G2>99,1))
=...
- 1
-1
votes
1
answer
58
views
Convert Complex Table to Simple Table
I have the below table:
I need my table to be formatted in either Excel or PowerBi to look like the below:
Basically, I need the date to become a column.
I am unsure where to start with this. Please ...
- 19
-1
votes
1
answer
56
views
DAX Calculation With Multiple Parameters
I am trying to calculate a daily net balance based on 4 fields.
1.) Balance from Previous Day
2.) Net Invoice Amount Current Day
3.) Payments on Current Day
4.) Adjustments Made Current Day.
In excel ...
- 11
0
votes
1
answer
277
views
How to apply/change color to partial text in a cell from the resultant excel formula in VBA?
I have this statement in excel cell B12 which is actually the resultant value of a very complex excel formula:
"● Up to January 23, 2024 - The national mean monthly temperature was 10.12 °C, ...
1
vote
2
answers
51
views
Copy a formula in Excel from one worksheet to another and have worksheet name in formula update
I have an Excel workbook with each months accounting sheet tabs, Jan > Dec. In opening debits, I have a formula:
=IF(JAN!D31>0,JAN!D31,JAN!-D31)
which says, if D31 is greater than zero, then ...
- 11
0
votes
1
answer
63
views
conditional formatting after selecting a cell from list
I want row of a table to get color after selecting a number in a cell of that row from list
I want row to have red color when we select 1 and blue color when we select 2 form dropdown list
thanks
...
0
votes
0
answers
31
views
What is a way I can copy over only cells that respond to a certain criteria (a string on both sides) to a different sheet in Excel?
I have a list of trades. They have their own info, etc. I want each trade to show up in its own sheet for data purposes with some kind of formula. Say I have 12 products, my entire list of trades ...
- 1
0
votes
0
answers
27
views
Conditional Format Date From Date In Another Cell
I have three date columns, Manufacture Date (B2), Inspection Date (C2), Future Inspection Date (D2), the Future Inspection Date column looks at columns B2 & C2 to determine the latest date from ...
0
votes
0
answers
51
views
Make auto fill form a part of table
I have a table (table1) then is my data table same as below
Table1
and I have a form then that have a table inside it same as below
Form
I need when I enter the "POD" from "Table1" ...
0
votes
0
answers
67
views
Dynamic array with VBA - Excel [duplicate]
I m using VBA code to import below formula to specific cell:
.Range("AA9").Formula = "=SORT(UNIQUE(FILTER(tblClients[Clients],ISNUMBER(SEARCH($C$2,tblClients[Clients])),""Not ...
- 8,116
0
votes
4
answers
130
views
Looking up Excel values based on sheets and rows
I was wondering if it's possible to look up values based on sheet names and certain columns inside those sheets? I was trying to wrap my head around that idea, but couldn't actually find a solution, ...
- 3
0
votes
2
answers
159
views
Excel 2007: AVERAGEIF, SUMIF, COUNTIF, MAXIF, MINIF across multiple sheets, multple rows
@MGonet - I am very much appreciative for the answer you gave, and also the idea to use /COUNT as an alternative denominator (which I have decided to do). I have run into two different problems, ...
- 3
-2
votes
4
answers
248
views
How to convert text string (d h m s) to time format in excel
I have a column of data with the below format and need to convert it to a time format by hours to use in calculations and filtering like hh:mm for the first entry, no need for seconds
1d 8h 8m 23sm, ...
0
votes
1
answer
131
views
Excel sum formula but with Criteria from multiple tables and columns
I have 3 Sheets / Tables in my Excel...
Table 1 (Master Stock):
Table 2 (SubLocation Rules):
Table 3 (Product Stock Summarize):
The goal in table 3 is To sum the Product Stock on Table 1 but with ...
0
votes
0
answers
33
views
xlookup to differentiate value "x" and value "x o" and return max date
I've an excel spread sheet that is looking through a list of names. Many repeat.
But to differentiate what a person has done, a " o" is added after there name at points.
I need the max date ...
0
votes
2
answers
54
views
IF Function 2 outcomes
I have a meeting reading sheet with opening and closing readings where I have a simple formula subtracting the closing from the opening to give a consumption reading. I need to add a purchases column ...
0
votes
1
answer
126
views
Excel Dynamic Search Formula
I am attempting to create a search function without using VBA and just using formulas for a contacts list. Any clarification on the breakdown of the formula would also help as I am new to using excel ...
1
vote
1
answer
164
views
WORKDAY.INTL not skipping holidays
There's clearly a mistake that I've made that I can't seem to fix. I'm making a table to outline dates for product information (as per screenshot)
My current formula im working with is
=TEXT(WORKDAY....
- 11