ExcelBanter - If statement not returning the value i need

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - If statement not returning the value i need (https://www.excelbanter.com/excel-worksheet-functions/215601-if-statement-not-returning-value-i-need.html)

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

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

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

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

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