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

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 x225, 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.

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

This should work for you:

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

Mark Lincoln

On Jul 12, 3:16 pm, Jen wrote:
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 x225, 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.

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

=IF(A1<100,100,IF(A1225,225,A1))

"Jen" wrote:

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 x225, 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.

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

Jen,

Try this,

=IF(M1*0.11<100,100,IF(M1*0.11250,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

"Jen" wrote:

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 x225, 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.

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

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


Replace @mailinator.com with @tiscali.co.uk


"Jen" wrote in message
...
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 x225, 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.

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

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


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Jen" wrote in message
...
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 x225, 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.

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

Ah well, Sandy, overall that is two characters longer !! <bg

Pete

On Jul 12, 9:17 pm, "Sandy Mann" wrote:
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


Replace @mailinator.com with @tiscali.co.uk

"Sandy Mann" wrote in message

...



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


Replace @mailinator.com with @tiscali.co.uk

"Jen" wrote in message
...
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 x225, 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.- Hide quoted text -

- Show quoted text -

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

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


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Jen" wrote in message
...
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 x225, 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.

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

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


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Jen" wrote in message
...
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 x225, 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.

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

My eyes must be going - I didn't spot that until I read your
correction !!

Pete

On Jul 12, 9:38 pm, "Sandy Mann" wrote:
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


Replace @mailinator.com with @tiscali.co.uk

"Sandy Mann" wrote in message

...



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


Replace @mailinator.com with @tiscali.co.uk

"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk

"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk

"Jen" wrote in message
...
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 x225, 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.- Hide quoted text -

- Show quoted text -

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

=MIN(MAX(A1,100),225)

"Jen" wrote:

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 x225, 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.

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

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

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

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

=MEDIAN(100,M1*N1,225)

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

"Harlan Grove" wrote in message
...

=MEDIAN(100,M1*N1,225)

I like it! I had to think about it but I Like it.

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

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


Replace @mailinator.com with @tiscali.co.uk


"Harlan Grove" wrote in message
...
"Sandy Mann" wrote...
And before someone comes back and says it. Put 0.11 in a cell and use:

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

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

=MEDIAN(100,M1*N1,225)

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

Now that is a truly elegant solution.

Mark Lincoln

On Jul 12, 11:47 pm, "Harlan Grove" wrote:
"Sandy Mann" wrote...
And before someone comes back and says it. Put 0.11 in a cell and use:

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

...

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

=MEDIAN(100,M1*N1,225)