Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with mod function
I have a problem with the mod function...I need to make it conditional on
the related adjacent cell...here's what I have =IF(K2=180,MOD(K2,180)) What I need is if K2 is equal to or greater than 180 then execute the mod function in the appropriate adjacent cell If K2 is less than 180 then do not execute the mode function in the adjacent cell...leave the exiting number , if any, alone The above executes the mod function ok if equal or greater than 180...but if less than 180...it's returning a false statement ? I'm sure this is simple but I can't come up with changes that give the above desired results. Thanks, Tim |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with mod function
You should add one more parameter in your IF formula:
=IF(K2=180,MOD(K2,180),"") this formula check if K2=180 and if it is, it returns the MOD's result, if it not, it returns blank. http://www.exciter.gr Custom Excel Applications and Functions! On Oct 31, 8:06 pm, "Tim" wrote: I have a problem with the mod function...I need to make it conditional on the related adjacent cell...here's what I have =IF(K2=180,MOD(K2,180)) What I need is if K2 is equal to or greater than 180 then execute the mod function in the appropriate adjacent cell If K2 is less than 180 then do not execute the mode function in the adjacent cell...leave the exiting number , if any, alone The above executes the mod function ok if equal or greater than 180...but if less than 180...it's returning a false statement ? I'm sure this is simple but I can't come up with changes that give the above desired results. Thanks, Tim |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with mod function
You just need to include the value_if_false argument:
=IF(K2=180,MOD(K2,180),K2) -- Biff Microsoft Excel MVP "Tim" wrote in message ... I have a problem with the mod function...I need to make it conditional on the related adjacent cell...here's what I have =IF(K2=180,MOD(K2,180)) What I need is if K2 is equal to or greater than 180 then execute the mod function in the appropriate adjacent cell If K2 is less than 180 then do not execute the mode function in the adjacent cell...leave the exiting number , if any, alone The above executes the mod function ok if equal or greater than 180...but if less than 180...it's returning a false statement ? I'm sure this is simple but I can't come up with changes that give the above desired results. Thanks, Tim |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with mod function
It's returning a false statement because that's the default "value-if-
false" value. Do you mean like this instead: =IF(K2=180,MOD(K2,180),K2) That would not make sense. A MOD() on a number less than 180 will return that number. You might as well leave it =MOD(K2,180) and forget about the if. On Oct 31, 2:06 pm, "Tim" wrote: I have a problem with the mod function...I need to make it conditional on the related adjacent cell...here's what I have =IF(K2=180,MOD(K2,180)) What I need is if K2 is equal to or greater than 180 then execute the mod function in the appropriate adjacent cell If K2 is less than 180 then do not execute the mode function in the adjacent cell...leave the exiting number , if any, alone The above executes the mod function ok if equal or greater than 180...but if less than 180...it's returning a false statement ? I'm sure this is simple but I can't come up with changes that give the above desired results. Thanks, Tim |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with mod function
Are you working with two cells or three? Plus, the IF formula below
doesn't have an ELSE condition set. That's where you're getting the FALSE from. An IF formula has three parts: =IF(TheCondition, WhatHappensIfTrue, WhatHappensIfFalse). You're missing the last part. It should look something like: =IF(K2=180,MOD(K2,18),somethingelse) Hope that helps! Cory On Oct 31, 1:06 pm, "Tim" wrote: I have a problem with the mod function...I need to make it conditional on the related adjacent cell...here's what I have =IF(K2=180,MOD(K2,180)) What I need is if K2 is equal to or greater than 180 then execute the mod function in the appropriate adjacent cell If K2 is less than 180 then do not execute the mode function in the adjacent cell...leave the exiting number , if any, alone The above executes the mod function ok if equal or greater than 180...but if less than 180...it's returning a false statement ? I'm sure this is simple but I can't come up with changes that give the above desired results. Thanks, Tim |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with mod function
This is really close...however...in the adjacent column where the mod
function is run...there are existing values If K2 is = to or greater than 180...I want the mod value returned when the function is run in J2 If K2 is less than 180...I want no action...the existing value in J2 (usually 0,1, or 2) is to be left alone The below formulas replaces the 0,1, or 2 in J2 with an empty cell...is there a way to leave the J2 value alone if K2 is less than 180 ? Thanks, Tim "www.exciter.gr: Custom Excel Applications!" wrote in message oups.com... You should add one more parameter in your IF formula: =IF(K2=180,MOD(K2,180),"") this formula check if K2=180 and if it is, it returns the MOD's result, if it not, it returns blank. http://www.exciter.gr Custom Excel Applications and Functions! On Oct 31, 8:06 pm, "Tim" wrote: I have a problem with the mod function...I need to make it conditional on the related adjacent cell...here's what I have =IF(K2=180,MOD(K2,180)) What I need is if K2 is equal to or greater than 180 then execute the mod function in the appropriate adjacent cell If K2 is less than 180 then do not execute the mode function in the adjacent cell...leave the exiting number , if any, alone The above executes the mod function ok if equal or greater than 180...but if less than 180...it's returning a false statement ? I'm sure this is simple but I can't come up with changes that give the above desired results. Thanks, Tim |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with mod function
Sorry didnt understand youw question well. I agree with others, you
should use formula: =IF(K2=180,MOD(K2,180),K2) Best http://www.exciter.gr Custom Excel Applications and Functions! On Oct 31, 9:32 pm, "Tim" wrote: This is really close...however...in the adjacent column where the mod function is run...there are existing values If K2 is = to or greater than 180...I want the mod value returned when the function is run in J2 If K2 is less than 180...I want no action...the existing value in J2 (usually 0,1, or 2) is to be left alone The below formulas replaces the 0,1, or 2 in J2 with an empty cell...is there a way to leave the J2 value alone if K2 is less than 180 ? Thanks, Tim "www.exciter.gr:Custom Excel Applications!" wrote in ooglegroups.com... You should add one more parameter in your IF formula: =IF(K2=180,MOD(K2,180),"") this formula check if K2=180 and if it is, it returns the MOD's result, if it not, it returns blank. http://www.exciter.gr Custom Excel Applications and Functions! On Oct 31, 8:06 pm, "Tim" wrote: I have a problem with the mod function...I need to make it conditional on the related adjacent cell...here's what I have =IF(K2=180,MOD(K2,180)) What I need is if K2 is equal to or greater than 180 then execute the mod function in the appropriate adjacent cell If K2 is less than 180 then do not execute the mode function in the adjacent cell...leave the exiting number , if any, alone The above executes the mod function ok if equal or greater than 180...but if less than 180...it's returning a false statement ? I'm sure this is simple but I can't come up with changes that give the above desired results. Thanks, Tim- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with mod function
Not with a function.
On Oct 31, 3:32 pm, "Tim" wrote: This is really close...however...in the adjacent column where the mod function is run...there are existing values If K2 is = to or greater than 180...I want the mod value returned when the function is run in J2 If K2 is less than 180...I want no action...the existing value in J2 (usually 0,1, or 2) is to be left alone The below formulas replaces the 0,1, or 2 in J2 with an empty cell...is there a way to leave the J2 value alone if K2 is less than 180 ? Thanks, Tim "www.exciter.gr:Custom Excel Applications!" wrote in ooglegroups.com... You should add one more parameter in your IF formula: =IF(K2=180,MOD(K2,180),"") this formula check if K2=180 and if it is, it returns the MOD's result, if it not, it returns blank. http://www.exciter.gr Custom Excel Applications and Functions! On Oct 31, 8:06 pm, "Tim" wrote: I have a problem with the mod function...I need to make it conditional on the related adjacent cell...here's what I have =IF(K2=180,MOD(K2,180)) What I need is if K2 is equal to or greater than 180 then execute the mod function in the appropriate adjacent cell If K2 is less than 180 then do not execute the mode function in the adjacent cell...leave the exiting number , if any, alone The above executes the mod function ok if equal or greater than 180...but if less than 180...it's returning a false statement ? I'm sure this is simple but I can't come up with changes that give the above desired results. Thanks, Tim- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with mod function
Problem is K2 can be any where from 1 or 2 (equaling J2) to 179 to 400+
The final working number I need is the value of J2....assuming K2 is less than 180. If K2 is greater than 180...then I want the mod function to give me the normal remainder With the new below formula...If K2 is say 127...the function will erase the 0, 1, or 2 in J2...and insert the K2 value of 127 ....which I am trying to prevent from happening ? Maybe what I want can't be done...hope I am clear on the above...? Can the mode function ignore the value (make no change) in cell J2...if the value of the adjoining K2 is less than 180... But still perform the mod function...giving me the remainder...if the value of K2 is = or greater than 180 ? "www.exciter.gr: Custom Excel Applications!" wrote in message oups.com... Sorry didnt understand youw question well. I agree with others, you should use formula: =IF(K2=180,MOD(K2,180),K2) Best http://www.exciter.gr Custom Excel Applications and Functions! On Oct 31, 9:32 pm, "Tim" wrote: This is really close...however...in the adjacent column where the mod function is run...there are existing values If K2 is = to or greater than 180...I want the mod value returned when the function is run in J2 If K2 is less than 180...I want no action...the existing value in J2 (usually 0,1, or 2) is to be left alone The below formulas replaces the 0,1, or 2 in J2 with an empty cell...is there a way to leave the J2 value alone if K2 is less than 180 ? Thanks, Tim "www.exciter.gr:Custom Excel Applications!" wrote in ooglegroups.com... You should add one more parameter in your IF formula: =IF(K2=180,MOD(K2,180),"") this formula check if K2=180 and if it is, it returns the MOD's result, if it not, it returns blank. http://www.exciter.gr Custom Excel Applications and Functions! On Oct 31, 8:06 pm, "Tim" wrote: I have a problem with the mod function...I need to make it conditional on the related adjacent cell...here's what I have =IF(K2=180,MOD(K2,180)) What I need is if K2 is equal to or greater than 180 then execute the mod function in the appropriate adjacent cell If K2 is less than 180 then do not execute the mode function in the adjacent cell...leave the exiting number , if any, alone The above executes the mod function ok if equal or greater than 180...but if less than 180...it's returning a false statement ? I'm sure this is simple but I can't come up with changes that give the above desired results. Thanks, Tim- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with mod function
If the condition (K2=180) is not true...then I want the function to take no
action...just leave the value in J2 alone ? can this be done ? wrote in message oups.com... Are you working with two cells or three? Plus, the IF formula below doesn't have an ELSE condition set. That's where you're getting the FALSE from. An IF formula has three parts: =IF(TheCondition, WhatHappensIfTrue, WhatHappensIfFalse). You're missing the last part. It should look something like: =IF(K2=180,MOD(K2,18),somethingelse) Hope that helps! Cory On Oct 31, 1:06 pm, "Tim" wrote: I have a problem with the mod function...I need to make it conditional on the related adjacent cell...here's what I have =IF(K2=180,MOD(K2,180)) What I need is if K2 is equal to or greater than 180 then execute the mod function in the appropriate adjacent cell If K2 is less than 180 then do not execute the mode function in the adjacent cell...leave the exiting number , if any, alone The above executes the mod function ok if equal or greater than 180...but if less than 180...it's returning a false statement ? I'm sure this is simple but I can't come up with changes that give the above desired results. Thanks, Tim |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with mod function
Sorry, this cant be done with a formula, only with VBA coding
http://www.exciter.gr Custom Excel Applications and Functions! On Oct 31, 9:32 pm, "Tim" wrote: This is really close...however...in the adjacent column where the mod function is run...there are existing values If K2 is = to or greater than 180...I want the mod value returned when the function is run in J2 If K2 is less than 180...I want no action...the existing value in J2 (usually 0,1, or 2) is to be left alone The below formulas replaces the 0,1, or 2 in J2 with an empty cell...is there a way to leave the J2 value alone if K2 is less than 180 ? Thanks, Tim "www.exciter.gr:Custom Excel Applications!" wrote in ooglegroups.com... You should add one more parameter in your IF formula: =IF(K2=180,MOD(K2,180),"") this formula check if K2=180 and if it is, it returns the MOD's result, if it not, it returns blank. http://www.exciter.gr Custom Excel Applications and Functions! On Oct 31, 8:06 pm, "Tim" wrote: I have a problem with the mod function...I need to make it conditional on the related adjacent cell...here's what I have =IF(K2=180,MOD(K2,180)) What I need is if K2 is equal to or greater than 180 then execute the mod function in the appropriate adjacent cell If K2 is less than 180 then do not execute the mode function in the adjacent cell...leave the exiting number , if any, alone The above executes the mod function ok if equal or greater than 180...but if less than 180...it's returning a false statement ? I'm sure this is simple but I can't come up with changes that give the above desired results. Thanks, Tim- Hide quoted text - - Show quoted text - |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with mod function
The value of J2 can come from one of two sources: manual input of
user, or result of a calculated formula. It cannot be both. If you already have a manual entry, a calculated formula will overwrite it. You need to do one of two things: 1. Use column L to determine the "final" value, based on J and K, with this formula: =IF(K2<180,J2,MOD(K2,180)) 2. Use a Worksheet_Change event hook to modify J2 any time K2 is affected (or any cell in J and K). However, this will still overwrite the value in J2 and you won't be able to bring it back if K2 ever goes above 180. I think option #1 is your optimal solution. On Oct 31, 3:56 pm, "Tim" wrote: Problem is K2 can be any where from 1 or 2 (equaling J2) to 179 to 400+ The final working number I need is the value of J2....assuming K2 is less than 180. If K2 is greater than 180...then I want the mod function to give me the normal remainder With the new below formula...If K2 is say 127...the function will erase the 0, 1, or 2 in J2...and insert the K2 value of 127 ...which I am trying to prevent from happening ? Maybe what I want can't be done...hope I am clear on the above...? Can the mode function ignore the value (make no change) in cell J2...if the value of the adjoining K2 is less than 180... But still perform the mod function...giving me the remainder...if the value of K2 is = or greater than 180 ? "www.exciter.gr:Custom Excel Applications!" wrote in ooglegroups.com... Sorry didnt understand youw question well. I agree with others, you should use formula: =IF(K2=180,MOD(K2,180),K2) Best http://www.exciter.gr Custom Excel Applications and Functions! On Oct 31, 9:32 pm, "Tim" wrote: This is really close...however...in the adjacent column where the mod function is run...there are existing values If K2 is = to or greater than 180...I want the mod value returned when the function is run in J2 If K2 is less than 180...I want no action...the existing value in J2 (usually 0,1, or 2) is to be left alone The below formulas replaces the 0,1, or 2 in J2 with an empty cell...is there a way to leave the J2 value alone if K2 is less than 180 ? Thanks, Tim "www.exciter.gr:CustomExcel Applications!" wrote in ooglegroups.com... You should add one more parameter in your IF formula: =IF(K2=180,MOD(K2,180),"") this formula check if K2=180 and if it is, it returns the MOD's result, if it not, it returns blank. http://www.exciter.gr Custom Excel Applications and Functions! On Oct 31, 8:06 pm, "Tim" wrote: I have a problem with the mod function...I need to make it conditional on the related adjacent cell...here's what I have =IF(K2=180,MOD(K2,180)) What I need is if K2 is equal to or greater than 180 then execute the mod function in the appropriate adjacent cell If K2 is less than 180 then do not execute the mode function in the adjacent cell...leave the exiting number , if any, alone The above executes the mod function ok if equal or greater than 180...but if less than 180...it's returning a false statement ? I'm sure this is simple but I can't come up with changes that give the above desired results. Thanks, Tim- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |