Forum Discussion
Allocate Amounts every quarter
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
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 Replies
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.
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
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.
