Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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?

Posted on 2016-11-27
22
Medium Priority
?
65 Views
Last Modified: 2017-01-17
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).
0
Comment
Question by:hermesalpha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 7
  • 3
22 Comments
 
LVL 7

Expert Comment

by:Andy
ID: 41903714
Hi,

You'd be better uploading a sample with some values to show context.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41904016
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
 

Author Comment

by:hermesalpha
ID: 41904024
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:hermesalpha
ID: 41904028
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
 
LVL 7

Assisted Solution

by:Andy
Andy earned 300 total points
ID: 41904039
-56.7 is about right as -14.2*2*2=-56.8
0
 
LVL 7

Expert Comment

by:Andy
ID: 41904041
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41904049
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
 

Author Comment

by:hermesalpha
ID: 41904132
I tried ISBLANK function now, but I still get the same minus figure:

=IFERROR(IF(ISBLANK(U15);"";(U15*2.5-T15*2)*2);"")
0
 

Author Comment

by:hermesalpha
ID: 41904137
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41904146
=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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41904156
Does this do what you need?

=IFERROR(((IF(U15="";0;U15)*2.5)-(IF(U15="";0;T15)*2))*2;"")
0
 

Author Comment

by:hermesalpha
ID: 41904161
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
 

Author Comment

by:hermesalpha
ID: 41904168
Or this?: =IFERROR(IF(U15<>;(U15*2.5-T15*2)*2);"")
0
 

Author Comment

by:hermesalpha
ID: 41904170
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41904172
=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
 

Author Comment

by:hermesalpha
ID: 41904180
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
 

Author Comment

by:hermesalpha
ID: 41904184
If U15 ISNUMBER?
0
 

Author Comment

by:hermesalpha
ID: 41904186
=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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41904207
What is the formula in U15?
0
 

Author Comment

by:hermesalpha
ID: 41904265
This is the formula in U15 (the result of the formula is 0):

=IFERROR((J15/J19)*U14;"")
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 1700 total points
ID: 41904297
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
 

Author Comment

by:hermesalpha
ID: 41904305
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question