Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Fill a blank cell with a value using if conditions
I have data in 3 columns.using "if "' condition I have to, upto a
certain number, fill a value in 4th column.There may be blank cells in 4th column.Again I have to scroll down the rows and fill blank cells in the 4th column using another "if " condition.That is I want to fill cells in 4th column, only when it is blankHow to do it? I have given an illustration below; Excel Sheet contains data as follows: A B C D 1 S M 1 2 B F 1 3 M F 2 4 S M 1 5 B M 1 6 S M 1 7 M F 2 8 S M 1 Using if condition that is if A=''S" and B="M", I fill 4th column 1st and 4th row with a value "True1" for 2 times A B C D 1 S M 1 True1 2 B F 1 3 M F 2 4 S M 1 True1 5 B M 1 6 S M 1 7 M F 2 8 S M 1 Again i want to use another set of If conditions such as if A=S,and B=M ,I want to fill 4th column 6th and 8 th rows with a value "True2" for 2 times as shown below: A B C D 1 S M 1 True1 2 B F 1 3 M F 2 4 S M 1 True1 5 B M 1 6 S M 1 True2 7 M F 2 8 S M 1 True2 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Fill a blank cell with a value using if conditions
Try
=IF(AND(A1="S",B1="M"),"True"&INT((SUMPRODUCT(--($A$1:A1="S"),--($B$1:B1="M"))+1)/2),"") best wishes -- Bernard Liengme http://people.stfx.ca/bliengme Microsoft Excel MVP "rjagathe" wrote in message ... I have data in 3 columns.using "if "' condition I have to, upto a certain number, fill a value in 4th column.There may be blank cells in 4th column.Again I have to scroll down the rows and fill blank cells in the 4th column using another "if " condition.That is I want to fill cells in 4th column, only when it is blankHow to do it? I have given an illustration below; Excel Sheet contains data as follows: A B C D 1 S M 1 2 B F 1 3 M F 2 4 S M 1 5 B M 1 6 S M 1 7 M F 2 8 S M 1 Using if condition that is if A=''S" and B="M", I fill 4th column 1st and 4th row with a value "True1" for 2 times A B C D 1 S M 1 True1 2 B F 1 3 M F 2 4 S M 1 True1 5 B M 1 6 S M 1 7 M F 2 8 S M 1 Again i want to use another set of If conditions such as if A=S,and B=M ,I want to fill 4th column 6th and 8 th rows with a value "True2" for 2 times as shown below: A B C D 1 S M 1 True1 2 B F 1 3 M F 2 4 S M 1 True1 5 B M 1 6 S M 1 True2 7 M F 2 8 S M 1 True2 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Fill a blank cell with a value using if conditions
On Dec 17, 10:38*pm, "Bernard Liengme"
wrote: Try =IF(AND(A1="S",B1="M"),"True"&INT((SUMPRODUCT(--($A$1:A1="S"),--($B$1:B1="M "))+1)/2),"") best wishes -- Bernard Liengmehttp://people.stfx.ca/bliengme Microsoft Excel MVP "rjagathe" wrote in message ... I have data in 3 columns.using "if "' condition I have to, upto a certain number, fill a value in 4th column.There may be blank cells in 4th column.Again I have to scroll down the rows and fill blank cells in the 4th column using another "if " *condition.That is I want to fill cells in 4th column, only when it is blankHow to do it? *I have given an illustration below; Excel Sheet contains data as follows: * * * A * *B * *C * D *1 * S * *M * *1 *2 * B * *F * * 1 *3 * M * F * * *2 *4 * S * *M * * 1 *5 * B * *M * *1 6 * S * * M * * 1 7 * M * *F * * * 2 8 * *S * * M * * 1 Using if condition that is if A=''S" and B="M", I fill 4th column 1st and 4th *row with a value "True1" for 2 times * * * A * *B * *C * D *1 * S * *M * *1 * *True1 *2 * B * *F * * 1 *3 * M * F * * *2 *4 * S * M * *1 * *True1 *5 * B * *M * *1 6 * S * * M * * 1 7 * M * *F * * * 2 8 * *S * * M * * 1 Again i want to use another set of If conditions such as if A=S,and B=M ,I want to fill 4th column 6th and 8 th *rows with a value "True2" for 2 times as shown below: * * A * *B * *C * *D 1 * S * *M * *1 * *True1 2 * B * *F * * 1 3 * M * *F * *2 4 * S * *M * *1 * *True1 5 * B * *M * *1 6 * S * *M * *1 * *True2 7 * M * F * * *2 8 * S * *M * *1 * *True2 Sir, I pasted your formula in the formula bar and clicked "Enter" key.But an error message appears stating that there are some errors. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Fill a blank cell with a value using if conditions
The formula works for me. Make sure you didn't pick up a Line Feed character
at the end of the formula when you copied it... after you paste the formula into the Formula Bar, it should occupy only one line... if you see the text cursor on a different line, then you picked up a Line Feed and the will make the formula not work. To correct the problem (if this is what went wrong), just hit the BackSpace key to remove the Line Feed (if done correctly, the text cursor will be at the end of the formula; hit the Enter key to commit the formula). -- Rick (MVP - Excel) "rjagathe" wrote in message ... On Dec 17, 10:38 pm, "Bernard Liengme" wrote: Try =IF(AND(A1="S",B1="M"),"True"&INT((SUMPRODUCT(--($A$1:A1="S"),--($B$1:B1="M "))+1)/2),"") best wishes -- Bernard Liengmehttp://people.stfx.ca/bliengme Microsoft Excel MVP "rjagathe" wrote in message ... I have data in 3 columns.using "if "' condition I have to, upto a certain number, fill a value in 4th column.There may be blank cells in 4th column.Again I have to scroll down the rows and fill blank cells in the 4th column using another "if " condition.That is I want to fill cells in 4th column, only when it is blankHow to do it? I have given an illustration below; Excel Sheet contains data as follows: A B C D 1 S M 1 2 B F 1 3 M F 2 4 S M 1 5 B M 1 6 S M 1 7 M F 2 8 S M 1 Using if condition that is if A=''S" and B="M", I fill 4th column 1st and 4th row with a value "True1" for 2 times A B C D 1 S M 1 True1 2 B F 1 3 M F 2 4 S M 1 True1 5 B M 1 6 S M 1 7 M F 2 8 S M 1 Again i want to use another set of If conditions such as if A=S,and B=M ,I want to fill 4th column 6th and 8 th rows with a value "True2" for 2 times as shown below: A B C D 1 S M 1 True1 2 B F 1 3 M F 2 4 S M 1 True1 5 B M 1 6 S M 1 True2 7 M F 2 8 S M 1 True2 Sir, I pasted your formula in the formula bar and clicked "Enter" key.But an error message appears stating that there are some errors. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Fill a blank cell with a value using if conditions
On Dec 18, 11:17*pm, "Rick Rothstein"
wrote: The formula works for me. Make sure you didn't pick up a Line Feed character at the end of the formula when you copied it... after you paste the formula into the Formula Bar, it should occupy only one line... if you see the text cursor on a different line, then you picked up a Line Feed and the will make the formula not work. To correct the problem (if this is what went wrong), just hit the BackSpace key to remove the Line Feed (if done correctly, the text cursor will be at the end of the formula; hit the Enter key to commit the formula). -- Rick (MVP - Excel) "rjagathe" wrote in message ... On Dec 17, 10:38 pm, "Bernard Liengme" wrote: Try =IF(AND(A1="S",B1="M"),"True"&INT((SUMPRODUCT(--($A$1:A1="S"),--($B$1:B1="M "))+1)/2),"") best wishes -- Bernard Liengmehttp://people.stfx.ca/bliengme Microsoft Excel MVP "rjagathe" wrote in message ... I have data in 3 columns.using "if "' condition I have to, upto a certain number, fill a value in 4th column.There may be blank cells in 4th column.Again I have to scroll down the rows and fill blank cells in the 4th column using another "if " condition.That is I want to fill cells in 4th column, only when it is blankHow to do it? I have given an illustration below; Excel Sheet contains data as follows: A B C D 1 S M 1 2 B F 1 3 M F 2 4 S M 1 5 B M 1 6 S M 1 7 M F 2 8 S M 1 Using if condition that is if A=''S" and B="M", I fill 4th column 1st and 4th row with a value "True1" for 2 times A B C D 1 S M 1 True1 2 B F 1 3 M F 2 4 S M 1 True1 5 B M 1 6 S M 1 7 M F 2 8 S M 1 Again i want to use another set of If conditions such as if A=S,and B=M ,I want to fill 4th column 6th and 8 th rows with a value "True2" for 2 times as shown below: A B C D 1 S M 1 True1 2 B F 1 3 M F 2 4 S M 1 True1 5 B M 1 6 S M 1 True2 7 M F 2 8 S M 1 True2 Sir, * * I pasted your formula in the formula bar and clicked "Enter" key.But an error message appears stating that there are some errors. Now it works.But it returns only the value "True0" in all the 4 rows viz., D1, D4,D6 and D8.I want "True1" in D1 and D4 and "True2" in D6 and D8. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Fill a blank cell with a value using if conditions
I'm not sure what to tell you... the formula produces True1 and True2 in the
correct locations using the sample data you posted... I can find no way for that formula to produce True0... did you copy/paste the formula that Bernard posted or did you try to type it in? If you tried to type it in, I'm guessing you did so incorrectly... try copy/pasting the formula instead. -- Rick (MVP - Excel) "rjagathe" wrote in message ... On Dec 18, 11:17 pm, "Rick Rothstein" wrote: The formula works for me. Make sure you didn't pick up a Line Feed character at the end of the formula when you copied it... after you paste the formula into the Formula Bar, it should occupy only one line... if you see the text cursor on a different line, then you picked up a Line Feed and the will make the formula not work. To correct the problem (if this is what went wrong), just hit the BackSpace key to remove the Line Feed (if done correctly, the text cursor will be at the end of the formula; hit the Enter key to commit the formula). -- Rick (MVP - Excel) "rjagathe" wrote in message ... On Dec 17, 10:38 pm, "Bernard Liengme" wrote: Try =IF(AND(A1="S",B1="M"),"True"&INT((SUMPRODUCT(--($A$1:A1="S"),--($B$1:B1="M "))+1)/2),"") best wishes -- Bernard Liengmehttp://people.stfx.ca/bliengme Microsoft Excel MVP "rjagathe" wrote in message ... I have data in 3 columns.using "if "' condition I have to, upto a certain number, fill a value in 4th column.There may be blank cells in 4th column.Again I have to scroll down the rows and fill blank cells in the 4th column using another "if " condition.That is I want to fill cells in 4th column, only when it is blankHow to do it? I have given an illustration below; Excel Sheet contains data as follows: A B C D 1 S M 1 2 B F 1 3 M F 2 4 S M 1 5 B M 1 6 S M 1 7 M F 2 8 S M 1 Using if condition that is if A=''S" and B="M", I fill 4th column 1st and 4th row with a value "True1" for 2 times A B C D 1 S M 1 True1 2 B F 1 3 M F 2 4 S M 1 True1 5 B M 1 6 S M 1 7 M F 2 8 S M 1 Again i want to use another set of If conditions such as if A=S,and B=M ,I want to fill 4th column 6th and 8 th rows with a value "True2" for 2 times as shown below: A B C D 1 S M 1 True1 2 B F 1 3 M F 2 4 S M 1 True1 5 B M 1 6 S M 1 True2 7 M F 2 8 S M 1 True2 Sir, I pasted your formula in the formula bar and clicked "Enter" key.But an error message appears stating that there are some errors. Now it works.But it returns only the value "True0" in all the 4 rows viz., D1, D4,D6 and D8.I want "True1" in D1 and D4 and "True2" in D6 and D8. |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Fill a blank cell with a value using if conditions
On Dec 20, 1:33*pm, "Rick Rothstein"
wrote: I'm not sure what to tell you... the formula produces True1 and True2 in the correct locations using the sample data you posted... I can find no way for that formula to produce True0... did you copy/paste the formula that Bernard posted or did you try to type it in? If you tried to type it in, I'm guessing you did so incorrectly... try copy/pasting the formula instead. -- Rick (MVP - Excel) "rjagathe" wrote in message ... On Dec 18, 11:17 pm, "Rick Rothstein" wrote: The formula works for me. Make sure you didn't pick up a Line Feed character at the end of the formula when you copied it... after you paste the formula into the Formula Bar, it should occupy only one line... if you see the text cursor on a different line, then you picked up a Line Feed and the will make the formula not work. To correct the problem (if this is what went wrong), just hit the BackSpace key to remove the Line Feed (if done correctly, the text cursor will be at the end of the formula; hit the Enter key to commit the formula). -- Rick (MVP - Excel) "rjagathe" wrote in message .... On Dec 17, 10:38 pm, "Bernard Liengme" wrote: Try =IF(AND(A1="S",B1="M"),"True"&INT((SUMPRODUCT(--($A$1:A1="S"),--($B$1:B1="M "))+1)/2),"") best wishes -- Bernard Liengmehttp://people.stfx.ca/bliengme Microsoft Excel MVP "rjagathe" wrote in message .... I have data in 3 columns.using "if "' condition I have to, upto a certain number, fill a value in 4th column.There may be blank cells in 4th column.Again I have to scroll down the rows and fill blank cells in the 4th column using another "if " condition.That is I want to fill cells in 4th column, only when it is blankHow to do it? I have given an illustration below; Excel Sheet contains data as follows: A B C D 1 S M 1 2 B F 1 3 M F 2 4 S M 1 5 B M 1 6 S M 1 7 M F 2 8 S M 1 Using if condition that is if A=''S" and B="M", I fill 4th column 1st and 4th row with a value "True1" for 2 times A B C D 1 S M 1 True1 2 B F 1 3 M F 2 4 S M 1 True1 5 B M 1 6 S M 1 7 M F 2 8 S M 1 Again i want to use another set of If conditions such as if A=S,and B=M ,I want to fill 4th column 6th and 8 th rows with a value "True2" for 2 times as shown below: A B C D 1 S M 1 True1 2 B F 1 3 M F 2 4 S M 1 True1 5 B M 1 6 S M 1 True2 7 M F 2 8 S M 1 True2 Sir, I pasted your formula in the formula bar and clicked "Enter" key.But an error message appears stating that there are some errors. Now it works.But it returns only the value "True0" in all the 4 rows viz., D1, D4,D6 and D8.I want "True1" in D1 and D4 and "True2" in D6 and D8. I am using office 2007.I have copy -pasted the formula.Will it work in office 2007.May be that is the problem. |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Fill a blank cell with a value using if conditions
On Dec 20, 4:26*pm, rjagathe wrote:
On Dec 20, 1:33*pm, "Rick Rothstein" wrote: I'm not sure what to tell you... the formula produces True1 and True2 in the correct locations using the sample data you posted... I can find no way for that formula to produce True0... did you copy/paste the formula that Bernard posted or did you try to type it in? If you tried to type it in, I'm guessing you did so incorrectly... try copy/pasting the formula instead.. -- Rick (MVP - Excel) "rjagathe" wrote in message .... On Dec 18, 11:17 pm, "Rick Rothstein" wrote: The formula works for me. Make sure you didn't pick up a Line Feed character at the end of the formula when you copied it... after you paste the formula into the Formula Bar, it should occupy only one line... if you see the text cursor on a different line, then you picked up a Line Feed and the will make the formula not work. To correct the problem (if this is what went wrong), just hit the BackSpace key to remove the Line Feed (if done correctly, the text cursor will be at the end of the formula; hit the Enter key to commit the formula). -- Rick (MVP - Excel) "rjagathe" wrote in message .... On Dec 17, 10:38 pm, "Bernard Liengme" wrote: Try =IF(AND(A1="S",B1="M"),"True"&INT((SUMPRODUCT(--($A$1:A1="S"),--($B$1:B1="M "))+1)/2),"") best wishes -- Bernard Liengmehttp://people.stfx.ca/bliengme Microsoft Excel MVP "rjagathe" wrote in message .... I have data in 3 columns.using "if "' condition I have to, upto a certain number, fill a value in 4th column.There may be blank cells in 4th column.Again I have to scroll down the rows and fill blank cells in the 4th column using another "if " condition.That is I want to fill cells in 4th column, only when it is blankHow to do it? I have given an illustration below; Excel Sheet contains data as follows: A B C D 1 S M 1 2 B F 1 3 M F 2 4 S M 1 5 B M 1 6 S M 1 7 M F 2 8 S M 1 Using if condition that is if A=''S" and B="M", I fill 4th column 1st and 4th row with a value "True1" for 2 times A B C D 1 S M 1 True1 2 B F 1 3 M F 2 4 S M 1 True1 5 B M 1 6 S M 1 7 M F 2 8 S M 1 Again i want to use another set of If conditions such as if A=S,and B=M ,I want to fill 4th column 6th and 8 th rows with a value "True2" for 2 times as shown below: A B C D 1 S M 1 True1 2 B F 1 3 M F 2 4 S M 1 True1 5 B M 1 6 S M 1 True2 7 M F 2 8 S M 1 True2 Sir, I pasted your formula in the formula bar and clicked "Enter" key.But an error message appears stating that there are some errors. Now it works.But it returns only the value "True0" in all the 4 rows viz., D1, D4,D6 and D8.I want "True1" in D1 and D4 and "True2" in D6 and D8. I am using office 2007.I have copy -pasted the formula.Will it work in office 2007.May be that is the problem. I found the bug .There is an unnecessary space $B$1:B1="M ".Now it works fine. As an improvement to this formula ,I want "5n' number of occurrences (not just 2) in the results showing True 1 and True 2 and so on.That is I may want 5, "True1" , 15 "True2" and 55 "True3".Pl help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do include a (blank) cell within a Custom Fill List? | Excel Discussion (Misc queries) | |||
returning blank cell when conditions not met | Excel Worksheet Functions | |||
Automatically fill blank cell by same as just above record | Excel Discussion (Misc queries) | |||
Fill column blanks from last non-blank cell | Excel Discussion (Misc queries) | |||
Fill cell that is blank | Excel Worksheet Functions |