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

excel

Formulas and Functions

Steve_SumProductCom
Feb 09, 2025
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))))

Steve_SumProductCom

MVP

Feb 09, 2025

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))))

TomerIwanir

Copper Contributor

Feb 09, 2025

yes! thank you so much !

Forum Discussion

Formula assistant

Resources