Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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? |
#7
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
put formula results into a different cell if it is empty | Excel Worksheet Functions | |||
how do i write a formula and keep in in formula form, so it DOESN. | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |