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 ,etci have to put amount on jan and skip feb and march and put the second amount in april Etc, till consuming the total amount. Thanks in advance

Hussein_Elsayed1605 You have not provided sufficient details for an intelligent solution. Re: ``after deduct the down payment Ex) Total amount 1,000,000 [....] i have to put amount on jan`` Is 1,000,000 the amount before or after the down payment? Is the "amount on jan" the down payment? Usually, the down payment is irrelevant. Re: ``skip feb and march and put the second amount in april Etc`` Without limits, one solution is to "put" $1 in April, $1 in July, $1 in Oct etc for nearly 1 million quarters. Ridiculous, I know. The point is: without some constrains or additional information, there are infinite solutions. What do the amount 1,000,000 and "down payment" represent? Why are you making payments every quarter? For example, is this a loan? Is it an asset value that you are depreciating? Over what period of time do you want to decrease the amount? Is there "interest" involved? Etc, etc, etc. If this is a loan, please specify: the amount of the loan (1,000,000?), the rate of interest (preferrably quarterly; otherwise annually); the number of quarters over which to amortize the loan (i.e. pay interest and decrease the principal). And how should we determine the amount paid in Jan? Is that even relevant, if the loan amount is $1,000,000?

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

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?

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

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.

Excel issue | Microsoft Community Hub

11 Replies

JoeUser2004
Bronze Contributor
Oct 16, 2022
Hussein_Elsayed1605

You have not provided sufficient details for an intelligent solution.

Re: ``after deduct the down payment Ex) Total amount 1,000,000 [....] i have to put amount on jan``

Is 1,000,000 the amount before or after the down payment?

Is the "amount on jan" the down payment?

Usually, the down payment is irrelevant.

Re: ``skip feb and march and put the second amount in april Etc``

Without limits, one solution is to "put" $1 in April, $1 in July, $1 in Oct etc for nearly 1 million quarters.

Ridiculous, I know. The point is: without some constrains or additional information, there are infinite solutions.

What do the amount 1,000,000 and "down payment" represent? Why are you making payments every quarter? For example, is this a loan? Is it an asset value that you are depreciating? Over what period of time do you want to decrease the amount? Is there "interest" involved? Etc, etc, etc.

If this is a loan, please specify: the amount of the loan (1,000,000?), the rate of interest (preferrably quarterly; otherwise annually); the number of quarters over which to amortize the loan (i.e. pay interest and decrease the principal). And how should we determine the amount paid in Jan? Is that even relevant, if the loan amount is $1,000,000?
- 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
    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?

Forum Discussion

Excel issue

11 Replies

Resources