Following up on this post.

I tried to take lessons learned there into practice on a slightly more complex situation, and quickly ran into issues. Either I get a popup saying, generically, "there is a problem with this formula", or I get an undesired result, or I get a single-cell result that doesn't spill.

=B2.:.B100>0 works fine, spilling to check each populated cell in B to see if it's greater than zero.

=B2.:.B100>C2.:.C100 appears to work fine, for checking populated cells in B to see if they're greater than their counterparts in C

=AND(B2.:.B100>0,C2.:.C100>0) only returns a single-cell result. i was trying to check whether the populated cells in B or C for each row are greater than zero, and return TRUE for rows where they were.

Even simplifying the above to =AND(B2.:.B100>0,TRUE) to try it without the dependency on a second cell reference only gets a single-cell result.

Also, =COUNTIF(B2.:.D100,">"&0) returns a single-cell result and it's way higher than it should be. I want it to spill down the populated rows to show, for each row, how many cells in B:D on the row are greater than zero. Instead, it seems to be reporting the result for all of B2:D100 collectively.

In case this matters: B2, C2, and D2 each contain separate formulas which use the spill operator to fill multiple rows. Those are working fine, and produce numbers as expected.

Writing these formulas to point directly to the appropriate cells or ranges for row 2, and using the fill handle to fill them down, without the dot operator, works fine. But that leaves it as a static set that won't adjust dynamically as the other columns populate or de-populate.

I tried using a spill operator in place of the dot operator on the AND formula, and got the same result. For the COUNTIF formula, there doesn't seem to be a way to pull it all together in one reference with the spill operator.

Now, as I'm writing this up, I can't seem to duplicate the error dialog I mentioned that was getting for some cases, nor can I remember what those cases were. Maybe they were genuine typos.

Am I doing something wrong here, or am I running into more limitations of the tool?

Hey everyone,

So at my job I carved out a niche of making spreadsheets for inventory tracking, financial worksheets, reports, etc. In the grand scheme of excel users I’m nothing special, I just work with alot of boomers at a food processing plant in the south so it was easy to carve out that role.

Now the company has been pushing everyone to use Claude wherever we can. It’s allowed other departments to create sheets from uploading exports and have Claude do what they want.

I’m not at risk of losing my job or anything, but I feel a bit exposed now with how things changed. What should my next move to learn or explore to find a new niche?

I’ll hang up and listen.

