Help with if formula please.

[Lynz]

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

 

[joeu2004]

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?

 

[joeu2004]

Errata for typos....

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.

 

[Lynz]

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

 

[Lynz]

Errata for typos....



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

 

[joeu2004]

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
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 C13>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?

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="",C13>21))

 

[joeu2004]

 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="",C13>21))

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.

 

[Lynz]

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 C13>21.

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="",C13>21))

I will try this thanks.