Allocate Amounts every quarter

[Hussein_Mohamed]

Dears

Could you please assist

I need to devide the amount of Cell #E5 over 8 Years by 10% on the same sales month in cell #B5 and every 3 month need to devid the rest of amount (90%) on equal payments according to the attached sample.

Appreciat your help and thanks in advance

[#2 · Accepted solution]

@Hussein_Mohamed 

I hope I have understood your task correctly.

You can copy the following formula into cell G5 and then copy it to cell KB5. It also works for other rows below.

=IFS(
    G$3<EOMONTH($B5,-1)+1,0,
    G$3=EOMONTH($B5,-1)+1,$E5*0.1,
    G$3-(EOMONTH($B5,-1)+1)>EOMONTH($B5,95)+1-EOMONTH($B5,-1)+1,0,
    MOD(MONTH(G$3)-MONTH($B5),3)=0,$E5*0.9/32,
    TRUE,0
)

Please note that your month information in line 3 was partially incorrect. I have now automatically set these to the 1st of the month.

[#3]

@dscheikey 

it works well thank you,

if i have many rows and try to copy the formula i notice that the top row move down, (G2 become G3)

is there any way to make the top row still the same without moving.

Thanks in advance

[#4]

@Hussein_Mohamed

If a line is not to change when copied down, use the $ sign in front of the line number. For example, G$3. If the column and row number are not to change, place a $ sign in front of the column and the row. For example, $G$3.

In order to set up the copying correctly, I used exactly this procedure. Take a look at the formula. When copying to rows below, only the area in columns B and E is moved down. This makes sense, as other values will probably be used for further calculations in other rows.