Help on IF formula

[lee]

I have a problem that Im trying to solve but keep coming to a dead end, I
need help on the writing a "IF" formula in excel: it goes like this

A1-"Self" A2-1005.00 A3-1000
A1-"Other" A2-1002.00 A3- 0

if A1 ="Self" and A2 is >= 1000 then the value in A3 should be 1000

If A2 is <1000, then the value should equal the value in A2 ie less that
1000...eg... if A2 above was 929, then A3 would reflect 929.

if A1 ="Other" any value in A2( whether greater or less than does not
matter)then A3 should reflect or equal A2

I have sincerely tried the above using if, and and or and somehow i cannot
get through...

please help....if Im not at the right site please guide me through another
site....

thank you livius

also please can you reply to my work address:

(e-mail address removed)

 

[macropod]

Hi Lee,

Perhaps:
=IF(A1="Other",A2,IF((A1="Self")*(A2>=1000)=0,A2,1000))
but you haven't full explained the logic. For example, what is supposed to happen if A1 is neither "Other" nor "Self"? If it can
only be "Other" or "Self", the formula could be reduced to:
=IF(A1="Other",A2,IF(A2>=1000,A2,1000))
but the 'correct' answer depends on which condition(s) take precedence.

 

[lee]

Hello macropod,

Thank you for your reply and effort.....im sorry it did not work this time
and will attempt below to explain:

To answer your query:
A1 has to be either "self" or "other"

A brief on the logic is...a (A1) is "self "and has faulted and is fined
(A2)1050...since his fine is above 1000 he will pay only (A3) 1000 mandotary
and whatever is above 1000 is forfeited.
If his fine is below (A2)1000...he pays the difference i.e (A3) 1000 -
(amount below 1000)

If the fine is faulted to another individual "other" he gets exempted and
pays nothing. (A3)"0", irrespective if the value is above or below (A2)
1000...
A1-"Self" A2-1005.00 A3-
A1-"Other" A2-1002.00 A3-

if A1 ="Self" and A2 is > 1000 then the value in A3 should be 1000 and if A2
is <1000, then the value should equal the value in A2 or A2=1000-A2

If A1 ="Other" any value in A2( whether greater or less than 1000) then A3 = 0

To answer your query:
A1 has to be either "self" or "other"

A brief on the logic is...a A1 is "self "and has faulted and is fined
1050...since his fine is above 1000 he will pay only 1000 mandotary and
whatever is above 1000 is forfeited.
If his fine is below 1000...he pays the difference i.e 1000 - (amount below
1000)

If the fine is faulted to another individual "other" he gets exempted and
pays nothing. "0", irrespective if the value is above or below 1000...

thanks so much....lee

Hi Lee,

Perhaps:
=IF(A1="Other",A2,IF((A1="Self")*(A2>=1000)=0,A2,1000))
but you haven't full explained the logic. For example, what is supposed to happen if A1 is neither "Other" nor "Self"? If it can
only be "Other" or "Self", the formula could be reduced to:
=IF(A1="Other",A2,IF(A2>=1000,A2,1000))
but the 'correct' answer depends on which condition(s) take precedence.

--
Cheers
macropod
[MVP - Microsoft Word]

I have a problem that Im trying to solve but keep coming to a dead end, I
need help on the writing a "IF" formula in excel: it goes like this

A1-"Self" A2-1005.00 A3-1000
A1-"Other" A2-1002.00 A3- 0

if A1 ="Self" and A2 is >= 1000 then the value in A3 should be 1000

If A2 is <1000, then the value should equal the value in A2 ie less that
1000...eg... if A2 above was 929, then A3 would reflect 929.

if A1 ="Other" any value in A2( whether greater or less than does not
matter)then A3 should reflect or equal A2

I have sincerely tried the above using if, and and or and somehow i cannot
get through...

please help....if Im not at the right site please guide me through another
site....

thank you livius

also please can you reply to my work address:

(e-mail address removed)

 

[lee]

Hello macropod,

My apologies, the first formula you wrote worked...i just just the value of
A2 to "0"
Brilliant macropod... i just have one more query what is the " * " you
inserted stand for in the formula...as I have not come across this before....

Once again than you very much....

lee

IF(A1="Other",A2,IF((A1="Self")*(A2>=1000)=0,A2,1000)) "macropod"

IF(A1="Other",0,IF((A1="Self")*(A2>=1000)=0,A2,1000)) "macropod"

Hello macropod,

Thank you for your reply and effort.....im sorry it did not work this time
and will attempt below to explain:

To answer your query:
A1 has to be either "self" or "other"

A brief on the logic is...a (A1) is "self "and has faulted and is fined
(A2)1050...since his fine is above 1000 he will pay only (A3) 1000 mandotary
and whatever is above 1000 is forfeited.
If his fine is below (A2)1000...he pays the difference i.e (A3) 1000 -
(amount below 1000)

