Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If statement not returning the value i need
please see following formula:
=IF(M81100<750,"6000100","4200710")&IF(M81=750," 1201060","4200710")&IF(I81="TONER","6001000","4200 710") I need to return numbers in a cell if the $ is less than 100, return 4200710, greater than 100 but less than 750, return 6000100, and if 750, return 1201060 and then if "I" column has the text "TONER" return 4700710. this the result of my formula, i know i am close and i just missing the "key" elements to get this to work..excel 2007 420071012010604200710 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If statement not returning the value i need
Hi Barbi,
Try: =IF(M81<100,4200710,IF(M81<750,6000100,1201060))&I F(I81="TONER",6001000,4200710) -- Cheers macropod [MVP - Microsoft Word] "barbi b" <barbi wrote in message ... please see following formula: =IF(M81100<750,"6000100","4200710")&IF(M81=750," 1201060","4200710")&IF(I81="TONER","6001000","4200 710") I need to return numbers in a cell if the $ is less than 100, return 4200710, greater than 100 but less than 750, return 6000100, and if 750, return 1201060 and then if "I" column has the text "TONER" return 4700710. this the result of my formula, i know i am close and i just missing the "key" elements to get this to work..excel 2007 420071012010604200710 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If statement not returning the value i need
Okay i am almost there (Thank you by the way, you have no idea how long i
have been working on this bugger!!!) =IF(M9100<750,"6000100",IF(M9=750,"1201060",IF(I 9="TONER","4200710","6000100"))) SO, if Colum M9 & Has "TONER" on I9 is 150. it returns the correct amount, but if Column M9 is $1500.00 then it returns 1201060 here is an example of the data i am working with: TONER 150.00 4200710 HOLLY 150.00 6000100 TONER 15,000.00 1201060 I really can't thank you enough for your quick response. I used to have curley hair before this...now it is straight! "macropod" wrote: Hi Barbi, Try: =IF(M81<100,4200710,IF(M81<750,6000100,1201060))&I F(I81="TONER",6001000,4200710) -- Cheers macropod [MVP - Microsoft Word] "barbi b" <barbi wrote in message ... please see following formula: =IF(M81100<750,"6000100","4200710")&IF(M81=750," 1201060","4200710")&IF(I81="TONER","6001000","4200 710") I need to return numbers in a cell if the $ is less than 100, return 4200710, greater than 100 but less than 750, return 6000100, and if 750, return 1201060 and then if "I" column has the text "TONER" return 4700710. this the result of my formula, i know i am close and i just missing the "key" elements to get this to work..excel 2007 420071012010604200710 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If statement not returning the value i need
Hi,
You may also want to read up on the VLOOKUP() function in Excel's Help menu. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "barbi b" <barbi wrote in message ... please see following formula: =IF(M81100<750,"6000100","4200710")&IF(M81=750," 1201060","4200710")&IF(I81="TONER","6001000","4200 710") I need to return numbers in a cell if the $ is less than 100, return 4200710, greater than 100 but less than 750, return 6000100, and if 750, return 1201060 and then if "I" column has the text "TONER" return 4700710. this the result of my formula, i know i am close and i just missing the "key" elements to get this to work..excel 2007 420071012010604200710 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If statement not returning the value i need
The first thing you need to understand is that:
IF(M9100<750 ... is not a valid construct. If you mean "If M9 is between 100 and 750" then you need to use: =if(and(m9100,m9<750),... However, it's unlikely you need to use AND in your situation. When Excel's If statement evaluates to True, it returns the True part of the formula and quits. When you go at your situation in order, it becomes much simpler. Try something like: =if(m9<100,"what you want when M9 is less than 100",if(m9<750,6000100,1201060)) You don't have to check for M9 being greater than 750, because it won't get there unless it is. Now you need to explain what "TONER" has to do with your situation. In each case (<100, <750, others), what do you want to happen when I9 equals "TONER"? Regards, Fred "barbi b" wrote in message ... Okay i am almost there (Thank you by the way, you have no idea how long i have been working on this bugger!!!) =IF(M9100<750,"6000100",IF(M9=750,"1201060",IF(I 9="TONER","4200710","6000100"))) SO, if Colum M9 & Has "TONER" on I9 is 150. it returns the correct amount, but if Column M9 is $1500.00 then it returns 1201060 here is an example of the data i am working with: TONER 150.00 4200710 HOLLY 150.00 6000100 TONER 15,000.00 1201060 I really can't thank you enough for your quick response. I used to have curley hair before this...now it is straight! "macropod" wrote: Hi Barbi, Try: =IF(M81<100,4200710,IF(M81<750,6000100,1201060))&I F(I81="TONER",6001000,4200710) -- Cheers macropod [MVP - Microsoft Word] "barbi b" <barbi wrote in message ... please see following formula: =IF(M81100<750,"6000100","4200710")&IF(M81=750," 1201060","4200710")&IF(I81="TONER","6001000","4200 710") I need to return numbers in a cell if the $ is less than 100, return 4200710, greater than 100 but less than 750, return 6000100, and if 750, return 1201060 and then if "I" column has the text "TONER" return 4700710. this the result of my formula, i know i am close and i just missing the "key" elements to get this to work..excel 2007 420071012010604200710 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If statement not returning the value i need
Fred,
Thanks so much for your answer-it really helps. If I9 has "toner" in the descp i need it to return a value of 4200710. I really am very grateful for your input. Thanks "Fred Smith" wrote: The first thing you need to understand is that: IF(M9100<750 ... is not a valid construct. If you mean "If M9 is between 100 and 750" then you need to use: =if(and(m9100,m9<750),... However, it's unlikely you need to use AND in your situation. When Excel's If statement evaluates to True, it returns the True part of the formula and quits. When you go at your situation in order, it becomes much simpler. Try something like: =if(m9<100,"what you want when M9 is less than 100",if(m9<750,6000100,1201060)) You don't have to check for M9 being greater than 750, because it won't get there unless it is. Now you need to explain what "TONER" has to do with your situation. In each case (<100, <750, others), what do you want to happen when I9 equals "TONER"? Regards, Fred "barbi b" wrote in message ... Okay i am almost there (Thank you by the way, you have no idea how long i have been working on this bugger!!!) =IF(M9100<750,"6000100",IF(M9=750,"1201060",IF(I 9="TONER","4200710","6000100"))) SO, if Colum M9 & Has "TONER" on I9 is 150. it returns the correct amount, but if Column M9 is $1500.00 then it returns 1201060 here is an example of the data i am working with: TONER 150.00 4200710 HOLLY 150.00 6000100 TONER 15,000.00 1201060 I really can't thank you enough for your quick response. I used to have curley hair before this...now it is straight! "macropod" wrote: Hi Barbi, Try: =IF(M81<100,4200710,IF(M81<750,6000100,1201060))&I F(I81="TONER",6001000,4200710) -- Cheers macropod [MVP - Microsoft Word] "barbi b" <barbi wrote in message ... please see following formula: =IF(M81100<750,"6000100","4200710")&IF(M81=750," 1201060","4200710")&IF(I81="TONER","6001000","4200 710") I need to return numbers in a cell if the $ is less than 100, return 4200710, greater than 100 but less than 750, return 6000100, and if 750, return 1201060 and then if "I" column has the text "TONER" return 4700710. this the result of my formula, i know i am close and i just missing the "key" elements to get this to work..excel 2007 420071012010604200710 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If statement not returning the value i need
Then try:
=if(i9="TONER",4200710,if(m9<100,"Less than 100",if(m9<750,6000100,1201060))) Regards, Fred "barbi b" wrote in message ... Fred, Thanks so much for your answer-it really helps. If I9 has "toner" in the descp i need it to return a value of 4200710. I really am very grateful for your input. Thanks "Fred Smith" wrote: The first thing you need to understand is that: IF(M9100<750 ... is not a valid construct. If you mean "If M9 is between 100 and 750" then you need to use: =if(and(m9100,m9<750),... However, it's unlikely you need to use AND in your situation. When Excel's If statement evaluates to True, it returns the True part of the formula and quits. When you go at your situation in order, it becomes much simpler. Try something like: =if(m9<100,"what you want when M9 is less than 100",if(m9<750,6000100,1201060)) You don't have to check for M9 being greater than 750, because it won't get there unless it is. Now you need to explain what "TONER" has to do with your situation. In each case (<100, <750, others), what do you want to happen when I9 equals "TONER"? Regards, Fred "barbi b" wrote in message ... Okay i am almost there (Thank you by the way, you have no idea how long i have been working on this bugger!!!) =IF(M9100<750,"6000100",IF(M9=750,"1201060",IF(I 9="TONER","4200710","6000100"))) SO, if Colum M9 & Has "TONER" on I9 is 150. it returns the correct amount, but if Column M9 is $1500.00 then it returns 1201060 here is an example of the data i am working with: TONER 150.00 4200710 HOLLY 150.00 6000100 TONER 15,000.00 1201060 I really can't thank you enough for your quick response. I used to have curley hair before this...now it is straight! "macropod" wrote: Hi Barbi, Try: =IF(M81<100,4200710,IF(M81<750,6000100,1201060))&I F(I81="TONER",6001000,4200710) -- Cheers macropod [MVP - Microsoft Word] "barbi b" <barbi wrote in message ... please see following formula: =IF(M81100<750,"6000100","4200710")&IF(M81=750," 1201060","4200710")&IF(I81="TONER","6001000","4200 710") I need to return numbers in a cell if the $ is less than 100, return 4200710, greater than 100 but less than 750, return 6000100, and if 750, return 1201060 and then if "I" column has the text "TONER" return 4700710. this the result of my formula, i know i am close and i just missing the "key" elements to get this to work..excel 2007 420071012010604200710 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF statement not returning a blank so a chart line won't gap | Charts and Charting in Excel | |||
Logical test in IF statement returning incorrect result | Excel Worksheet Functions | |||
Returning a string of values in an IF statement | Excel Discussion (Misc queries) | |||
Returning Specific Cell Content using IF Statement | Excel Discussion (Misc queries) | |||
Returning Specific Cell Content using IF Statement | Excel Discussion (Misc queries) |