Copied logic test retuning a value of zero

Private Equity Guy

New Member
Joined
Oct 2, 2010
Messages
11
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.
 

Some videos you may like

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
Joined
Sep 21, 2010
Messages
153
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
Joined
Apr 28, 2004
Messages
75,817
Office Version
  1. 365
Platform
  1. Windows
Isn't the original formula an array formula?
 

jlatendre

Board Regular
Joined
Sep 21, 2010
Messages
153
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
Joined
Oct 2, 2010
Messages
11

ADVERTISEMENT

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
Joined
Oct 2, 2010
Messages
11
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
Joined
Sep 21, 2010
Messages
153

ADVERTISEMENT

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
Joined
Apr 28, 2004
Messages
75,817
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,756
Messages
5,524,677
Members
409,597
Latest member
Dannydev

This Week's Hot Topics

Top