Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with if formula please.
Hi I am trying to write a formula but am getting a bit lost.
I want a formula in a7 that will return 1 if e7 is 0.1 and c13 is between 1 and 21. If c13 is blank and e7 is 0.1 I want a7 to be 7 Thank you for any assistance you can offer Lyn |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with if formula please.
On Feb 26, 7:06*pm, Lynz wrote:
I am trying to write a formula but am getting a bit lost. I want a formula in a7 that will return 1 if e7 is 0.1 and c13 is between 1 and 21. *If c13 is blank and e7 is 0.1 I want a7 to be 7 What if neither of those conditions are true? Perhaps the following formula in A7: =IF(AND(E7=0.1,1<=C13,C13<=21),1,IF(AND(E7=0.1,C13 =""),7,"")) That returns the null string (appears blank) if neither condition is true. However, if E7 contains a formula instead of a constant and you do not explicitly round its result appropriately, what __appears__ to be 0.1 in E7. In that case, the best solution is to explicitly round the result of E7 in the formula in E7 itself. Alternatively: =IF(ROUND(E7,1)=0.1,IF(C13="",7,IF(AND(1<=C13,C13< =21),1,""),"") Note: The same might be true of C13. What are the contents of C13 and E7: constants or formulas? If the latter, what are the formulas? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with if formula please.
Errata for typos....
On Feb 26, 7:57*pm, joeu2004 wrote: However, if E7 contains a formula instead of a constant and you do not explicitly round its result appropriately, what __appears__ to be 0.1 in E7. That is: .... what __appears__ to be 0.1 in E7 might not be exactly that. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with if formula please.
On 27/02/2011 4:57 p.m., joeu2004 wrote:
On Feb 26, 7:06 pm, Lynz wrote: I am trying to write a formula but am getting a bit lost. I want a formula in a7 that will return 1 if e7 is 0.1 and c13 is between 1 and 21. If c13 is blank and e7 is 0.1 I want a7 to be 7 What if neither of those conditions are true? Perhaps the following formula in A7: =IF(AND(E7=0.1,1<=C13,C13<=21),1,IF(AND(E7=0.1,C13 =""),7,"")) That returns the null string (appears blank) if neither condition is true. However, if E7 contains a formula instead of a constant and you do not explicitly round its result appropriately, what __appears__ to be 0.1 in E7. In that case, the best solution is to explicitly round the result of E7 in the formula in E7 itself. Alternatively: =IF(ROUND(E7,1)=0.1,IF(C13="",7,IF(AND(1<=C13,C13< =21),1,""),"") Note: The same might be true of C13. What are the contents of C13 and E7: constants or formulas? If the latter, what are the formulas? Hi Joeu4 c13 will have a persons age in it if they are under 21 so will be a constant, and e7 0.1 is an constant and is the number of a class so will not be involved in any calculations. Thanks for your response I will try them out and see how I go Cheeers, Lyn |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with if formula please.
On 27/02/2011 5:00 p.m., joeu2004 wrote:
Errata for typos.... On Feb 26, 7:57 pm, joeu2004 wrote: However, if E7 contains a formula instead of a constant and you do not explicitly round its result appropriately, what __appears__ to be 0.1 in E7. That is: .... what __appears__ to be 0.1 in E7 might not be exactly that. Hi again, your formula worked fine, thanks. however I think I may have to try something else as i have 6 possible variables for E7 and 12 for a7. a7 will be between 1,2,3,4,5,or 6 if c13 is less than 21 and will be either 7,8,9,10,11 or 12 if c13 is 21. E7 will be either 0.1, 0.2, 1.2, 1.4, 2.1, 2.2 or six similar numbers. Sorry I didnt give enough information but I wasnt too sure what sort of formula I should be looking for to start with. Lynz |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with if formula please.
On Feb 27, 1:09*am, Lynz wrote:
your formula worked fine, thanks. however I think I may have to try something else as i have 6 possible variables for E7 and 12 for a7. a7 will be between 1,2,3,4,5,or 6 if c13 is less than 21 and will be either 7,8,9,10,11 or 12 if c13 is 21. *E7 will be either 0.1, 0.2, 1.2, 1.4, 2.1, 2.2 or six similar numbers. Sorry I didnt give enough information but I wasnt too sure what sort of formula I should be looking for to start with. I am sure we can help you, if you wish. But you will need to be specific and complete in specifying your requirements. For example, "or six similar numbers" is too vague to work with -- unless you mean that you will fill in those numbers later. Also, you speak of "c13 is less than 21" and "c13 is 21", which does not cover C13=21. And it does not cover C13="", as you did in your initial posting. I think you are saying: 1. A7 should be 1, 2, 3, 4, 5, 6 when E7 is 0.1, 0.2, 1.2, 1.4, 2.1, 2.2 respectively and C13<=21. NOTE: I use <=21 here because previously you said "between 1 and 21", which I interpreted as inclusive. 2. A7 should be 7, 8, 9, 10, 11, 12 when E7 is 0.1, 0.2, 1.2, 1.4, 2.1, 2.2 respectively and C1321. 3. What should A7 be when C13 appears blank? Previously, you said 7 when E7 is 0.1. Do you mean 7 thru 12 depending on E7 as per in #2? 4. Can E7 be anything but one of those six numbers (or some set of six numbers)? In particular, can E7 appear blank? If so, what should A7 be in that case? Perhaps the following does what you want in A7: =IF(E7="","",MATCH(E7,{0.1,0.2,1.2,1.4,2.1,2.2}) +6*OR(C13="",C1321)) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with if formula please.
On Feb 27, 2:04*am, joeu2004 wrote:
On Feb 27, 1:09*am, Lynz wrote: *E7 will be either 0.1, 0.2, 1.2, 1.4, 2.1, 2.2 or six similar numbers. [....] Perhaps the following does what you want in A7: =IF(E7="","",MATCH(E7,{0.1,0.2,1.2,1.4,2.1,2.2}) +6*OR(C13="",C1321)) If you meant that the six possible values for E7 are variable, put them into a table, e.g. X1:X6, in ascending order and use X1:X6 in place of {0.1,0.2,1.2,1.4,2.1,2.2} in the formula above. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with if formula please.
On 27/02/2011 11:04 p.m., joeu2004 wrote:
On Feb 27, 1:09 am, Lynz wrote: your formula worked fine, thanks. however I think I may have to try something else as i have 6 possible variables for E7 and 12 for a7. a7 will be between 1,2,3,4,5,or 6 if c13 is less than 21 and will be either 7,8,9,10,11 or 12 if c13 is 21. E7 will be either 0.1, 0.2, 1.2, 1.4, 2.1, 2.2 or six similar numbers. Sorry I didnt give enough information but I wasnt too sure what sort of formula I should be looking for to start with. I am sure we can help you, if you wish. But you will need to be specific and complete in specifying your requirements. Ok, thanks For example, "or six similar numbers" is too vague to work with -- unless you mean that you will fill in those numbers later. This week they are 0.1, 0.2, 1.2, 1.4. 2.1, 2.2 and next week they may be 0.2, 0.4, 1.3, 1.5, 2.2, 2.6. so i will have to fill them in. Also, you speak of "c13 is less than 21" and "c13 is 21", which does not cover C13=21. And it does not cover C13="", as you did in your initial posting. C13 will only have the persons age in it if they are 21 or under. The over 21's dont have to put in their age so c13 will be blank for them. I think you are saying: 1. A7 should be 1, 2, 3, 4, 5, 6 when E7 is 0.1, 0.2, 1.2, 1.4, 2.1, 2.2 respectively and C13<=21. Yes. NOTE: I use <=21 here because previously you said "between 1 and 21", which I interpreted as inclusive. yes 2. A7 should be 7, 8, 9, 10, 11, 12 when E7 is 0.1, 0.2, 1.2, 1.4, 2.1, 2.2 respectively and C1321. when c13 is blank, as C13 will not have an age if person is 21. 3. What should A7 be when C13 appears blank? Previously, you said 7 when E7 is 0.1. Do you mean 7 thru 12 depending on E7 as per in #2? yes 4. Can E7 be anything but one of those six numbers (or some set of six numbers)? In particular, can E7 appear blank? If so, what should A7 be in that case? If E7 is blank , A7 should also be blank Perhaps the following does what you want in A7: =IF(E7="","",MATCH(E7,{0.1,0.2,1.2,1.4,2.1,2.2}) +6*OR(C13="",C1321)) I will try this thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|