×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# 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

### RE: Excel Formula

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($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 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, 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 (OP) Thanks Skip. Awesome. What if I have date 31-January-2021 in cell A15? The expected outcome is 0. Thanks, arv ### RE: Excel Formula 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, 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 (OP) 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 ### RE: Excel Formula 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($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

(OP)
Awesome, Thanks heaps Skip.

### RE: Excel Formula

In between snaps.🫡🏈

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

(OP)
Enjoy the game 😀.

### RE: Excel Formula

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

### RE: Excel Formula

(OP)
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($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

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

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!