Forum Widgets
Latest Discussions
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
Loss carry forward for limited years
Hello Excel Community, Excel Version: O365 (Enterprise) I’m running into a mental block trying to model loss carryforward with limited years using a spill formula. Below is a simplified example of what I’m trying to build a formula for. Rules: • A loss from any year can be carried forward for N years, after which it expires and can no longer be used. • In any year with a positive tax liability, if there is any remaining loss from the past N years, you deduct using a FIFO approach (oldest losses used first). • Any unclaimed balance from year N+1 expires, even if it hasn’t been fully used. I’ve found plenty of examples showing loss carryforward without expiration, but I’m struggling to build something that handles both FIFO and expiration in a dynamic array formula. I feel like this must be a solved problem and I’m just missing something obvious. Any help would be greatly appreciated! ------------------------------------------------------------------------------------------
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.
Random Contractor Selection List
Hi, I would like to set up an automated random contractor list for tender purpose - is this possible? For example, I have Contractor 1 - Contractor 10, I would like to choose 2 contractors for each tender/quotation request randomly. Currently, it is done manually which is time consuming and could not ensure impartiality in selection - if I can setup the contractor rota to generate random selection, it would be wonderful. Please can anyone advise and help with the correct way to setup my excel sheet? Many thanksMultiple 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.
Bank recon matching
Happy New Year everyone! Can someone please assist me with this to a matching exercise for a bank reconciliation. Tried to do XLOOKUP with REGEXTEST but no luck. Below is the link to the excel file. https://1drv.ms/x/c/80b0e5c8d40a83ef/IQAn2IxiOBDpSJBIIZCfPj3TAexy2EEPYT5VjpDkcpTzpB4?e=S4NEuo
