Jan 08 2023 01:28 AM
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
Jan 08 2023 05:56 AM
SolutionI 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.
Jan 08 2023 06:21 AM
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
Jan 08 2023 06:32 AM
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.
Jan 08 2023 05:56 AM
SolutionI 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.