Forum Widgets
Latest Discussions
IF OR statements
I have a cashbook spreadsheet which in Col A shows money in, Col B shows money out, Col C shows the cost code, and Cols D, E, F, etc one for each of the cost codes. So, for example, if there's a cost of £100 in Col B and a "Gardening" cost code in col C then the value in Col B is copied over to, let's say, Col F which is the Gardening costs column. That's easy. In the Gardening costs column is F and there's a gardening cost in row 10, we have: =IF(C10="Gardening",F10=B10,"") But suppose there's money coming in, for example, a refund for gardening overcharged. This income would be shown in Col A. What I want to do is to repeat the above but add something along the lines that if there's an income in Col a, that should also be moved to the gardening column but as a negative amount. I've struggled to write this without success. any help greatly received! Thanks to the community. G.GreyNomadMay 15, 2025Copper Contributor30Views0likes2CommentsAutoSum Not working
I am having trouble getting my autosum working in the newest Excel on Mac. I have researched here and tried I think all the obvious - the column is set as a numerical cell, the formula looks right, but only the formula appears in the cell there is no calculationrobertreiffphotographyMay 15, 2025Occasional Reader18Views0likes1CommentNeed help with a formula
Hi all, I've got a problem with a formula I'm trying to use =XLOOKUP(A4:A540,(OFFSET('XLOOKUP page'!$B$4,1,COLUMN(B4)*2-4)):(OFFSET('XLOOKUP page'!$B$540,1,COLUMN(B540)*2-4)),(OFFSET('XLOOKUP page'!$A$4,1,COLUMN(B4)*2-4)):(OFFSET('XLOOKUP page'!$A$540,1,COLUMN(B540)*2-4))) The formula is supposed to do an XLOOKUP and when I drag the formula across it goes up in increments of 2 but however I try to do it I always get an error or the first column works and the subsequent ones dragged across don't work. Could anyone help please.AidenTayMay 15, 2025Occasional Reader7Views0likes0CommentsSpill formula with cell reference from lambda map
Hi, Hopefully someone can give me any advice on the following. I want to combine a spilled formula array with some logic to determine the label of each cell. That determination is done where the formula looks at the value of the cell itself and the adjacent cell. In orange I tried to combine the spilled formula but it returns blanks. Formula is as follows: =LET(tbl;XLOOKUP(A2#&"|"&B1#;tbl_Data[Nummer]&"|"&tbl_Data[PostingDate];tbl_Data[Status];;0); MAP(tbl;LAMBDA(a; LET(b;INDIRECT(ADDRESS(ROW(a);COLUMN(a))); x;AND(OFFSET(b;;0)="Uitgebracht";ISERROR(OFFSET(b;;1))); y;AND(OFFSET(b;;1)="Opdracht";OFFSET(b;;0)="Uitgebracht"); z;AND(ISERROR(OFFSET(b;;0));ISERROR(OFFSET(b;;1))); IFERROR(IFS(x;"Vervallen";y;"Nieuw";z;"");""))))) Anybody any idea what goes wrong? Many thanks, MichielSolvedMichielS340May 15, 2025Copper Contributor71Views0likes4CommentsWhy isn't my boxplot displaying the data I provided?
I tried a different method of grouping my data together to make my boxplot but there's nothing in the chart. Why isn't it showing anything and how do I fix it? Please help as soon as possiblesknnakjsdicjMay 15, 2025Occasional Reader24Views0likes1CommentUsing Filters to Auto-Update Complex Formulas
Hi, This is my first post here. Hoping someone here may be able to assist me. I have the below spreadsheet. There is currently 500 rows of data. At the Top is a Summary Grid with formulas summarizing the data below (Rating, Employee Level, etc. ) I'm also showing an example of one of the formulas in Cell F3. The other cells contain similar formulas. Ask: How do I get the Summary Grid to only display the data based on the rows visible, which is determined by Column T filter selections. For example, if the user selects "Level 4 Manager 2" and this manager has 10 employees, how do I get the Summary Grid to display just the data based on those 10 employees (versus the total 500). I've seen videos/sites that use the Subtotal or Aggregate formula, but it appears those can only be applied to specific functions, not a more complex formula. Is this possible?ebellezaMay 15, 2025Copper Contributor121Views0likes6CommentsA LAMBDA Word Search Puzzle
I've created a 15x10 Word Search. I've included a word list (dynamic range) containing words which might be found in the word search. The goal is to then extract those words when they appear in the word search. The catch is the words can appear 1 of 4 ways: horizontal left-to-right, horizontal in reverse, vertical, and vertical in reverse (No diagonals!). (The words currently in the puzzle are in blue for illustrative purposes) When I started this exercise, I thought of a solution involving SCAN and some text accumulation. There are a few issues with this method. SCAN would need to go through the puzzle twice (Can't VSTACK then SCAN) - once going through the range as normal then through the range when flipped (w/CHOOSECOLS and TRANSPOSE). The dimensions of each are not the same and it gets messy. Ultimately, the method I used starts by finding the position of words (Regular or in reverse) for a given row, pulling the words, filtering, splitting (Have to account for potentially multiple words in a line), stacking, and producing a list of words found. That's the short of it. You'll have to see the workbook. I'm interested in if SCAN can potentially make the solution a bit simpler.SolvedPatrick2788May 15, 2025Silver Contributor20KViews1like29CommentsWordle with Excel Solver
Amid the plenty of solutions and techniques that are coming for the newly viral word-puzzle game Wordle. I have also tried to make an excel-based solution for the puzzle. Because, being an excel enthusiast, I believe that Excel provides more flexibility and customization power than other analytical and problem-solving tools. The primary aim of sharing the solution with the respected members of this excellent community is to have different logic and alternative solving approaches that would surely provide some learning opportunities. The element of interest for the members is to model an approach that would produce more close and accurate guesses. For this solver, I have got inspiration from Mr. Robert Gascon, who occasionally demonstrates excellent features of traditional excel. Therefore the solver is a non-VBA / non-CSE model and does not require the 365 version of Excel. Thoughtful comments, especially from Mr. Robert Gascon Twifoo and Mr. Sergei Baklan SergeiBaklan about alternate solving approaches will be valuable. The solution of two real games along with the solution strategy is also attached for reference purposes. Thanks TauqeertauqeeracmaMay 15, 2025Steel Contributor9.1KViews4likes11Comments
Resources
Tags
- excel42,694 Topics
- Formulas and Functions24,771 Topics
- Macros and VBA6,420 Topics
- office 3656,050 Topics
- Excel on Mac2,658 Topics
- BI & Data Analysis2,380 Topics
- Excel for web1,927 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,642 Topics