formula help please
 

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?

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.


bill gras wrote in message
...
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?

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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=379583

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)


"bill gras" wrote:

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?

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?

--
Regards,
Ron

this may be a second response

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

"bill gras" wrote:

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?

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)



"bill gras" wrote:

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?