IF Statement - Timing and Span of variable

**PERE** · 10-15-2013, 05:24 AM

Hi,

Refer attached. My goal is to spread the cost in B1 from the start date in C1 and span in D1. The result is where ever the cost starts, it evenly spans based on the number of months in D1 for the subsequent months.

Any thoughts are greatly appreciated!

PERE

**SDCh** · 10-15-2013, 05:37 AM

May be like this:

Please Login or Register  to view this content.

copy to the right until column O and copy down for more row

**benishiryo** · 10-15-2013, 05:45 AM

hi Pere. B1 is empty. i suppose you mean B6. C1 & D1 are empty too. i suppose you mean B2 & D6. so using those cell references, try this in E6:
=IF(AND(E$2>=$B$2,E$2<=EOMONTH($B$2,$D6-1)),$B6/$D6,0)

if i'm wrong about the cell references, match mine to where your cost, start date & Span are. also, it would be helpful to always manually key in what you're hoping to see

**PERE** · 10-15-2013, 05:47 AM

Apologies i should have stated the below cell references. The formula you have provided doesn't work if you change C6

Refer attached. My goal is to spread the cost in B6 from the start date in C6 and span in D6. The result is where ever the cost starts, it evenly spans based on the number of months in D1 for the subsequent months.

**PERE** · 10-15-2013, 05:47 AM

above response was for SDCh

**PERE** · 10-15-2013, 05:50 AM

Thanks benishiryo....i'm still stuck. If I insert your formula, it works if C6 is a fixed month, however if it changes i.e. to month 2 your formula doesn't work?

**benishiryo** · 10-15-2013, 06:15 AM

my solution was based on the idea the start date is B2 (a real date). i'm supposing C6 is going to be like what's in the file then? a month number that is in line with E4:O4? if that's so:
=IF(AND(E$4>=$C6,E$4<=$C6+$D6-1),$B6/$D6,0)

if C6 is going to be a real date, then:
=IF(AND(E$2>=$C6,E$2<=EOMONTH($C6,$D6-1)),$B6/$D6,0)

**PERE** · 10-15-2013, 08:10 PM

Thanks Benishiryo.....=IF(AND(E$4>=$C6,E$4<=$C6+$D6-1),$B6/$D6,0) works well.

IF Statement - Timing and Span of variable

LinkBack

Thread Tools

Rate This Thread

Display

IF Statement - Timing and Span of variable

Re: IF Statement - Timing and Span of variable

Re: IF Statement - Timing and Span of variable

Re: IF Statement - Timing and Span of variable

Re: IF Statement - Timing and Span of variable

Re: IF Statement - Timing and Span of variable

Re: IF Statement - Timing and Span of variable

Re: IF Statement - Timing and Span of variable

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] For Next Statement with If Statement Reducing Upper Variable

Variable averaging span question.

VB IF statement using a Variable

[SOLVED] Variable IF statement (in VBA)

Bookmarks

Bookmarks

Posting Permissions