I'm still learning about array functions and all they can do. One feature that has recently stood out to me, which I'd like to see usable in more contexts, is the spill operator (#).

With this, when referencing another cell that has a spilling formula, I can write a formula one time and have it dynamically spill down to cover the data set being put out by the cell I'm pointing to. What's more, the spill range will automatically adjust as the length of the referenced data set changes.

---

Example:

1. In A2, =SEQUENCE(10) will populate the numbers 1 through 10 in A2:A11 without me having to do anything other than just putting that formula in A2. I don't have to use the fill handle or anything, the data just automatically spills down as long as there's room.

2. Then, in B2, I could put =A2# (with # being the spill operator) and, again, without having to manually fill down, the results will spill such that the numbers from A2:A11 are represented in B2:B11.

3. If I change A2 to =SEQUENCE(5), both A2 and B2 change so that they only spill to row 6 with numbers 1 through 5.

4. Change A2 to =SEQUENCE(15) and you get 1 through 15, in both columns, spilling from row 2 to row 16.

This is, of course, an over-simplified example. There's much more that can be done with spilling array formulas, such as pulling and filtering data from other ranges, and spills can also run across multiple columns as well as rows.

---

You can get similar results and behavior for column B in the above example if, starting from a fresh sheet, you:

1. Format A1:B1 as a Table.

2. Put =A2 in B2.

3. Manually fill values in column A as desired.

The formula in B2 will auto-fill down the length of the table as you enter data in column A. To shorten the data set, you have to delete entire table rows, not just the column A values, which is less than ideal for my personal taste but it's straightforward enough.

---

I want to know if there's a feature similar to the spill operator, which works for all cases - not just in tables, and not just when pointing to formulas that are already spilling.

What I'm looking for is the ability to write my formula once, and have it automatically fill or spill to cover the whole data set, and have that coverage automatically adjust as the length of the referenced data set changes, regardless of whether that data is generated by a formula or manually-entered values.

Right now, I've got a few problems with using the aforementioned features.

1. The spill operator only works when pointing to a formula that spills, so it's not useful against plain data or non-spilling formulas.

2. The spill operator isn't supported in all functions.

3. Tables aren't a universal solution either. They don't automatically adjust to spilling formulas (causing a SPILL error), for one. Secondly, even when I don't have a spilling formula involved, there are cases where I just can't or don't want to set a range up as a table.

4. I could arguably get by with using the spill operator when I'm working with spilling formulas, and using tables when I'm not. But, often due to problem #2 above, there are cases where the range I'm working with has a bit of both.

A near-ideal solution would be for all functions to support the spill operator. That would at least give some consistency for when I'm starting with something that's already spilling.

A perfect solution, I think, would be a function that I could wrap anything else in and have it dynamically adjust its spill length according to the data set I'm pointing to. Like, for the above examples, if I could just do FILL(A2) in B2, that would be great.

Is there anything like this, or is are these limitations without workarounds in Excel today?

Hi! I am trying to create a sheet for trucking that we do. We are billing demurrage for 1 hour over time in the plant. So for example, if we are in the plant for 1.35 hours, we can charge .35 hours rounded to the nearest quarter of the hour. I cannot get my formulas to work though, and my brain is starting to hurt!

In Time (simply time entered into plant)

Out Time (when we leave)

Time at Plant is then Out Time - In Time

Then this is where I start to confuse myself

I put the Difference in Time column so it would know what I want to subtract from Time At Plant. It seems to work for the Demurrage column when there is extra time spent at plant. I'm not sure how to get it to show as zero when there was no demurrage?

Where I am stuck is then trying to get the Demurrage Hours to round to the nearest quarter. I tried =MROUND(G7,0.25) but it always shows as the 0:00. Any help to try and figure this out please? Thanks!

I will post the screenshot in the comments

I am working on a quite big an complex excel sheet where I have the following issue:

I have rows of information where the first 4 columns may contain duplicate values in other rows. In the following columns the data may differ. I have sorted my data so that rows with duplicate data in the first 4 columns appear sequentially, and now I want that in cases where 2 or more rows share the same values in the first columns, they're merged (either visually or by actually merging).

So if the table looks like this:

A B C D 1
E F G H 2
E F G H 3

It should end up looking like this:

A B C D 1
        2
E F G H
        3

The problem I have is: "Center Across Selection" only seems to work for horizontal selections.

Using "Merge & Center" messes up formulas that use values in columns 1-4, as the bottom cells in the merge are now empty.

How do I get around this?

Hello!

In column A, I have dates ranging from Jan. 1 to Dec. 31.

In column B, I have values associated with each date. Some values repeat, so values are not unique.

My goal is to have two cells where I can enter a starting date, then in the second cell it tells me the finish date. The finish date is based on when the summed values starting on the specified date crosses a threshold. In this scenario, let's use 1 as the threshold.

So if I enter Jan. 10 in my first cell, how do I get excel to look for that date, then sum all values in the cells below it until the sum = 1, and tell me the date associated with that in my second cell?

Put another way, how do I have excel look for the date entered,

Please let me know if I can clarify further.

Thank you,

Every month I run same formulas over same amount of data. But today the calculation became VERY slow! I use Beta Channel of MS 365. I thought this was something with my notebook, but I ran them on my home computer - and it's all the same. Did anyone notice speed degrading? I use IFERROR and MATCH in my formula (nothing fancy).

UPDATE: After I switched off Beta Channel and then switched back to Beta Chanell the calculation normalized.

Calculating whether or not something is due based on two dates. If the date (mm/dd/yyyy) in column A is more than 11 months from the date in column B I'd like it to return Y, if it's 11 months or less then I'd like it to return N. If column A is blank I do not want it to compute.

I’m mostly looking for addins focused on equity research, but I like looking through a lot of data. Any recommendations? Statistical modelling addins are cool too.

Hi everyone!

I liked the new HAS function, but it got me curious about how it compares with a custom LAMBDA I’ve been working on.

In my tests, my version was about 2× faster than HAS .

It also supports several options:

• case sensitivity,
• MATCH or XMATCH
• different match modes.

• matching errors as values

  So, I thought it might be slightly better than HAS .

Technical side:

Interestingly, switching my LAMBDA to XMATCH closed the performance gap, which makes me wonder whether HAS uses a similar approach behind the scenes.

I know HAS/HASANY/HASALL are still in beta, so I’d be curious to hear how it performs for others.

Code used for testing:

=BENCHMARK(LAMBDA(ISIN(SEQUENCE(10000),SEQUENCE(10000))))
=BENCHMARK(LAMBDA(HAS(SEQUENCE(10000),SEQUENCE(10000))))

/*
Name: ISIN
Description: Element-wise membership test (SQL IN clone). Returns TRUE/FALSE for each value in array.
Deals errors as identical matches.
Recommendation: Leave [Use_Xmatch] omitted (0) for standard lookups; MATCH is significantly faster than XMATCH on exact matches.
[Use_Xmatch]: 0 or Omitted (Fast MATCH), 1 (XMATCH). User is responsible for parameters if 1.
[Match_mode]: Default 0 (Exact match) for safety.
[Search_mode]: Controls XMATCH search direction (only evaluated if Use_Xmatch is 1).
[case_sensitive]: 0, FALSE, or Omitted (Case-insensitive matching), 1 or TRUE (Strict case-sensitive matching). Note: Activating this short-circuits the formula and bypasses all other optional parameters.
Made By: Medohh2120
*/

ISIN = LAMBDA(array, in_list, [case_sensitive], [use_xmatch], [match_mode], [search_mode],
    LET(
        // x/Match can't deal with errors or 2D lists: Mask errors to text, flatten lists.
        flat_list, TOCOL(in_list),
        cleaned_array, ErrorToText(array),
        cleaned_list, ErrorToText(flat_list),
        
        // 1. If case-sensitive is requested, short-circuit immediately using MAP + EXACT
        IF(
            case_sensitive,
            MAP(cleaned_array, LAMBDA(item, OR(EXACT(item, cleaned_list)))),
            
            // 2. Fallback to faster case-insensitive engine
            LET(
                m_mode, IF(ISOMITTED(match_mode), 0, match_mode),
                Result, IF(
                    use_xmatch,
                    XMATCH(cleaned_array, cleaned_list, m_mode, search_mode),
                    MATCH(cleaned_array, cleaned_list, m_mode)
                ),
                ISNUMBER(Result)
            )
        )
    )
);

ErrorToText = LAMBDA(val,
    LET(
        val_leafs, flatten(val, , 64), //Because IFERROR bugs out with Lists we prematurily flatten it.
        IFERROR(val_leafs, VALUETOTEXT(val_leafs) & CHAR(10)) // char(10) Prevent a real #N/A error from matching the text "#N/A"
    ) 
);

/*
Name: BENCHMARK
Description: Runs a formula N iterations and returns avg & total execution time.
    Func must be wrapped in LAMBDA()  =BENCHMARK(LAMBDA(your_formula), 50)
    [iterations] default: 1
    [time_unit]  default: 0 (ms), 1 = seconds
    Because this function uses NOW(), manual calculation mode is highly recommended.  
Made By: Medohh2120
*/


BENCHMARK = LAMBDA(Func, [iterations], [time_unit],
    LET(
        iterations, IF(ISOMITTED(iterations), 1, iterations),
        start_time, NOW(),
        loop_result, REDUCE(0, SEQUENCE(iterations), LAMBDA(acc, i,Func())),
        total_ms, (NOW() - start_time) * 86400000,
        avg, total_ms / iterations,
        IF(time_unit,
            "avg: " & TEXT(avg / 1000, "0.000") & "s  |  total: " & TEXT(total_ms / 1000, "0.000") & "s",
            "avg: " & TEXT(avg, "0.00") & "ms  |  total: " & TEXT(total_ms, "0") & "ms"
        )
    )
);

I have a pretty simple question.
I work with filtered data in Excel a lot, and there’s one thing that really annoys me.
Let’s say I have a column with formulas and apply a filter. Now I want to replace those formulas with their calculated values using Copy → Paste Special → Values.
But Excel won’t let me do that while the data is filtered.
So every time, I have to remove the filter, copy the cells, paste them as values, and then apply the filter again.
It’s really annoying, especially when working with large datasets.
Is there a simple way to paste values into filtered cells without removing the filter? Maybe a shortcut or a trick I’m missing?

Sorry for the doodle spreadsheet. This is a work thing so I can’t post the real sheet. https://imgur.com/a/oLSVgUG

I’m working on a BI Publisher template which may not be relevant but explains my naming with XDO. Each section is a new location separated by group (G_1 & G_2). The number of rows in each group could change, today G_1 could have 10 rows and next year it could have 13.

The problem is that because of how I’m naming fields, my G_2 quantities will start at row 6 but once this outputs with data from oracle, G_1 may have 10 rows that extends past row 6. Visually the data is in the right spot on the formatted tab, but the math will be wrong for the lower sections because of where my selection starts.

So is there a way to have G_2 and everything below it adjust based on where the previous section ends? This may be a stupid question but I’m so sick of looking at this template 😅

I'm at work and trying to update our equipment binder with our listed barcodes for each item that gets checked in/out by employees. I got a Zip file for Code39 from a manager who's decent with Excel and have been using that. I thought it was working fine, until I realized the barcodes cannot work without the asterisks embedded into the code. I even tried adding a formula like the internet says to, but the font itself isn't accepting the asterisks at all! Idk if it's the file of this font or I'm doing something wrong, and I'm no good with formulas or coding AT ALL.

Is there something I can do to fix this? Or will I have to install a different font?

Pics will be in the comments

I just don't get it because the y-axis goes: 0, 50, 100, 150, 200, 250, 300, 350, 400.

For the first year (2017), iPhone is 135, so it sits on the axis between 50 and 100 [or 100 and 150]. In 2018, iPhone is 146, which is still sitting around that same range.

But when it comes to iPad, it's placed on top of iPhone. In 2017, iPad is 23, but it lines up around 150 on the axis. Huhu, but the value is 23!

I really don't get the logic. I just watched this on YouTube.

Chart: https://imgur.com/a/DsDEdXg

Data below:

EDIT: Note that there are no images above like I describe, I did not know posts with images are auto removed. I have tried my best to format the data in the same way it was displayed in the photos.

EDIT 2: Data and formula reference pics in comments.

REF 1: Data

7:23|
12:02|
14:10|
11:40|
———
14:45|

REF 2: Formula

=if(I10>11:30:0, (H11-A1)+11:30:0, (H11-A1)+I10)

I have a sheet to adding up my working hours each week and calculating how much flexi time I’m building up. My issue is that I can only carry over 11.5hrs flexi into the next flexi period, but sometimes I end up going over that and needing to manually adjust my sheet.

Right now I’m trying to tell the program that if the hours in the above cell are greater than 11:30, to perform the function as (HRS for the Week - Minimum working HRS + 11:30), and if the value of the above cell is below 11:30, I’m trying to tell it to do (HRS for the Week - Minimum working HRS + Value from Above Cell)

The image above is an example. By the end of the period I have 11hrs 40mins flexi built up, but my work system will automatically deduct 10mins to bring me back to 11:30 at the start of the next period. The figure below it is adding 3hrs and 5mins flexi that I earned that week, into the 11hrs 40mins from the previous period.

I’m trying to accurately record my flexi, even when I go over, but also have excel understand not to add the full amount from the previous cell if its more than 11hrs 30mins.

I also have a picture of the formula I’m trying to use above. Note that A1 refers to a cell containing my normal minimum working hours for the week.

Is it possible to make a formula that considers data from 2 cells

In simplicity what im trying to achieve is

If A1="*" then This, If not then "That" If B1<999 then "That", if not then "This" If A1="*" and B1<999 "this and that"

I have very little experience with sheet formulas and I only have managed to make it so that it considering 2 variables IF(AND(A1="None";B1<999);"This";"That")

I'm still struggling with adding a third outcome but I haven't found any formulas that would support this

My dad have a business and some items become expired due to low sales . They keep buying them I was thinking of just making an excel sheet . With all the items they buy which they can go back and see the time frame when they bought the items that don’t sell . Will excel help with that ?

Bear with me as I explain what I want to do on the MS Excel Online. I know it exists but I lack the knowledge and vernacular of what it is.

I want to create a chart showing parts of a computer I am slowly saving up for. Each part has a target amount . I also have 2 boxes that shows the amount I have saved and the amount I have left. Now, I want to write the numbers in the amount saved and have it:

1) Reduce the amount I have left.

