| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Formula
Hi all,
I am getting so confused with this formula that my head is aching can anyone help? =IF((AB16="",0,AND(COUNTIF($AB$16:$AB$30,AB16)=1,B 16='lot', C16='Y')),COUNTIF($AB$16:$AB$30,AB16),1/COUNTIF($AB$16:$AB$30,AB16))) I am guessing that i dont have the syntax correct or the brackets in the correct place, please let me know if you need further explanation although im just as confused. Thanks Tanya |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with Formula
I think you mean something like this:
=IF(AB16="",0,IF(AND(COUNTIF($AB$16:$AB$30,AB16)=1 ,B16="lot",C16="Y"), 1,1/COUNTIF($AB$16:$AB$30,AB16))) All one formula - be wary of spurious linebreaks. Hope this helps. Pete On Jul 3, 11:14 pm, Tanya wrote: Hi all, I am getting so confused with this formula that my head is aching can anyone help? =IF((AB16="",0,AND(COUNTIF($AB$16:$AB$30,AB16)=1,B 16='lot', C16='Y')),COUNTIF($AB$16:$AB$30,AB16),1/COUNTIF($AB$16:$AB$30,AB16))) I am guessing that i dont have the syntax correct or the brackets in the correct place, please let me know if you need further explanation although im just as confused. Thanks Tanya |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with Formula
The first part looks like it should be
=IF(AB16="",0,IF(AND(COUNTIF($AB$16:$AB$30,AB16)=1 ,B16="lot",C16="Y") now the question is, what do you want if the IF(AND part is TRUE If the countif part is 1, B16 equals lot and C16 is Y, what do you want as a result? If you want the result of the countif you might as well put 1 there since that is the condition, so maybe =IF(AB16="",0,IF(AND(COUNTIF($AB$16:$AB$30,AB16)=1 ,B16="lot",C16="Y"),1,1/COUNTIF($AB$16:$AB$30,AB16))) -- Regards, Peo Sjoblom "Tanya" wrote in message ... Hi all, I am getting so confused with this formula that my head is aching can anyone help? =IF((AB16="",0,AND(COUNTIF($AB$16:$AB$30,AB16)=1,B 16='lot', C16='Y')),COUNTIF($AB$16:$AB$30,AB16),1/COUNTIF($AB$16:$AB$30,AB16))) I am guessing that i dont have the syntax correct or the brackets in the correct place, please let me know if you need further explanation although im just as confused. Thanks Tanya |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with Formula
Hi Pete,
Thanks thats fantastic it was doing my head in 8-) Tanya "Pete_UK" wrote: I think you mean something like this: =IF(AB16="",0,IF(AND(COUNTIF($AB$16:$AB$30,AB16)=1 ,B16="lot",C16="Y"), 1,1/COUNTIF($AB$16:$AB$30,AB16))) All one formula - be wary of spurious linebreaks. Hope this helps. Pete On Jul 3, 11:14 pm, Tanya wrote: Hi all, I am getting so confused with this formula that my head is aching can anyone help? =IF((AB16="",0,AND(COUNTIF($AB$16:$AB$30,AB16)=1,B 16='lot', C16='Y')),COUNTIF($AB$16:$AB$30,AB16),1/COUNTIF($AB$16:$AB$30,AB16))) I am guessing that i dont have the syntax correct or the brackets in the correct place, please let me know if you need further explanation although im just as confused. Thanks Tanya |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with Formula
Sorry me again, the only problem i have is that when i delete the word lot
out of B16, A16 still contains 1 when it should be 0? and the same with when i delete Y out of C16 again A16 should be 0 it needs to meet both those criteria to = 1, which is what i thought the formula was doing? Thanks again tanya "Pete_UK" wrote: I think you mean something like this: =IF(AB16="",0,IF(AND(COUNTIF($AB$16:$AB$30,AB16)=1 ,B16="lot",C16="Y"), 1,1/COUNTIF($AB$16:$AB$30,AB16))) All one formula - be wary of spurious linebreaks. Hope this helps. Pete On Jul 3, 11:14 pm, Tanya wrote: Hi all, I am getting so confused with this formula that my head is aching can anyone help? =IF((AB16="",0,AND(COUNTIF($AB$16:$AB$30,AB16)=1,B 16='lot', C16='Y')),COUNTIF($AB$16:$AB$30,AB16),1/COUNTIF($AB$16:$AB$30,AB16))) I am guessing that i dont have the syntax correct or the brackets in the correct place, please let me know if you need further explanation although im just as confused. Thanks Tanya |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with Formula
Okay, Tanya, I think you need this slight variation to achieve that:
=IF(OR(AB16="",B16="",C16=""),0,IF(AND(COUNTIF($AB $16:$AB $30,AB16)=1,B16="lot",C16="Y"),1,1/COUNTIF($AB$16:$AB$30,AB16))) Hope this helps. Pete On Jul 3, 11:40 pm, Tanya wrote: Sorry me again, the only problem i have is that when i delete the word lot out of B16, A16 still contains 1 when it should be 0? and the same with when i delete Y out of C16 again A16 should be 0 it needs to meet both those criteria to = 1, which is what i thought the formula was doing? Thanks again tanya "Pete_UK" wrote: I think you mean something like this: =IF(AB16="",0,IF(AND(COUNTIF($AB$16:$AB$30,AB16)=1 ,B16="lot",C16="Y"), 1,1/COUNTIF($AB$16:$AB$30,AB16))) All one formula - be wary of spurious linebreaks. Hope this helps. Pete On Jul 3, 11:14 pm, Tanya wrote: Hi all, I am getting so confused with this formula that my head is aching can anyone help? =IF((AB16="",0,AND(COUNTIF($AB$16:$AB$30,AB16)=1,B 16='lot', C16='Y')),COUNTIF($AB$16:$AB$30,AB16),1/COUNTIF($AB$16:$AB$30,AB16))) I am guessing that i dont have the syntax correct or the brackets in the correct place, please let me know if you need further explanation although im just as confused. Thanks Tanya- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with Formula
Maybe you should explain what you are trying to do? Since you divide
1/countif and if the countif returns 1 then the answer will be 1 regardless of lot or Y Maybe this is what you want =IF(OR(AB16="", B16<"lot",C16<"Y"),0,IF(COUNTIF($AB$16:$AB$30,AB 16)=1,1,1/COUNTIF($AB$16:$AB$30,AB16))) -- Regards, Peo Sjoblom "Tanya" wrote in message ... Sorry me again, the only problem i have is that when i delete the word lot out of B16, A16 still contains 1 when it should be 0? and the same with when i delete Y out of C16 again A16 should be 0 it needs to meet both those criteria to = 1, which is what i thought the formula was doing? Thanks again tanya "Pete_UK" wrote: I think you mean something like this: =IF(AB16="",0,IF(AND(COUNTIF($AB$16:$AB$30,AB16)=1 ,B16="lot",C16="Y"), 1,1/COUNTIF($AB$16:$AB$30,AB16))) All one formula - be wary of spurious linebreaks. Hope this helps. Pete On Jul 3, 11:14 pm, Tanya wrote: Hi all, I am getting so confused with this formula that my head is aching can anyone help? =IF((AB16="",0,AND(COUNTIF($AB$16:$AB$30,AB16)=1,B 16='lot', C16='Y')),COUNTIF($AB$16:$AB$30,AB16),1/COUNTIF($AB$16:$AB$30,AB16))) I am guessing that i dont have the syntax correct or the brackets in the correct place, please let me know if you need further explanation although im just as confused. Thanks Tanya |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with Formula
Thankyou both i now have it doing exactly what i need 8-) you guys are
wonderful i tweaked a couple of things and its working great! Thanks again Tanya "Peo Sjoblom" wrote: The first part looks like it should be =IF(AB16="",0,IF(AND(COUNTIF($AB$16:$AB$30,AB16)=1 ,B16="lot",C16="Y") now the question is, what do you want if the IF(AND part is TRUE If the countif part is 1, B16 equals lot and C16 is Y, what do you want as a result? If you want the result of the countif you might as well put 1 there since that is the condition, so maybe =IF(AB16="",0,IF(AND(COUNTIF($AB$16:$AB$30,AB16)=1 ,B16="lot",C16="Y"),1,1/COUNTIF($AB$16:$AB$30,AB16))) -- Regards, Peo Sjoblom "Tanya" wrote in message ... Hi all, I am getting so confused with this formula that my head is aching can anyone help? =IF((AB16="",0,AND(COUNTIF($AB$16:$AB$30,AB16)=1,B 16='lot', C16='Y')),COUNTIF($AB$16:$AB$30,AB16),1/COUNTIF($AB$16:$AB$30,AB16))) I am guessing that i dont have the syntax correct or the brackets in the correct place, please let me know if you need further explanation although im just as confused. Thanks Tanya |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with Formula
Good to hear that you got it to work as you wanted - thanks for
feeding back. Pete On Jul 4, 12:10 am, Tanya wrote: Thankyou both i now have it doing exactly what i need 8-) you guys are wonderful i tweaked a couple of things and its working great! Thanks again Tanya "Peo Sjoblom" wrote: The first part looks like it should be =IF(AB16="",0,IF(AND(COUNTIF($AB$16:$AB$30,AB16)=1 ,B16="lot",C16="Y") now the question is, what do you want if the IF(AND part is TRUE If the countif part is 1, B16 equals lot and C16 is Y, what do you want as a result? If you want the result of the countif you might as well put 1 there since that is the condition, so maybe =IF(AB16="",0,IF(AND(COUNTIF($AB$16:$AB$30,AB16)=1 ,B16="lot",C16="Y"),1,1/C*OUNTIF($AB$16:$AB$30,AB16))) -- Regards, Peo Sjoblom "Tanya" wrote in message ... Hi all, I am getting so confused with this formula that my head is aching can anyone help? =IF((AB16="",0,AND(COUNTIF($AB$16:$AB$30,AB16)=1,B 16='lot', C16='Y')),COUNTIF($AB$16:$AB$30,AB16),1/COUNTIF($AB$16:$AB$30,AB16))) I am guessing that i dont have the syntax correct or the brackets in the correct place, please let me know if you need further explanation although im just as confused. Thanks Tanya- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
