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.

 

[jlatendre]

I am not 100% sure what you need, but from what I can see it looks like you are missing a , or 2 and your brackets are just misplaced, this is what I have changed it to, (I deleted all your workbook references though). Lemme know if this works/helps.

=IF(AND($R$1:$V$1>=$C19,$R$1:$V$1=$D19,$R$1:$V$1=$E19,$R$1:$V$1=$F19,),$C$9,0)

Jesse

 

[Norie]

Isn't the original formula an array formula?

 

[jlatendre]

I am sorry, I am still learning excel and only trying to help, but after reviewing your code, i do not see what array you are talking about, but I do see that at the end of your IF statement you are missing what happens if your value is true or false, is this done purposely or am I just reading it wrong? By the looks of it to me it should come up as 0 because you have not told it what to do if it is true or false. Please correct me if im wrong.

Jesse

 

[Private Equity Guy]

Not sure that was the answer:

=IF(AND('Sales Data by Channel'!$R$1:$V$1>=Assumptions!$C19,'Sales Data by
=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),Assumptions!$C$6,IF(AND('Sales Data by

Channel'!$R$1:$V$1>=Assumptions!$D19,'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),Assumptions!$C$7,IF(AND('Sales Data by

Channel'!$R$1:$V$1>=Assumptions!$E19,'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!$B$9,IF('Sales Data by

Channel'!$R$1:$V$1>=Assumptions!$F19,Assumptions!$C$9,0))))
Channel'!$R$1:$V$1>=Assumptions!$F19,Assumptions!$C$9,0))))

This is the formulas stacked up and I can't see what you are referencing. Is there other information I can provide you?

 

[Private Equity Guy]

jlatendre,

The last value in the formula is 0 which is the value if false. The the values if true a built throughout the meat of the formula. Still can't figure out how it will work perfectly in on cell and give a totally different answer in the next cells even when the entries are absolute?

 

[jlatendre]

You are missing brackets somewhere, you only have ( ( but than at the end you have ) ) ) ) something is wrong with them. And you don't have the true and false statements, all you have is the logical test, I think I know what you want,

=IF($R$1:$V$1>=(AND($C19,$R$1:$V$1=$D19,$R$1:$V$1=$E19,$R$1:$V$1=$F19,)),$C$9,0)

Try this with adding back in your notebooks, if you need my help with it let me know. Hope this helps

Jesse

 

[Norie]

The reason I think it's an array formula is because you have comparisons of ranges against single values.

Try entering the formula with CTRL + ENTER.

 

[Spiky]

Don't you mean CTRL + SHIFT + ENTER ?

 

[Private Equity Guy]

Not sure I understand. What would that combination of keys accomplish and when do I use it?