One last request

[John Holt]

Cell I2 is a forumea cell that adds values from F2+G2+H2
I have now copied this cell ie. I2 into 13 down to I50.
The "#VALUE" now appears in these cells.
How do I get keep the formulea in these cells , however remove the notation
# VALUE.
Thanks in advance.

John Holt

 

[J.E. McGimpsey]

Cell I2 is a forumea cell that adds values from F2+G2+H2
I have now copied this cell ie. I2 into 13 down to I50.
The "#VALUE" now appears in these cells.
How do I get keep the formulea in these cells , however remove the notation
# VALUE.

You'll get the #VALUE! error if any of the cells in the formula have
a #VALUE! error, or if any of the cells have Text.

I suspect you've got space characters in the cells referenced by the
formulae returning the #VALUE! error. Clear the input cells and the
errors should go away.

 

[John Holt]

You'll get the #VALUE! error if any of the cells in the formula have
a #VALUE! error, or if any of the cells have Text.

I suspect you've got space characters in the cells referenced by the
formulae returning the #VALUE! error. Clear the input cells and the
errors should go away.

Thank you, I have done as you suggested and I now have $0.00 showing in the
I column, how do I get to show them blank whilst keeping the formulea in
them.

 

[Harvey Waxman]

Thank you, I have done as you suggested and I now have $0.00 showing in the
I column, how do I get to show them blank whilst keeping the formulea in
them.

If F2 AND G2 AND H2 are all blank then enter a blank, otherwise put in the total:

IF(AND(F2="",G2="",H2="",)"",F2+G2+H2)

I think that will work.

 

[John Holt]

IF(AND(F2="",G2="",H2="",)"",F2+G2+H2)

I don't understand!!!

Yes columns F, G H are all blank, awaiting values to be inputted at a later
stage.
How do I put in a blank???
Just to be sure I will advise of my situation

CELL I2 contains the formulea =D2+E2+G2+H2 which is fine.
However when I copy and paste to other cells in column I the figures
$0.00 appear, I wish to remove this notation without removing the formulea

Thanks for your help!!

John Holy

 

[Harvey Waxman]

I don't understand!!!

Yes columns F, G H are all blank, awaiting values to be inputted at a later
stage.
How do I put in a blank???

I had assumed those cells were blank. If they aren't, they need to be. Select
them and clear them by choosing 'clear contents' not by using the delete key. I
also apologize for the misplaced comma in the above formula:

it should be IF(AND(F2="",G2="",H2=""),"",F2+G2+H2)

What it says is this: if cells F2 G2 and H2 are all blank (="") then display
nothing ("") otherwise display the total of F2+G2+H2.

If there is anything at all other than a number in those 'empty' cells, F,G,H,
the formula will display #VALUE!. I don't think I can explain it any more
clearly.
I hope it helps.

CELL I2 contains the formulea =D2+E2+G2+H2 which is fine.
However when I copy and paste to other cells in column I the figures
$0.00 appear, I wish to remove this notation without removing the formulea

Your original post used FGH so you will have to change the formula to use DEGH.