Weighted Avg, omit text from calc

yburman

New Member
Joined
Jan 27, 2003
Messages
17
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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Have you tried changing SpreadDate to a statement like this:

If(isnumber(spreaddate),spreaddate,False)
 
Upvote 0
Thank you for your help. At the risk of appearing extremely novice, do you mean replacing "spreaddate" like this?

=SUMPRODUCT($N$2:$N$600>=(IF(ISNUMBER(spreaddate),spreaddate,FALSE))*($T$2:$T$600="no")*(SPREAD),LOANAMOUNT/SUMPRODUCT($N$2:$N$600>=(IF(ISNUMBER(spreaddate),spreaddate,FALSE))*($T$2:$T$600="no")*(SPREAD)))
 
Upvote 0
The column that is a mix of dates and text is column "N". Should the ISNUMBER be attached to that argument as opposed to the spreaddate somehow?
 
Upvote 0
yes i assumed that your named range SpreadDATE was what was causing you a problem.

sounds like you are on it.
 
Upvote 0

Forum statistics

Threads
1,224,506
Messages
6,179,159
Members
452,892
Latest member
yadavagiri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top