I have a need to calculate the weighted average spread based on whether the date in a column ("N") fits a given date criteria (and some other criteria). If text as opposed to a date is in the column, the result changes. I would like the option of having a date or text in that cell and have the calculation ignore the text cells. Here is the formula I use to get the spread and it is fine until "text" is introduced into column "N" as opposed to a date.
=SUMPRODUCT(--($N$2:$N$600>=SpreadDate),--($T$2:$T$600="no"),--(SPREAD),LOANAMOUNT/SUMPRODUCT(--($N$2:$N$600>=SpreadDate),--($T$2:$T$600="no"),--(SPREAD)))
Is it possible to use the column for both date and text input or should I just keep it a date column and leave it at that? Thank you for your help with this.
=SUMPRODUCT(--($N$2:$N$600>=SpreadDate),--($T$2:$T$600="no"),--(SPREAD),LOANAMOUNT/SUMPRODUCT(--($N$2:$N$600>=SpreadDate),--($T$2:$T$600="no"),--(SPREAD)))
Is it possible to use the column for both date and text input or should I just keep it a date column and leave it at that? Thank you for your help with this.