Creating a formula with ISERROR

Question

Hi,

I have the following formula which I am trying to use with an ISERROR function and it doesn't seem to matter what I do, it just won't work.

The formula I currently have is:

=IF($S$26="$",SUM(ROUND(K20*$S$4,2)),"")

But this brings up the #VALUE! error if the "$" is showing in S26.  I need this cell to calculate if there is a $ and if anything else then leave blank.

I thought by added the ISERROR function this would fix it but Excel keeps saying there are things missing. 

Any help would be gratefully received.

Many thanks,

Kath

Answer 1

Thank you for your reply.  

Actually, now you've said that, its quite obvious and both cells returned "false". However, both cells already contain forumlas and it I think that the K20 one is the problem.  I have other cells which have the same formula as K20 but they work because the cell in column J has no calculation in it already.

in K20    =IF(ISERROR(J20*I20),"",(J20*I20))

in S26    =IF(M59="USD","$",IF(M59="STERLING","£"))

I'm basically trying to show/hide information based on the currency.  

If its USD then I need the cell to preform a calculation based on a value in J20*S4 which is the exchange rate and if its Sterling (or a £ sign) then do/show nothing.

Not sure if this makes sense, but thanks for your help,

Kath

Answer 2

Hi,

The problem is with either K20 or S4 or both and I suspect one or both of then is a text value that looks like a number.

Put this in an empty cell

=isnumber(K20)

and

=isnumber(S4)

Both of those should return TRUE but if they return false then the value in that cell is text.

Answer 3

Hi, could you provide an example of your data, formula make no sense you apply sum but there is no range to sum