Forum Widgets
Latest Discussions
Simplifying cost calculation using array instead of IF statement
Hello, I am in the process of calculating the cost of refining precious metals based on user input of specific parameters. For example, if a certain dore intake of Silver has 90% Silver (Ag) content then lookup the specific processes and multiply the cost per oz with the intake ounces. I have attempted to combine IFS and Xlookup for each process separately but the formula looks very unwieldy. I am also enclosing a slightly simpler formula of IFS and sum where the total cost is calculated in one cell (Q12). Here is the link: https://docs.google.com/spreadsheets/d/1hizmF6EwhxOPEeR10bXJsBOKeOtXude8/edit?usp=drive_link&ouid=103354753371375324640&rtpof=true&sd=true I am looking to see if I can have a more dynamic iteration of the formula in Cell Q12 as well as in the calculation of the individual processes in Row 4 , Cols P:V. Thank you. Regards, Shams.SolvedShamsMDec 30, 2025Copper Contributor80Views0likes5Comments
Move repeating columns into rows
Hello guys, I have a set of data that looks like this: Name Hours Date Hours Date Hours Date John 3 1-Jan 4 5-Jan Ann 4 4-Jan 2 8-Jan 2 9-Jan Each Hours data cell have a comment in it, and I'm trying to turn it into something like this: Name Hours Date John 3 1-Jan John 4 5-Jan Ann 4 4-Jan Ann 2 8-Jan Ann 2 9-Jan Is there a way for me to do that while retaining all the comments in each Hours data cell? I'm using Excel 2016. Best Regards, JohnSolvedJohn5Dec 28, 2025Copper Contributor122Views1like4CommentsIndex and match mystery (for me that is...)
Hello, I am having a hard time with an Index and match function I created. It's very simple but I am overlooking something. I am creating a file in which I can keep track of my golf progress. In golf you use a handicap system. This means that based on your formal handicap you get a number of extra strokes on a course. For instance: The formal handicap of a player is 21.3 Ranges: From to Course handicap additional strokes 16,4 19,4 3 19,5 22,5 4 22,6 25,7 5 In this example the player is awarded 4 additional strokes based on that 21.3 falls in the range from 19,5 to 22,5. I've created a function but it doesn't always seem to work...It does return a value but not always the correct one.... =INDEX(C21:C36;MATCH(C48;A21:A36+(C48<=B21:B36);1)) In cell C48 people can fill in their formal handicap. In cells A21-A36 the 'from' values of the range In cells B21-B36 the 'to' values of the range In cells C21-C36 the extra strokes for the course handicap What am I doing wrong? Merry Xmas!!!! :-)SolvedeacrusherDec 25, 2025Copper Contributor151Views0likes7CommentsIf Condition incorrectly evaluating flat file Date Column
Hello, I have the following file that has the Date field coming from a Red Hat Linux environment and comes into excel as a.csv extension. I saved the file as an excel workbook and am trying to evaluate a IF statement where the Date will be ignored if the date in the cell is >Nov 2025 or <April 2025. Even though I have changed the format in Column A to be a Date and custom format of "mmm yy" it is still not evaluating properly. I have gone ahead and created a test worksheet in this workbook and typed in sample dates and this time the IF statement evaluates correctly. How do I then either a) mass convert the native Date Column to a Date format or b) create a helper Column B pointing to Column A: (text(A2, "MMM YY"), copying and pasting as values and then changing it to Date format with custom date convention of mmm yy. Here is the link to the file: https://docs.google.com/spreadsheets/d/1zYTGXGtvwCDWhhZp6yoDfnyH4zSDTYAk/edit?usp=drive_link&ouid=103354753371375324640&rtpof=true&sd=true Is there a workaround to the Date column so that excel evaluates is as a true date (serial) data? Thank you. Regards, Shams.SolvedShamsMDec 22, 2025Copper Contributor48Views0likes1Comment
PivotBy or GroupBy
Dear Experts, Quick question, I have a data like this from Column A~F, and want to make a data like in Col J~L So, I want a summary , that for the slot0 && txnum==1, what are the unique values of numOfPrb, but in case of slot5, we have more than one unique value and it spills, could you please suggest a solution for this, I tried Filter function etc.. may be there's solution within , or if Groupby or Pivotby can help here. Attached is the worksheet, Br, AnupamSolved110Views0likes1CommentCan't print this document! Print preview not matching Page Layout or Page Break Preview
I'm trying to "print" this to PDF, but can't get all my tables and graphs to fit nicely when printing This is my view in Page Break Preview This is my view in Page Layout This is the Print Preview screen, I think when it's scaling the rows to fit on one page, it's not scaling the graphs as well so they keep moving around. How do I make this look nice when I print? I have 16 tables with graphs plus a footer, so they aren't going to fit all on one page, that's why I set it to print over 4 pages.SolvedtuckerbDec 19, 2025Copper Contributor77Views0likes2Comments
Sumifs with Custom Excel Data type from power Query and using dot notation
I am just experimenting with Custom Excel Data Types and dot notation. I was trying to come up with a equivalent to sumifs without any luck. In my example I use fake data and I am trying to summairze charges by team color and department Here is a drop box link to my spreadsheet Here is the expected outcome Yellow Red Green Purple Orange Red Feet 1,384,281 1,067,303 884,288 1,112,979 1,005,634 1,167,165 Hands 1,267,428 1,262,445 827,956 963,616 1,041,856 902,571 Hip 946,395 948,135 955,020 799,842 1,014,142 829,546 Knee 991,524 1,072,020 953,689 1,139,318 1,218,487 1,001,327 Spine 933,123 1,373,616 910,488 795,726 860,861 1,019,545SolvedBALDACCOUNTANTDec 18, 2025Copper Contributor83Views0likes4CommentsNon-Consecutive Cell Referencing
Hi, folks. I'm attempting to create a spreadsheet that contains links from consecutive cells to consecutive cells in another worksheet that are separated by 5 intervening cells. I'll call the original consecutive spreadsheet "Orig" (for original). So, I know that if I put "='Orig'!A3" in cell B3 and then copy that down, it will update the relative formula consecutively, i.e. B3='Orig'!A3, B4='Orig'!A4, B5='Orig'!A5, B6='Orig'!A6.... that much I get. What I need to do is find a way to do the same thing, but to increase the resulting link.....so that if I copied the formula down column B I would get: B3='Orig'!A3, B8=Orig'!A4, B13='Orig'!A6', etc so that the new worksheet is moving down 5 cells relative to the Orig sheet consecutive order. I've read where someone used a formula using the INDIRECT function but that's beyond my beginner level. Many thanks, and merry Xmas to all!SolvedMattKW1Dec 18, 2025Copper Contributor258Views0likes17Comments
Calculate overlapping hours
Hello, I need to report how many hours a staff member supervised one or more volunteers. For a very simplified example, Volunteer Name Date Start Time End Time Supervisor Fred 1/1/26 1:00pm 3:00pm Lucy Ethel 1/1/26 2:30pm 4:30pm Lucy Here 4 volunteer hours were served, but because there was a 30 minute overlap, Lucy only supervised for 3.5 hours. Is there a way to get Excel to calculate that? To say: look at all the entries with matching date and matching supervisor, and add up non-overlapping time. I'm not expecting this to be possible, but I thought I'd ask. Thanks!SolvedvolunteersfplDec 17, 2025Copper Contributor346Views1like13CommentsLogical test for same text string existing anywhere in both ranges.
Hello. I have a Table of film credits, including the names of directors and writers. Some films have multiple directors (up to 3 individuals), whose names are in columns F, G and H. The writers' names (up to 4 individuals) are in columns J, K, L and M. I want to test for whether the film has a writer/director - e.g, one of the director names in the range F:H is the same as one of the writer names in the range J:M. I have created a column O to contain a formula with a logical test returning Y if there is a writer/director present. I tried =IF(Table4[@[Wri1]:[Wri4]]=[@Dir1]:[Dir3],Y,N) but this returns a spill error. Can anyone help?SolvedExtopiaDec 17, 2025Copper Contributor163Views1like10Comments
Resources
Tags
- excel43,481 Topics
- Formulas and Functions25,204 Topics
- Macros and VBA6,527 Topics
- office 3656,243 Topics
- Excel on Mac2,704 Topics
- BI & Data Analysis2,455 Topics
- Excel for web1,988 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,680 Topics