Simple Formula

[ExcelRoy]

Hi Guys,

I have a simple formula that returns a #VALUE error

How do i change to avoid the error

=IF(SUM(Q146-Q142)="","",SUM(Q146-Q142))

Thanks

 

[Jonmo1]

This
SUM(Q146-Q142)=""
will Never be true, because SUM returns a number, not a text string..

Try
=IF(ISERROR(Q146-Q142),"",Q146-Q142)

 

[Aladin Akyurek]

Hi Guys,

I have a simple formula that returns a #VALUE error

How do i change to avoid the error

=IF(SUM(Q146-Q142)="","",SUM(Q146-Q142))

Thanks

Maybe...

Either:

=IF(COUNT(Q142,Q146)=2,Q146-Q142,"")

Or:

=N(Q146)-N(Q142)

 

[T. Valko]

Hi Guys,

I have a simple formula that returns a #VALUE error

How do i change to avoid the error

=IF(SUM(Q146-Q142)="","",SUM(Q146-Q142))

Thanks

Try this...

=IF(COUNT(Q142,Q146)<2,"",Q146-Q142)

 

[ExcelRoy]

HHhmmm

I must be doing something wrong, none of these formula's work?

They all show "" and no sums?

 

[Jonmo1]

What is in Q142 and Q146?

At the time you expect to see a SUM, What do these formulas return?

=ISNUMBER(Q142)
=ISNUMBER(Q146)

 

[Aladin Akyurek]

HHhmmm

I must be doing something wrong, none of these formula's work?

They all show "" and no sums?

This one too?

=N(Q146)-N(Q142)

 

[ExcelRoy]

Hi Jonmo,

Q146 is an overall total of merged cells (Q15:S138)
Q142 is an overall total of merged cells (Q15:S138) with specific wording

What i am basically trying to do is have a basic workbook as a template so i need everything to show "" at the beginning but when values are put into the cells then a total shows up in these cells

Hope that makes sense

Thanks

Aladin,

This works but doesn't show the "" if no values are calculated

Thanks

 

[Aladin Akyurek]

Hi Jonmo,

Q146 is an overall total of merged cells (Q15:S138)
Q142 is an overall total of merged cells (Q15:S138) with specific wording

What i am basically trying to do is have a basic workbook as a template so i need everything to show "" at the beginning but when values are put into the cells then a total shows up in these cells

Hope that makes sense

Thanks

Aladin,

This works but doesn't show the "" if no values are calculated

Thanks

It looks like the cells in question house text numbers... What formulas exactly do you have in them?

In the meantime...

=IF(AND(Q142="",Q146=""),"",N(Q146)-N(Q142))

Does this give you the desired calculation state?

 

[ExcelRoy]

Hi Aladin,

The cells are formatted as "£" currency

Your formula calculates fine, but shows £0.00 when nothing is entered

Thanks