Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove false from formula response in cell | Excel Discussion (Misc queries) | |||
Changing Background Color as a True/False Response | Excel Discussion (Misc queries) | |||
True / False Formula | Excel Discussion (Misc queries) | |||
Formula True False help | Excel Worksheet Functions | |||
IF hypotheticals that are not TRUE/FALSE response | Excel Worksheet Functions |