Excel Formula 1

[arvarr]

Hi
I'm trying to populate from column D to S with value from column B.

Criteria:
1. If Period (column A3:A14) = Period (column D1:S1), then insert value (number of times as per column C across columns)

Attached is the raw data with results i am trying to achieve.

Thanks,
arv

 

[SkipVought]

Arv,

I think that unless you INSERT a column (D), you'll have a circular reference.

My formula
E21: =IF($A21>E$19,0,IF($A21=E$19,$B21,IF(SUM($D2121)<$B21*$C21,$B21,0)))

You can make the ZEROs disappear using a Conditional Format.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

Here's your workbook.

BTW, if...
E1: 31-Jan-2023
...then...
F1: =DATE(YEAR(E1),MONTH(E1)+2,0)-1
...and COPY/PASTE across

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Thanks Skip. Awesome.

What if I have date 31-January-2021 in cell A15?

The expected outcome is 0.

Thanks,
arv

 

[SkipVought]

You didn't include a requirement for a date previous to the minimum date in the display range.

You'll have to wait until the Super Bowl is over--ie. Probably tomorrow.o

Why don't you take a crack at it.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Hi Skip
I think I can add another column to overcome this problem.

I just realized I will have negative numbers in column B and the formula needs to be adjusted.

Thanks,
arv

 

[SkipVought]

Revised formula
if
Column A Period less than min Row 1 period OR
Column A Period greater than than max Row 1 period OR
then result is ZERO.

Accommodated negative amounts.

=IF(OR($A3>E$1,$A3<MIN($1:$1),$A3>MAX($1:$1)),0,IF($A3=E$1,$B3,IF(ABS(SUM($D33))<ABS($B3*$C3),$B3,0)))

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Awesome, Thanks heaps Skip.

 

[SkipVought]

In between snaps.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Enjoy the game .

 

[arvarr]

Yikes, the only issue is when its a negative amount, it extends more than the # of times.

 

[arvarr]

Accommodated negative amounts.
If negative amount. It only shows in one column.

=IF(OR($A3>E$1,$A3<MIN($1:$1),$A3>MAX($1:$1)),0,IF($A3=E$1,$B3,IF(ABS(SUM($D33))<$B3*$C3),$B3,0)))


I tried adding Abs below but it extends more than # Of times.

=IF(OR($A3>E$1,$A3<MIN($1:$1),$A3>MAX($1:$1)),0,IF($A3=E$1,$B3,IF(ABS(SUM($D33))<ABS($B3*$C3),$B3,0)))

Thanks,
arv

 

[SkipVought]

Try this

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Thanks heaps. I had one closing bracket in the sum section and 2 closing brackets after.

I shall leave you to enjoy the game.

Much appreciated .

Regards
arv