In Excel 2007, why do I get a minus result for one cell but not the other when both cells are formatted exactly the same?

This is the formula I have in cell U36: =IFERROR(IF(U15="";"";(U15*2.5-T15*2)*2);"")

Then I have exactly the same formula (other cell references, but exactly the same pattern) in cell J17, but for J17 I get no minus displayed, only nothing is displayed (which is correct).
LVL 1
hermesalphaAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
If the result in U15 is zero then maybe the check in the other formula needs to check for zero not "".

For the result of U15 to be zero, it is evaluating the (J15/J19)*U14 part of the formula and the result is zero; if the result of that part of the formula was an error the result would be "".
0
 
AndyIt ConsultantCommented:
Hi,

You'd be better uploading a sample with some values to show context.
0
 
Rob HensonFinance AnalystCommented:
In cell U36, the formula refers to U15 and T15, what are the values in those cells? If U15 looks blank is it truly blank?

For cell J17, which cells are referenced and what are the values of those?

Thanks
Rob
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
hermesalphaAuthor Commented:
Hi,

In T15 the value is 14.2, and in U15 no value.

In cell J17, it refers to an empty cell (cell J15):
=IFERROR(IF(J15="";"";(J15*2.5-I15*2)*2);"")
I15 has value 14.0.

So in J17 I get an empty cell (which is correct), but in U36 I get -56.7 displayed (nothing should be displayed here).

I could see the difference now: there is a formula in U15 whereas there is no formula in J17.
0
 
hermesalphaAuthor Commented:
Is it possible somehow to tell Excel not to use a cell in a formula if that cell contains a formula and not a value?
0
 
AndyConnect With a Mentor It ConsultantCommented:
-56.7 is about right as -14.2*2*2=-56.8
0
 
AndyIt ConsultantCommented:
You could use the ISFORMULA function to determine if a formula exists and use that in your function.

http://www.contextures.com/excelisformulafunction.html
0
 
Rob HensonFinance AnalystCommented:
If result of formula in U15 is "" then formula will recognise as blank. If result us " " then it looks blank but doesn't match "" so does the formula.
0
 
hermesalphaAuthor Commented:
I tried ISBLANK function now, but I still get the same minus figure:

=IFERROR(IF(ISBLANK(U15);"";(U15*2.5-T15*2)*2);"")
0
 
hermesalphaAuthor Commented:
Can I rewrite the part of this formula that contains (U15*2.5-T15*2)*2) so that U15 and T15 both counts as 0 if there is no value in U15?
0
 
Rob HensonFinance AnalystCommented:
=IFERROR(IF(OR(U15="";T15="");"";(U15*2.5-T15*2)*2);"")

ISBLANK does not work if the cell contains a formula, even if the result of the formula is "".  ISBLANK only gives TRUE when the cell is actually empty.

EDIT: Ignore suggested formula, I misread the requirement.
0
 
Rob HensonFinance AnalystCommented:
Does this do what you need?

=IFERROR(((IF(U15="";0;U15)*2.5)-(IF(U15="";0;T15)*2))*2;"")
0
 
hermesalphaAuthor Commented:
What about something in this way (although this particular formula does not work, needs to be modified)?:

=IFERROR(IF (U15 <> ;(U15*2.5-T15*2)*2);"")
0
 
hermesalphaAuthor Commented:
Or this?: =IFERROR(IF(U15<>;(U15*2.5-T15*2)*2);"")
0
 
hermesalphaAuthor Commented:
Rob, I tried your formula now but it still gets the same minus figure.

The problem is this part:

-14.2*2*2=-56.8
0
 
Rob HensonFinance AnalystCommented:
=IFERROR(IF(U15<>"";(U15*2.5-T15*2)*2;"");"")

You had omitted a does not equal value and a FALSE option for the IF statement, both amendments underlined and bold.
0
 
hermesalphaAuthor Commented:
You were right Rob, the formula works now. But still the same minus figure. However, when I think about it, the cell is probably not counted as empty because there is a formula in it. Is there any way to make Excel count the cell as empty if there is no value in it BUT a formula?

In other words, "if U15 contains value" instead of "if U15 is not empty"?
0
 
hermesalphaAuthor Commented:
If U15 ISNUMBER?
0
 
hermesalphaAuthor Commented:
=IFERROR(IF(ISNUMBER(U15);(U15*2.5-T15*2)*2;"");"")

I get no error message for above formula, but still the same minus figure.
0
 
Rob HensonFinance AnalystCommented:
What is the formula in U15?
0
 
hermesalphaAuthor Commented:
This is the formula in U15 (the result of the formula is 0):

=IFERROR((J15/J19)*U14;"")
0
 
hermesalphaAuthor Commented:
Great, that worked, thanks! I only needed to change the "" to 0, like this:

=IFERROR(IF(U15=0;"";(U15*2.5-T15*2)*2);"")
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.