• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Variable Month Formula

AZExcel

Member
Hello All,


I am working on a project that involves tracking a credit. The tool uses data validation to trigger certain functions within the formula that I am being challenged with.


This is the formula:


=IFERROR(ROUND(IF(A17="TTD",$G$5*E17,IF(A17="TPD",($G$4/30.416*E17)-(B17))*0.6667)+IF(A17="PERM",$G$7/DAY(DATE(YEAR(C17),MONTH(C17)+1,1)-1)*E17),2),"")

(see file rows 16 and 17)


My challenge is with the part of the formula when "perm" is selected.

When "Perm" is selected this indicates a full monthly benefit (for the most part) but in some cases usually at the being or the end a partial month is needed.


When this happens (partial payment) what is happening now is the formula takes the monthly benefit and divides it into the number of day in the month x the number days and returns the amount of credit.


What I want to happen is for the formula to continue to recognize if a full month is entered (usually from _/1/YEAR- _OEM/YEAR) and return the monthly benefit. In other words whether there be 28 days in the month or 31 the formula will return the same monthly benefit. However, and this is my issue, if there is a partial payment input let’s say 1/1/2014-01/14/2014 , I want the formula to take the monthly benefit /30.416 x by number of days and return the value.


Is this possible? Any suggestions are appreciated
 

Attachments

  • CREDIT TRACKER WORKING MODELMay2014.xlsm
    85.2 KB · Views: 0
Hi ,

I have not yet gone through your worksheet in detail ; however , I would like to point out two things :

1. What is the reason for a formula like : =IF(F13=0,"",IF(F13<0,G12,G12-F13))

Would it make any difference if it were rewritten as : =IF(F13<=0,G12,G12-F13) ?

2. What is the reason for a formula like : =IF(C17&D17="","",D17-C17+1)

Should it not be =IF(OR(C17="",D17=""),"",D17-C17+1) ?

Narayan
 
Narayan,

Thanks for the question. The main reason I constructed the formula(s) the way I did was to avoid value errors and to have the range blank when there is no data.

Your first formula: IF(F13<=0,G12,G12-F13) did not accomplish this goal
The 2nd one: IF(OR(C17="",D17=""),"",D17-C17+1) accomplishes this goal

I will consider revising the 2nd formula. Thanks for the question
 
Hi ,

I do not know whether F13 = 0 is a possibility , but with your formula , if that condition is satisfied , then all the other results disappear. If this is acceptable to you , there is no problem.

Narayan
 
Narayan,

Very excellent... I did not think to use a helper column. Thank you very much for helping with this problem!!!
 
Back
Top