Forum Widgets
Latest Discussions
Different functions for each column in a subtotal
ive got a sheet where i want to subtotal using different functions in different columns. i will always have different data every time i want this to execute, so cant hardwire the row range, also each group (based on date) will have different number of rows. Eg, on change of date, sum column B, count column C, max column D. I hope i explained the problem adequately.
Chat with Copilot popup on every Excel launch
An ad for Copilot shows up every single time I open Excel. Clicking Not Now closes it, only for it to reopen again when I open Excel later. Is there any way to disable this or remember my selection each time? It is frustrating to have to deal with it all day long. I do not have a Copilot tab in File > Options. Optional connected experiences is already disabled. Microsoft® Excel® for Microsoft 365 MSO (Version 2511 Build 16.0.19426.20218) 64-bit Windows 11 Pro 25H2
Why can't I post my reply to this thread
Hi, Why can't I post my reply to this thread: Calculate hours using pivot table | Microsoft Community Hub I have tried several times so far to post my reply and at first moment it seems to be accepted because the page displays my replay, but after refreshing the page it "disappears", meaning it no longer exists. Anyone have any ideas? Thnx.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.
Need assistance to correct a formula
I am using the following formula to calculate weekly hours. I want to change it to calculate the hours with the starting on Monday going to Sunday and display the result in column G on the Sunday. For example - calculate totals from Monday Jan 6 to Sunday Jan 12, inclusive. Thanks in advance for your help. =IF(WEEKDAY(B6)=7, IF(SUMIFS(D:D,B:B, ">="&B6-6,B:B, "<="&B6)>0, SUMIFS(D:D,B:B, ">="&B6-6,B:B, "<="&B6), ""), "") A B C D E F G 1 Date Hours Purchases Rate Daily Cost Hrs / wk 2 1-Jan Wed 5 $20.00 $100.00 3 2-Jan Thu 5 $0.00 4 3-Jan Fri $20.00 5 4-Jan Sat $20.00 10.00 6 5-Jan Sun 7 $20.00 $140.00 7 6-Jan Mon $20.00 8 7-Jan Tue $20.00 9 8-Jan Wed $20.00 10 9-Jan Thu $20.00 11 10-Jan Fri $20.00 12 11-Jan Sat $20.00 7.00 13 12-Jan Sun 3 $20.00 $60.00Playing with conditional formatting
This formatting is something I used to restructure the way I wrote formulas between 2015 and 2018. In the original I used VBA but now, as often happens, worksheet formulas used within conditional formatting are sufficient. The formula defining the condition is = DIRECTREFλ(A1) //Function to test for the presence of direct cell references DIRECTREFλ = LAMBDA(cell, IF(ISFORMULA(cell), REGEXTEST(FORMULATEXT(cell), "\b[A-Z]{1,3}\${0,1}\d{1,6}\b")) ); The original was pretty effective as a training aid to avoid the practice of relative referencing and use defined names! That said, not many followed my lead! Mind you when I posted a discussion on Chandoo it didn't meet with overwhelming support, though subsequent events in the form of spilt ranges have made my life so much easier! The post did however stimulate more discussion than I had anticipated. The A1 notation is an abomination that has no place within serious spreadsheet design -- discuss? | Chandoo.org Excel Forums - Become Awesome in ExcelHelp with changing a formula
Hi - I am using a formula to sum hours worked for a week. Currently it calculating the values beginning on Sunday to Saturday. I would like to change it to sum from Monday to Sunday and display the result in column G on the Sunday of that week. I'm hoping you can help me. TIA I am currently using the following formula. =IF(WEEKDAY(A2)=7, IF(SUMIFS(C:C,A:A, ">="&A2-6,A:A, "<="&A2)>0, SUMIFS(C:C,A:A, ">="&A2-6,A:A, "<="&A2), ""), "") A B C D E F G H 1 Date Hours Purchases Rate Daily Cost Hrs / wk 2 1-Jan Wed 2.5 $20.00 $50.00 3 2-Jan Thu 4 3-Jan Fri $20.00 5 4-Jan Sat 3 $20.00 $60.00 5.50 6 5-Jan Sun $20.00 7 6-Jan Mon $20.00 8 7-Jan Tue $20.00 9 8-Jan Wed $20.00 10 9-Jan Thu $20.00 11 10-Jan Fri $20.00 12 11-Jan Sat 3 $20.00 $60.00 3.00 13 12-Jan Sun 3 $20.00 $60.00
