help with mod function

[Tim]

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

 

[www.exciter.gr: Custom Excel Applications!]

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!

 

[T. Valko]

You just need to include the value_if_false argument:

=IF(K2>=180,MOD(K2,180),K2)

 

[iliace]

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.

 

[crferguson]

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

 

[Tim]

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!]

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!

 

[iliace]

Not with a 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

message





- Show quoted text -

 

[Tim]

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 ?

 

[Tim]

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 ?

 

[www.exciter.gr: Custom Excel Applications!]

Sorry, this cant be done with a formula, only with VBA coding

http://www.exciter.gr
Custom Excel Applications and Functions!

 

[iliace]

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.