-3

My problem: in Sheet1 Cell A5 formula: 

=((A1-A2)+(A3-A4))/2

In Sheet2 Cell A1 an IF function: 

=IF('Sheet1'!A5=1;"Good";0)

Excel only returns the "value if false".

Any suggestions?

UPDATE:

Where´s the thing:

I´ve tried with different cells and it works just fine it only fails with this specific cell

What might be the problem?

Problem Solved =IF('Sheet1'!A5<>1;"Good";0)

The equal element does not work alone.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Filipe
  • 1
  • 2
  • Try `=IF('Sheet1'!A5=1; "Good"; 0)`. –  Oct 19 '15 at 13:11
  • There are no semicolons in Excel's IF. – rbm Oct 19 '15 at 13:11
  • @rbm - There are if the computer's list separator is set for semi-colons (as is the case in several European countries). –  Oct 19 '15 at 13:13
  • @Jeeped - did not know that! thanks – rbm Oct 19 '15 at 13:15
  • Possible duplicate of [Excel - find a value and copy into different sheets](http://stackoverflow.com/questions/22035694/excel-find-a-value-and-copy-into-different-sheets) – Legionar Oct 19 '15 at 13:15
  • 1
    Now that you've added the missing tick (e.g. `'`, you might try rounding the returned value to avoid [15 digit precision floating point errors](http://blogs.office.com/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/). `=ROUND(((A1-A2)+(A3-A4))/2, 6)` rounds to a 6 decimal precision. –  Oct 19 '15 at 13:20

2 Answers2

0

Simplify A5's formula to =(A1-A2+A3-A4)/2. Sheet2's formula will be =IF(Sheet1!A5=1, "Good", "0")

I was not aware that some Locales use ; in IF as a separator. Today I learned. If you are in such Locale, use =IF(Sheet1!A5=1; "Good"; "0"). I tested this on Excel 2013 and it works as desired.

zedfoxus
  • 35,121
  • 5
  • 64
  • 63
0

Try following in A1 of sheet2 :

=IF(Sheet1!E1=1,"Good",0)

You will get value 0. And if you use following

=IF(Sheet1!E1=5,"Good",0)

you will get value "Good" provided sheet1:A5 has a value of 5 after computation.

Lalu
  • 168
  • 1
  • 2
  • 12