Getting a response other than true/false with an IF formula

J

Jen

I am having some trouble with this. I have this worksheet on loans. I have
to calculate a fee for each loan. ex: M1*.011=x. If x<100, it has to be
changed to 100. If x>225, it has to be changed to 225. If x is between 100
and 225, it stays that number. I'm trying to create an IF formula that will
take the numbers <100 and change them to 100 and take those >225 and change
them to 225, all the while keeping the numbers between 100 and 225 the
same....any help? The only thing I've been able to create is a response of
true or false, a response of only 100, or a response of only 225.
 
M

Mark Lincoln

This should work for you:

=IF(M1*0.11<100,100,IF(M1*0.11>225,225,M1*0.11))

Mark Lincoln
 
M

Mike H

Jen,

Try this,

=IF(M1*0.11<100,100,IF(M1*0.11>250,250,M1*0.11))

Incidentally it's good practice to put your constant (0.11) and reference
that instead of directly in the formula.

Mike
 
S

Sandy Mann

I think that the OP wanted an IF() but just for variety:

=MAX(100,(MIN(M1*0.11,225)))

Rick Rothstein will like it because it's shorter <g>
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
S

Sandy Mann

And before someone comes back and says it. Put 0.11 in a cell and use:

=MAX(100,(MIN(M1*N1,225)))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
S

Sandy Mann

OK take the extra perenthsis off (and don't let XL correct formulas for
you!) <g>

=MAX(100,MIN(M1*N1,225))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
S

Sandy Mann

My Spelling checker doesn't work in OE I meant of course parenthesis!

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
H

Harlan Grove

Sandy Mann said:
And before someone comes back and says it. Put 0.11 in a cell and use:

=MAX(100,(MIN(M1*N1,225)))
Click to expand...
....

Someday y'all may grok TERSE, but not today.

=MEDIAN(100,M1*N1,225)
 
S

Sandy Mann

Harlan Grove said:
=MEDIAN(100,M1*N1,225)
Click to expand...

I like it! I had to think about it but I Like it.
Someday y'all may grok TERSE, but not today.
Click to expand...

Reminds me of the story I heard of someone filling in a job application form
that had a box saying "Give a brief description of yourself" - he wrote in
"Concise"


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
Top