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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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
 
Upvote 0
Isn't the original formula an array formula?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top