# Copied logic test retuning a value of zero

#### Private Equity Guy

##### New Member
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.

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### jlatendre

##### Board Regular
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

##### Well-known Member
Isn't the original formula an array formula?

#### jlatendre

##### Board Regular
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

##### New Member

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

##### New Member
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

##### Board Regular

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

##### Well-known Member
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

##### Board Regular
Don't you mean CTRL + SHIFT + ENTER ?

#### Private Equity Guy

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

Replies
3
Views
46
Replies
2
Views
143
Replies
0
Views
62
Replies
4
Views
625
Replies
9
Views
96