#VALUE removal on negative sign

[sammymalta]

=-IF(ISBLANK('sheet 1'!CG2),"",'sheet 1'!CG2)
I have the above formula, if i have a + positive sign in front, when it finds no data, the cell comes BLANK, which is what i want, however when i put a negative sign in from it comes to #VALUE.
What can i do so it comes BLANK as well?
Note: In Sheet 1, cell CG2, the information is being picked up from another sheet, and in this cell cell CG2 the formula is =+IF(ISBLANK(CF2*AY2),"",(CF2*AY2))

I also have this formula which is doing the same thing, but i understand its because there is a +1 divided by formula
=+1/IF(ISBLANK('sheet 1'!CF39),"",'sheet 1'!CF39)
Is there a way to resolve or shall i do the division in a separate cell

Thanks
Sam

[Glenn Kennedy]

What about:

=IF(ISBLANK('Sheet 1'!CG2),"",'Sheet 1'!CG2*-1)

or

=IF(ISBLANK('Sheet 1'!CG2),"",-'Sheet 1'!CG2)

[sammymalta]

No it didnt work... SEE ATTACHED,

[Glenn Kennedy]

1. You didn't say that the source data conrtained a formula.

2. I assume its the absolute amount that you need.

3. try: =IF('amended export'!E2="","",ABS('amended export'!E2))

4. You don't actually need to add the + at the start of your formulae.

[sammymalta]

Ahh no.. ABS wont work, as sometimes the values are positives which means value would need changing to a negative. (but you made an assumption as i didnt give you full info)

BUT BUT BUT.....I amended your formula slightly to the below and it worked.Many thanks Glenn.
=IF('amended export'!E2="","",-('amended export'!E2))

I added to your reputation last time as its not allowing me again! Sorry about that.
https://www.excelforum.com/excel-for...ml#post4993644

[Glenn Kennedy]

No problems.... and you're welcome.