Excel Challenge - Pivoting poorly structured data
This is from an ExcelBI challenge. I thought it may be worth while posting my solution here as a demonstration of modern Excel methods. Challenge Like many of such challenges, the natural solution approach is to use BYROW but that creates the usual 'array of arrays' error. Solution: Gradually I am moving to a point at which I have no formulas showing in the workbook other than calls to Lambda functions. In this case, the worksheet formuloa is = PIVOTBYCATEGORYλ(OrderTbl) The function works row by row apportioning the amounts against the listed categories PIVOTBYCATEGORYλ // Groups and pivots table by category = LAMBDA(table, LET( normalised, BYROWλ(table, APPORTIONλ), // Identify fields from normalised table dimension, TAKE(DROP(normalised,,1),,2), category, TAKE(normalised,,1), partCost, TAKE(normalised,,-1), // Pivot by category return, PIVOTBY(dimension, category, partCost, SUM,,0,,0), return ) ); The function APPORTIONλ divides the amount between categories so each record within the source data returns a number of rows APPORTIONλ // Splits by category and assigns costs = LAMBDA(record, LET( category, TOCOL(REGEXEXTRACT(INDEX(record,4),"\w+",1)), amount, INDEX(record,3) / COUNTA(category), year, YEAR(INDEX(record,1)), region, IF(LEN(INDEX(record, 2)), INDEX(record, 2), "Unknown"), broadcast, B∕CASTλ(HSTACK(region, year, amount), category), return, HSTACK(category, broadcast), return ) ); /* FUNCTION NAME: B∕CASTλ DESCRIPTION: Broadcasts the terms of a vector over the shape of a second array */ B∕CASTλ = LAMBDA(vector, array, IF({1}, vector, array)); The key to making the formula work is the function BYROWλ that I wrote to generalise the inbuilt but over-restrictive BYROW function. The PIVOTBY function returned the required crosstab from the normalised data array
Current Date and time per cell
I have a table with one column listing rows with a dynamic text box (open; closed) in the next column I would like to display the date and time when the status changes. The purpose is to show when a task was completed, I have used the now() function but it keeps refreshing, I need it stay the date The snip of the sheet below shows how is is currently working, so can any one help with some code for column J please. thank you Lee
Change in return of AVERAGE function - Mac Excel
I was puzzled that a spreadsheet I use daily generated an error today that had not existed in earlier versions. Attempts to confirm the validity of the spreadsheet functions by running prior versions that had previously run error free resulted in the same error. Eventually, it seems that the operation of the AVERAGE function has been changed in an Excel program update that was installed yesterday. Previously, if an AVERAGE function addressed a range of empty cells, it would return a zero value. Now it is returning the error #DIV/0! This is strange because the AVERAGE function will now return zero if the addressed range contains zeros rather than just being empty. Not sure if Microsoft intended this change or if the change might also apply to other functions. In muy case, I'm able to change my spreadsheet to provide for this but it would have been nice to have some warning.Write formulas with natural language using Copilot in Excel
Writing formulas can feel intimidating, especially when you’re unsure of the syntax or which function to use. We’ve already made formula writing easier with formula completion, which proactively suggests and autocompletes formulas as you type. Now, we’re introducing an additional on-grid Copilot tool that takes it a step further: With this new capability, you can simply describe what you need, and Copilot will create the formula for you. This means no more struggling to remember complex syntax, and faster and more natural interactions with your data, especially if you’re new to Excel – plus, a helpful alternative when formula completion doesn’t return the right suggestions or when you prefer typing in your own words. Together, these tools make formula writing faster, easier, and more intuitive, so you can focus on insights, not syntax. How it works In Excel for the Web, select the cell in which you want to enter a formula. Type = in the cell or the formula bar, and then click on the Ask Copilot for a formula option that appears. NOTE: You can also use the keyboard shortcut Ctrl + , to move the focus into the input box. Describe the kind of formula you want in natural language. For example: “Calculate total profit". Review the formula suggestion, the description, and the preview of the result on the grid. Then, select either Keep it if the suggestion works for you, or Discard, and then type = and run the Ask Copilot for a formula option again. Tips and tricks You can ask Copilot to modify existing formulas: In cases where you already have a formula but need to adjust it, simply describe the change you want, and Copilot will update the formula for you. For example, you can modify a return on assets calculation to include average assets for period. You can ask for formulas that require data from different sheets, such as calculating the asset turnover rate using values from separate Income Statements and Balance Sheets tabs. In cases where the formula completion doesn’t match your needs – for example, Copilot spells the month out but you want the format to be MMM – describe the exact format you need, and Copilot will generate the correct formula for you. Scenarios to try Copilot can generate formulas of varying complexity for different needs: Return a unique list of salespersons from the transactions table. Calculate the total units sold for each salesperson in the list. Calculate the total sales for each quarter - even when the sales table doesn't include the quarter, only the date. Extract the state out of the customer address. Compute profit for each transaction with a lookup function that uses data from another table. Known issues This feature currently supports one formula or one formula column or range at a time. Multiple formulas support is being considered for future updates. Availability This feature is currently rolling out to Excel for Web users with a Microsoft 365 Copilot license. Feedback We appreciate your insights regarding formula suggestions using natural language! You can share your feedback with us in the result card using the thumbs up or down buttons, or by selecting the Feedback button in the upper right-hand corner of Excel for Web, and then selecting either Give a compliment, Report a problem, or Make a suggestion.Multiple Inventory entries on the same item
Okay so I'm wondering if there is a way for excel to automatically advance to the next column when entering inventory data so if I need to make another entry on the same item I don't have to advance to the next cell myself. This would be helpful in streamlining inventory and making it foolproof when adding inventory to a specific item (without having to worry about deleting previously entered data). I'm hoping there is a way to do this so I can take a tablet when completing my inventory and avoid the old clipboard and paper.
