## Excel Formula

## Excel Formula

(OP)

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

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

## RE: Excel Formula

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($D21:D21)<$B21*$C21,$B21,0)))You can make the ZEROs disappear using a Conditional Format.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"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!## RE: Excel Formula

BTW, if...

E1:

31-Jan-2023...then...

F1:

=DATE(YEAR(E1),MONTH(E1)+2,0)-1...and COPY/PASTE across

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"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!## RE: Excel Formula

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

The expected outcome is 0.

Thanks,

arv

## RE: Excel Formula

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,

_{ Just traded in my OLD subtlety... for a NUance!}"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!## RE: Excel Formula

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

## RE: Excel 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($D3:D3))<ABS($B3*$C3),$B3,0)))

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"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!## RE: Excel Formula

## RE: Excel Formula

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"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!## RE: Excel Formula

## RE: Excel Formula

## RE: Excel Formula

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($D3:D3))<$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($D3:D3))<ABS($B3*$C3),$B3,0)))

Thanks,

arv

## RE: Excel Formula

_{ Just traded in my OLD subtlety... for a NUance!}"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!## RE: Excel Formula

I shall leave you to enjoy the game.

Much appreciated 👍👍👍.

Regards

arv