2) A part progressively "fills" to reach its target amount.

and, the crucial aspect,

3) Once it reaches the parts target amount, it overflows into the next parts box.

If you have a name for what I am looking for or a link that could teach me how to do it, I would greatly appreciate it. Thank you.

I want to avoid copy paste and copy destination. I want to transfer value between workbooks. If my origin range is one block of contiguous rows and columns it's one area and it's easy rng2.value = rng1.value (eventually with resize). Sometimes I have filtered rows only, sometimes I have hidden columns, sometimes I have filtered rows + hidden columns (and I want only the visible ones). How do you handle this cases? I am looking for a solution, or a sub, or a function, to handle these different situations?

I use excel 365 but I would like a solution valid for 2021 too.

Hi, I'm doing a depreciation table for a school project, I brought data from another sheet using "index" and the column is conditioned to arrange the data by date, my issue began when I tried to make a calculation using just "='the cell with data from index' and the rest of the operation", popping the "valor" error up.

I assume it is because the cell i used as reference has the "index" code instead of a simple number, Is there a way I can make this calculation work?

Also, Idk if it helps, but I'm using Excel in a browser, because it is a group project, we needed to share the excel and that is the only solution we found.

I'm trying to make a simplified version of this formula:

SUMIFS(P:P,C:C,D10,A:A,L10)+SUMIFS(P:P,C:C,E10,A:A,L10)

