Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

# Allocate Amounts every quarter

Brass Contributor

# Allocate Amounts every quarter

Dears

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.

3 Replies
best response confirmed by Hussein_Mohamed (Brass Contributor)
Solution

# Re: Allocate Amounts every quarter

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.

# Re: Allocate Amounts every quarter

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.

# Re: Allocate Amounts every quarter

@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.

1 best response

Accepted Solutions
best response confirmed by Hussein_Mohamed (Brass Contributor)
Solution

# Re: Allocate Amounts every quarter

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.