Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help on IF formula
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: |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help on IF formula
Hi Lee,
Perhaps: =IF(A1="Other",A2,IF((A1="Self")*(A2=1000)=0,A2,1 000)) 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] "lee" wrote in message ... 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: |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help on IF formula
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 "macropod" wrote: Hi Lee, Perhaps: =IF(A1="Other",A2,IF((A1="Self")*(A2=1000)=0,A2,1 000)) 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] "lee" wrote in message ... 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: |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help on IF formula
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,10 00)) "macropod" IF(A1="Other",0,IF((A1="Self")*(A2=1000)=0,A2,100 0)) "macropod" "lee" wrote: 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 "macropod" wrote: Hi Lee, Perhaps: =IF(A1="Other",A2,IF((A1="Self")*(A2=1000)=0,A2,1 000)) 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] "lee" wrote in message ... 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: |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help on IF formula
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] "lee" wrote in message ... 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,10 00)) "macropod" IF(A1="Other",0,IF((A1="Self")*(A2=1000)=0,A2,100 0)) "macropod" "lee" wrote: 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 "macropod" wrote: Hi Lee, Perhaps: =IF(A1="Other",A2,IF((A1="Self")*(A2=1000)=0,A2,1 000)) 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] "lee" wrote in message ... 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: |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help on IF formula
Hello Macropod,
Thank you very much....education is continuous and endless, always learning each day. Thank you very much once again... "macropod" wrote: 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] "lee" wrote in message ... 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,10 00)) "macropod" IF(A1="Other",0,IF((A1="Self")*(A2=1000)=0,A2,100 0)) "macropod" "lee" wrote: 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 "macropod" wrote: Hi Lee, Perhaps: =IF(A1="Other",A2,IF((A1="Self")*(A2=1000)=0,A2,1 000)) 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] "lee" wrote in message ... 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: |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|