#VALUE problem
 

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

#VALUE problem
 

Please post the formula//
--
If this post helps click Yes
---------------
Jacob Skaria


"Andrew Mackenzie" wrote:

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

#VALUE problem
 

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 he

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



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" wrote in message
...
Please post the formula//
--
If this post helps click Yes
---------------
Jacob Skaria


"Andrew Mackenzie" wrote:

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

#VALUE problem
 

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
---------------
Jacob Skaria


"Andrew Mackenzie" wrote:

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 he

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



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" wrote in message
...
Please post the formula//
--
If this post helps click Yes
---------------
Jacob Skaria


"Andrew Mackenzie" wrote:

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