Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
"IF" function question
Hi,
I have an IF function comparing 2 cells and it is under column C: A B C cat Dog "=if(A1=B1,1,0)" (here result is 0) Dog bird "=if(A2=B2,1,0)" (here result is 0) Now, every time the result is ZERO in column C, I will move the corresponding data on column B downwards "Insert - Shift cells down". The problem here is that my formula on column C also moves downwards! So if I moved down the data on B1 (Dog), the formula on C1 becomes "=if(A1=B2,1,0)". Is there a way that the formula will still compare the original 2 cells even if I moved the data down?? That is, the formula on C1 will still be "=if(A1=B1,1,0)" Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
"IF" function question
Try this in C1:
=--(INDIRECT("A1")=INDIRECT("B1")) That will *always* refer to A1 and B1 -- Biff Microsoft Excel MVP "Lorderon" wrote in message ... Hi, I have an IF function comparing 2 cells and it is under column C: A B C cat Dog "=if(A1=B1,1,0)" (here result is 0) Dog bird "=if(A2=B2,1,0)" (here result is 0) Now, every time the result is ZERO in column C, I will move the corresponding data on column B downwards "Insert - Shift cells down". The problem here is that my formula on column C also moves downwards! So if I moved down the data on B1 (Dog), the formula on C1 becomes "=if(A1=B2,1,0)". Is there a way that the formula will still compare the original 2 cells even if I moved the data down?? That is, the formula on C1 will still be "=if(A1=B1,1,0)" Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
"IF" function question
Hi,
Thanks for your reply, but unfortunately this didn't. Pls take note the A1 and B1 are Cell locations. Let me explain further what I'm doing.... I'm comparing the data of column A and column B, if they are the same the value on column C will be 1 else it will be 0. So if I found out that the value on Col A and Col B are not the same (meaning vale on Col C is 0), I will "shift down" the data on Col B (the different one only) to match Col A. The problem here is that, the formula on Col C is shifted as well. So for example I shifted the data on cell location B1 downwards the formula on cell location C1 becomes "=if(A1=B2,1,0)". I want it to be still the same as the original. ORIGINAL DATA: Col A Col B Col C cat Dog "=if(A1=B1,1,0)", result is 0 Dog bird "=if(A2=B2,1,0)", result is 0 bird eat "=if(A3=B3,1,0)", result is 0 So, I looked for the data which are different and match them together, so I "moved down" the data on Col B to match Col A, so it becomes.... SHIFTED DATA: Col A Col B Col C (all the formula changed!!) cat "=if(A1=B2,1,0)", result is 0 Dog Dog "=if(A2=B3,1,0)", result is 0 bird bird "=if(A3=B4,1,0)", result is 0 eat As you can see the formula on Col C also shifted, if they maintained my original formula the values on cell C1 is still 0, value on cell C2 will be 1, value on cell C3 will be 1. But now they are all 0 because the formula shifted after I moved down the data on cell location B1. "T. Valko" wrote: Try this in C1: =--(INDIRECT("A1")=INDIRECT("B1")) That will *always* refer to A1 and B1 -- Biff Microsoft Excel MVP "Lorderon" wrote in message ... Hi, I have an IF function comparing 2 cells and it is under column C: A B C cat Dog "=if(A1=B1,1,0)" (here result is 0) Dog bird "=if(A2=B2,1,0)" (here result is 0) Now, every time the result is ZERO in column C, I will move the corresponding data on column B downwards "Insert - Shift cells down". The problem here is that my formula on column C also moves downwards! So if I moved down the data on B1 (Dog), the formula on C1 becomes "=if(A1=B2,1,0)". Is there a way that the formula will still compare the original 2 cells even if I moved the data down?? That is, the formula on C1 will still be "=if(A1=B1,1,0)" Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
"IF" function question
The formula I suggested does what you want but it's limited to those EXACT
cells only. Try this one in C1 and copy down as needed: =--(OFFSET(C1,,-2)=OFFSET(C1,,-1)) -- Biff Microsoft Excel MVP "Lorderon" wrote in message ... Hi, Thanks for your reply, but unfortunately this didn't. Pls take note the A1 and B1 are Cell locations. Let me explain further what I'm doing.... I'm comparing the data of column A and column B, if they are the same the value on column C will be 1 else it will be 0. So if I found out that the value on Col A and Col B are not the same (meaning vale on Col C is 0), I will "shift down" the data on Col B (the different one only) to match Col A. The problem here is that, the formula on Col C is shifted as well. So for example I shifted the data on cell location B1 downwards the formula on cell location C1 becomes "=if(A1=B2,1,0)". I want it to be still the same as the original. ORIGINAL DATA: Col A Col B Col C cat Dog "=if(A1=B1,1,0)", result is 0 Dog bird "=if(A2=B2,1,0)", result is 0 bird eat "=if(A3=B3,1,0)", result is 0 So, I looked for the data which are different and match them together, so I "moved down" the data on Col B to match Col A, so it becomes.... SHIFTED DATA: Col A Col B Col C (all the formula changed!!) cat "=if(A1=B2,1,0)", result is 0 Dog Dog "=if(A2=B3,1,0)", result is 0 bird bird "=if(A3=B4,1,0)", result is 0 eat As you can see the formula on Col C also shifted, if they maintained my original formula the values on cell C1 is still 0, value on cell C2 will be 1, value on cell C3 will be 1. But now they are all 0 because the formula shifted after I moved down the data on cell location B1. "T. Valko" wrote: Try this in C1: =--(INDIRECT("A1")=INDIRECT("B1")) That will *always* refer to A1 and B1 -- Biff Microsoft Excel MVP "Lorderon" wrote in message ... Hi, I have an IF function comparing 2 cells and it is under column C: A B C cat Dog "=if(A1=B1,1,0)" (here result is 0) Dog bird "=if(A2=B2,1,0)" (here result is 0) Now, every time the result is ZERO in column C, I will move the corresponding data on column B downwards "Insert - Shift cells down". The problem here is that my formula on column C also moves downwards! So if I moved down the data on B1 (Dog), the formula on C1 becomes "=if(A1=B2,1,0)". Is there a way that the formula will still compare the original 2 cells even if I moved the data down?? That is, the formula on C1 will still be "=if(A1=B1,1,0)" Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
"IF" function question
Hi,
It finally worked! Thanks! "T. Valko" wrote: The formula I suggested does what you want but it's limited to those EXACT cells only. Try this one in C1 and copy down as needed: =--(OFFSET(C1,,-2)=OFFSET(C1,,-1)) -- Biff Microsoft Excel MVP "Lorderon" wrote in message ... Hi, Thanks for your reply, but unfortunately this didn't. Pls take note the A1 and B1 are Cell locations. Let me explain further what I'm doing.... I'm comparing the data of column A and column B, if they are the same the value on column C will be 1 else it will be 0. So if I found out that the value on Col A and Col B are not the same (meaning vale on Col C is 0), I will "shift down" the data on Col B (the different one only) to match Col A. The problem here is that, the formula on Col C is shifted as well. So for example I shifted the data on cell location B1 downwards the formula on cell location C1 becomes "=if(A1=B2,1,0)". I want it to be still the same as the original. ORIGINAL DATA: Col A Col B Col C cat Dog "=if(A1=B1,1,0)", result is 0 Dog bird "=if(A2=B2,1,0)", result is 0 bird eat "=if(A3=B3,1,0)", result is 0 So, I looked for the data which are different and match them together, so I "moved down" the data on Col B to match Col A, so it becomes.... SHIFTED DATA: Col A Col B Col C (all the formula changed!!) cat "=if(A1=B2,1,0)", result is 0 Dog Dog "=if(A2=B3,1,0)", result is 0 bird bird "=if(A3=B4,1,0)", result is 0 eat As you can see the formula on Col C also shifted, if they maintained my original formula the values on cell C1 is still 0, value on cell C2 will be 1, value on cell C3 will be 1. But now they are all 0 because the formula shifted after I moved down the data on cell location B1. "T. Valko" wrote: Try this in C1: =--(INDIRECT("A1")=INDIRECT("B1")) That will *always* refer to A1 and B1 -- Biff Microsoft Excel MVP "Lorderon" wrote in message ... Hi, I have an IF function comparing 2 cells and it is under column C: A B C cat Dog "=if(A1=B1,1,0)" (here result is 0) Dog bird "=if(A2=B2,1,0)" (here result is 0) Now, every time the result is ZERO in column C, I will move the corresponding data on column B downwards "Insert - Shift cells down". The problem here is that my formula on column C also moves downwards! So if I moved down the data on B1 (Dog), the formula on C1 becomes "=if(A1=B2,1,0)". Is there a way that the formula will still compare the original 2 cells even if I moved the data down?? That is, the formula on C1 will still be "=if(A1=B1,1,0)" Thanks. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
"IF" function question
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Lorderon" wrote in message ... Hi, It finally worked! Thanks! "T. Valko" wrote: The formula I suggested does what you want but it's limited to those EXACT cells only. Try this one in C1 and copy down as needed: =--(OFFSET(C1,,-2)=OFFSET(C1,,-1)) -- Biff Microsoft Excel MVP "Lorderon" wrote in message ... Hi, Thanks for your reply, but unfortunately this didn't. Pls take note the A1 and B1 are Cell locations. Let me explain further what I'm doing.... I'm comparing the data of column A and column B, if they are the same the value on column C will be 1 else it will be 0. So if I found out that the value on Col A and Col B are not the same (meaning vale on Col C is 0), I will "shift down" the data on Col B (the different one only) to match Col A. The problem here is that, the formula on Col C is shifted as well. So for example I shifted the data on cell location B1 downwards the formula on cell location C1 becomes "=if(A1=B2,1,0)". I want it to be still the same as the original. ORIGINAL DATA: Col A Col B Col C cat Dog "=if(A1=B1,1,0)", result is 0 Dog bird "=if(A2=B2,1,0)", result is 0 bird eat "=if(A3=B3,1,0)", result is 0 So, I looked for the data which are different and match them together, so I "moved down" the data on Col B to match Col A, so it becomes.... SHIFTED DATA: Col A Col B Col C (all the formula changed!!) cat "=if(A1=B2,1,0)", result is 0 Dog Dog "=if(A2=B3,1,0)", result is 0 bird bird "=if(A3=B4,1,0)", result is 0 eat As you can see the formula on Col C also shifted, if they maintained my original formula the values on cell C1 is still 0, value on cell C2 will be 1, value on cell C3 will be 1. But now they are all 0 because the formula shifted after I moved down the data on cell location B1. "T. Valko" wrote: Try this in C1: =--(INDIRECT("A1")=INDIRECT("B1")) That will *always* refer to A1 and B1 -- Biff Microsoft Excel MVP "Lorderon" wrote in message ... Hi, I have an IF function comparing 2 cells and it is under column C: A B C cat Dog "=if(A1=B1,1,0)" (here result is 0) Dog bird "=if(A2=B2,1,0)" (here result is 0) Now, every time the result is ZERO in column C, I will move the corresponding data on column B downwards "Insert - Shift cells down". The problem here is that my formula on column C also moves downwards! So if I moved down the data on B1 (Dog), the formula on C1 becomes "=if(A1=B2,1,0)". Is there a way that the formula will still compare the original 2 cells even if I moved the data down?? That is, the formula on C1 will still be "=if(A1=B1,1,0)" Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text "comparison" operator for "contains" used in an "IF" Function | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Question about "Find" function in Edit menu | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
"Disk is Full" add-on question to "Can't reset last cell" post tod | Excel Discussion (Misc queries) |