Forum Discussion
Solved
Formula Changed And Now I'm Getting "#NAME?" Errors?
Good morning all! I've used a formula to calculate many aspects of my data - and it works amazing, until this week for some reason. Now whenever I change the filters on my table, the formula breaks:
=SUM(
MMULT(
--(Table57[[PM]:[CM5]] = Table1[@[Name]:[Name]] ),
_xlfn.SEQUENCE( COLUMNS( Table57[[PM]:[CM5]] ), , 1,0) ) *
Table57[[SUM]:[SUM]] *
( Table57[[Start Date]:[Start Date]] <=
DATEVALUE(Table1[[#Headers],[7/1/2022]] )
) *
( Table57[[End Date]:[End Date]] >=
DATEVALUE(Table1[[#Headers],[7/1/2022]] )
)
)
I think it has to do with "_xlfn.SEQUENCE" function, because I dont remember that being like that - but I don't remember exactly what I used to have. Did a function change recently within Excel?
Here's what happens when I change the view:
Any thoughts? Thank you!
- as noted SEQUENCE should definitely be recognized and you should fix your Excel.
also as noted that formula could probably be improved.
as for a quick fix to get rid of SEQUENCE I might try just deleting it and see if it works but if it is needed to make sure the array lengths for the MMULT() are correct then:
SEQUENCE( COLUMNS( Table57[[PM]:[CM5]] ), , 1,0) )
could be
TRANSPOSE(--LEN(Table57[[#HEADERS],[PM]:[CM5]] )>0) )
- It appears you're working in a version of Excel that does not support dynamic arrays so SEQUENCE is not available. It's worth checking to see if XLOOKUP and FILTER are available, too.
- I checked and XLOOKUP and filter are available, but not sequence. Is there an easy replacement for that function?
- That's odd. Which version of Excel are you running?
- Is that part even needed?
SEQUENCE( COLUMNS( Table57[[PM]:[CM5]] ), , 1,0) ) *
that sequence just creates a column of 1 values which doesn't change the multiplication, I don't think.- Probably not. The entire formula could be simplified once we know the goal. It might be a good idea to get MMULT out of there, too.
