formula help please

[Guest]

In cell C7 I have 5 numbers (11400) I have extractet each number with the MID
function and put them in cells A1 number 1 A2 number 1 A3 number 4 A4
number 0 A5 number 0 so A1 has the formula MID(C7,1,1) I need to add two
cells together providing that cell A1=1 The formula I used
is:=IF(A1=1,J6,J6+B6) But what it does is , adds the cells j6+b6 which should
be just j6 on its own. If I replace the cell A1 fomula MID(C7,1,1) with just
number 1 it gives me the correct answer. I have tried different cell formats.
Can any one tell me why?

 

[R.VENKATARAMAN]

try type
=IF(A1="1",J6,J6+B6)
note apostrophes in 1
when you use mid the value in A1 is as text and not number

try this and see whether you get it.

 

[mangesh_yadav]

Hi,

the mid function converts your number to text, and so the IF is not
evaluating it correctly. Use the following formula instead.

=VALUE(MID($C$7,COLUMN(),1))


Mangesh

 

[Guest]

The MID() function returns a text string, meaning that the digit 1 in cell A1
looks like "1" to Excel. 1 <> "1", so the IF() is false and you get the
second result.

Make either of these changes

=IF(A1="1",J6,J6+B6)
=IF(Value(A1)=1,J6,J6+B6)

 

[Guest]

By using the MID function, the returned value is TEXT...not a number.
Consequently the letter "1" is not the same as the number 1.

You have 2 ways to go with this:
=IF(--A1=1,J6,J6+B6)

OR

Change your MID functions to this format:
=--MID(C7,1,1)

Does that help?

 

[Guest]

this may be a second response

the mid function is text
try
=IF(value(A1)=1,J6,J6+B6)

 

[Guest]

The MID() function's result is a text value, which Excel sees as "1". In
your IF() you are testing A1 for a numeric 1, not a text "1". Thus, the test
fails and you get the false results

Try either of these

=IF(A1="1",J6,J6+B6)
=IF(VALUE(A1)=1,J6,J6+B6)