Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Some more help to edit some formulas
Hi all,
First I like to thank Max for all the help of my previous post. Second, I like to thank the others for the help on my other posts I made a few months ago. I have a simular excel file that just has an extra addition, but with my very limited understanding of how you guys make these formulas, I can't quite make an adjustment that works. Here are the cells involved and formulas: Column B: is an identifier number from 1 to 500 Cell D: =CONCATENATE(E28,F28,G28,H28,K28,I28,K28,J28) Cell E: =IF(BB28="—‹",IF(BD28="—‹","B","F"),IF(BD28="—‹ ","I","X")) Cell F: =IF(R280,CONCATENATE(0,LEFT(R28,3)),"xxxx") Cell G: =IF(AB28="R","R",IF(AB28="T","T",IF(AB28="A","A"," X"))) Cell H: =B28 Cell K: _ Cell I: =IF(ISBLANK(AC28)=TRUE,"xxxx",AC28) Cell J: =IF(ISBLANK(AX28)=TRUE,"XXX",LEFT(AX28,3)) Cell AB will have values of either "A", "R", or "T" Cell AZ will have value of either "1.0" or "1.1" Cell BB will have value of either "—‹" or "-" Cell BC will have value of either "—‹" or "-" Cell BD will have value of either "—‹" or "-" Cell BE will have value of either "—‹" or "-" The "—‹" is the degree character, just in Japanese its a circle. With no values inputted for this row Cell C looks like this: XxxxxX014_xxxx_XXX For what I need to be done is, when Cell AB has either "A" or "T" and Cell AZ has 1.0, Cell BB, BC, BD and BE should have "—‹". IF Cell AB has "R" and Cell AZ has 1.0, then Cell BB and BD have "—‹" and Cell BC and BE should have "-". Also when Cell AB has "A", beginning character in Cell C should start with a "B", if Cell AB has "T" or "R", then the beginning character should be an "F". Now, when Cell AZ has 1.1 and Cell AB has either "A" or "T", then Cells BB and BCshould have "—‹" and Cells BD and BE should have "-". If Cell AZ has 1.1 and Cell AB has "R", then Cell BB has "—‹" and Cells BC,BD, and BD has "-". Also when Cell AZ is 1.1, and Cell AB has "A", beginning character in Cell C should start with a "1B", if Cell AB has "T" or "R", then its a "1F". So for example, Cell AF is "A", Cell AZ is "1.0", then Cell C should be "BxxxxX014_xxxx_XXX" and Cells BB to BE have "—‹" or If Cell AF has "T" and Cell AZ is "1.1", then Cell C should be "1FxxxxX014_xxxx_XXX" and Cells BB and BC have "—‹", Cells BD and BE has "-". For a different file with a simular setup, I was kindly given these formulas: =IF(OR(AF445="",AX445=""),"",IF(AX445=1,IF(OR(AF44 5="A",AF445="V"),IF(AZ445="—‹",IF(BA445="-","F","B"),IF(BA445="—‹","I","X")),""),IF(AX445=1 .1,IF(AND(AF445="A",AZ445="—‹",BA445="-"),"1B",IF(AND(AF445="V",AZ445="—‹",BA445="-"),"1F",IF(AND(AF445="A",AZ445="—‹",BA446="—‹"), "1I",IF(AND(AF445="V",AZ445="—‹",BA445="—‹"),"1X ",""))))))) =IF(OR(AX445=1.1,AX445=1),"—‹","") =IF(AX445=1.1,"-",IF(AND(AX445=1,AF445="A"),"—‹",IF(AND(AX445=1,A F445="V"),"-",""))) The Column letters are different, but the sheet format is fairly the same. I hope I haven't confused or discouraged anyone from looking at this. Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Some more help to edit some formulas
Hi,
I was able to figure a few of the problems I had, but now have only one thing that I need help with. So, if anyone is willing, please help me. With no values inputted for this row Cell C looks like this: XxxxxX014_xxxx_XXX When Cell AZ is 1.0, and Cell AB has "A", beginning character in Cell C should start with a "B". If Cell AB has "T" or "R", then the beginning character should be an "F". Also when Cell AZ is 1.1, and Cell AB has "A", beginning character in Cell C should start with a "1B". If Cell AB has "T" or "R", then its a "1F". So for example, Cell AF is "A", Cell AZ is "1.0", then Cell C should be "BxxxxX014_xxxx_XXX" or If Cell AF has "T" and Cell AZ is "1.1", then Cell C should be "1FxxxxX014_xxxx_XXX" Thank you. "Hell-fire" wrote: Hi all, First I like to thank Max for all the help of my previous post. Second, I like to thank the others for the help on my other posts I made a few months ago. I have a simular excel file that just has an extra addition, but with my very limited understanding of how you guys make these formulas, I can't quite make an adjustment that works. Here are the cells involved and formulas: Column B: is an identifier number from 1 to 500 Cell D: =CONCATENATE(E28,F28,G28,H28,K28,I28,K28,J28) Cell E: =IF(BB28="—‹",IF(BD28="—‹","B","F"),IF(BD28="—‹ ","I","X")) Cell F: =IF(R280,CONCATENATE(0,LEFT(R28,3)),"xxxx") Cell G: =IF(AB28="R","R",IF(AB28="T","T",IF(AB28="A","A"," X"))) Cell H: =B28 Cell K: _ Cell I: =IF(ISBLANK(AC28)=TRUE,"xxxx",AC28) Cell J: =IF(ISBLANK(AX28)=TRUE,"XXX",LEFT(AX28,3)) Cell AB will have values of either "A", "R", or "T" Cell AZ will have value of either "1.0" or "1.1" Cell BB will have value of either "—‹" or "-" Cell BC will have value of either "—‹" or "-" Cell BD will have value of either "—‹" or "-" Cell BE will have value of either "—‹" or "-" The "—‹" is the degree character, just in Japanese its a circle. With no values inputted for this row Cell C looks like this: XxxxxX014_xxxx_XXX For what I need to be done is, when Cell AB has either "A" or "T" and Cell AZ has 1.0, Cell BB, BC, BD and BE should have "—‹". IF Cell AB has "R" and Cell AZ has 1.0, then Cell BB and BD have "—‹" and Cell BC and BE should have "-". Also when Cell AB has "A", beginning character in Cell C should start with a "B", if Cell AB has "T" or "R", then the beginning character should be an "F". Now, when Cell AZ has 1.1 and Cell AB has either "A" or "T", then Cells BB and BCshould have "—‹" and Cells BD and BE should have "-". If Cell AZ has 1.1 and Cell AB has "R", then Cell BB has "—‹" and Cells BC,BD, and BD has "-". Also when Cell AZ is 1.1, and Cell AB has "A", beginning character in Cell C should start with a "1B", if Cell AB has "T" or "R", then its a "1F". So for example, Cell AF is "A", Cell AZ is "1.0", then Cell C should be "BxxxxX014_xxxx_XXX" and Cells BB to BE have "—‹" or If Cell AF has "T" and Cell AZ is "1.1", then Cell C should be "1FxxxxX014_xxxx_XXX" and Cells BB and BC have "—‹", Cells BD and BE has "-". For a different file with a simular setup, I was kindly given these formulas: =IF(OR(AF445="",AX445=""),"",IF(AX445=1,IF(OR(AF44 5="A",AF445="V"),IF(AZ445="—‹",IF(BA445="-","F","B"),IF(BA445="—‹","I","X")),""),IF(AX445=1 .1,IF(AND(AF445="A",AZ445="—‹",BA445="-"),"1B",IF(AND(AF445="V",AZ445="—‹",BA445="-"),"1F",IF(AND(AF445="A",AZ445="—‹",BA446="—‹"), "1I",IF(AND(AF445="V",AZ445="—‹",BA445="—‹"),"1X ",""))))))) =IF(OR(AX445=1.1,AX445=1),"—‹","") =IF(AX445=1.1,"-",IF(AND(AX445=1,AF445="A"),"—‹",IF(AND(AX445=1,A F445="V"),"-",""))) The Column letters are different, but the sheet format is fairly the same. I hope I haven't confused or discouraged anyone from looking at this. Thank you. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Some more help to edit some formulas
Maybe this does what you are asking for?
=IF(AZ10="1.0",IF(AB10="A","B","X"),IF(AZ10="1.1", IF(AB10="A","1B",IF(OR(AB10="T",AB10="R"),"F","X") )))&"xxxxX014_xxxx_XXX" Rick "Hell-fire" wrote in message ... Hi, I was able to figure a few of the problems I had, but now have only one thing that I need help with. So, if anyone is willing, please help me. With no values inputted for this row Cell C looks like this: XxxxxX014_xxxx_XXX When Cell AZ is 1.0, and Cell AB has "A", beginning character in Cell C should start with a "B". If Cell AB has "T" or "R", then the beginning character should be an "F". Also when Cell AZ is 1.1, and Cell AB has "A", beginning character in Cell C should start with a "1B". If Cell AB has "T" or "R", then its a "1F". So for example, Cell AF is "A", Cell AZ is "1.0", then Cell C should be "BxxxxX014_xxxx_XXX" or If Cell AF has "T" and Cell AZ is "1.1", then Cell C should be "1FxxxxX014_xxxx_XXX" Thank you. "Hell-fire" wrote: Hi all, First I like to thank Max for all the help of my previous post. Second, I like to thank the others for the help on my other posts I made a few months ago. I have a simular excel file that just has an extra addition, but with my very limited understanding of how you guys make these formulas, I can't quite make an adjustment that works. Here are the cells involved and formulas: Column B: is an identifier number from 1 to 500 Cell D: =CONCATENATE(E28,F28,G28,H28,K28,I28,K28,J28) Cell E: =IF(BB28="—‹",IF(BD28="—‹","B","F"),IF(BD28="—‹ ","I","X")) Cell F: =IF(R280,CONCATENATE(0,LEFT(R28,3)),"xxxx") Cell G: =IF(AB28="R","R",IF(AB28="T","T",IF(AB28="A","A"," X"))) Cell H: =B28 Cell K: _ Cell I: =IF(ISBLANK(AC28)=TRUE,"xxxx",AC28) Cell J: =IF(ISBLANK(AX28)=TRUE,"XXX",LEFT(AX28,3)) Cell AB will have values of either "A", "R", or "T" Cell AZ will have value of either "1.0" or "1.1" Cell BB will have value of either "—‹" or "-" Cell BC will have value of either "—‹" or "-" Cell BD will have value of either "—‹" or "-" Cell BE will have value of either "—‹" or "-" The "—‹" is the degree character, just in Japanese its a circle. With no values inputted for this row Cell C looks like this: XxxxxX014_xxxx_XXX For what I need to be done is, when Cell AB has either "A" or "T" and Cell AZ has 1.0, Cell BB, BC, BD and BE should have "—‹". IF Cell AB has "R" and Cell AZ has 1.0, then Cell BB and BD have "—‹" and Cell BC and BE should have "-". Also when Cell AB has "A", beginning character in Cell C should start with a "B", if Cell AB has "T" or "R", then the beginning character should be an "F". Now, when Cell AZ has 1.1 and Cell AB has either "A" or "T", then Cells BB and BCshould have "—‹" and Cells BD and BE should have "-". If Cell AZ has 1.1 and Cell AB has "R", then Cell BB has "—‹" and Cells BC,BD, and BD has "-". Also when Cell AZ is 1.1, and Cell AB has "A", beginning character in Cell C should start with a "1B", if Cell AB has "T" or "R", then its a "1F". So for example, Cell AF is "A", Cell AZ is "1.0", then Cell C should be "BxxxxX014_xxxx_XXX" and Cells BB to BE have "—‹" or If Cell AF has "T" and Cell AZ is "1.1", then Cell C should be "1FxxxxX014_xxxx_XXX" and Cells BB and BC have "—‹", Cells BD and BE has "-". For a different file with a simular setup, I was kindly given these formulas: =IF(OR(AF445="",AX445=""),"",IF(AX445=1,IF(OR(AF44 5="A",AF445="V"),IF(AZ445="—‹",IF(BA445="-","F","B"),IF(BA445="—‹","I","X")),""),IF(AX445=1 .1,IF(AND(AF445="A",AZ445="—‹",BA445="-"),"1B",IF(AND(AF445="V",AZ445="—‹",BA445="-"),"1F",IF(AND(AF445="A",AZ445="—‹",BA446="—‹"), "1I",IF(AND(AF445="V",AZ445="—‹",BA445="—‹"),"1X ",""))))))) =IF(OR(AX445=1.1,AX445=1),"—‹","") =IF(AX445=1.1,"-",IF(AND(AX445=1,AF445="A"),"—‹",IF(AND(AX445=1,A F445="V"),"-",""))) The Column letters are different, but the sheet format is fairly the same. I hope I haven't confused or discouraged anyone from looking at this. Thank you. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Some more help to edit some formulas
Think you could try this in C28:
=IF(OR(AZ28="",AB28=""),"",IF(AND(AZ28=1,AB28="A") ,"B"&D28,IF(AND(AZ28=1,OR(AB28={"T","R"})),"F"&D28 ,IF(AND(AZ28=1.1,AB28="A"),"1B"&D28,IF(AND(AZ28=1. 1,OR(AB28={"T","R"})),"1F"&D28,""))))) I presumed that the conditional prefix (ie: B,F,1B,1F) is to be concatenated with the string generated in D28, and that nothing is to show in C28 if either AZ28 or AB28 is empty It's easier/shorter to just use the ampersand operator: & to concatenate cells or constants and cells, instead of the longish CONCATENATE function. (I used "&" liberally in the above, eg: ... "1F"&D28, ..) Trust the above does it here for you. If it did help, could you kindly click the "Yes" button at the bottom as the answer to the question: "Was this post helpful to you?" before you post your reply to me from where you're reading this in MS' webpage <g. Thanks. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Hell-fire" wrote: Hi, I was able to figure a few of the problems I had, but now have only one thing that I need help with. So, if anyone is willing, please help me. With no values inputted for this row Cell C looks like this: XxxxxX014_xxxx_XXX When Cell AZ is 1.0, and Cell AB has "A", beginning character in Cell C should start with a "B". If Cell AB has "T" or "R", then the beginning character should be an "F". Also when Cell AZ is 1.1, and Cell AB has "A", beginning character in Cell C should start with a "1B". If Cell AB has "T" or "R", then its a "1F". So for example, Cell AF is "A", Cell AZ is "1.0", then Cell C should be "BxxxxX014_xxxx_XXX" or If Cell AF has "T" and Cell AZ is "1.1", then Cell C should be "1FxxxxX014_xxxx_XXX" Thank you. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Some more help to edit some formulas
Hi Rick,
It didn't quite work, but no worries, the solution Max gave me works. Thank you for looking at my post. "Rick Rothstein (MVP - VB)" wrote: Maybe this does what you are asking for? =IF(AZ10="1.0",IF(AB10="A","B","X"),IF(AZ10="1.1", IF(AB10="A","1B",IF(OR(AB10="T",AB10="R"),"F","X") )))&"xxxxX014_xxxx_XXX" Rick "Hell-fire" wrote in message ... Hi, I was able to figure a few of the problems I had, but now have only one thing that I need help with. So, if anyone is willing, please help me. With no values inputted for this row Cell C looks like this: XxxxxX014_xxxx_XXX When Cell AZ is 1.0, and Cell AB has "A", beginning character in Cell C should start with a "B". If Cell AB has "T" or "R", then the beginning character should be an "F". Also when Cell AZ is 1.1, and Cell AB has "A", beginning character in Cell C should start with a "1B". If Cell AB has "T" or "R", then its a "1F". So for example, Cell AF is "A", Cell AZ is "1.0", then Cell C should be "BxxxxX014_xxxx_XXX" or If Cell AF has "T" and Cell AZ is "1.1", then Cell C should be "1FxxxxX014_xxxx_XXX" Thank you. "Hell-fire" wrote: Hi all, First I like to thank Max for all the help of my previous post. Second, I like to thank the others for the help on my other posts I made a few months ago. I have a simular excel file that just has an extra addition, but with my very limited understanding of how you guys make these formulas, I can't quite make an adjustment that works. Here are the cells involved and formulas: Column B: is an identifier number from 1 to 500 Cell D: =CONCATENATE(E28,F28,G28,H28,K28,I28,K28,J28) Cell E: =IF(BB28="—‹",IF(BD28="—‹","B","F"),IF(BD28="—‹ ","I","X")) Cell F: =IF(R280,CONCATENATE(0,LEFT(R28,3)),"xxxx") Cell G: =IF(AB28="R","R",IF(AB28="T","T",IF(AB28="A","A"," X"))) Cell H: =B28 Cell K: _ Cell I: =IF(ISBLANK(AC28)=TRUE,"xxxx",AC28) Cell J: =IF(ISBLANK(AX28)=TRUE,"XXX",LEFT(AX28,3)) Cell AB will have values of either "A", "R", or "T" Cell AZ will have value of either "1.0" or "1.1" Cell BB will have value of either "—‹" or "-" Cell BC will have value of either "—‹" or "-" Cell BD will have value of either "—‹" or "-" Cell BE will have value of either "—‹" or "-" The "—‹" is the degree character, just in Japanese its a circle. With no values inputted for this row Cell C looks like this: XxxxxX014_xxxx_XXX For what I need to be done is, when Cell AB has either "A" or "T" and Cell AZ has 1.0, Cell BB, BC, BD and BE should have "—‹". IF Cell AB has "R" and Cell AZ has 1.0, then Cell BB and BD have "—‹" and Cell BC and BE should have "-". Also when Cell AB has "A", beginning character in Cell C should start with a "B", if Cell AB has "T" or "R", then the beginning character should be an "F". Now, when Cell AZ has 1.1 and Cell AB has either "A" or "T", then Cells BB and BCshould have "—‹" and Cells BD and BE should have "-". If Cell AZ has 1.1 and Cell AB has "R", then Cell BB has "—‹" and Cells BC,BD, and BD has "-". Also when Cell AZ is 1.1, and Cell AB has "A", beginning character in Cell C should start with a "1B", if Cell AB has "T" or "R", then its a "1F". So for example, Cell AF is "A", Cell AZ is "1.0", then Cell C should be "BxxxxX014_xxxx_XXX" and Cells BB to BE have "—‹" or If Cell AF has "T" and Cell AZ is "1.1", then Cell C should be "1FxxxxX014_xxxx_XXX" and Cells BB and BC have "—‹", Cells BD and BE has "-". For a different file with a simular setup, I was kindly given these formulas: =IF(OR(AF445="",AX445=""),"",IF(AX445=1,IF(OR(AF44 5="A",AF445="V"),IF(AZ445="—‹",IF(BA445="-","F","B"),IF(BA445="—‹","I","X")),""),IF(AX445=1 .1,IF(AND(AF445="A",AZ445="—‹",BA445="-"),"1B",IF(AND(AF445="V",AZ445="—‹",BA445="-"),"1F",IF(AND(AF445="A",AZ445="—‹",BA446="—‹"), "1I",IF(AND(AF445="V",AZ445="—‹",BA445="—‹"),"1X ",""))))))) =IF(OR(AX445=1.1,AX445=1),"—‹","") =IF(AX445=1.1,"-",IF(AND(AX445=1,AF445="A"),"—‹",IF(AND(AX445=1,A F445="V"),"-",""))) The Column letters are different, but the sheet format is fairly the same. I hope I haven't confused or discouraged anyone from looking at this. Thank you. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Some more help to edit some formulas
Hi Max,
Thanks again for helping me out. The formula works after I took out the D28 from it. Other than that, it is doing what its suppose to do. Examining the formulas you gave me before has taught me a lot on how the logic on how they work. Thanks again. "Max" wrote: Think you could try this in C28: =IF(OR(AZ28="",AB28=""),"",IF(AND(AZ28=1,AB28="A") ,"B"&D28,IF(AND(AZ28=1,OR(AB28={"T","R"})),"F"&D28 ,IF(AND(AZ28=1.1,AB28="A"),"1B"&D28,IF(AND(AZ28=1. 1,OR(AB28={"T","R"})),"1F"&D28,""))))) I presumed that the conditional prefix (ie: B,F,1B,1F) is to be concatenated with the string generated in D28, and that nothing is to show in C28 if either AZ28 or AB28 is empty It's easier/shorter to just use the ampersand operator: & to concatenate cells or constants and cells, instead of the longish CONCATENATE function. (I used "&" liberally in the above, eg: ... "1F"&D28, ..) Trust the above does it here for you. If it did help, could you kindly click the "Yes" button at the bottom as the answer to the question: "Was this post helpful to you?" before you post your reply to me from where you're reading this in MS' webpage <g. Thanks. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Hell-fire" wrote: Hi, I was able to figure a few of the problems I had, but now have only one thing that I need help with. So, if anyone is willing, please help me. With no values inputted for this row Cell C looks like this: XxxxxX014_xxxx_XXX When Cell AZ is 1.0, and Cell AB has "A", beginning character in Cell C should start with a "B". If Cell AB has "T" or "R", then the beginning character should be an "F". Also when Cell AZ is 1.1, and Cell AB has "A", beginning character in Cell C should start with a "1B". If Cell AB has "T" or "R", then its a "1F". So for example, Cell AF is "A", Cell AZ is "1.0", then Cell C should be "BxxxxX014_xxxx_XXX" or If Cell AF has "T" and Cell AZ is "1.1", then Cell C should be "1FxxxxX014_xxxx_XXX" Thank you. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Some more help to edit some formulas
welcome, Hell-fire.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Hell-fire" wrote in message ... Hi Max, Thanks again for helping me out. The formula works after I took out the D28 from it. Other than that, it is doing what its suppose to do. Examining the formulas you gave me before has taught me a lot on how the logic on how they work. Thanks again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
Edit Paste Special Formulas | Excel Worksheet Functions | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
hide formulas but can edit on the same cells | Charts and Charting in Excel | |||
Why can't I edit my excel document? Edit buttons shaded. | New Users to Excel |