Forum Discussion

Hussein_Elsayed1605's avatar
Hussein_Elsayed1605
Copper Contributor
Oct 16, 2022

Excel issue

Dears, Have a greet day, i have an issue with an amount i need to divide it into smaller amount every quarter after deduct the down payment Ex) Total amount 1,000,000  Jan - feb - march - Apr  ,e...
excel
Hussein_Elsayed1605's avatar
Hussein_Elsayed1605
Copper Contributor
Oct 16, 2022

JoeUser2004 

Thank you Mr. Joe for your quick reply.

Just sales figures (projected). If I have a unit will be sold over 10 years quarterly payment and there is a down payment xx will deduct from the total unit value and the rest will be divided over the period. How can I do this with a formula

 

N.B: the time line will be monthly ? jan - feb - march, Etc.

I hope you got my issue

 

Thanks

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Oct 16, 2022

Hussein_Elsayed1605 

=IF(OR(A7="january",A7="april",A7="july",A7="october"),($B$3-$B$4)/($B$5*4),"")

Does this return your expected result?

 

  • Hussein_Elsayed1605's avatar
    Hussein_Elsayed1605
    Copper Contributor
    Oct 16, 2022

    Dears,

    Attached is the sample of my issue, i hope find a formula to help get the target as shown in the attached.

     

    Thanks in advance

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Oct 17, 2022

      Hussein_Elsayed1605 

       

      In F12:

       

      =LET(Start, MATCH(9.99999999999999E+307, $A$4:$DJ$4), Total, INDEX($A$4:$DJ$4, Start), DownPayment, INDEX($A$6:$DJ$6, Start), Payment, (Total-DownPayment)/36, Payment*(MOD(COLUMN()-Start,3)=0))

       

      Fill to the right.

      • Hussein_Elsayed1605's avatar
        Hussein_Elsayed1605
        Copper Contributor
        Oct 17, 2022

        HansVogelaar 

        JoeUser2004 

        Thank you for your support, 

        attached is the full version that include the sales figures per month, i couldn't apply the formula provided.

        as i have many sales figures in a different months and need your usual support to advice me about the proper formula to divide the amount after excluding the down payment over the given year.

         

        Thanks

    • JoeUser2004's avatar
      JoeUser2004
      Bronze Contributor
      Oct 17, 2022

      Hussein_Elsayed1605 

       

      Thanks for attaching an example file that demonstrates the result that you are looking for.  That is the quickest way to get what you need.

       

      The following shows one approach.  Click the image to enlarge.  See the attached Excel file for details.

       

       

      B8:   =IF(AND(1<=COLUMN(B2)-$B$14, COLUMN(B2)-$B$14<=$B$12, MOD(COLUMN(B2)-$B$14, 3)=0), $B$13, 0)
B11:  =IFERROR(INDEX(A4:JI4,B14)-INDEX(A6:JI6,B14), 0)
B13:  =IFERROR(B11/INT(B12/3), 0)
B14:  =IFERROR(MATCH(TRUE,INDEX(ISNUMBER(A6:JI6),1,0),0), 0)

       

       

       

      The formulas in B11, B13 and B14 are for convenience and efficiency.

       

      However, I believe that the additional data in B12 (payment term in months after the down payment) is necessary.

       

      The formula in B14 determines the column number of the down payment.  The use of INDEX(ISNUMBER(A6:JI6),1,0) is a trick to avoid needing to array-enter the formula.  Effectively, it is ISNUMBER(A6:JI6).

       

      FYI, I made some subtle changes to the cell formats, primarily so that zero appears to be blank.

       

      Also, you had a mysterious unexplained value in JJ6.  I changed it to =SUM(B6:JI6), which is similar to JJ4 and JJ8.

       

Resources