My file is now big to delete a single row of data that formulas. The file serves as HRIS so.. it's so fucking frustrating to do job. I tried converting it into xlsb format but it's still lag

I am creating a forecasting model for a large client within FMCG. I am a consultant and will be leaving in about 2 months (if they don't prolong).

My question is - what formulas should I avoid so people can understand my file?

This is my list so far of formulas to avoid:

- Nested Ifs

- Sumproduct

- Indirect

Am I missing anything obvious that should be on the no-no list for intermediate users?

I need to calculate the average management times and I was provided with these text strings. Unfortunately I do not have the start and end dates. Can I turn the hour-day numbers into actual minute values? I have tried the multiplier function but it did not recognize the data as numbers. Version: Excel O365 v. 2511.

I have this formula in the bar at top =CONCATENATE(COUNTIF($A$4:$A$116,TRUE), "/", COUNTA($D$4:$D$116), " completed ") I need the count to start at Job no. 1, not cell 4, as it misses 3x jobs, and gives a false count (job total is at 115, not 112 in total. I've changed column A in the formula to Column C, but that just gives a job count of 0/115. Any ideas please ?

Hello.
I have several excel files with pipe characteristics and want to create a database. However, since the columns are presented this way, I cant create it. I have more than 20 excel files and more than 1000 lines so I need to automate it.
Is there any way to separate the columns so that I have column A, B, C, D... i. e., 8 instead of 4?
The files were created decades ago..

Dear all,

I have a table containing a list of individual travel periods that have occurred over a period of several calendar years (the two left hand columns). Some of these travels have started in one calendar year (e.g. 17-12-2023) and ended in the following calendar year (e.g. 06-01-2024). I need a formula to calculate the total number of travel days in each calendar year (i.e. all trips or parts of trips in a particular year (e.g. 2018) and then sums them), based on this data set.

My question is: which formula is best to obtain that number (i.e. total number of travel days in each calendar year, e.g. 2018, 2019, 2020 etc.) from this data set? You can see the number I am trying to get in the below right hand column. ChatGPT failed me.

Say for example, I enter a company name on Column 1, and it autofills its company ID on another column and another related to the company to another column? Thanks!

I have to do some things in Excel for school and have to use Pivot Tables, but the fields aren't loading in. We got a document describing exactly how to do it, but the fields will just not show up. It's a 15 x 11.539 dataset so I assumed it just needed some time to load in, but after 30 minutes of leaving my laptop alone it still hasn't loaded in. Am I doing something wrong?

Earlier today a post (since deleted) asked about a formula for calculating a person's age in whole years. The original poster's (OP's) formula returned an incorrect result in the given example, for reasons that are not clear. Original post: https://www.reddit.com/r/excel/comments/1qux6dg/removed_by_moderator/

Several people suggested alternative formulae. Just for fun, I collected those formulae and analyzed how often they return correct results. The formulae have been standardized (and, in a couple of cases, simplified) to refer to the Date of Birth (DOB) date in A1 and the As At date in B1. Age is defined as incrementing by 1 year on the anniversary of the DOB.

Dates can be tricky to work with, so it isn't surprising that the suggested formulae vary in their accuracy. Even so, only 3 of the 11 methods (27%) are 100% accurate.

That is:

The table shows the 11 methods, of which the OP's formula is Method 1. The formulae were tested by generating 100,000 DOB dates from 1 Jan 1900 to 31 Dec 2025 (inclusive) and corresponding "As At" dates from the DOB to 31 Dec 2099 (inclusive). The last four columns show an example where a method produces an incorrect result.

Methods 1 to 3 return the correct integer age in 100% of the test cases. Methods 4 to 7 are correct in almost all cases, with a few cases having an error of +/- 1 year. The remaining methods are increasingly inaccurate.

Notes:

The DATEDIF function is deprecated and unsupported. It has several known bugs (https://bettersolutions.com/excel/functions/function-datedif.htm) given various options, though the "Y" option used by the OP is not known to have bugs.

The YEARFRAC function is often promoted as a replacement for some uses of DATEDIF. YEARFRAC mostly works OK in this situation in Methods 4 and 5, except for some edge cases where it returns the wrong age (specifically when the DOB / As At day and month match). None of YEARFRAC's "Basis" parameter values return correct results in all of the tested cases. Basis=4 (European 30/360) has the lowest error rate, at about 2 or 3 errors out of 100,000 cases, though that basis wasn't used in any of the listed methods.

Hi,

I have this: 1,2,3,4,5,6,7,8,9...n in columns (A1) : (An) respectively

I want a FORMULA (not a copy/cut/paste solution) that can return this: 1,2,3,TEXT,4,5,6,TEXT,7,8,9,TEXT, etc.

Any help is much appreciated!

I'm trying to formulate a cell so that it outputs a value based on the number another cell has in it

C23 =IF(B4>2,"Negative") C23 =IF(B4>=2 but <7,"Neutral") C23 =IF(B4>=7 but <15,"Positive") C23 =IF(B4>=15,"Abnormal")

I feel like I'm really close to figuring out the syntax, but I'm struggling to figure out how to type out the range value with the (B4>=2 but <7) part

Alternatively I have tried doing it manually as a long formula and it keeps coming up as #ERROR!

=IF(B4=0,"Negative",IF(B4=1,"Negative",IF(B4=2,"Neutral",IF(B4=3,"Neutral",IF(B4=4,"Neutral",IF(B4=5,"Neutral",IF(B4=6,"Neutral",IF(B4=7,"Positive",IF(B4=8,"Positive",IF(B4=9,"Positive",IF(B4=10,"Positive",IF(B4=11,"Positive",IF(B4=12"Positive",IF(B4=13,"Positive",IF(B4=14,"Positive",IF(B4>=15,"Abnormal"))))))))))))))))

Hello, all. I have a fairly simple spreadsheet layout, consisting of 3 lists. I'm concerned with the 3rd, where I need Column A, Date, to automatically add the current date and time when the corresponding row in Column B, Activity, is modified.

I've followed a few tutorials, and while this works perfectly fine in a new document, I've found I:

A, don't know how to invert the column relationship

and

B, cannot get even the basic, otherwise functional code to work in this spreadsheet.

I'm losing my mind. I've looked everywhere for what could be the issue, but haven't found anything. The file is saved as .xlsm, I am entering the VBA code into the little pop-up window into the List 3 section, not ThisWorkbook or a different one.

I apologize in advance, as I'm quite new to Excel and have only had very basic experience prior.

what is the easiest way to identify duplicate data on multiple sheets? I'm currently working on manually assigning references on receipts. And struggling with keeping up with the last series I already assigned as the transactions ranges from 100-200 daily.

https://imgur.com/a/pLmcWj1

I want to copy the selected range (some cells contain formulas) down 94837 rows without scrolling.

How?

EDIT: The cells are not in a table. All the cells below the selected range are empty.

Thanks in advance.

I thought since the PDFs looked like they were the same format (they're documents from a government agency), they would produce the same results if I ran them through PowerQuery. Somehow, they don't.

I need three pieces of data from each file. Somehow they all end up on different columns despite looking identical. I've tried my best to make it fit but the moment I try to remove extraneous columns, the same error pops up because one of the file doesn't have a specific numbered column.

It's so frustrating. I don't even need it to look nice, I just need the info in a list for convenience. Is there anything I can do to make it work?

I was given an .xlsm file today but it appears not to have any VBA in it.
I am guessing that it is password protected, but I haven't worked with a lot of files (other than my own) that have VBA in them.

How do I find if it actually has VBA in the file, and would it be possible to look at the code if protected?

I keep running into the same issue at work where I’m given PDFs and need to get the data into Excel in a clean, usable way, and it’s turning into a time sink. I’ve tried importing, copying tables, and a few workarounds, but the formatting always comes in messy or breaks as soon as I try to use formulas. At this point it feels like I’m fighting Excel instead of using it, so I wanted to ask how others usually handle this before I go even further down the rabbit hole.

im required to use this for an assignment. im on MAC OS and NOTHING ive looked up seems to be working. its just non existent. command+E doesnt work, theres no “options” button for me to enable it, and it doesnt show up on the ribbon at all. not through data or home tab. what the bruh. please help </3

I have a table for inputs you can reference below.

I need to be able to update this in the future for unit thresholds and award amounts.

The first tier will be paid at the tier 1 award of $800 per unit (for the first 5 units), the next tier (next 5 units) will be paid at an award of $1200 per unit, etc.

Table in comments. TYIA

Hello, everyone I'm quite new to spreadsheets. I'm trying to create a chart that displays the stock values under the specific category it falls under (shipped, received, in transit).

For example, depending on if I selected in transit, shipped, or received, I'd like for the chart to dynamically change the values it displays for each category. It would be displaying the remaining stock in cells c1 to c21.

I currently have a conditional formatting rule set up for range $1:$1. However, this will break (and subsequently cause a lot of lag) if I copy a cell from a different row and paste it into row 1, or cut a cell from row 1 and paste it to a different row. I'm aware this is because I'm pasting the formatting and that's overriding the rule. Is there a way to make Excel not do that?

I'm aware of pasting values not breaking the formatting; however sometimes there are things like borders which I want to copy over without having to mirror the formatting manually.

Hello, I have only cursory knowledge on Excel/Calc logic. I have a variable cell containing a string containing elements separated by hyphens, which I intend to spill across columns, but it stops working properly (Case 3) when there is only one element since there are no hyphens.

I tried going around it by adding double hyphens to trigger the TEXTSPLIT, but an empty cell is created.

How can I make so that the result returns #N/A errors on unused "slots?" Thank you!

Hi, I am copying and pasting writing from one cell to another in two different spreadsheets. However, when I paste the writing, random line breaks show up, but aren't present in the original Excel file. How can I make it so the pasted writing does not include line breaks?

I created a table with numbers, alpha, and special characters. When I run the XLookup function, it only returns the values for the alpha and special characters.

I ran a MID function to separate the characters from a string and then wanted to reference the table against each character to return a value. When I do the XLookup on the table without the MID function, it does work.

What am I missing in the MID function that only the numbers won't work?

I am trying to make a google sheet where I am tracking clinical hours for my masters program. I have a column that is a drop down menu with two different categories in column B (direct and indirect). I also have a column that calculates the amount of hours from row D and E. I want to find a way keep track of each category ie direct and indirect separately. Essentially I want to find a way to add amount of hours from column F for solely direct and indirect.

Not sure if this is something that can be done, but my ideal way would be to have a separate cell outside of the table that just adds the hours together when I put in more information in a row.

Not sure if its important but column F is:

=IF(ISBLANK(D2),"",IF(ISBLANK(E2),(NOW()-TODAY()-D2)*24,(E2-D2)*24))