If the fine is faulted to another individual "other" he gets exempted and
pays nothing. (A3)"0", irrespective if the value is above or below (A2)
1000...
A1-"Self" A2-1005.00 A3-
A1-"Other" A2-1002.00 A3-

if A1 ="Self" and A2 is > 1000 then the value in A3 should be 1000 and if A2
is <1000, then the value should equal the value in A2 or A2=1000-A2

If A1 ="Other" any value in A2( whether greater or less than 1000) then A3 = 0

To answer your query:
A1 has to be either "self" or "other"

A brief on the logic is...a A1 is "self "and has faulted and is fined
1050...since his fine is above 1000 he will pay only 1000 mandotary and
whatever is above 1000 is forfeited.
If his fine is below 1000...he pays the difference i.e 1000 - (amount below
1000)

If the fine is faulted to another individual "other" he gets exempted and
pays nothing. "0", irrespective if the value is above or below 1000...

thanks so much....lee

Hi Lee,

Perhaps:
=IF(A1="Other",A2,IF((A1="Self")*(A2>=1000)=0,A2,1000))
but you haven't full explained the logic. For example, what is supposed to happen if A1 is neither "Other" nor "Self"? If it can
only be "Other" or "Self", the formula could be reduced to:
=IF(A1="Other",A2,IF(A2>=1000,A2,1000))
but the 'correct' answer depends on which condition(s) take precedence.

--
Cheers
macropod
[MVP - Microsoft Word]

I have a problem that Im trying to solve but keep coming to a dead end, I
need help on the writing a "IF" formula in excel: it goes like this

A1-"Self" A2-1005.00 A3-1000
A1-"Other" A2-1002.00 A3- 0

if A1 ="Self" and A2 is >= 1000 then the value in A3 should be 1000

If A2 is <1000, then the value should equal the value in A2 ie less that
1000...eg... if A2 above was 929, then A3 would reflect 929.

if A1 ="Other" any value in A2( whether greater or less than does not
matter)then A3 should reflect or equal A2

I have sincerely tried the above using if, and and or and somehow i cannot
get through...

please help....if Im not at the right site please guide me through another
site....

thank you livius

also please can you reply to my work address:

(e-mail address removed)

 

[macropod]

Hi lee,

(A1="Self") and (A2>=1000) both return TRUE or FALSE, depending on whether the equivalence (=) statement is satisfied. Using
(A1="Self")*(A2>=1000) simply multiples these two TRUE/FALSE resutls to return 1 if both are TRUE and 0 otherwise - it's effectively
the same as using an AND test. If I'd used '+' instead of '*', it would have been equivalent to an OR test.

--
Cheers
macropod
[MVP - Microsoft Word]

Hello macropod,

My apologies, the first formula you wrote worked...i just just the value of
A2 to "0"
Brilliant macropod... i just have one more query what is the " * " you
inserted stand for in the formula...as I have not come across this before....

Once again than you very much....

lee

IF(A1="Other",A2,IF((A1="Self")*(A2>=1000)=0,A2,1000)) "macropod"

IF(A1="Other",0,IF((A1="Self")*(A2>=1000)=0,A2,1000)) "macropod"

Hello macropod,

Thank you for your reply and effort.....im sorry it did not work this time
and will attempt below to explain:

To answer your query:
A1 has to be either "self" or "other"

A brief on the logic is...a (A1) is "self "and has faulted and is fined
(A2)1050...since his fine is above 1000 he will pay only (A3) 1000 mandotary
and whatever is above 1000 is forfeited.
If his fine is below (A2)1000...he pays the difference i.e (A3) 1000 -
(amount below 1000)

If the fine is faulted to another individual "other" he gets exempted and
pays nothing. (A3)"0", irrespective if the value is above or below (A2)
1000...
A1-"Self" A2-1005.00 A3-
A1-"Other" A2-1002.00 A3-

if A1 ="Self" and A2 is > 1000 then the value in A3 should be 1000 and if A2
is <1000, then the value should equal the value in A2 or A2=1000-A2

If A1 ="Other" any value in A2( whether greater or less than 1000) then A3 = 0

To answer your query:
A1 has to be either "self" or "other"

A brief on the logic is...a A1 is "self "and has faulted and is fined
1050...since his fine is above 1000 he will pay only 1000 mandotary and
whatever is above 1000 is forfeited.
If his fine is below 1000...he pays the difference i.e 1000 - (amount below
1000)

If the fine is faulted to another individual "other" he gets exempted and
pays nothing. "0", irrespective if the value is above or below 1000...

thanks so much....lee

Hi Lee,

