|
I am having trouble with the IF function and a compound condition
I want to use the IF function to set a value of 54 if A100=369 and
B100="Gas" or A100=391 and B100="Gas". Otherwise the value should be 18. I used =IF((A100=369 and B100="Gas") or (A100=391 and B100="Gas"),54,18) and got an error. I also used =IF(B100="Gas" and (A100=369 or A100=391), 54,18) and also got an error. Any ideas? |
I am having trouble with the IF function and a compound condition
One way:
=IF(AND(OR(A100={369,391}),B100="Gas"),54,18) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "rub" wrote: I want to use the IF function to set a value of 54 if A100=369 and B100="Gas" or A100=391 and B100="Gas". Otherwise the value should be 18. I used =IF((A100=369 and B100="Gas") or (A100=391 and B100="Gas"),54,18) and got an error. I also used =IF(B100="Gas" and (A100=369 or A100=391), 54,18) and also got an error. Any ideas? |
I am having trouble with the IF function and a compound condition
=IF(OR(AND(A100=391,B100="gas"), AND(A100=369,B100="gas")),54,18)
Vaya con Dios, Chuck, CABGx3 "rub" wrote in message ups.com... I want to use the IF function to set a value of 54 if A100=369 and B100="Gas" or A100=391 and B100="Gas". Otherwise the value should be 18. I used =IF((A100=369 and B100="Gas") or (A100=391 and B100="Gas"),54,18) and got an error. I also used =IF(B100="Gas" and (A100=369 or A100=391), 54,18) and also got an error. Any ideas? |
I am having trouble with the IF function and a compound condition
One way is to translate your requirements word for word:
=if(or(and(a100=369,b100="Gas"),and(a100=391,b100= "Gas")),54,18) Another way that's equivalent Since B100 has to be Gas for both 369 and 391: =if(and(b100="gas",or(a100=369,a100=391)),54,18) And another way to write that formula that makes it easier if the number of values in A100 can get bigger: =if(and(b100="gas",or(a100={369,391})),54,18) rub wrote: I want to use the IF function to set a value of 54 if A100=369 and B100="Gas" or A100=391 and B100="Gas". Otherwise the value should be 18. I used =IF((A100=369 and B100="Gas") or (A100=391 and B100="Gas"),54,18) and got an error. I also used =IF(B100="Gas" and (A100=369 or A100=391), 54,18) and also got an error. Any ideas? -- Dave Peterson |
I am having trouble with the IF function and a compound condition
Thanks it works.
Dave Peterson wrote: One way is to translate your requirements word for word: =if(or(and(a100=369,b100="Gas"),and(a100=391,b100= "Gas")),54,18) Another way that's equivalent Since B100 has to be Gas for both 369 and 391: =if(and(b100="gas",or(a100=369,a100=391)),54,18) And another way to write that formula that makes it easier if the number of values in A100 can get bigger: =if(and(b100="gas",or(a100={369,391})),54,18) rub wrote: I want to use the IF function to set a value of 54 if A100=369 and B100="Gas" or A100=391 and B100="Gas". Otherwise the value should be 18. I used =IF((A100=369 and B100="Gas") or (A100=391 and B100="Gas"),54,18) and got an error. I also used =IF(B100="Gas" and (A100=369 or A100=391), 54,18) and also got an error. Any ideas? -- Dave Peterson |
I am having trouble with the IF function and a compound condition
Thanks for your help.
All 3 suggestions work CLR wrote: =IF(OR(AND(A100=391,B100="gas"), AND(A100=369,B100="gas")),54,18) Vaya con Dios, Chuck, CABGx3 "rub" wrote in message ups.com... I want to use the IF function to set a value of 54 if A100=369 and B100="Gas" or A100=391 and B100="Gas". Otherwise the value should be 18. I used =IF((A100=369 and B100="Gas") or (A100=391 and B100="Gas"),54,18) and got an error. I also used =IF(B100="Gas" and (A100=369 or A100=391), 54,18) and also got an error. Any ideas? |
I am having trouble with the IF function and a compound condition
thank you
Max wrote: One way: =IF(AND(OR(A100={369,391}),B100="Gas"),54,18) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "rub" wrote: I want to use the IF function to set a value of 54 if A100=369 and B100="Gas" or A100=391 and B100="Gas". Otherwise the value should be 18. I used =IF((A100=369 and B100="Gas") or (A100=391 and B100="Gas"),54,18) and got an error. I also used =IF(B100="Gas" and (A100=369 or A100=391), 54,18) and also got an error. Any ideas? |
I am having trouble with the IF function and a compound condition
I have a different twist on the same question. For some cells I need
to leave the current value in the cell alone if the IF premise if false. So in other words instead of the value being 18, I would like to not change the value of the cell. Any ideas? rub wrote: I want to use the IF function to set a value of 54 if A100=369 and B100="Gas" or A100=391 and B100="Gas". Otherwise the value should be 18. I used =IF((A100=369 and B100="Gas") or (A100=391 and B100="Gas"),54,18) and got an error. I also used =IF(B100="Gas" and (A100=369 or A100=391), 54,18) and also got an error. Any ideas? |
I am having trouble with the IF function and a compound condition
It depends on what "some cells" is, how many there are, and what values you
wish displayed.........in other words, we need more details........ Vaya con Dios, Chuck, CABGx3 "rub" wrote in message ups.com... I have a different twist on the same question. For some cells I need to leave the current value in the cell alone if the IF premise if false. So in other words instead of the value being 18, I would like to not change the value of the cell. Any ideas? rub wrote: I want to use the IF function to set a value of 54 if A100=369 and B100="Gas" or A100=391 and B100="Gas". Otherwise the value should be 18. I used =IF((A100=369 and B100="Gas") or (A100=391 and B100="Gas"),54,18) and got an error. I also used =IF(B100="Gas" and (A100=369 or A100=391), 54,18) and also got an error. Any ideas? |
I am having trouble with the IF function and a compound condition
Maybe you mean something like this in say, D100:
=IF(C100="","",IF(AND(OR(A100={369,391}),B100="Gas "),54,C100)) Provided C100 isn't empty (this is just a first check done on C100), D100 will then return the value in C100 as the value_if_False (instead of the previous value of 18) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "rub" wrote in message ups.com... I have a different twist on the same question. For some cells I need to leave the current value in the cell alone if the IF premise if false. So in other words instead of the value being 18, I would like to not change the value of the cell. Any ideas? |
I am having trouble with the IF function and a compound condition
welcome ..
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "rub" wrote in message ups.com... thank you |
I am having trouble with the IF function and a compound condition
I am putting this function in every cell in one column so that if that
row contains 369 and "Gas" then 54 should be the result or else what is already in the cell. Address Utility Units 369 Gas 54 1710 Elec 36 The 36 would have already been in the cell. The 54 was added by the formula CLR wrote: It depends on what "some cells" is, how many there are, and what values you wish displayed.........in other words, we need more details........ Vaya con Dios, Chuck, CABGx3 "rub" wrote in message ups.com... I have a different twist on the same question. For some cells I need to leave the current value in the cell alone if the IF premise if false. So in other words instead of the value being 18, I would like to not change the value of the cell. Any ideas? rub wrote: I want to use the IF function to set a value of 54 if A100=369 and B100="Gas" or A100=391 and B100="Gas". Otherwise the value should be 18. I used =IF((A100=369 and B100="Gas") or (A100=391 and B100="Gas"),54,18) and got an error. I also used =IF(B100="Gas" and (A100=369 or A100=391), 54,18) and also got an error. Any ideas? |
I am having trouble with the IF function and a compound condition
That will work if I use a new column. Is there anyway to do it in the
existing column so something like C100=IF(C100="","",IF(AND(OR(A100={369,391}),B100= "Gas"),54,C100)) Max wrote: Maybe you mean something like this in say, D100: =IF(C100="","",IF(AND(OR(A100={369,391}),B100="Gas "),54,C100)) Provided C100 isn't empty (this is just a first check done on C100), D100 will then return the value in C100 as the value_if_False (instead of the previous value of 18) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "rub" wrote in message ups.com... I have a different twist on the same question. For some cells I need to leave the current value in the cell alone if the IF premise if false. So in other words instead of the value being 18, I would like to not change the value of the cell. Any ideas? |
I am having trouble with the IF function and a compound condition
That would require VBA, not "just a formula copied down"..........
If you would rather have a code solution instead of a formula one, post back and someone will help. Vaya con Dios, Chuck, CABGx3 "rub" wrote in message ups.com... I am putting this function in every cell in one column so that if that row contains 369 and "Gas" then 54 should be the result or else what is already in the cell. Address Utility Units 369 Gas 54 1710 Elec 36 The 36 would have already been in the cell. The 54 was added by the formula CLR wrote: It depends on what "some cells" is, how many there are, and what values you wish displayed.........in other words, we need more details........ Vaya con Dios, Chuck, CABGx3 "rub" wrote in message ups.com... I have a different twist on the same question. For some cells I need to leave the current value in the cell alone if the IF premise if false. So in other words instead of the value being 18, I would like to not change the value of the cell. Any ideas? rub wrote: I want to use the IF function to set a value of 54 if A100=369 and B100="Gas" or A100=391 and B100="Gas". Otherwise the value should be 18. I used =IF((A100=369 and B100="Gas") or (A100=391 and B100="Gas"),54,18) and got an error. I also used =IF(B100="Gas" and (A100=369 or A100=391), 54,18) and also got an error. Any ideas? |
I am having trouble with the IF function and a compound condition
Is there anyway to do it in the existing column ..
Similar to Chuck's response in the other branch, not possible via formulas. Hang around awhile for responses from those versed in vba, or try a new posting in .excel.programming. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "rub" wrote in message ps.com... That will work if I use a new column. Is there anyway to do it in the existing column so something like C100=IF(C100="","",IF(AND(OR(A100={369,391}),B100= "Gas"),54,C100)) |
I am having trouble with the IF function and a compound condition
thanks for the help
Max wrote: Is there anyway to do it in the existing column .. Similar to Chuck's response in the other branch, not possible via formulas. Hang around awhile for responses from those versed in vba, or try a new posting in .excel.programming. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "rub" wrote in message ps.com... That will work if I use a new column. Is there anyway to do it in the existing column so something like C100=IF(C100="","",IF(AND(OR(A100={369,391}),B100= "Gas"),54,C100)) |
| All times are GMT +1. The time now is 07:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com