#VALUE problem

[Andrew Mackenzie]

Hi,

Wonder if anyone can help.

I have a formula which is something like this:

=IF(G3=0,"-",=big formula) where "big formula" is, erm, a big formula. The
result is currently showing #VALUE even though G3 does equal zero. I have
confirmed this by highlighting the G3=0 part of the formula and pressing F9
where it shows in the formula bar that this is TRUE. Furthermore if I make
G3 equal to something other than zero the big formula works.

Can anyone tell my why I might be getting this #VAUE?

Thanks very much,

Andrew

 

[Jacob Skaria]

Please post the formula//

 

[Andrew Mackenzie]

OK Jacob, but I'm not sure if this will help (and you will drive yourself
crazy trying to figure out what I am doing here:

=IF($Q$357=0,"-"(SUMPRODUCT((dceValueDate<=deMonthEnd)*(dceDept="CBD -
Direct
Lending")*(dceFX=FALSE)*(dceValueDate>=deStartMonth)*(dceGBPAmount>0)*(dceGB
PAmount)*(dceMargin))+SUMPRODUCT((dceValueDate<=deMonthEnd)*(dceDept="CBD -
Capital
Markets")*(dceFX=FALSE)*(dceValueDate>=deStartMonth)*(dceGBPAmount>0)*(dceGB
PAmount)*(dceMargin))+SUMPRODUCT((dceValueDate<=deMonthEnd)*(dceDept="Manche
ster")*(dceFX=FALSE)*(dceValueDate>=deStartMonth)*(dceGBPAmount>0)*(dceGBPAm
ount)*(dceMargin)))/($Q$357-SUMPRODUCT((dceGBPAmount)*(dceDept="CBD - Direct
Lending")*(dceValueDate<=deMonthEnd)*(dceValueDate>=deStartMonth)*(dceFX=TRU
E)*(dceGBPAmount>0)))-SUMPRODUCT((dceGBPAmount)*(dceDept="CBD - Capital
Markets")*(dceValueDate<=deMonthEnd)*(dceValueDate>=deStartMonth)*(dceFX=TRU
E)*(dceGBPAmount>0)))-SUMPRODUCT((dceGBPAmount)*(dceDept="Manchester")*(dceV
alueDate<=deMonthEnd)*(dceValueDate>=deStartMonth)*(dceFX=TRUE)*(dceGBPAmoun
t>0))



Surely if the first part of the IF statement is TRUE then the formula should
return "-" regardless of what the big formula is doing.


Cheers,

Andrew

 

[Jacob Skaria]

Dear Andrew

I am sure you would have sorted this out by now. But still did you try with
a small/simple formula..may be in a fresh workbook..

If this post helps click Yes