Perhaps:
=IF(A1="Other",A2,IF((A1="Self")*(A2>=1000)=0,A2,1000))
but you haven't full explained the logic. For example, what is supposed to happen if A1 is neither "Other" nor "Self"? If it
can
only be "Other" or "Self", the formula could be reduced to:
=IF(A1="Other",A2,IF(A2>=1000,A2,1000))
but the 'correct' answer depends on which condition(s) take precedence.

--
Cheers
macropod
[MVP - Microsoft Word]


I have a problem that Im trying to solve but keep coming to a dead end, I
need help on the writing a "IF" formula in excel: it goes like this

A1-"Self" A2-1005.00 A3-1000
A1-"Other" A2-1002.00 A3- 0

if A1 ="Self" and A2 is >= 1000 then the value in A3 should be 1000

If A2 is <1000, then the value should equal the value in A2 ie less that
1000...eg... if A2 above was 929, then A3 would reflect 929.

if A1 ="Other" any value in A2( whether greater or less than does not
matter)then A3 should reflect or equal A2

I have sincerely tried the above using if, and and or and somehow i cannot
get through...

please help....if Im not at the right site please guide me through another
site....

thank you livius

also please can you reply to my work address:

(e-mail address removed)

 

[lee]

Hello Macropod,

Thank you very much....education is continuous and endless, always learning
each day.

Thank you very much once again...

Hi lee,

(A1="Self") and (A2>=1000) both return TRUE or FALSE, depending on whether the equivalence (=) statement is satisfied. Using
(A1="Self")*(A2>=1000) simply multiples these two TRUE/FALSE resutls to return 1 if both are TRUE and 0 otherwise - it's effectively
the same as using an AND test. If I'd used '+' instead of '*', it would have been equivalent to an OR test.

--
Cheers
macropod
[MVP - Microsoft Word]

Hello macropod,

My apologies, the first formula you wrote worked...i just just the value of
A2 to "0"
Brilliant macropod... i just have one more query what is the " * " you
inserted stand for in the formula...as I have not come across this before....

Once again than you very much....

lee

IF(A1="Other",A2,IF((A1="Self")*(A2>=1000)=0,A2,1000)) "macropod"

IF(A1="Other",0,IF((A1="Self")*(A2>=1000)=0,A2,1000)) "macropod"

Hello macropod,

Thank you for your reply and effort.....im sorry it did not work this time
and will attempt below to explain:

To answer your query:
A1 has to be either "self" or "other"

A brief on the logic is...a (A1) is "self "and has faulted and is fined
(A2)1050...since his fine is above 1000 he will pay only (A3) 1000 mandotary
and whatever is above 1000 is forfeited.
If his fine is below (A2)1000...he pays the difference i.e (A3) 1000 -
(amount below 1000)

If the fine is faulted to another individual "other" he gets exempted and
pays nothing. (A3)"0", irrespective if the value is above or below (A2)
1000...
A1-"Self" A2-1005.00 A3-
A1-"Other" A2-1002.00 A3-

if A1 ="Self" and A2 is > 1000 then the value in A3 should be 1000 and if A2
is <1000, then the value should equal the value in A2 or A2=1000-A2

If A1 ="Other" any value in A2( whether greater or less than 1000) then A3 = 0

To answer your query:
A1 has to be either "self" or "other"

A brief on the logic is...a A1 is "self "and has faulted and is fined
1050...since his fine is above 1000 he will pay only 1000 mandotary and
whatever is above 1000 is forfeited.
If his fine is below 1000...he pays the difference i.e 1000 - (amount below
1000)

If the fine is faulted to another individual "other" he gets exempted and
pays nothing. "0", irrespective if the value is above or below 1000...

thanks so much....lee



:

Hi Lee,

Perhaps:
=IF(A1="Other",A2,IF((A1="Self")*(A2>=1000)=0,A2,1000))
but you haven't full explained the logic. For example, what is supposed to happen if A1 is neither "Other" nor "Self"? If it
can
only be "Other" or "Self", the formula could be reduced to:
=IF(A1="Other",A2,IF(A2>=1000,A2,1000))
but the 'correct' answer depends on which condition(s) take precedence.

--
Cheers
macropod
[MVP - Microsoft Word]


I have a problem that Im trying to solve but keep coming to a dead end, I
need help on the writing a "IF" formula in excel: it goes like this

A1-"Self" A2-1005.00 A3-1000
A1-"Other" A2-1002.00 A3- 0

if A1 ="Self" and A2 is >= 1000 then the value in A3 should be 1000

If A2 is <1000, then the value should equal the value in A2 ie less that
1000...eg... if A2 above was 929, then A3 would reflect 929.

if A1 ="Other" any value in A2( whether greater or less than does not
matter)then A3 should reflect or equal A2

I have sincerely tried the above using if, and and or and somehow i cannot
get through...

please help....if Im not at the right site please guide me through another
site....

thank you livius

also please can you reply to my work address:

(e-mail address removed)