# Excel issue

Occasional Contributor

# 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  ,etc

i have to put amount on jan and skip feb and march and put the second amount in april Etc, till consuming the total amount.

11 Replies

# Re: Excel issue

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?

# Re: Excel issue

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

# Re: Excel issue

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

Does this return your expected result?

# Re: Excel issue

Dears,

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

# Re: Excel issue

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.

# Re: Excel issue

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.

# Re: Excel issue

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

GIGO!

# Re: Excel issue

You have lost me completely.

# Re: Excel issue

I am very sorry for the misunderstanding of my question. I just wanted to help me in making an equation on excel on a form with all the data attached to it, but I cannot modify the equation or make an equation in a simpler way to reach the desired result.

I am sorry again for the miss-leading of my question again.