| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Then Dilemma
I am trying to solve this problem:
If a value in cell D4 is from 1 to 50 then I want a certain celll to show $2000 if the value in cell D4 is 50 to 100 then I want that certain cell to show $5000 instead, and if the value in cell D4 is greater than 100 then I want to show $10000 instead. Seems pretty simple here is my logic, It just says formula error??? Please help IF((D4<=50,"2000",""),IF(D450<100,"5000",""),IF(D 4=100,"10000","")) -- Please help out a moderate excel user |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Then Dilemma
modify this idea to suit starting with the higher number
=if(d4100,1,if(d450,2,if(d425,3,"")))) -- Don Guillett SalesAid Software "Excel Confused" wrote in message ... I am trying to solve this problem: If a value in cell D4 is from 1 to 50 then I want a certain celll to show $2000 if the value in cell D4 is 50 to 100 then I want that certain cell to show $5000 instead, and if the value in cell D4 is greater than 100 then I want to show $10000 instead. Seems pretty simple here is my logic, It just says formula error??? Please help IF((D4<=50,"2000",""),IF(D450<100,"5000",""),IF(D 4=100,"10000","")) -- Please help out a moderate excel user |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Then Dilemma
More confused? It also shows error on this formula
-- Please help out a moderate excel user "Don Guillett" wrote: modify this idea to suit starting with the higher number =if(d4100,1,if(d450,2,if(d425,3,"")))) -- Don Guillett SalesAid Software "Excel Confused" wrote in message ... I am trying to solve this problem: If a value in cell D4 is from 1 to 50 then I want a certain celll to show $2000 if the value in cell D4 is 50 to 100 then I want that certain cell to show $5000 instead, and if the value in cell D4 is greater than 100 then I want to show $10000 instead. Seems pretty simple here is my logic, It just says formula error??? Please help IF((D4<=50,"2000",""),IF(D450<100,"5000",""),IF(D 4=100,"10000","")) -- Please help out a moderate excel user |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Then Dilemma
Sorry I am now more confused?? It also showed error when pasting this formula
-- Please help out a moderate excel user "Don Guillett" wrote: modify this idea to suit starting with the higher number =if(d4100,1,if(d450,2,if(d425,3,"")))) -- Don Guillett SalesAid Software "Excel Confused" wrote in message ... I am trying to solve this problem: If a value in cell D4 is from 1 to 50 then I want a certain celll to show $2000 if the value in cell D4 is 50 to 100 then I want that certain cell to show $5000 instead, and if the value in cell D4 is greater than 100 then I want to show $10000 instead. Seems pretty simple here is my logic, It just says formula error??? Please help IF((D4<=50,"2000",""),IF(D450<100,"5000",""),IF(D 4=100,"10000","")) -- Please help out a moderate excel user |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Then Dilemma
Hello,
Your approach: =IF(D4<=50,2000,IF(D4<100,5000,10000)) HTH, Bernd |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Then Dilemma
What does HTH mean?
-- Please help out a moderate excel user " wrote: Hello, Your approach: =IF(D4<=50,2000,IF(D4<100,5000,10000)) HTH, Bernd |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Then Dilemma
Excel Confused,
HTH means "Hope This Helps". This happens to me all the time: I see acronyms that I don't know what they mean. I go to http://www.acronymfinder.com/ to see if they have the meanings there. Did any of these 3 solutions fix your problem? Conan Kelly "Excel Confused" wrote in message ... What does HTH mean? -- Please help out a moderate excel user " wrote: Hello, Your approach: =IF(D4<=50,2000,IF(D4<100,5000,10000)) HTH, Bernd |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Then Dilemma
Hi Conan, thanks for the replies. I unfortuantely have not got a solution
just yet. Let me rephrase my problem: D4 is a cell I will enter different numbers into every time I use this workbook. If I enter a number from 1 through 50 in D4, I would like the cell I am entering the formual into to display $2,000. If I enter 50 through 100 in D4 I need the cell I am entering the same formula into to display $5,000. If I enter 100 or greater in D4 I need the cell I am entering the same formula into to display $10,000. There are two cells in total, one is D4 the data entry cell that could contain any number and the other is the cell that should display the relevant answer, which is one of three answers as stated above i.e. $2,000, $5,000 or $10,000 based on the number entered in cell D4. HTH Help Me. Thanks for your help on this one. -- Please help out a moderate excel user "Conan Kelly" wrote: Excel Confused, HTH means "Hope This Helps". This happens to me all the time: I see acronyms that I don't know what they mean. I go to http://www.acronymfinder.com/ to see if they have the meanings there. Did any of these 3 solutions fix your problem? Conan Kelly "Excel Confused" wrote in message ... What does HTH mean? -- Please help out a moderate excel user " wrote: Hello, Your approach: =IF(D4<=50,2000,IF(D4<100,5000,10000)) HTH, Bernd |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Then Dilemma
Thanks Problem Solved. I used Bernd's Formula and I was not referencing the
wrong cell as the cell D4 was on another sheet. Sorry about the confusion. Really appreciate the help -- Please help out a moderate excel user "Conan Kelly" wrote: Excel Confused, HTH means "Hope This Helps". This happens to me all the time: I see acronyms that I don't know what they mean. I go to http://www.acronymfinder.com/ to see if they have the meanings there. Did any of these 3 solutions fix your problem? Conan Kelly "Excel Confused" wrote in message ... What does HTH mean? -- Please help out a moderate excel user " wrote: Hello, Your approach: =IF(D4<=50,2000,IF(D4<100,5000,10000)) HTH, Bernd |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Then Dilemma
Acronymfinder also suggests "Hit The Highway" <g
"Conan Kelly" wrote: Excel Confused, HTH means "Hope This Helps". This happens to me all the time: I see acronyms that I don't know what they mean. I go to http://www.acronymfinder.com/ to see if they have the meanings there. Did any of these 3 solutions fix your problem? Conan Kelly "Excel Confused" wrote in message ... What does HTH mean? -- Please help out a moderate excel user " wrote: Hello, Your approach: =IF(D4<=50,2000,IF(D4<100,5000,10000)) HTH, Bernd |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Then Dilemma
Not intending any offense or anything, I just thought some of the other
suggestions on that site for some of the common acronyms were pretty amusing. "JMB" wrote: Acronymfinder also suggests "Hit The Highway" <g "Conan Kelly" wrote: Excel Confused, HTH means "Hope This Helps". This happens to me all the time: I see acronyms that I don't know what they mean. I go to http://www.acronymfinder.com/ to see if they have the meanings there. Did any of these 3 solutions fix your problem? Conan Kelly "Excel Confused" wrote in message ... What does HTH mean? -- Please help out a moderate excel user " wrote: Hello, Your approach: =IF(D4<=50,2000,IF(D4<100,5000,10000)) HTH, Bernd |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Then Dilemma
Try something like this:
With A1: (a number) B1: =CHOOSE(MIN(CEILING((A1+(A1=100))/50,1),3)+1,0,2000,5000,10000) Does that help? *********** Regards, Ron XL2002, WinXP "Excel Confused" wrote: I am trying to solve this problem: If a value in cell D4 is from 1 to 50 then I want a certain celll to show $2000 if the value in cell D4 is 50 to 100 then I want that certain cell to show $5000 instead, and if the value in cell D4 is greater than 100 then I want to show $10000 instead. Seems pretty simple here is my logic, It just says formula error??? Please help IF((D4<=50,"2000",""),IF(D450<100,"5000",""),IF(D 4=100,"10000","")) -- Please help out a moderate excel user |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Then Dilemma
The cell I am entering the formula in shows VALUE
-- Please help out a moderate excel user "Ron Coderre" wrote: Try something like this: With A1: (a number) B1: =CHOOSE(MIN(CEILING((A1+(A1=100))/50,1),3)+1,0,2000,5000,10000) Does that help? *********** Regards, Ron XL2002, WinXP "Excel Confused" wrote: I am trying to solve this problem: If a value in cell D4 is from 1 to 50 then I want a certain celll to show $2000 if the value in cell D4 is 50 to 100 then I want that certain cell to show $5000 instead, and if the value in cell D4 is greater than 100 then I want to show $10000 instead. Seems pretty simple here is my logic, It just says formula error??? Please help IF((D4<=50,"2000",""),IF(D450<100,"5000",""),IF(D 4=100,"10000","")) -- Please help out a moderate excel user |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Then Dilemma
You'd most likely receive the #VALUE! error if the referenced cell is NOT a
number. What cell values do you have? *********** Regards, Ron XL2002, WinXP "Excel Confused" wrote: The cell I am entering the formula in shows VALUE -- Please help out a moderate excel user "Ron Coderre" wrote: Try something like this: With A1: (a number) B1: =CHOOSE(MIN(CEILING((A1+(A1=100))/50,1),3)+1,0,2000,5000,10000) Does that help? *********** Regards, Ron XL2002, WinXP "Excel Confused" wrote: I am trying to solve this problem: If a value in cell D4 is from 1 to 50 then I want a certain celll to show $2000 if the value in cell D4 is 50 to 100 then I want that certain cell to show $5000 instead, and if the value in cell D4 is greater than 100 then I want to show $10000 instead. Seems pretty simple here is my logic, It just says formula error??? Please help IF((D4<=50,"2000",""),IF(D450<100,"5000",""),IF(D 4=100,"10000","")) -- Please help out a moderate excel user |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Then Dilemma
THXS for reply, the referenced cell is a number please see my reply before
this one where I have reworded my original question. HTH -- Please help out a moderate excel user "Ron Coderre" wrote: You'd most likely receive the #VALUE! error if the referenced cell is NOT a number. What cell values do you have? *********** Regards, Ron XL2002, WinXP "Excel Confused" wrote: The cell I am entering the formula in shows VALUE -- Please help out a moderate excel user "Ron Coderre" wrote: Try something like this: With A1: (a number) B1: =CHOOSE(MIN(CEILING((A1+(A1=100))/50,1),3)+1,0,2000,5000,10000) Does that help? *********** Regards, Ron XL2002, WinXP "Excel Confused" wrote: I am trying to solve this problem: If a value in cell D4 is from 1 to 50 then I want a certain celll to show $2000 if the value in cell D4 is 50 to 100 then I want that certain cell to show $5000 instead, and if the value in cell D4 is greater than 100 then I want to show $10000 instead. Seems pretty simple here is my logic, It just says formula error??? Please help IF((D4<=50,"2000",""),IF(D450<100,"5000",""),IF(D 4=100,"10000","")) -- Please help out a moderate excel user |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Then Dilemma
Hi , thanks for the reply. I unfortuantely have not got a solution
just yet. Let me rephrase my problem: D4 is a cell I will enter different numbers into every time I use this workbook. If I enter a number from 1 through 50 in D4, I would like the cell I am entering the formual into to display $2,000. If I enter 50 through 100 in D4 I need the cell I am entering the same formula into to display $5,000. If I enter 100 or greater in D4 I need the cell I am entering the same formula into to display $10,000. There are two cells in total, one is D4 the data entry cell that could contain any number and the other is the cell that should display the relevant answer, which is one of three answers as stated above i.e. $2,000, $5,000 or $10,000 based on the number entered in cell D4. HTH Help Me. Thanks for your help on this one. -- -- Please help out a moderate excel user "Ron Coderre" wrote: Try something like this: With A1: (a number) B1: =CHOOSE(MIN(CEILING((A1+(A1=100))/50,1),3)+1,0,2000,5000,10000) Does that help? *********** Regards, Ron XL2002, WinXP "Excel Confused" wrote: I am trying to solve this problem: If a value in cell D4 is from 1 to 50 then I want a certain celll to show $2000 if the value in cell D4 is 50 to 100 then I want that certain cell to show $5000 instead, and if the value in cell D4 is greater than 100 then I want to show $10000 instead. Seems pretty simple here is my logic, It just says formula error??? Please help IF((D4<=50,"2000",""),IF(D450<100,"5000",""),IF(D 4=100,"10000","")) -- Please help out a moderate excel user |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Then Dilemma
Maybe something like this:
With D4: any value, including text (but not an error) E4: =CHOOSE(MIN(CEILING((N(D4)+(N(D4)=100))/50,1),3)+1,0,2000,5000,10000) Does that help? *********** Regards, Ron XL2002, WinXP "Excel Confused" wrote: Hi , thanks for the reply. I unfortuantely have not got a solution just yet. Let me rephrase my problem: D4 is a cell I will enter different numbers into every time I use this workbook. If I enter a number from 1 through 50 in D4, I would like the cell I am entering the formual into to display $2,000. If I enter 50 through 100 in D4 I need the cell I am entering the same formula into to display $5,000. If I enter 100 or greater in D4 I need the cell I am entering the same formula into to display $10,000. There are two cells in total, one is D4 the data entry cell that could contain any number and the other is the cell that should display the relevant answer, which is one of three answers as stated above i.e. $2,000, $5,000 or $10,000 based on the number entered in cell D4. HTH Help Me. Thanks for your help on this one. -- -- Please help out a moderate excel user "Ron Coderre" wrote: Try something like this: With A1: (a number) B1: =CHOOSE(MIN(CEILING((A1+(A1=100))/50,1),3)+1,0,2000,5000,10000) Does that help? *********** Regards, Ron XL2002, WinXP "Excel Confused" wrote: I am trying to solve this problem: If a value in cell D4 is from 1 to 50 then I want a certain celll to show $2000 if the value in cell D4 is 50 to 100 then I want that certain cell to show $5000 instead, and if the value in cell D4 is greater than 100 then I want to show $10000 instead. Seems pretty simple here is my logic, It just says formula error??? Please help IF((D4<=50,"2000",""),IF(D450<100,"5000",""),IF(D 4=100,"10000","")) -- Please help out a moderate excel user |
|
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Then Dilemma
It shows $0.00 and does not change when I enter a different number in D4.
Unfortuantely this did not help. I really think it is an IF problem though. So If D4 is a number from 1 through 50 I need B3 to display $2,000. And If D4 is a number from 50 through 100 I need B3 to display $5,000 instead. And If D4 has a number greater than 100 I need B3 to display $10,000. D4 will have a different number entered every time I use my workbook, it is just a cell I put a number into. B3 needs to give me only one of three answers no matter what number D4 contains based on the ranges stated above, B3 needs to show me $2,000, $5,000 or $10,000 only -- Please help out a moderate excel user "Ron Coderre" wrote: Maybe something like this: With D4: any value, including text (but not an error) E4: =CHOOSE(MIN(CEILING((N(D4)+(N(D4)=100))/50,1),3)+1,0,2000,5000,10000) Does that help? *********** Regards, Ron XL2002, WinXP "Excel Confused" wrote: Hi , thanks for the reply. I unfortuantely have not got a solution just yet. Let me rephrase my problem: D4 is a cell I will enter different numbers into every time I use this workbook. If I enter a number from 1 through 50 in D4, I would like the cell I am entering the formual into to display $2,000. If I enter 50 through 100 in D4 I need the cell I am entering the same formula into to display $5,000. If I enter 100 or greater in D4 I need the cell I am entering the same formula into to display $10,000. There are two cells in total, one is D4 the data entry cell that could contain any number and the other is the cell that should display the relevant answer, which is one of three answers as stated above i.e. $2,000, $5,000 or $10,000 based on the number entered in cell D4. HTH Help Me. Thanks for your help on this one. -- -- Please help out a moderate excel user "Ron Coderre" wrote: Try something like this: With A1: (a number) B1: =CHOOSE(MIN(CEILING((A1+(A1=100))/50,1),3)+1,0,2000,5000,10000) Does that help? *********** Regards, Ron XL2002, WinXP "Excel Confused" wrote: I am trying to solve this problem: If a value in cell D4 is from 1 to 50 then I want a certain celll to show $2000 if the value in cell D4 is 50 to 100 then I want that certain cell to show $5000 instead, and if the value in cell D4 is greater than 100 then I want to show $10000 instead. Seems pretty simple here is my logic, It just says formula error??? Please help IF((D4<=50,"2000",""),IF(D450<100,"5000",""),IF(D 4=100,"10000","")) -- Please help out a moderate excel user |
|
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Then Dilemma
Hi
The formula originally posted by Bernd =IF(D4<=50,2000,IF(D4<100,5000,10000)) or the response from Ron of =CHOOSE(MIN(CEILING((N(D4)+(N(D4)=100))/50,1),3)+1,0,2000,5000,10000) each produce the result as per your specification, provided you are entering valid numbers in cell D4 If you are not getting the correct result, you either have not copied the formulae exactly as posted, or your data entered is incorrect. -- Regards Roger Govier "Excel Confused" wrote in message ... It shows $0.00 and does not change when I enter a different number in D4. Unfortuantely this did not help. I really think it is an IF problem though. So If D4 is a number from 1 through 50 I need B3 to display $2,000. And If D4 is a number from 50 through 100 I need B3 to display $5,000 instead. And If D4 has a number greater than 100 I need B3 to display $10,000. D4 will have a different number entered every time I use my workbook, it is just a cell I put a number into. B3 needs to give me only one of three answers no matter what number D4 contains based on the ranges stated above, B3 needs to show me $2,000, $5,000 or $10,000 only -- Please help out a moderate excel user "Ron Coderre" wrote: Maybe something like this: With D4: any value, including text (but not an error) E4: =CHOOSE(MIN(CEILING((N(D4)+(N(D4)=100))/50,1),3)+1,0,2000,5000,10000) Does that help? *********** Regards, Ron XL2002, WinXP "Excel Confused" wrote: Hi , thanks for the reply. I unfortuantely have not got a solution just yet. Let me rephrase my problem: D4 is a cell I will enter different numbers into every time I use this workbook. If I enter a number from 1 through 50 in D4, I would like the cell I am entering the formual into to display $2,000. If I enter 50 through 100 in D4 I need the cell I am entering the same formula into to display $5,000. If I enter 100 or greater in D4 I need the cell I am entering the same formula into to display $10,000. There are two cells in total, one is D4 the data entry cell that could contain any number and the other is the cell that should display the relevant answer, which is one of three answers as stated above i.e. $2,000, $5,000 or $10,000 based on the number entered in cell D4. HTH Help Me. Thanks for your help on this one. -- -- Please help out a moderate excel user "Ron Coderre" wrote: Try something like this: With A1: (a number) B1: =CHOOSE(MIN(CEILING((A1+(A1=100))/50,1),3)+1,0,2000,5000,10000) Does that help? *********** Regards, Ron XL2002, WinXP "Excel Confused" wrote: I am trying to solve this problem: If a value in cell D4 is from 1 to 50 then I want a certain celll to show $2000 if the value in cell D4 is 50 to 100 then I want that certain cell to show $5000 instead, and if the value in cell D4 is greater than 100 then I want to show $10000 instead. Seems pretty simple here is my logic, It just says formula error??? Please help IF((D4<=50,"2000",""),IF(D450<100,"5000",""),IF(D 4=100,"10000","")) -- Please help out a moderate excel user |
|
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Then Dilemma
Thanks Problem Solved. I used Bernd's Formula and I was not referencing the
wrong cell as the cell D4 was on another sheet. Sorry about the confusion. Really appreciate the help -- Please help out a moderate excel user "Ron Coderre" wrote: Maybe something like this: With D4: any value, including text (but not an error) E4: =CHOOSE(MIN(CEILING((N(D4)+(N(D4)=100))/50,1),3)+1,0,2000,5000,10000) Does that help? *********** Regards, Ron XL2002, WinXP "Excel Confused" wrote: Hi , thanks for the reply. I unfortuantely have not got a solution just yet. Let me rephrase my problem: D4 is a cell I will enter different numbers into every time I use this workbook. If I enter a number from 1 through 50 in D4, I would like the cell I am entering the formual into to display $2,000. If I enter 50 through 100 in D4 I need the cell I am entering the same formula into to display $5,000. If I enter 100 or greater in D4 I need the cell I am entering the same formula into to display $10,000. There are two cells in total, one is D4 the data entry cell that could contain any number and the other is the cell that should display the relevant answer, which is one of three answers as stated above i.e. $2,000, $5,000 or $10,000 based on the number entered in cell D4. HTH Help Me. Thanks for your help on this one. -- -- Please help out a moderate excel user "Ron Coderre" wrote: Try something like this: With A1: (a number) B1: =CHOOSE(MIN(CEILING((A1+(A1=100))/50,1),3)+1,0,2000,5000,10000) Does that help? *********** Regards, Ron XL2002, WinXP "Excel Confused" wrote: I am trying to solve this problem: If a value in cell D4 is from 1 to 50 then I want a certain celll to show $2000 if the value in cell D4 is 50 to 100 then I want that certain cell to show $5000 instead, and if the value in cell D4 is greater than 100 then I want to show $10000 instead. Seems pretty simple here is my logic, It just says formula error??? Please help IF((D4<=50,"2000",""),IF(D450<100,"5000",""),IF(D 4=100,"10000","")) -- Please help out a moderate excel user |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Page numbering dilemma | Excel Worksheet Functions | |||
| Multple criteria dilemma | Excel Worksheet Functions | |||
| Rolling up to the correct Month Dilemma | Excel Discussion (Misc queries) | |||
| Hyperlink Dilemma | Excel Discussion (Misc queries) | |||
| Lookup Table Dilemma | Excel Worksheet Functions | |||
Hybrid Mode
