I am trying to refine a formula for projected collection of membership dues. The projection needs to take into account varying rates, billing options, and when the membership was renewed. My current formula takes the first two into account, but it is an inelegant and long formula to which I do not know how to add in the third variable. I'm sure there is a simpler way to do this calculation with an array formula, I'm just not familiar enough with them. The current formula is bellow and sample data is attached including the below formula in cell F10. Any help would be greatly appreciated.
=SUMIF(C2:C15,"In Full",A2:A15)+SUMIF(C2:C15,"Semi-Annual",A2:A15)*(IF((TODAY()-G2)<375/2,0.5,1))+SUMIF(C2:C15,"Tri-Annual",A2:A15)*(IF((TODAY()-G2)<380/3,1/3,IF((TODAY()-G2)<380*2/3,2/3,1)))+SUMIF(C2:C15,"Quarterly",A2:A15)*(IF((TODAY()-G2)<385/4,0.25,IF((TODAY()-G2)<385/2,0.5,(IF((TODAY()-G2)<385*3/4,0.75,1)))))+SUMIF(C2:C15,"Monthly",A2:A15)*((TODAY()-G2)/365)
Bookmarks