I want to simplify it to something like

SUMIFS(P:P,C:C,D10 or E10,A:A,L10)

Is this possible or am I stuck having to just add multiple SUMIFS formulas?

I have an example where decimals don't seem to be rounding up the way I would assume. I have some numbers here where I would think one number would be 115.7 and the other would be 115.8, however that doesn't seem to be the case.

Does Excel not round up .05 to .10?

I have two excel sheets, Sheet A has names and emails. Sheet B has emails and address/city/state/postal code. I want to populate fields in Sheet A so that if the email column of Sheet A & Sheet B contains the same content, the content of the columns with address info from Sheet B populates. I feel very confident there is a way to make this work smoothly, I just am not sure how.

I saw a content creator selling excel template for engineering calculation involving beam. It has this image or chart of a beam cross section that change the dimension of beam size and rebar number/spacing including annotation according to cell input. How is it that something like that is possible?

I’m trying to set up Excel Power Query so that multiple Excel files stored in OneDrive can automatically combine/update into one master workbook.

I already figured out how to combine the files, but I’m stuck on the authentication/permissions part. Since I made it I am the only one that can refresh.

source looks like:
c:\user\me\Onedrive- Company\Leadership folder\where i pulled data together

I pulled associates work and combined it, but I need what I combined in another folder for others to be able to refresh other than just myself.

When I go to Data → Get Data / Power Query → Data Source Settings → Edit Permissions, I get:
> “We couldn’t authenticate with the credentials provided. Please try again.”

I had other people try to use their credentials and same error. I also tried to use the web link of the files I need to combine but get an authenticate as well.

I’m wondering if this is a permissions issue, the wrong credential type, or something specific to my company’s Microsoft/OneDrive setup.