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: ...
- 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.
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.
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.
The formula does (did before the sequence error) the following:
It references the timeframe of the project duration in comparison to the date within the top table's header, checks for the name in the bottom table against the name in the top table, and sums the score from all projects for that relative quarter for the individual.
IE - Person 1 is assigned to Project 1 that runs for 1 year with a value of 12 and Project 2 that runs for 2 years with a value of 15. The formula would indicate that for 1 year the person's sum total would be 27 and for one year it would be 15. (very rough example)
Hope this helps with clarification!
