Forum Discussion
Formula assistant
Hi All,
Attached the following pic:
Im trying to create the yellow cells by formula (I made it manually)
the rules:
if the column date and the raw date is a match so it should give us column H value (exm:cell J7, the column date is 01/01/2025, same as row 7 date so im getting H7 value)
one cell to the right where we got our match I want it to take the left cell divide by minus 4 and multy by 3 (15360/4*-3 = -11520)
and than to the next 3 cell to the right, I want to get the -11520/-3 = 3840
after 3 cells I want it to get zeroed,
thx all in advanced !
I recommend that you add a "counter" row as shown in row 2 below so you can accomplish this. Then the formula in I6 can be:
=IF($C6=I$1,$H6,IF(I$2-MATCH($C6,$I$1:$T$1)=1,$H6/4*(-3),IF(AND(I$2-MATCH($C6,$I$1:$T$1)>1,I$2-MATCH($C6,$I$1:$T$1)<5),($H6/4*(-3))/3,0)))
=IF($C6=I$1,$H6,IF(I$2-MATCH($C6,$I$1:$T$1)=1,$H6/4*(-3),IF(AND(I$2-MATCH($C6,$I$1:$T$1)>1,I$2-MATCH($C6,$I$1:$T$1)<5),($H6/4*(-3))/3,0)))Copy across and down. It can be simplified a little bit to =LET(baseval,MATCH($C6,$I$1:$T$1),IF($C6=I$1,$H6,IF(I$2-baseval=1,$H6/4*(-3),IF(AND(I$2-baseval>1,I$2-baseval<5),($H6/4*(-3))/3,0))))
=LET(baseval,MATCH($C6,$I$1:$T$1),IF($C6=I$1,$H6,IF(I$2-baseval=1,$H6/4*(-3),IF(AND(I$2-baseval>1,I$2-baseval<5),($H6/4*(-3))/3,0))))
5 Replies
It's tempting to draw up a recursive staircasing solution with MUNIT but that function is not very robust and combining the matrices is messy.
This can be done with mostly shaping and wrapping:=LET( k, ROWS(dates), w, k * 2 - 1, a, amount / -4 * 3, b, a / SEQUENCE(, 3, -3, 0), stack, EXPAND(HSTACK(amount, a, b), , w + 1, 0), return, WRAPROWS(DROP(TOCOL(stack), -k), w, 0), return )This workbook offers a radically different way of working!
Note that it uses a function MAPĪ» that many would regard as the work of the Devil.
Thankfully it is a lot easier to use than it was to write!I recommend that you add a "counter" row as shown in row 2 below so you can accomplish this. Then the formula in I6 can be:
=IF($C6=I$1,$H6,IF(I$2-MATCH($C6,$I$1:$T$1)=1,$H6/4*(-3),IF(AND(I$2-MATCH($C6,$I$1:$T$1)>1,I$2-MATCH($C6,$I$1:$T$1)<5),($H6/4*(-3))/3,0)))
=IF($C6=I$1,$H6,IF(I$2-MATCH($C6,$I$1:$T$1)=1,$H6/4*(-3),IF(AND(I$2-MATCH($C6,$I$1:$T$1)>1,I$2-MATCH($C6,$I$1:$T$1)<5),($H6/4*(-3))/3,0)))Copy across and down. It can be simplified a little bit to =LET(baseval,MATCH($C6,$I$1:$T$1),IF($C6=I$1,$H6,IF(I$2-baseval=1,$H6/4*(-3),IF(AND(I$2-baseval>1,I$2-baseval<5),($H6/4*(-3))/3,0))))
=LET(baseval,MATCH($C6,$I$1:$T$1),IF($C6=I$1,$H6,IF(I$2-baseval=1,$H6/4*(-3),IF(AND(I$2-baseval>1,I$2-baseval<5),($H6/4*(-3))/3,0))))yes! thank you so much !
In I6:
=IF(I$1=$C6, $H6, IF(AND(I$1>$C6, I$1<EDATE($C6, 3)), -3/4*H6, 0))
Fill down and to the right.
