IF statement returns #VALUE

[andrew21]

perhaps someone could suggest corrections to this:

=IF(AND(C79<0,D79>E79),"Distribution",""),IF(AND(C79>0,D79>E79),"Accumulation","")

I would like the cell to which this formula is entered to display th
word Accumulation or Distribution depending on the above criteria bein
met..otherwise the cell should remain blank...any help on this i
greatly appreciated
andre

 

[JE McGimpsey]

one way:

=IF(OR(D79<=E79,C79=0),"",IF(C79<0,"Distribution","Accumulation"))

 

[Don]

Hi Andrew,

I believe that you need the following:

=IF(AND(C79<0,D79>E79),"Distribution",IF(AND
(C79>0,D79>E79),"Accumulation",""))

That's if I understood you correctly.

HTH,

Don

 

[andrew21]

thanks guys for your formulas..i have tried both and each migh
work...at least in the formula editor it seems to give the correc
result...however in the cell itself only the formula is visible instea
of the words Accumulation or Distribution..hmmm.

 

[JulieD]

Hi Andrew

some ideas
.. have you got a "=" in front of the formula and no spaces?
.... under tools/ options view, ensure Formulas is unticked

any success?

Cheers
JulieD

 

[andrew21]

Hi Julie
i've double checked your suggestions and still no luck...the formula
are visible in the cel

 

[JE McGimpsey]

They were probably entered in a cell formatted as text. Change the
format to General, hit F2 to enter edit mode, then Enter.

 

[Don]

Hi Andrew,

Sounds like you may have pasted into the cell instead of
the formula bar or, as Julia points out you may have
omitted the "="....check both, it should work.

Don

 

[andrew21]

Eureka!..JE you hit the nail on the head...of course i pre-formatted th
cell for text thinking that 's logical since i wanted the cell t
display a word...by the way Don and JE both of your formula
work..thank you both and also Julie thanks for your suggestion