Copied logic test retuning a value of zero

[Private Equity Guy]

I am working on a spreadsheet that allows me to manipulate the payroll expenses for the sales force of a portfolio company and the formula does not want to behave. I created a logic statement with absolutes that produces a value of $3000. When this statement is carried over to the five corresponding/adjacent cells a value of 0 is produced.

Example Cell R4 contains:
=IF(AND('Sales Data by Channel'!$R$1:$V$1>=Assumptions!$C19,'Sales Data by Channel'!$R$1:$V$1<Assumptions!$D19),Assumptions!$C$6,IF(AND('Sales Data by Channel'!$R$1:$V$1>=Assumptions!$D19,'Sales Data by Channel'!$R$1:$V$1<Assumptions!$E19),Assumptions!$C$7,IF(AND('Sales Data by Channel'!$R$1:$V$1>=Assumptions!$E19,'Sales Data by Channel'!$R$1:$V$1<Assumptions!$F19),Assumptions!$B$9,IF('Sales Data by Channel'!$R$1:$V$1>=Assumptions!$F19,Assumptions!$C$9,0))))

and Cell S4 contains:
=IF(AND('Sales Data by Channel'!$S$1:$V$1>=Assumptions!$C19,'Sales Data by Channel'!$S$1:$V$1<Assumptions!$D19),Assumptions!$C$6,IF(AND('Sales Data by Channel'!$S$1:$V$1>=Assumptions!$D19,'Sales Data by Channel'!$S$1:$V$1<Assumptions!$E19),Assumptions!$C$7,IF(AND('Sales Data by Channel'!$S$1:$V$1>=Assumptions!$E19,'Sales Data by Channel'!$S$1:$V$1<Assumptions!$F19),Assumptions!$B$9,IF('Sales Data by Channel'!$S$1:$V$1>=Assumptions!$F19,Assumptions!$C$9,0))))

I know the problem is due to a FALSE statement because I changed the FALSE value to 1 and the fields equaled 1.

Please help as I need to finish this by end of business today.

 

[RoryA]

Perhaps it would be easier if you explained what the formula is supposed to be doing.

 

[Private Equity Guy]

Jesse, I don't understand. Do I need to replicate this 4 different times so all for options are present or are you telling me this statement should work?

 

[jlatendre]

Alright I have added everytihng back in properly I think for your file, this should work for you, you will just place it in for your first column and drag it over like you tried before, if this doesnt work give me a link to your worksheet and I will try to figure it out,

=IF('Sales Data by Channel'!$R$1:$V$1>=(AND(Assumptions!$C19,'Sales Data by Channel'$R$1:$V$1=Assumptions!$D19,'Sales Data by Channel'!$R$1:$V$1=Assumptions!$E19,'Sales Data by Channel'!$R$1:$V$1=Assumptions!$F19,)),Assumptions!$C$9,0)

Jesse

 

[Private Equity Guy]

True, let me start off by saying thank you to everyone for putting up with me. i am new to posting for help and I am sure that I came up short in terms of articulation.

The formula:
=IF(AND('Sales Data by Channel'!R1:V1>=Assumptions!$C19,'Sales Data by Channel'!R1:V1<Assumptions!$D19),Assumptions!$C$6,IF(AND('Sales Data by Channel'!R1:V1>=Assumptions!$D19,'Sales Data by Channel'!R1:V1<Assumptions!$E19),Assumptions!$C$7,IF(AND('Sales Data by Channel'!R1:V1>=Assumptions!$E19,'Sales Data by Channel'!R1:V1<Assumptions!$F19),Assumptions!$C$8,IF('Sales Data by Channel'!R1:V1>=Assumptions!$F19,Assumptions!$C$9,0))))

The Assumptions/Explanation:
Sales Data by Channel R1:V1 - the month within a calendar year that a sales person is generating revenue/training in 5 sub markets (referrals, non-referrals, Small Builder, Mid Builder, Closets).

Assumptions $C19 - this is a link to the assumptions page which points to the start date of a new sales person. The first 3 months for a new sales person is considered training and we will assume no revenue is being generated.

Assumptions $D19 - this is a link to the assumptions page which points to the first date a sales person will begin generating revenue (4th month after hire). This is why I tried to create >= and < statements.

Assumptions $C$6 - this is a link to the assumptions page which points to the amount of revenue being generated per channel during the training period which is zero.

Assumptions $E19 - this is a link to the assumptions page which points to the first increase in revenue which occurs one month after the sales person begins generating revenue

Assumptions $C$7 - this is a link to the assumptions page which points to the amount of revenue being generated per channel during the initial revenue period which is $3000.

Assumptions $F19 - this is a link to the assumptions page which points to the Second and final increase in revenue which occurs one month after the initial build up

Assumptions $C$8 - this is a link to the assumptions page which points to the amount of revenue being generated per channel during the 1st build up period which is $5000.

Assumptions $C$9 - this is a link to the assumptions page which points to the amount of revenue being generated per channel during the 2nd and final build up period which is $8000.

Now you can see that I am trying to create a formula that allow me to view different hiring scenarios

I hope this adds more color.

Thank you

 

[RoryA]

What is the basis for comparison between the 5 cells in R1:V1 and the four date cells? Do you require all 5 cells to match each of the four criteria?

 

[Private Equity Guy]

The 5 cells are sub cells of the merged cell R1:V1 (Date) and the should all have the same formula/values.